

CREATE TABLE IF NOT EXISTS categories (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  slug VARCHAR(140) UNIQUE,
  is_active TINYINT(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS products (
  id INT AUTO_INCREMENT PRIMARY KEY,
  category_id INT NULL,
  name VARCHAR(200) NOT NULL,
  slug VARCHAR(220) UNIQUE,
  description TEXT,
  price DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  image_url VARCHAR(500),
  sku VARCHAR(120),
  stock_qty INT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS orders (
  id INT AUTO_INCREMENT PRIMARY KEY,
  customer_name VARCHAR(160) NOT NULL,
  customer_phone VARCHAR(60) NOT NULL,
  customer_email VARCHAR(160),
  pickup_time VARCHAR(60) NOT NULL,
  payment_method VARCHAR(40) NOT NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'new',
  total DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS order_items (
  id INT AUTO_INCREMENT PRIMARY KEY,
  order_id INT NOT NULL,
  product_id INT NOT 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 RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- seed categories
INSERT INTO categories (name, slug, is_active) VALUES
('Drinks', 'drinks', 1),
('Snacks', 'snacks', 1),
('Essentials', 'essentials', 1)
ON DUPLICATE KEY UPDATE name=VALUES(name);

-- seed products
INSERT INTO products (category_id, name, slug, description, price, image_url, sku, stock_qty, is_active) VALUES
(1, 'Bottled Water (16oz)', 'bottled-water-16oz', 'Cold spring water.', 1.50, '', 'WATER16', 100, 1),
(1, 'Iced Coffee', 'iced-coffee', 'Sweet and cold.', 2.99, '', 'COFFEEICED', 25, 1),
(2, 'Potato Chips', 'potato-chips', 'Classic salted chips.', 1.99, '', 'CHIPS01', 50, 1),
(3, 'Phone Charger', 'phone-charger', 'USB-C charger.', 12.99, '', 'CHRGRUSBC', 10, 1);
