-- Admin users
CREATE TABLE IF NOT EXISTS admin_users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(255) UNIQUE NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Brands
CREATE TABLE IF NOT EXISTS brands (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) UNIQUE NOT NULL
);

INSERT IGNORE INTO brands (name) VALUES
('Titan'), ('Sonata'), ('POLICE'), ('KENNETH COLE NEW YORK'),
('Casio'), ('Daniel Klein'), ('Guess'), ('Michael Kors'), ('Skagen'),
('Fossil'), ('Emporio Armani'), ('Diesel'), ('Santa Barbara Polo & Racquet Club');

-- Categories (Gender)
CREATE TABLE IF NOT EXISTS categories (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50) UNIQUE NOT NULL -- 'Men', 'Women'
);

INSERT IGNORE INTO categories (name) VALUES ('Men'), ('Women');

-- Products
CREATE TABLE IF NOT EXISTS products (
  id INT AUTO_INCREMENT PRIMARY KEY,
  sku VARCHAR(100) UNIQUE NOT NULL,
  title VARCHAR(255) NOT NULL,
  description TEXT,
  price DECIMAL(10,2) NOT NULL,
  brand_id INT,
  category_id INT,
  image_main VARCHAR(255),
  stock INT NOT NULL DEFAULT 0,
  -- Additional information (optional)
  gender ENUM('Men','Women','Unisex') DEFAULT NULL,
  family VARCHAR(255),
  mechanism_type VARCHAR(255),
  movement VARCHAR(255),
  case_material VARCHAR(255),
  case_plating_color VARCHAR(255),
  case_size VARCHAR(50),
  band_material VARCHAR(255),
  band_color VARCHAR(255),
  band_size VARCHAR(50),
  dial_color VARCHAR(255),
  water_resistance VARCHAR(255),
  warranty VARCHAR(255),
  -- Flags
  is_featured TINYINT(1) DEFAULT 0,
  is_best_seller TINYINT(1) DEFAULT 0,
  is_new_arrival TINYINT(1) DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (brand_id) REFERENCES brands(id) ON DELETE SET NULL,
  FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL
);

-- Seasonal collections ("for_her", "for_him")
CREATE TABLE IF NOT EXISTS seasonal_collections (
  id INT AUTO_INCREMENT PRIMARY KEY,
  type ENUM('for_her', 'for_him') NOT NULL,
  product_id INT NOT NULL,
  UNIQUE KEY unique_type (type),
  FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
);

-- Orders
CREATE TABLE IF NOT EXISTS orders (
  id INT AUTO_INCREMENT PRIMARY KEY,
  order_number VARCHAR(50) UNIQUE NOT NULL,
  payment_method ENUM('COD','ONLINE') NOT NULL,
  status ENUM('PENDING','CONFIRMED','PAID','CANCELLED') DEFAULT 'PENDING',
  total DECIMAL(10,2) NOT NULL,
  -- Billing details
  billing_first_name VARCHAR(100) NOT NULL,
  billing_last_name VARCHAR(100) NOT NULL,
  billing_company VARCHAR(255),
  billing_country VARCHAR(100) NOT NULL,
  billing_address1 VARCHAR(255) NOT NULL,
  billing_address2 VARCHAR(255),
  billing_city VARCHAR(100) NOT NULL,
  billing_district VARCHAR(100) NOT NULL,
  billing_zip VARCHAR(20),
  billing_phone VARCHAR(50) NOT NULL,
  billing_email VARCHAR(255) NOT NULL,
  ship_different TINYINT(1) DEFAULT 0,
  shipping_address1 VARCHAR(255),
  shipping_address2 VARCHAR(255),
  shipping_city VARCHAR(100),
  shipping_district VARCHAR(100),
  shipping_zip VARCHAR(20),
  order_notes TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS order_items (
  id INT AUTO_INCREMENT PRIMARY KEY,
  order_id INT NOT NULL,
  product_id INT NULL,
  qty INT NOT NULL,
  price DECIMAL(10,2) NOT NULL,
  FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
  FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE SET NULL
);
-- Users (site customers)
CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255),
  email VARCHAR(255) UNIQUE NOT NULL,
  password_hash VARCHAR(255),
  google_id VARCHAR(255),
  avatar_url VARCHAR(255),
  reset_token VARCHAR(255),
  reset_expires_at DATETIME,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);