-- Minimal no-JS PHP shop schema
CREATE TABLE IF NOT EXISTS products (
  id INT AUTO_INCREMENT PRIMARY KEY,
  slug VARCHAR(160) UNIQUE NOT NULL,
  name VARCHAR(200) NOT NULL,
  description TEXT,
  price_minor INT NOT NULL DEFAULT 0,
  image VARCHAR(255),
  is_active TINYINT(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS orders (
  id INT AUTO_INCREMENT PRIMARY KEY,
  order_id VARCHAR(40) UNIQUE NOT NULL,
  name VARCHAR(160) NOT NULL,
  email VARCHAR(200) NOT NULL,
  address TEXT NOT NULL,
  total_minor INT NOT NULL,
  currency CHAR(3) NOT NULL DEFAULT 'GBP',
  status VARCHAR(40) NOT NULL DEFAULT 'pending',
  created_at DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS order_items (
  id INT AUTO_INCREMENT PRIMARY KEY,
  order_id VARCHAR(40) NOT NULL,
  product_id INT NOT NULL,
  name VARCHAR(200) NOT NULL,
  qty INT NOT NULL,
  price_minor INT NOT NULL,
  KEY (order_id),
  CONSTRAINT fk_oi_order FOREIGN KEY (order_id) REFERENCES orders(order_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Demo data
INSERT INTO products (slug, name, description, price_minor, image) VALUES
('demo-tee', 'Demo Tee', 'A comfortable cotton tee.', 1499, 'https://via.placeholder.com/480x360'),
('mug', 'Coffee Mug', '12oz ceramic mug.', 999, 'https://via.placeholder.com/480x360');
