-- ============================================
-- SELLING WEBSITE DATABASE — run in phpMyAdmin
-- ============================================
CREATE DATABASE IF NOT EXISTS selling_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE selling_db;

CREATE TABLE IF NOT EXISTS items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    price DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    stock INT NOT NULL DEFAULT 0,
    category VARCHAR(100),
    status ENUM('active','inactive') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS sales (
    id INT AUTO_INCREMENT PRIMARY KEY,
    item_id INT NOT NULL,
    customer_name VARCHAR(255) NOT NULL,
    customer_email VARCHAR(255),
    customer_phone VARCHAR(50),
    quantity INT NOT NULL DEFAULT 1,
    unit_price DECIMAL(10,2) NOT NULL,
    total_price DECIMAL(10,2) NOT NULL,
    payment_method ENUM('cash','card','online','other') DEFAULT 'cash',
    status ENUM('pending','completed','refunded','cancelled') DEFAULT 'completed',
    notes TEXT,
    sale_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (item_id) REFERENCES items(id) ON DELETE RESTRICT
);

CREATE TABLE IF NOT EXISTS admins (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(100) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    email VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO items (name, description, price, stock, category) VALUES
('Wireless Headphones', 'Premium noise-cancelling headphones', 89.99, 25, 'Electronics'),
('Running Shoes', 'Lightweight sport shoes', 59.99, 40, 'Footwear'),
('Coffee Maker', 'Automatic drip coffee machine', 45.00, 15, 'Appliances'),
('Backpack', '30L waterproof hiking backpack', 35.00, 30, 'Bags'),
('Smart Watch', 'Fitness tracker with heart rate monitor', 129.99, 10, 'Electronics');

INSERT INTO sales (item_id, customer_name, customer_email, quantity, unit_price, total_price, payment_method) VALUES
(1, 'Ahmed Khan', 'ahmed@example.com', 1, 89.99, 89.99, 'card'),
(2, 'Sara Ali', 'sara@example.com', 2, 59.99, 119.98, 'cash'),
(3, 'Usman Malik', 'usman@example.com', 1, 45.00, 45.00, 'online'),
(5, 'Fatima Zahra', 'fatima@example.com', 1, 129.99, 129.99, 'card'),
(4, 'Hassan Raza', 'hassan@example.com', 3, 35.00, 105.00, 'cash');

-- Default: admin / admin123
INSERT INTO admins (username, password, email) VALUES
('admin', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'admin@store.com');