CREATE DATABASE IF NOT EXISTS veera_shop CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE veera_shop;

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

CREATE TABLE IF NOT EXISTS categories(
 id INT AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(100) NOT NULL UNIQUE
);

CREATE TABLE IF NOT EXISTS products(
 id INT AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(180) NOT NULL,
 category_id INT NULL,
 price DECIMAL(10,2) NOT NULL,
 compare_price DECIMAL(10,2) DEFAULT NULL,
 description TEXT,
 image VARCHAR(255) DEFAULT '',
 sizes VARCHAR(255) DEFAULT '6,7,8,9,10',
 colors VARCHAR(255) DEFAULT 'Black',
 stock INT DEFAULT 0,
 featured TINYINT(1) DEFAULT 0,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(category_id) REFERENCES categories(id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS customers(
 id INT AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(150) NOT NULL,
 email VARCHAR(180),
 phone VARCHAR(30) NOT NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS orders(
 id INT AUTO_INCREMENT PRIMARY KEY,
 customer_id INT NULL,
 order_no VARCHAR(40) UNIQUE NOT NULL,
 total DECIMAL(10,2) NOT NULL,
 payment_method VARCHAR(40) NOT NULL DEFAULT 'COD',
 status VARCHAR(40) NOT NULL DEFAULT 'PLACED',
 address TEXT NOT NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(customer_id) REFERENCES customers(id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS order_items(
 id INT AUTO_INCREMENT PRIMARY KEY,
 order_id INT NOT NULL,
 product_id INT NOT NULL,
 product_name VARCHAR(180) NOT NULL,
 size VARCHAR(20),
 qty INT NOT NULL,
 price DECIMAL(10,2) NOT NULL,
 FOREIGN KEY(order_id) REFERENCES orders(id) ON DELETE CASCADE
);

INSERT IGNORE INTO categories(name) VALUES ('Sneakers'),('Formal'),('Casual'),('Sandals'),('Boots');

-- bcrypt hash for ChangeMe123!
INSERT INTO admins(username,password_hash) VALUES
('admin','$2y$10$u4b0H4n7g7h0xZQwM7e8Oe6J7wzK9f0m1b8g4tW7eJY6mQmQn9V7G')
ON DUPLICATE KEY UPDATE username=username;