SET NAMES utf8mb4;
SET time_zone = '+02:00';

CREATE TABLE IF NOT EXISTS schema_migrations (
  version VARCHAR(80) PRIMARY KEY,
  applied_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT IGNORE INTO schema_migrations (version) VALUES ('20260721_stability_security_v1');

CREATE TABLE IF NOT EXISTS users (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  email VARCHAR(190) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  role ENUM('owner','admin','staff') NOT NULL DEFAULT 'staff',
  active TINYINT(1) NOT NULL DEFAULT 1,
  session_version INT UNSIGNED NOT NULL DEFAULT 1,
  last_login_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS settings (
  id TINYINT UNSIGNED PRIMARY KEY DEFAULT 1,
  company_name VARCHAR(160) NOT NULL DEFAULT 'Soda ZW',
  trading_name VARCHAR(160) NOT NULL DEFAULT 'Soda ZW',
  email VARCHAR(190) NULL,
  phone VARCHAR(50) NOT NULL DEFAULT '+263 77 173 9696',
  address VARCHAR(255) NOT NULL DEFAULT '100 Livingstone Avenue',
  city VARCHAR(100) NULL,
  country VARCHAR(100) NOT NULL DEFAULT 'Zimbabwe',
  tax_number VARCHAR(100) NULL,
  currency_code CHAR(3) NOT NULL DEFAULT 'USD',
  invoice_prefix VARCHAR(20) NOT NULL DEFAULT 'SZW-INV-',
  quote_prefix VARCHAR(20) NOT NULL DEFAULT 'SZW-QUO-',
  next_invoice_number INT UNSIGNED NOT NULL DEFAULT 1,
  next_quote_number INT UNSIGNED NOT NULL DEFAULT 1,
  default_tax_rate DECIMAL(5,2) NOT NULL DEFAULT 0.00,
  default_due_days SMALLINT UNSIGNED NOT NULL DEFAULT 7,
  bank_details TEXT NULL,
  payment_instructions TEXT NULL,
  invoice_terms TEXT NULL,
  logo_path VARCHAR(255) NOT NULL DEFAULT 'assets/sodazw-logo.png',
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT IGNORE INTO settings (id) VALUES (1);

CREATE TABLE IF NOT EXISTS customers (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(160) NOT NULL,
  company_name VARCHAR(160) NULL,
  email VARCHAR(190) NULL,
  phone VARCHAR(50) NULL,
  whatsapp VARCHAR(50) NULL,
  tax_number VARCHAR(100) NULL,
  address VARCHAR(255) NULL,
  city VARCHAR(100) NULL,
  customer_type VARCHAR(40) NOT NULL DEFAULT 'Household',
  area VARCHAR(100) NULL,
  preferred_payment VARCHAR(80) NULL,
  marketing_consent TINYINT(1) NOT NULL DEFAULT 0,
  last_contact_at DATETIME NULL,
  next_refill_due DATE NULL,
  notes TEXT NULL,
  active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_customers_name (name),
  INDEX idx_customers_phone (phone)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS products (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  sku VARCHAR(80) NULL UNIQUE,
  name VARCHAR(180) NOT NULL,
  description TEXT NULL,
  category VARCHAR(80) NOT NULL DEFAULT 'Other',
  unit_price DECIMAL(13,2) NOT NULL DEFAULT 0.00,
  cost_price DECIMAL(13,2) NOT NULL DEFAULT 0.00,
  tax_rate DECIMAL(5,2) NOT NULL DEFAULT 0.00,
  stock_quantity DECIMAL(13,2) NOT NULL DEFAULT 0.00,
  track_stock TINYINT(1) NOT NULL DEFAULT 0,
  reorder_level DECIMAL(13,2) NOT NULL DEFAULT 0.00,
  active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_products_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT IGNORE INTO products (sku,name,description,unit_price,tax_rate) VALUES
('SZW-QC-WHT','White Quick Connect Starter Kit','White machine, pink 60L cylinder and bottle',150.00,0.00),
('SZW-QC-BLK','Black Quick Connect Starter Kit','Black machine, pink 60L cylinder and bottle',150.00,0.00),
('SZW-QC-CYL','Pink Quick Connect 60L Cylinder','Pink-system CO2 cylinder',55.00,0.00),
('SZW-FG-MINT','Fizz&Go Mint Steel Bottle','Stainless-steel bottle for bubbles on the go',45.00,0.00),
('SZW-1L-WHT','White-Base 1L Bottle','Reusable carbonating bottle',20.00,0.00),
('SZW-1L-BLK','Black-Base 1L Bottle','Reusable carbonating bottle',20.00,0.00),
('SZW-FG-BUNDLE','White Fizz&Go Bundle','Quick Connect machine, cylinder and steel bottle',190.00,0.00),
('SZW-CL-AQUA','Aqua Classic Starter Kit','Aqua machine, blue cylinder and bottle',150.00,0.00),
('SZW-CL-WHT','White Classic Starter Kit','White machine, blue cylinder and bottle',150.00,0.00),
('SZW-CL-BLK','Black Classic Starter Kit','Black machine, blue cylinder and bottle',150.00,0.00),
('SZW-CL-CYL','Classic Blue 60L Cylinder','Screw-in CO2 cylinder',60.00,0.00),
('SZW-CO2-REFILL','CO2 Gas Cylinder Refill','Refill service for a compatible customer-supplied empty cylinder',35.00,0.00),
('SZW-SYR-PG','Pink Grapefruit Syrup','Sugar-free pink grapefruit flavour',12.00,0.00),
('SZW-SYR-CR-ZERO','Cranberry Raspberry Zero Sugar Syrup','Zero-sugar cranberry and raspberry flavour; makes approximately 9 litres',12.00,0.00),
('SZW-SYR-TONIC','Tonic Syrup','Classic tonic-flavoured syrup',12.00,0.00),
('SZW-SYR-LEM','Lemonade Syrup','Classic lemonade flavour',12.00,0.00),
('SZW-SYR-CREAM','Cream Soda Syrup','Classic cream soda flavour',12.00,0.00);

CREATE TABLE IF NOT EXISTS documents (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  type ENUM('quotation','invoice','credit_note') NOT NULL,
  number VARCHAR(60) NOT NULL UNIQUE,
  customer_id BIGINT UNSIGNED NOT NULL,
  source_document_id BIGINT UNSIGNED NULL,
  issue_date DATE NOT NULL,
  due_date DATE NULL,
  status ENUM('draft','sent','accepted','rejected','partial','paid','overdue','cancelled') NOT NULL DEFAULT 'draft',
  currency_code CHAR(3) NOT NULL DEFAULT 'USD',
  subtotal DECIMAL(13,2) NOT NULL DEFAULT 0.00,
  discount_type ENUM('fixed','percent') NOT NULL DEFAULT 'fixed',
  discount_value DECIMAL(13,2) NOT NULL DEFAULT 0.00,
  discount_total DECIMAL(13,2) NOT NULL DEFAULT 0.00,
  tax_total DECIMAL(13,2) NOT NULL DEFAULT 0.00,
  delivery_fee DECIMAL(13,2) NOT NULL DEFAULT 0.00,
  total DECIMAL(13,2) NOT NULL DEFAULT 0.00,
  notes TEXT NULL,
  terms TEXT NULL,
  customer_reference VARCHAR(120) NULL,
  order_source VARCHAR(40) NOT NULL DEFAULT 'WhatsApp',
  fulfilment_method VARCHAR(30) NOT NULL DEFAULT 'Pickup',
  delivery_address VARCHAR(255) NULL,
  stock_processed_at DATETIME NULL,
  share_token CHAR(64) NULL,
  share_enabled TINYINT(1) NOT NULL DEFAULT 1,
  share_view_count INT UNSIGNED NOT NULL DEFAULT 0,
  share_first_viewed_at DATETIME NULL,
  share_last_viewed_at DATETIME NULL,
  created_by BIGINT UNSIGNED NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_documents_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT fk_documents_source FOREIGN KEY (source_document_id) REFERENCES documents(id) ON UPDATE CASCADE ON DELETE SET NULL,
  CONSTRAINT fk_documents_user FOREIGN KEY (created_by) REFERENCES users(id) ON UPDATE CASCADE ON DELETE RESTRICT,
  INDEX idx_documents_type_status (type, status),
  INDEX idx_documents_issue_date (issue_date),
  INDEX idx_documents_customer (customer_id),
  UNIQUE KEY uq_document_share_token (share_token)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS document_items (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  document_id BIGINT UNSIGNED NOT NULL,
  product_id BIGINT UNSIGNED NULL,
  description VARCHAR(255) NOT NULL,
  quantity DECIMAL(13,2) NOT NULL DEFAULT 1.00,
  unit_price DECIMAL(13,2) NOT NULL DEFAULT 0.00,
  unit_cost DECIMAL(13,2) NOT NULL DEFAULT 0.00,
  tax_rate DECIMAL(5,2) NOT NULL DEFAULT 0.00,
  line_subtotal DECIMAL(13,2) NOT NULL DEFAULT 0.00,
  line_tax DECIMAL(13,2) NOT NULL DEFAULT 0.00,
  line_total DECIMAL(13,2) NOT NULL DEFAULT 0.00,
  sort_order SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  CONSTRAINT fk_items_document FOREIGN KEY (document_id) REFERENCES documents(id) ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT fk_items_product FOREIGN KEY (product_id) REFERENCES products(id) ON UPDATE CASCADE ON DELETE SET NULL,
  INDEX idx_items_document (document_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS inventory_movements (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  product_id BIGINT UNSIGNED NOT NULL,
  movement_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  movement_type ENUM('opening','purchase','sale','return','adjustment') NOT NULL,
  quantity_change DECIMAL(13,2) NOT NULL,
  reference_type VARCHAR(40) NULL,
  reference_id BIGINT UNSIGNED NULL,
  notes VARCHAR(255) NULL,
  created_by BIGINT UNSIGNED NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_inventory_product_date (product_id,movement_date),
  CONSTRAINT fk_inventory_product FOREIGN KEY (product_id) REFERENCES products(id),
  CONSTRAINT fk_inventory_user FOREIGN KEY (created_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS payment_accounts (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  type ENUM('cash','mobile_money','bank','card','online','other') NOT NULL DEFAULT 'other',
  currency_code CHAR(3) NOT NULL DEFAULT 'USD',
  details VARCHAR(255) NULL,
  active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_payment_account (name,currency_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT IGNORE INTO payment_accounts (name,type,currency_code,details) VALUES
('Cash USD','cash','USD','Physical cash'),
('EcoCash USD','mobile_money','USD','EcoCash merchant or wallet'),
('Bank Transfer USD','bank','USD','Nostro / FCA transfer'),
('POS / Swipe USD','card','USD','Zimswitch or card terminal'),
('Paynow USD','online','USD','Online settlement'),
('OneMoney USD','mobile_money','USD','OneMoney wallet'),
('InnBucks USD','mobile_money','USD','InnBucks wallet'),
('O’mari USD','mobile_money','USD','O’mari wallet'),
('Cash ZiG','cash','ZWG','Physical ZiG cash'),
('EcoCash ZiG','mobile_money','ZWG','EcoCash ZiG wallet'),
('Bank Transfer ZiG','bank','ZWG','Local ZiG transfer'),
('POS / Swipe ZiG','card','ZWG','Local card terminal');

CREATE TABLE IF NOT EXISTS payments (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  invoice_id BIGINT UNSIGNED NOT NULL,
  account_id BIGINT UNSIGNED NULL,
  payment_date DATE NOT NULL,
  amount DECIMAL(13,2) NOT NULL,
  currency_code CHAR(3) NOT NULL DEFAULT 'USD',
  received_amount DECIMAL(15,2) NULL,
  received_currency CHAR(3) NULL,
  exchange_rate DECIMAL(18,6) NOT NULL DEFAULT 1.000000,
  fee_amount DECIMAL(13,2) NOT NULL DEFAULT 0.00,
  method VARCHAR(80) NOT NULL,
  reference VARCHAR(150) NULL,
  notes TEXT NULL,
  created_by BIGINT UNSIGNED NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_payments_invoice FOREIGN KEY (invoice_id) REFERENCES documents(id) ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT fk_payments_account FOREIGN KEY (account_id) REFERENCES payment_accounts(id) ON UPDATE CASCADE ON DELETE SET NULL,
  CONSTRAINT fk_payments_user FOREIGN KEY (created_by) REFERENCES users(id) ON UPDATE CASCADE ON DELETE RESTRICT,
  INDEX idx_payments_invoice (invoice_id),
  INDEX idx_payments_date (payment_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS expenses (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  expense_date DATE NOT NULL,
  category VARCHAR(100) NOT NULL,
  supplier VARCHAR(160) NULL,
  description VARCHAR(255) NOT NULL,
  amount DECIMAL(13,2) NOT NULL,
  currency_code CHAR(3) NOT NULL DEFAULT 'USD',
  reference VARCHAR(150) NULL,
  created_by BIGINT UNSIGNED NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_expenses_user FOREIGN KEY (created_by) REFERENCES users(id) ON UPDATE CASCADE ON DELETE RESTRICT,
  INDEX idx_expenses_date (expense_date),
  INDEX idx_expenses_category (category)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS suppliers (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(180) NOT NULL,
  contact_name VARCHAR(160) NULL,
  email VARCHAR(190) NULL,
  phone VARCHAR(60) NULL,
  tax_number VARCHAR(80) NULL,
  address VARCHAR(255) NULL,
  notes TEXT NULL,
  active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_supplier_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS bills (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  supplier_id BIGINT UNSIGNED NOT NULL,
  number VARCHAR(100) NOT NULL,
  bill_date DATE NOT NULL,
  due_date DATE NOT NULL,
  description VARCHAR(255) NOT NULL,
  currency_code CHAR(3) NOT NULL DEFAULT 'USD',
  total DECIMAL(15,2) NOT NULL DEFAULT 0,
  status ENUM('unpaid','partial','paid','cancelled') NOT NULL DEFAULT 'unpaid',
  reference VARCHAR(120) NULL,
  notes TEXT NULL,
  created_by BIGINT UNSIGNED NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_bill_supplier_number (supplier_id,number),
  KEY idx_bill_due (due_date,status),
  CONSTRAINT fk_bill_supplier FOREIGN KEY (supplier_id) REFERENCES suppliers(id),
  CONSTRAINT fk_bill_user FOREIGN KEY (created_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS bill_payments (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  bill_id BIGINT UNSIGNED NOT NULL,
  account_id BIGINT UNSIGNED NULL,
  payment_date DATE NOT NULL,
  amount DECIMAL(15,2) NOT NULL,
  reference VARCHAR(120) NULL,
  notes TEXT NULL,
  created_by BIGINT UNSIGNED NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_bill_payment_date (payment_date),
  CONSTRAINT fk_bill_payment_bill FOREIGN KEY (bill_id) REFERENCES bills(id) ON DELETE CASCADE,
  CONSTRAINT fk_bill_payment_account FOREIGN KEY (account_id) REFERENCES payment_accounts(id) ON DELETE SET NULL,
  CONSTRAINT fk_bill_payment_user FOREIGN KEY (created_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS web_orders (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  order_number VARCHAR(40) NOT NULL UNIQUE,
  idempotency_key VARCHAR(64) NOT NULL UNIQUE,
  customer_id BIGINT UNSIGNED NULL,
  invoice_id BIGINT UNSIGNED NULL,
  customer_name VARCHAR(160) NOT NULL,
  phone VARCHAR(50) NOT NULL,
  email VARCHAR(190) NULL,
  fulfilment_method ENUM('Pickup','Delivery') NOT NULL DEFAULT 'Pickup',
  delivery_address VARCHAR(255) NULL,
  payment_preference VARCHAR(100) NULL,
  notes TEXT NULL,
  status ENUM('new','contacted','confirmed','invoiced','fulfilled','cancelled') NOT NULL DEFAULT 'new',
  currency_code CHAR(3) NOT NULL DEFAULT 'USD',
  subtotal DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  delivery_fee DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  total DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  client_ip_hash CHAR(64) NULL,
  user_agent VARCHAR(255) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_web_order_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE SET NULL,
  CONSTRAINT fk_web_order_invoice FOREIGN KEY (invoice_id) REFERENCES documents(id) ON DELETE SET NULL,
  INDEX idx_web_orders_status_created (status,created_at),
  INDEX idx_web_orders_phone (phone)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS web_order_items (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  web_order_id BIGINT UNSIGNED NOT NULL,
  product_id BIGINT UNSIGNED NULL,
  sku VARCHAR(80) NOT NULL,
  product_name VARCHAR(160) NOT NULL,
  quantity DECIMAL(13,2) NOT NULL DEFAULT 1.00,
  unit_price DECIMAL(13,2) NOT NULL DEFAULT 0.00,
  line_total DECIMAL(13,2) NOT NULL DEFAULT 0.00,
  CONSTRAINT fk_web_order_item_order FOREIGN KEY (web_order_id) REFERENCES web_orders(id) ON DELETE CASCADE,
  CONSTRAINT fk_web_order_item_product FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE SET NULL,
  INDEX idx_web_order_items_order (web_order_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS crm_opportunities (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  customer_id BIGINT UNSIGNED NOT NULL,
  web_order_id BIGINT UNSIGNED NULL,
  document_id BIGINT UNSIGNED NULL,
  title VARCHAR(180) NOT NULL,
  stage VARCHAR(40) NOT NULL DEFAULT 'new_enquiry',
  source VARCHAR(40) NOT NULL DEFAULT 'WhatsApp',
  value DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  currency_code CHAR(3) NOT NULL DEFAULT 'USD',
  expected_close_date DATE NULL,
  next_follow_up_at DATETIME NULL,
  assigned_to BIGINT UNSIGNED NULL,
  notes TEXT NULL,
  lost_reason VARCHAR(255) NULL,
  created_by BIGINT UNSIGNED NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_crm_opportunity_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE,
  CONSTRAINT fk_crm_opportunity_web_order FOREIGN KEY (web_order_id) REFERENCES web_orders(id) ON DELETE SET NULL,
  CONSTRAINT fk_crm_opportunity_document FOREIGN KEY (document_id) REFERENCES documents(id) ON DELETE SET NULL,
  CONSTRAINT fk_crm_opportunity_assignee FOREIGN KEY (assigned_to) REFERENCES users(id) ON DELETE SET NULL,
  CONSTRAINT fk_crm_opportunity_creator FOREIGN KEY (created_by) REFERENCES users(id),
  INDEX idx_crm_stage_followup (stage,next_follow_up_at),
  INDEX idx_crm_customer (customer_id),
  UNIQUE KEY uq_crm_web_order (web_order_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS crm_activities (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  opportunity_id BIGINT UNSIGNED NULL,
  customer_id BIGINT UNSIGNED NOT NULL,
  activity_type ENUM('call','whatsapp','email','meeting','task','note') NOT NULL DEFAULT 'task',
  subject VARCHAR(180) NOT NULL,
  due_at DATETIME NULL,
  completed_at DATETIME NULL,
  notes TEXT NULL,
  assigned_to BIGINT UNSIGNED NULL,
  created_by BIGINT UNSIGNED NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_crm_activity_opportunity FOREIGN KEY (opportunity_id) REFERENCES crm_opportunities(id) ON DELETE CASCADE,
  CONSTRAINT fk_crm_activity_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE,
  CONSTRAINT fk_crm_activity_assignee FOREIGN KEY (assigned_to) REFERENCES users(id) ON DELETE SET NULL,
  CONSTRAINT fk_crm_activity_creator FOREIGN KEY (created_by) REFERENCES users(id),
  INDEX idx_crm_activity_due (completed_at,due_at),
  INDEX idx_crm_activity_customer (customer_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS refill_jobs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  reference VARCHAR(40) NOT NULL UNIQUE,
  customer_id BIGINT UNSIGNED NOT NULL,
  opportunity_id BIGINT UNSIGNED NULL,
  cylinder_type ENUM('pink_quick_connect','blue_screw','compatible_other') NOT NULL,
  service_type ENUM('new_cylinder','refill','exchange') NOT NULL DEFAULT 'refill',
  quantity SMALLINT UNSIGNED NOT NULL DEFAULT 1,
  unit_price DECIMAL(13,2) NOT NULL DEFAULT 35.00,
  fulfilment_method ENUM('Pickup','Delivery') NOT NULL DEFAULT 'Pickup',
  delivery_address VARCHAR(255) NULL,
  delivery_fee DECIMAL(13,2) NOT NULL DEFAULT 0.00,
  status ENUM('received','awaiting_refill','ready','out_for_delivery','completed','cancelled') NOT NULL DEFAULT 'received',
  payment_status ENUM('unpaid','partial','paid') NOT NULL DEFAULT 'unpaid',
  received_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  due_at DATETIME NULL,
  completed_at DATETIME NULL,
  notes TEXT NULL,
  created_by BIGINT UNSIGNED NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_refill_customer FOREIGN KEY (customer_id) REFERENCES customers(id),
  CONSTRAINT fk_refill_opportunity FOREIGN KEY (opportunity_id) REFERENCES crm_opportunities(id) ON DELETE SET NULL,
  CONSTRAINT fk_refill_creator FOREIGN KEY (created_by) REFERENCES users(id),
  INDEX idx_refill_status_due (status,due_at),
  INDEX idx_refill_customer (customer_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS deliveries (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  reference VARCHAR(40) NOT NULL UNIQUE,
  customer_id BIGINT UNSIGNED NOT NULL,
  opportunity_id BIGINT UNSIGNED NULL,
  document_id BIGINT UNSIGNED NULL,
  refill_job_id BIGINT UNSIGNED NULL,
  delivery_address VARCHAR(255) NOT NULL,
  area VARCHAR(100) NULL,
  delivery_fee DECIMAL(13,2) NOT NULL DEFAULT 0.00,
  scheduled_for DATETIME NULL,
  driver_name VARCHAR(120) NULL,
  driver_phone VARCHAR(50) NULL,
  status ENUM('pending','scheduled','out_for_delivery','delivered','failed','cancelled') NOT NULL DEFAULT 'pending',
  proof_reference VARCHAR(160) NULL,
  notes TEXT NULL,
  delivered_at DATETIME NULL,
  created_by BIGINT UNSIGNED NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_delivery_customer FOREIGN KEY (customer_id) REFERENCES customers(id),
  CONSTRAINT fk_delivery_opportunity FOREIGN KEY (opportunity_id) REFERENCES crm_opportunities(id) ON DELETE SET NULL,
  CONSTRAINT fk_delivery_document FOREIGN KEY (document_id) REFERENCES documents(id) ON DELETE SET NULL,
  CONSTRAINT fk_delivery_refill FOREIGN KEY (refill_job_id) REFERENCES refill_jobs(id) ON DELETE SET NULL,
  CONSTRAINT fk_delivery_creator FOREIGN KEY (created_by) REFERENCES users(id),
  INDEX idx_delivery_status_schedule (status,scheduled_for),
  INDEX idx_delivery_customer (customer_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS audit_logs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NULL,
  action VARCHAR(100) NOT NULL,
  entity_type VARCHAR(80) NULL,
  entity_id BIGINT UNSIGNED NULL,
  details TEXT NULL,
  ip_address VARCHAR(45) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_audit_user FOREIGN KEY (user_id) REFERENCES users(id) ON UPDATE CASCADE ON DELETE SET NULL,
  INDEX idx_audit_created (created_at),
  INDEX idx_audit_entity (entity_type, entity_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS login_attempts (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(190) NOT NULL,
  ip_address VARCHAR(45) NOT NULL,
  attempted_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_login_attempts_lookup (email, ip_address, attempted_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS online_payment_attempts (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  provider VARCHAR(30) NOT NULL,
  web_order_id BIGINT UNSIGNED NOT NULL,
  invoice_id BIGINT UNSIGNED NULL,
  payment_id BIGINT UNSIGNED NULL,
  merchant_reference VARCHAR(80) NOT NULL,
  merchant_trace VARCHAR(80) NULL,
  provider_reference VARCHAR(120) NULL,
  amount DECIMAL(15,2) NOT NULL,
  currency_code CHAR(3) NOT NULL DEFAULT 'USD',
  browser_url TEXT NULL,
  poll_url TEXT NULL,
  payment_channel VARCHAR(100) NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'created',
  raw_response MEDIUMTEXT NULL,
  error_message VARCHAR(500) NULL,
  paid_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_online_payment_reference (provider,merchant_reference),
  KEY idx_online_payment_order (web_order_id,created_at),
  KEY idx_online_payment_status (provider,status,updated_at),
  CONSTRAINT fk_online_payment_order FOREIGN KEY (web_order_id) REFERENCES web_orders(id) ON DELETE CASCADE,
  CONSTRAINT fk_online_payment_invoice FOREIGN KEY (invoice_id) REFERENCES documents(id) ON DELETE SET NULL,
  CONSTRAINT fk_online_payment_payment FOREIGN KEY (payment_id) REFERENCES payments(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
