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

CREATE TABLE IF NOT EXISTS commerce_orders (
  id VARCHAR(255) PRIMARY KEY,
  number VARCHAR(255),
  customer_id VARCHAR(255),
  customer_name VARCHAR(255),
  email VARCHAR(255),
  phone VARCHAR(255),
  city VARCHAR(255),
  address TEXT,
  placed_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  status VARCHAR(255),
  payment_status VARCHAR(255),
  subtotal INT,
  discount INT,
  shipping INT,
  total INT,
  coupon VARCHAR(255),
  tracking VARCHAR(255),
  version INT DEFAULT 1,
  FOREIGN KEY (customer_id) REFERENCES commerce_customers(id)
);

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

CREATE TABLE IF NOT EXISTS commerce_events (
  id VARCHAR(255) PRIMARY KEY,
  order_id VARCHAR(255),
  status VARCHAR(255),
  at DATETIME DEFAULT CURRENT_TIMESTAMP,
  note TEXT,
  FOREIGN KEY (order_id) REFERENCES commerce_orders(id) ON DELETE CASCADE
);