CREATE DATABASE IF NOT EXISTS grrito;
USE grrito;

CREATE TABLE IF NOT EXISTS commerce_products (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  slug VARCHAR(255) NOT NULL UNIQUE,
  category VARCHAR(255) NOT NULL,
  description TEXT NOT NULL,
  price INT NOT NULL,
  image VARCHAR(1024) NOT NULL,
  badge VARCHAR(255) DEFAULT '',
  active INT DEFAULT 1
);

CREATE TABLE IF NOT EXISTS commerce_variants (
  id VARCHAR(255) PRIMARY KEY,
  product_id INT NOT NULL,
  size VARCHAR(255) NOT NULL,
  color VARCHAR(255) NOT NULL,
  hex VARCHAR(255) NOT NULL,
  quantity INT DEFAULT 30,
  version INT DEFAULT 1,
  active INT DEFAULT 1,
  FOREIGN KEY (product_id) REFERENCES commerce_products(id) ON DELETE CASCADE
);

-- Insert dummy data
INSERT IGNORE INTO commerce_products (id, name, slug, category, description, price, image, badge, active) VALUES
(1, 'Urban Grid Oversized Tee', 'urban-grid-tee', 'Oversized T-Shirts', 'Heavyweight cotton oversized tee with drop shoulders.', 1999, '/products/urban-grid/01-product.png', 'NEW', 1),
(2, 'Monolith Black Shirt', 'monolith-black', 'Oversized Shirts', 'Crisp structured overshirt in obsidian black.', 2499, '/products/urban-grid-olive/01-front.png', '', 1),
(3, 'Bone White Classic Tee', 'bone-white-classic', 'Oversized T-Shirts', 'Essential oversized tee in bone white.', 1899, '/products/urban-grid/04-back.png', 'BESTSELLER', 1),
(4, 'Olive Utility Shirt', 'olive-utility', 'Oversized Shirts', 'Durable cotton twill utility shirt.', 2899, '/products/urban-grid-olive/02-look.png', '', 1);

INSERT IGNORE INTO commerce_variants (id, product_id, size, color, hex, quantity) VALUES
('V1-S', 1, 'S', 'Black', '#000000', 50),
('V1-M', 1, 'M', 'Black', '#000000', 50),
('V2-M', 2, 'M', 'Black', '#000000', 30),
('V3-L', 3, 'L', 'White', '#FFFFFF', 40),
('V4-XL', 4, 'XL', 'Olive', '#556b2f', 20);

CREATE TABLE IF NOT EXISTS commerce_admins (id INT AUTO_INCREMENT PRIMARY KEY, email VARCHAR(255) UNIQUE NOT NULL, name VARCHAR(255), mobile VARCHAR(20), employee_id VARCHAR(100), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE IF NOT EXISTS commerce_otps (id INT AUTO_INCREMENT PRIMARY KEY, email VARCHAR(255) NOT NULL, otp VARCHAR(10) NOT NULL, expires_at TIMESTAMP NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);

CREATE TABLE IF NOT EXISTS commerce_customers (
  id VARCHAR(255) PRIMARY KEY,
  email VARCHAR(255) NOT NULL UNIQUE,
  name VARCHAR(255) NOT NULL,
  phone VARCHAR(255) NOT NULL,
  city VARCHAR(255) NOT NULL,
  created_at VARCHAR(255) NOT NULL
);

CREATE TABLE IF NOT EXISTS commerce_orders (
  id VARCHAR(255) PRIMARY KEY,
  number VARCHAR(255) NOT NULL UNIQUE,
  idempotency_key VARCHAR(255) NOT NULL UNIQUE,
  customer_id VARCHAR(255) NOT NULL,
  customer_name VARCHAR(255) NOT NULL,
  email VARCHAR(255) NOT NULL,
  phone VARCHAR(255) NOT NULL,
  city VARCHAR(255) NOT NULL,
  address VARCHAR(255) NOT NULL,
  placed_at VARCHAR(255) NOT NULL,
  status VARCHAR(255) DEFAULT 'Pending' NOT NULL,
  payment_status VARCHAR(255) DEFAULT 'unpaid' NOT NULL,
  subtotal INT NOT NULL,
  discount INT DEFAULT 0 NOT NULL,
  shipping INT DEFAULT 0 NOT NULL,
  total INT NOT NULL,
  coupon VARCHAR(255) DEFAULT '' NOT NULL,
  tracking VARCHAR(255) DEFAULT '' NOT NULL,
  version INT DEFAULT 1 NOT NULL,
  FOREIGN KEY (customer_id) REFERENCES commerce_customers(id)
);

CREATE TABLE IF NOT EXISTS commerce_events (
  id VARCHAR(255) PRIMARY KEY,
  order_id VARCHAR(255) NOT NULL,
  status VARCHAR(255) NOT NULL,
  at VARCHAR(255) NOT NULL,
  note VARCHAR(255) NOT NULL,
  actor VARCHAR(255) NOT NULL,
  FOREIGN KEY (order_id) REFERENCES commerce_orders(id)
);

CREATE TABLE IF NOT EXISTS commerce_items (
  id VARCHAR(255) PRIMARY KEY,
  order_id VARCHAR(255) NOT NULL,
  variant_id VARCHAR(255) NOT NULL,
  product_id INT NOT NULL,
  name VARCHAR(255) NOT NULL,
  size VARCHAR(255) NOT NULL,
  color VARCHAR(255) NOT NULL,
  quantity INT NOT NULL,
  unit_price INT NOT NULL,
  FOREIGN KEY (order_id) REFERENCES commerce_orders(id),
  FOREIGN KEY (variant_id) REFERENCES commerce_variants(id),
  FOREIGN KEY (product_id) REFERENCES commerce_products(id)
);

CREATE TABLE IF NOT EXISTS commerce_payments (
  id VARCHAR(255) PRIMARY KEY,
  order_id VARCHAR(255) NOT NULL,
  provider VARCHAR(255) NOT NULL,
  provider_order_id VARCHAR(255) NOT NULL UNIQUE,
  provider_payment_id VARCHAR(255) NOT NULL UNIQUE,
  amount INT NOT NULL,
  status VARCHAR(255) NOT NULL,
  verified_at VARCHAR(255) NOT NULL,
  FOREIGN KEY (order_id) REFERENCES commerce_orders(id)
);

CREATE TABLE IF NOT EXISTS commerce_payment_attempts (
  gateway_order_id VARCHAR(255) PRIMARY KEY,
  checkout_key VARCHAR(255) NOT NULL UNIQUE,
  amount INT NOT NULL,
  checkout_json TEXT NOT NULL,
  status VARCHAR(255) DEFAULT 'created' NOT NULL,
  created_at VARCHAR(255) NOT NULL
);