-- Baseline demo tables preserved for the /demo route.
-- These are kept read-only by the production platform.

CREATE TABLE IF NOT EXISTS merchants (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(150) NOT NULL,
  store_code VARCHAR(40) NOT NULL UNIQUE,
  contact_name VARCHAR(120) NOT NULL,
  business_number VARCHAR(40) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS shoppers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  full_name VARCHAR(150) NOT NULL,
  mobile_number VARCHAR(40) NOT NULL UNIQUE,
  auth_pin VARCHAR(12) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS payment_methods (
  id INT AUTO_INCREMENT PRIMARY KEY,
  shopper_id INT NOT NULL,
  label VARCHAR(120) NOT NULL,
  network VARCHAR(60) NOT NULL,
  masked_account VARCHAR(40) NOT NULL,
  token_ref VARCHAR(80) NOT NULL,
  billing_type VARCHAR(40) NOT NULL,
  is_default BOOLEAN NOT NULL DEFAULT FALSE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_pm_shopper FOREIGN KEY (shopper_id) REFERENCES shoppers(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS checkout_sessions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  session_code VARCHAR(40) NOT NULL UNIQUE,
  merchant_id INT NOT NULL,
  shopper_id INT NOT NULL,
  relay_device_code VARCHAR(40) NOT NULL,
  subtotal DECIMAL(10, 2) NOT NULL,
  tax DECIMAL(10, 2) NOT NULL,
  total DECIMAL(10, 2) NOT NULL,
  status ENUM('draft','pending_approval','settled','declined') NOT NULL DEFAULT 'draft',
  payment_method_id INT NULL,
  approved_at DATETIME NULL,
  settled_at DATETIME NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_cs_merchant FOREIGN KEY (merchant_id) REFERENCES merchants(id),
  CONSTRAINT fk_cs_shopper  FOREIGN KEY (shopper_id)  REFERENCES shoppers(id),
  CONSTRAINT fk_cs_pm       FOREIGN KEY (payment_method_id) REFERENCES payment_methods(id)
);

CREATE TABLE IF NOT EXISTS checkout_line_items (
  id INT AUTO_INCREMENT PRIMARY KEY,
  checkout_id INT NOT NULL,
  item_name VARCHAR(180) NOT NULL,
  quantity DECIMAL(10, 2) NOT NULL,
  unit_price DECIMAL(10, 2) NOT NULL,
  line_total DECIMAL(10, 2) NOT NULL,
  CONSTRAINT fk_cli_checkout FOREIGN KEY (checkout_id) REFERENCES checkout_sessions(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS session_events (
  id INT AUTO_INCREMENT PRIMARY KEY,
  checkout_id INT NOT NULL,
  actor_type VARCHAR(60) NOT NULL,
  actor_label VARCHAR(120) NOT NULL,
  event_type VARCHAR(120) NOT NULL,
  event_payload JSON NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_se_checkout FOREIGN KEY (checkout_id) REFERENCES checkout_sessions(id) ON DELETE CASCADE
)
