-- ============================================================
-- CMRS — Database Schema (MySQL)
-- File: database/schema.sql
-- Engine: InnoDB, Charset: utf8mb4
-- ============================================================

SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- Drop in child→parent order (to avoid FK errors)
-- ------------------------------------------------------------
DROP TABLE IF EXISTS audit_logs;
DROP TABLE IF EXISTS payouts;
DROP TABLE IF EXISTS transactions;
DROP TABLE IF EXISTS invoice_lines;
DROP TABLE IF EXISTS invoices;
DROP TABLE IF EXISTS rentals;
DROP TABLE IF EXISTS driver_documents;
DROP TABLE IF EXISTS booking_vehicles;
DROP TABLE IF EXISTS bookings;
DROP TABLE IF EXISTS vehicles;
DROP TABLE IF EXISTS drivers;
DROP TABLE IF EXISTS partners;
DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS roles;
DROP TABLE IF EXISTS companies;

-- ------------------------------------------------------------
-- companies
-- ------------------------------------------------------------
CREATE TABLE companies (
  id                   INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name                 VARCHAR(255) NOT NULL,
  base_currency        VARCHAR(10)  NOT NULL DEFAULT 'GBP',
  address              VARCHAR(255) NULL,
  phone                VARCHAR(255) NULL,
  email                VARCHAR(255) NULL,
  logo_url             VARCHAR(255) NULL,
  invoice_template_id  INT NULL,          -- reserved; no FK target provided
  UNIQUE KEY uk_companies_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- roles
-- ------------------------------------------------------------
CREATE TABLE roles (
  id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name         VARCHAR(255) NOT NULL,   -- Ops, Sales, Accounts, Admin, Management
  permissions  JSON         NULL,
  UNIQUE KEY uk_roles_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- users
-- ------------------------------------------------------------
CREATE TABLE users (
  id             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  company_id     INT UNSIGNED NOT NULL,
  name           VARCHAR(255) NOT NULL,
  email          VARCHAR(255) NOT NULL,
  password_hash  VARCHAR(255) NOT NULL,
  role_id        INT UNSIGNED NOT NULL,
  last_login     DATETIME NULL,
  UNIQUE KEY uk_users_company_email (company_id, email),
  CONSTRAINT fk_users_company
    FOREIGN KEY (company_id) REFERENCES companies(id)
    ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT fk_users_role
    FOREIGN KEY (role_id) REFERENCES roles(id)
    ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- partners
-- ------------------------------------------------------------
CREATE TABLE partners (
  id                INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  company_id        INT UNSIGNED NOT NULL,
  name              VARCHAR(255) NOT NULL,
  contact_name      VARCHAR(255) NULL,
  phone             VARCHAR(255) NULL,
  email             VARCHAR(255) NULL,
  commission_type   ENUM('fixed','percent') NOT NULL DEFAULT 'percent',
  commission_value  DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  notes             TEXT NULL,
  UNIQUE KEY uk_partners_company_name (company_id, name),
  CONSTRAINT fk_partners_company
    FOREIGN KEY (company_id) REFERENCES companies(id)
    ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- drivers
-- ------------------------------------------------------------
CREATE TABLE drivers (
  id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  company_id    INT UNSIGNED NOT NULL,
  name          VARCHAR(255) NOT NULL,
  phone         VARCHAR(255) NULL,
  email         VARCHAR(255) NULL,
  bank_details  JSON         NULL,        -- account name/number/sort, etc.
  address       VARCHAR(255) NULL,
  photo_url     VARCHAR(255) NULL,
  is_active     TINYINT(1)   NOT NULL DEFAULT 1,
  KEY idx_drivers_company_active (company_id, is_active),
  CONSTRAINT fk_drivers_company
    FOREIGN KEY (company_id) REFERENCES companies(id)
    ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- vehicles
-- ------------------------------------------------------------
CREATE TABLE vehicles (
  id               INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  company_id       INT UNSIGNED NOT NULL,
  reg_no           VARCHAR(255) NOT NULL,
  type             VARCHAR(255) NOT NULL,      -- S-Class, V-Class, MPV, etc.
  make_model       VARCHAR(255) NULL,          -- e.g., "Mercedes V-Class"
  capacity         VARCHAR(255) NULL,          -- free-form (e.g., "7 pax, 5 bags")
  current_status   ENUM('available','rented','maintenance') NOT NULL DEFAULT 'available',
  last_service_date DATE NULL,
  UNIQUE KEY uk_vehicles_company_reg (company_id, reg_no),
  KEY idx_vehicles_status (company_id, current_status),
  CONSTRAINT fk_vehicles_company
    FOREIGN KEY (company_id) REFERENCES companies(id)
    ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;




CREATE TABLE IF NOT EXISTS vehicle_maintenance (
  id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  vehicle_id   INT UNSIGNED NOT NULL,
  company_id   INT UNSIGNED NOT NULL,
  service_date DATE NOT NULL,
  service_type VARCHAR(50) NOT NULL,    -- MOT, Insurance, Repairs, Other
  notes        TEXT NULL,
  cost         DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  file_url     VARCHAR(255) NULL,       -- optional attachment
  created_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

  CONSTRAINT fk_vm_vehicle
    FOREIGN KEY (vehicle_id) REFERENCES vehicles(id)
    ON UPDATE CASCADE ON DELETE CASCADE,

  CONSTRAINT fk_vm_company
    FOREIGN KEY (company_id) REFERENCES companies(id)
    ON UPDATE CASCADE ON DELETE RESTRICT
);

CREATE INDEX IF NOT EXISTS idx_vm_vehicle_date ON vehicle_maintenance (vehicle_id, service_date);



ALTER TABLE vehicles
  ADD COLUMN IF NOT EXISTS mot_expiry_date        DATE NULL,
  ADD COLUMN IF NOT EXISTS insurance_expiry_date  DATE NULL,
  ADD COLUMN IF NOT EXISTS phv_expiry_date        DATE NULL;

CREATE INDEX IF NOT EXISTS idx_vehicle_expiry ON vehicles (mot_expiry_date, insurance_expiry_date, phv_expiry_date);


-- ------------------------------------------------------------
-- bookings
-- ------------------------------------------------------------
CREATE TABLE bookings (
  id                  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  company_id          INT UNSIGNED NOT NULL,
  booking_ref         VARCHAR(255) NOT NULL,
  partner_id          INT UNSIGNED NULL,
  booking_type        ENUM('Transfer','As Directed') NOT NULL DEFAULT 'Transfer',
  hours               DECIMAL(5,2) NULL,   -- for 'As Directed' jobs; can be fractional
  client_name         VARCHAR(255) NULL,
  client_phone        VARCHAR(255) NULL,
  client_email        VARCHAR(255) NULL,
  pickup_date         DATE NULL,
  pickup_time         TIME NULL,
  pickup_address      VARCHAR(255) NULL,
  via                 TEXT NULL,           -- optional multi-stop notes
  dropoff_address     VARCHAR(255) NULL,
  pax_count           INT NULL,
  luggage_count       INT NULL,
  notes               TEXT NULL,
  status              ENUM('New','Confirmed','Assigned','InProgress','Completed','Invoiced','Paid') NOT NULL DEFAULT 'New',
  total_client_price  DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  total_driver_price  DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  created_by          INT UNSIGNED NULL,   -- users.id
  created_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uk_bookings_company_ref (company_id, booking_ref),
  KEY idx_bookings_company_pickup (company_id, pickup_date, pickup_time),
  KEY idx_bookings_status (status),
  CONSTRAINT fk_bookings_company
    FOREIGN KEY (company_id) REFERENCES companies(id)
    ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT fk_bookings_partner
    FOREIGN KEY (partner_id) REFERENCES partners(id)
    ON UPDATE CASCADE ON DELETE SET NULL,
  CONSTRAINT fk_bookings_created_by
    FOREIGN KEY (created_by) REFERENCES users(id)
    ON UPDATE CASCADE ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- booking_vehicles (multi-vehicle/driver per booking)
-- ------------------------------------------------------------
CREATE TABLE booking_vehicles (
  id             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  booking_id     INT UNSIGNED NOT NULL,
  vehicle_id     INT UNSIGNED NULL,
  vehicle_type   VARCHAR(255) NULL,
  vehicle_reg_no VARCHAR(255) NULL,
  driver_id      INT UNSIGNED NULL,
  driver_price   DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  client_price   DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  sequence_order INT NOT NULL DEFAULT 1,
  KEY idx_bv_booking (booking_id, sequence_order),
  KEY idx_bv_vehicle (vehicle_id),
  KEY idx_bv_driver (driver_id),
  CONSTRAINT fk_bv_booking
    FOREIGN KEY (booking_id) REFERENCES bookings(id)
    ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT fk_bv_vehicle
    FOREIGN KEY (vehicle_id) REFERENCES vehicles(id)
    ON UPDATE CASCADE ON DELETE SET NULL,
  CONSTRAINT fk_bv_driver
    FOREIGN KEY (driver_id) REFERENCES drivers(id)
    ON UPDATE CASCADE ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- driver_documents (expiry tracking)
-- ------------------------------------------------------------
CREATE TABLE driver_documents (
  id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  driver_id     INT UNSIGNED NOT NULL,
  doc_type      VARCHAR(255) NOT NULL,     -- license, insurance, PCO, MOT, etc.
  upload_url    VARCHAR(255) NULL,
  expiry_date   DATE NULL,
  optional_flag TINYINT(1) NOT NULL DEFAULT 0,
  KEY idx_driver_docs_expiry (expiry_date),
  CONSTRAINT fk_driver_docs_driver
    FOREIGN KEY (driver_id) REFERENCES drivers(id)
    ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- rentals
-- ------------------------------------------------------------
CREATE TABLE rentals (
  id                     INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  vehicle_id             INT UNSIGNED NOT NULL,
  renter_name            VARCHAR(255) NOT NULL,
  phone                  VARCHAR(255) NULL,
  weekly_rate            DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  deposit_amount         DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  start_date             DATE NOT NULL,
  end_date               DATE NULL,
  deposit_refunded_flag  TINYINT(1) NOT NULL DEFAULT 0,
  status                 VARCHAR(255) NOT NULL DEFAULT 'active',
  KEY idx_rentals_vehicle_dates (vehicle_id, start_date, end_date),
  CONSTRAINT fk_rentals_vehicle
    FOREIGN KEY (vehicle_id) REFERENCES vehicles(id)
    ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;



/* 1A) Add company_id safely (nullable -> backfill -> not null) */
ALTER TABLE rentals
  ADD COLUMN IF NOT EXISTS company_id INT UNSIGNED NULL AFTER id;

UPDATE rentals r
JOIN vehicles v ON v.id = r.vehicle_id
SET r.company_id = v.company_id
WHERE r.company_id IS NULL;

ALTER TABLE rentals
  MODIFY COLUMN company_id INT UNSIGNED NOT NULL;

ALTER TABLE rentals
  ADD CONSTRAINT fk_rentals_company
  FOREIGN KEY (company_id) REFERENCES companies(id)
  ON UPDATE CASCADE ON DELETE RESTRICT;

/* 1B) Renter contact fields (email, address) */
ALTER TABLE rentals
  ADD COLUMN IF NOT EXISTS renter_email   VARCHAR(255) NULL AFTER phone,
  ADD COLUMN IF NOT EXISTS renter_address TEXT         NULL AFTER renter_email;

/* 1C) Finance & contract fields */
ALTER TABLE rentals
  ADD COLUMN IF NOT EXISTS deposit_status      ENUM('Held','Refunded','Forfeited') NOT NULL DEFAULT 'Held' AFTER deposit_amount,
  ADD COLUMN IF NOT EXISTS payment_frequency   ENUM('Weekly','Monthly')            NOT NULL DEFAULT 'Weekly' AFTER deposit_status,
  ADD COLUMN IF NOT EXISTS payment_notes       TEXT NULL AFTER payment_frequency,
  ADD COLUMN IF NOT EXISTS agreement_url       VARCHAR(255) NULL AFTER payment_notes;

/* Keep legacy flag if you want; otherwise you can drop it later:
   ALTER TABLE rentals DROP COLUMN deposit_refunded_flag; */

/* 1D) Status normalization + timestamps */
UPDATE rentals SET status='Active' WHERE LOWER(status)='active';

ALTER TABLE rentals
  MODIFY COLUMN status VARCHAR(32) NOT NULL DEFAULT 'Active';

ALTER TABLE rentals
  ADD COLUMN IF NOT EXISTS created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER status,
  ADD COLUMN IF NOT EXISTS updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP AFTER created_at;

/* 1E) Useful indexes */
CREATE INDEX IF NOT EXISTS idx_rentals_company_status ON rentals (company_id, status);
CREATE INDEX IF NOT EXISTS idx_rentals_dates          ON rentals (start_date, end_date);


CREATE TABLE IF NOT EXISTS rental_payments (
  id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  rental_id    INT UNSIGNED NOT NULL,
  payment_date DATE NOT NULL,
  amount       DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  method       VARCHAR(50) NULL,        -- e.g., Cash, Bank, Card
  reference    VARCHAR(100) NULL,       -- bank ref / receipt #
  notes        TEXT NULL,
  recorded_by  INT UNSIGNED NULL,       -- users.id (who recorded)
  created_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

  CONSTRAINT fk_rental_payments_rental
    FOREIGN KEY (rental_id) REFERENCES rentals(id)
    ON UPDATE CASCADE ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS idx_rp_rental_date ON rental_payments (rental_id, payment_date);




-- ------------------------------------------------------------
-- invoices
-- ------------------------------------------------------------
CREATE TABLE invoices (
  id                  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  company_id          INT UNSIGNED NOT NULL,
  invoice_no          VARCHAR(255) NOT NULL,
  related_booking_id  INT UNSIGNED NULL,
  related_rental_id   INT UNSIGNED NULL,
  client_name         VARCHAR(255) NULL,
  partner_name        VARCHAR(255) NULL,
  issue_date          DATE NOT NULL,
  due_date            DATE NULL,
  subtotal            DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  vat                 DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  total               DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  pdf_url             VARCHAR(255) NULL,
  status              VARCHAR(255) NOT NULL DEFAULT 'draft',
  UNIQUE KEY uk_invoices_company_no (company_id, invoice_no),
  KEY idx_invoices_dates (issue_date, due_date),
  CONSTRAINT fk_invoices_company
    FOREIGN KEY (company_id) REFERENCES companies(id)
    ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT fk_invoices_booking
    FOREIGN KEY (related_booking_id) REFERENCES bookings(id)
    ON UPDATE CASCADE ON DELETE SET NULL,
  CONSTRAINT fk_invoices_rental
    FOREIGN KEY (related_rental_id) REFERENCES rentals(id)
    ON UPDATE CASCADE ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- invoice_lines
-- ------------------------------------------------------------
CREATE TABLE invoice_lines (
  id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  invoice_id   INT UNSIGNED NOT NULL,
  description  VARCHAR(255) NOT NULL,
  qty          DECIMAL(10,2) NOT NULL DEFAULT 1.00,
  unit_price   DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  line_total   DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  KEY idx_invoice_lines_invoice (invoice_id),
  CONSTRAINT fk_invoice_lines_invoice
    FOREIGN KEY (invoice_id) REFERENCES invoices(id)
    ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- transactions (ledger)
-- ------------------------------------------------------------
CREATE TABLE transactions (
  id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  company_id    INT UNSIGNED NOT NULL,
  date          DATE NOT NULL,
  account_code  VARCHAR(255) NULL,
  type          ENUM('income','expense') NOT NULL,
  amount        DECIMAL(10,2) NOT NULL,
  booking_id    INT UNSIGNED NULL,
  rental_id     INT UNSIGNED NULL,
  notes         TEXT NULL,
  KEY idx_tx_company_date (company_id, date),
  KEY idx_tx_booking (booking_id),
  KEY idx_tx_rental (rental_id),
  CONSTRAINT fk_tx_company
    FOREIGN KEY (company_id) REFERENCES companies(id)
    ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT fk_tx_booking
    FOREIGN KEY (booking_id) REFERENCES bookings(id)
    ON UPDATE CASCADE ON DELETE SET NULL,
  CONSTRAINT fk_tx_rental
    FOREIGN KEY (rental_id) REFERENCES rentals(id)
    ON UPDATE CASCADE ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- payouts
-- ------------------------------------------------------------
CREATE TABLE payouts (
  id             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  company_id     INT UNSIGNED NOT NULL,
  driver_id      INT UNSIGNED NULL,
  partner_id     INT UNSIGNED NULL,
  period_start   DATE NULL,
  period_end     DATE NULL,
  total_amount   DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  payment_method VARCHAR(255) NULL,     -- bank_transfer, cash, etc.
  sent_flag      TINYINT(1) NOT NULL DEFAULT 0,
  KEY idx_payouts_company_period (company_id, period_start, period_end),
  KEY idx_payouts_driver (driver_id),
  KEY idx_payouts_partner (partner_id),
  CONSTRAINT fk_payouts_company
    FOREIGN KEY (company_id) REFERENCES companies(id)
    ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT fk_payouts_driver
    FOREIGN KEY (driver_id) REFERENCES drivers(id)
    ON UPDATE CASCADE ON DELETE SET NULL,
  CONSTRAINT fk_payouts_partner
    FOREIGN KEY (partner_id) REFERENCES partners(id)
    ON UPDATE CASCADE ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- audit_logs
-- ------------------------------------------------------------
CREATE TABLE audit_logs (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id     INT UNSIGNED NULL,
  action      VARCHAR(255) NOT NULL,     -- e.g., booking.create, invoice.send
  entity_type VARCHAR(255) NULL,         -- booking, invoice, vehicle, etc.
  entity_id   INT UNSIGNED NULL,
  diff        JSON NULL,
  timestamp   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_audit_time (timestamp),
  KEY idx_audit_entity (entity_type, entity_id),
  CONSTRAINT fk_audit_user
    FOREIGN KEY (user_id) REFERENCES users(id)
    ON UPDATE CASCADE ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Add self-employed driver's vehicle fields to drivers
ALTER TABLE drivers
  ADD COLUMN own_vehicle_reg_no VARCHAR(255) NULL AFTER photo_url,
  ADD COLUMN own_vehicle_type   VARCHAR(50)  NULL AFTER own_vehicle_reg_no,
  ADD COLUMN own_vehicle_color  VARCHAR(50)  NULL AFTER own_vehicle_type;


-- Run this after schema.sql (once)
ALTER TABLE bookings
  ADD COLUMN parking_fee DECIMAL(10,2) NOT NULL DEFAULT 0.00 AFTER notes,
  ADD COLUMN extra_waiting_fee DECIMAL(10,2) NOT NULL DEFAULT 0.00 AFTER parking_fee,
  ADD COLUMN completed_at DATETIME NULL AFTER status;

-- Optional safety (ignore errors if already there)
-- ALTER TABLE bookings ADD COLUMN IF NOT EXISTS ...


/* ============================================================
   Phase 4 — Sales & CRM (Partners + Quotes)
   Engine/Collation matches your project defaults
   ============================================================ */
SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci;

/* ------------------------------------------------------------
   1) PARTNERS
   ------------------------------------------------------------ */
CREATE TABLE IF NOT EXISTS partners (
  id               INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  company_id       INT UNSIGNED NOT NULL,
  name             VARCHAR(255) NOT NULL,
  contact_name     VARCHAR(255) NULL,
  phone            VARCHAR(50)  NULL,
  email            VARCHAR(255) NULL,
  address          TEXT NULL,

  commission_type  ENUM('fixed','percent') NOT NULL DEFAULT 'fixed',
  commission_value DECIMAL(10,2) NOT NULL DEFAULT 0.00,

  bank_details     TEXT NULL,         -- optional extra
  notes            TEXT NULL,

  created_at       DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at       DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  KEY idx_partners_company (company_id),
  KEY idx_partners_name    (name),

  CONSTRAINT fk_partners_company
    FOREIGN KEY (company_id) REFERENCES companies(id)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/* ------------------------------------------------------------
   2) CORPORATE ACCOUNTS
   ------------------------------------------------------------ */
CREATE TABLE IF NOT EXISTS corporates (
  id               INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  company_id       INT UNSIGNED NOT NULL,

  name             VARCHAR(255) NOT NULL,
  contact_person   VARCHAR(255) NULL,
  phone            VARCHAR(50)  NULL,
  email            VARCHAR(255) NULL,
  billing_address  TEXT NULL,

  credit_limit     DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  invoice_cycle    ENUM('weekly','monthly') NOT NULL DEFAULT 'monthly',
  payment_terms    VARCHAR(100)  NOT NULL DEFAULT '30 days',

  notes            TEXT NULL,
  status           ENUM('active','inactive') NOT NULL DEFAULT 'active',

  created_at       DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at       DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  KEY idx_corporates_company (company_id),
  KEY idx_corporates_name    (name),
  KEY idx_corporates_email   (email),
  KEY idx_corporates_status  (status),

  CONSTRAINT fk_corporates_company
    FOREIGN KEY (company_id) REFERENCES companies(id)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- Add corporate linkage to bookings (nullable)
ALTER TABLE bookings
  ADD COLUMN corporate_id INT UNSIGNED NULL AFTER partner_id,
  ADD KEY idx_bookings_corporate (corporate_id),
  ADD CONSTRAINT fk_bookings_corporate
    FOREIGN KEY (corporate_id) REFERENCES corporates(id)
    ON DELETE SET NULL ON UPDATE CASCADE;



/* ------------------------------------------------------------
   3) QUOTES (header)
   ------------------------------------------------------------ */
CREATE TABLE IF NOT EXISTS quotes (
  id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  company_id    INT UNSIGNED NOT NULL,

  partner_id    INT UNSIGNED NULL,
  corporate_id  INT UNSIGNED NULL,

  client_name   VARCHAR(255) NOT NULL,
  client_email  VARCHAR(255) NULL,

  quote_date    DATE NOT NULL,
  discount      DECIMAL(10,2) NOT NULL DEFAULT 0.00,

  status        ENUM('draft','sent','accepted','rejected') NOT NULL DEFAULT 'draft',
  notes         TEXT NULL,
  pdf_url       VARCHAR(255) NULL,

  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  KEY idx_quotes_company_date (company_id, quote_date),
  KEY idx_quotes_status       (status),
  KEY idx_quotes_partner      (partner_id),
  KEY idx_quotes_corporate    (corporate_id),

  CONSTRAINT fk_quotes_company
    FOREIGN KEY (company_id)  REFERENCES companies(id)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_quotes_partner
    FOREIGN KEY (partner_id)  REFERENCES partners(id)
    ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT fk_quotes_corporate
    FOREIGN KEY (corporate_id) REFERENCES corporates(id)
    ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/* ------------------------------------------------------------
   4) QUOTE LINES (items)
   ------------------------------------------------------------ */
CREATE TABLE IF NOT EXISTS quote_lines (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  quote_id    INT UNSIGNED NOT NULL,

  description VARCHAR(255) NOT NULL,
  quantity    INT NOT NULL DEFAULT 1,
  unit_price  DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  line_total  DECIMAL(10,2) NOT NULL DEFAULT 0.00,  -- usually quantity * unit_price

  KEY idx_quote_lines_quote (quote_id),

  CONSTRAINT fk_quote_lines_quote
    FOREIGN KEY (quote_id) REFERENCES quotes(id)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- PHASE 4 — QUOTATION MODULE (MySQL 5.7+/8.0)
-- Safe to run multiple times thanks to IF NOT EXISTS.
-- Engine/charset match the rest of CMRS.
-- ============================================================

/* ------------------------------------------------------------
   1) QUOTES (header)
   ------------------------------------------------------------ */
CREATE TABLE IF NOT EXISTS quotes (
  id                INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  company_id        INT UNSIGNED NOT NULL,
  partner_id        INT UNSIGNED NULL,
  corporate_id      INT UNSIGNED NULL,

  -- Human-facing reference, unique per company (e.g. BHC-27092025-123456)
  quote_ref         VARCHAR(50) NOT NULL,

  client_name       VARCHAR(255) NOT NULL,
  client_email      VARCHAR(255) NULL,

  quote_date        DATE NOT NULL,

  -- Discount support
  discount_type     ENUM('amount','percent') NOT NULL DEFAULT 'amount',
  discount_value    DECIMAL(10,2) NOT NULL DEFAULT 0.00,

  -- Stored totals for performance; keep in sync in app
  subtotal_amount   DECIMAL(10,2) NOT NULL DEFAULT 0.00, -- sum of line totals before discount
  total_amount      DECIMAL(10,2) NOT NULL DEFAULT 0.00, -- after discount

  status            ENUM('draft','sent','accepted','rejected','deleted') NOT NULL DEFAULT 'draft',

  notes             TEXT NULL,              -- internal notes
  pdf_url           VARCHAR(255) NULL,      -- generated PDF path (public/uploads/…)
  booking_id        INT UNSIGNED NULL,      -- set when converted to booking (optional link)

  created_by        INT UNSIGNED NULL,
  created_at        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  -- Indexes
  UNIQUE KEY uk_quotes_company_ref (company_id, quote_ref),
  KEY idx_quotes_company_date (company_id, quote_date),
  KEY idx_quotes_status (status),
  KEY idx_quotes_partner (partner_id),
  KEY idx_quotes_corporate (corporate_id),
  KEY idx_quotes_booking (booking_id),

  -- FKs
  CONSTRAINT fk_quotes_company   FOREIGN KEY (company_id)  REFERENCES companies(id)  ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_quotes_partner   FOREIGN KEY (partner_id)  REFERENCES partners(id)   ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT fk_quotes_corporate FOREIGN KEY (corporate_id)REFERENCES corporates(id) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT fk_quotes_booking   FOREIGN KEY (booking_id)  REFERENCES bookings(id)   ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/* ------------------------------------------------------------
   2) QUOTE LINES (detail)
   ------------------------------------------------------------ */
CREATE TABLE IF NOT EXISTS quote_lines (
  id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  quote_id      INT UNSIGNED NOT NULL,

  line_no       INT UNSIGNED NOT NULL DEFAULT 1,   -- display order
  description   TEXT NULL,                         -- large journey text, pickup → dropoff etc.
  vehicle_type  VARCHAR(100) NULL,                 -- optional (e.g., Business Sedan)
  quantity      INT UNSIGNED NOT NULL DEFAULT 1,
  unit_price    DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  line_total    DECIMAL(10,2) NOT NULL DEFAULT 0.00,

  KEY idx_quote_lines_quote (quote_id),
  KEY idx_quote_lines_order (quote_id, line_no),

  CONSTRAINT fk_quote_lines_quote FOREIGN KEY (quote_id)
    REFERENCES quotes(id)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/* ------------------------------------------------------------
   3) QUOTE STATUS HISTORY (optional but recommended)
      - Audits transitions: draft → sent → accepted/rejected
   ------------------------------------------------------------ */
CREATE TABLE IF NOT EXISTS quote_status_history (
  id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  quote_id     INT UNSIGNED NOT NULL,
  old_status   ENUM('draft','sent','accepted','rejected','deleted') NULL,
  new_status   ENUM('draft','sent','accepted','rejected','deleted') NOT NULL,
  note         VARCHAR(255) NULL,
  changed_by   INT UNSIGNED NULL,
  changed_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

  KEY idx_qsh_quote (quote_id),
  KEY idx_qsh_changed (changed_at),

  CONSTRAINT fk_qsh_quote FOREIGN KEY (quote_id)
    REFERENCES quotes(id)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/* ------------------------------------------------------------
   4) QUOTE EMAIL LOG (optional)
      - Records outbound email attempts for quotes (PDF send)
   ------------------------------------------------------------ */
CREATE TABLE IF NOT EXISTS quote_emails_log (
  id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  quote_id      INT UNSIGNED NOT NULL,
  recipient     VARCHAR(255) NOT NULL,
  subject       VARCHAR(255) NOT NULL,
  sent_at       DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  status        ENUM('queued','sent','failed') NOT NULL DEFAULT 'sent',
  message_id    VARCHAR(255) NULL,           -- provider msg id if available
  error_message TEXT NULL,

  KEY idx_qel_quote (quote_id),
  KEY idx_qel_status (status),

  CONSTRAINT fk_qel_quote FOREIGN KEY (quote_id)
    REFERENCES quotes(id)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;



/* ------------------------------------------------------------
   5) INVOICES (corporate monthly invoices)
   ------------------------------------------------------------ */
CREATE TABLE IF NOT EXISTS invoices (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  company_id      INT UNSIGNED NOT NULL,
  corporate_id    INT UNSIGNED NOT NULL,

  invoice_no      VARCHAR(50) NOT NULL,           -- e.g., CORP-YYYYMM-###
  invoice_date    DATE NOT NULL,
  period_start    DATE NULL,
  period_end      DATE NULL,

  subtotal        DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  discount_total  DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  tax_total       DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  grand_total     DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  currency        CHAR(3) NOT NULL DEFAULT 'GBP',

  status          ENUM('draft','pending','sent','paid','void') NOT NULL DEFAULT 'draft',
  pdf_url         VARCHAR(255) NULL,
  notes           TEXT NULL,

  created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  UNIQUE KEY uk_invoices_company_no (company_id, invoice_no),
  KEY idx_invoices_company_date (company_id, invoice_date),
  KEY idx_invoices_corporate    (corporate_id),
  KEY idx_invoices_status       (status),

  CONSTRAINT fk_invoices_company
    FOREIGN KEY (company_id)   REFERENCES companies(id)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_invoices_corporate
    FOREIGN KEY (corporate_id) REFERENCES corporates(id)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/* ------------------------------------------------------------
   6) INVOICE LINES
   ------------------------------------------------------------ */
CREATE TABLE IF NOT EXISTS invoice_lines (
  id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  invoice_id    INT UNSIGNED NOT NULL,

  -- Optional link back to a booking if the line originated from a completed job
  booking_id    INT UNSIGNED NULL,

  description   VARCHAR(255) NOT NULL,
  quantity      DECIMAL(10,2) NOT NULL DEFAULT 1.00,
  unit_price    DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  line_total    DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  tax_rate      DECIMAL(5,2)  NOT NULL DEFAULT 0.00,  -- e.g. 20.00 for 20%

  KEY idx_invoice_lines_invoice (invoice_id),
  KEY idx_invoice_lines_booking (booking_id),

  CONSTRAINT fk_invoice_lines_invoice
    FOREIGN KEY (invoice_id) REFERENCES invoices(id)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_invoice_lines_booking
    FOREIGN KEY (booking_id) REFERENCES bookings(id)
    ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


/* ------------------------------------------------------------
   INVOICES CORE TABLES
   ------------------------------------------------------------ */

/* 1) invoices
   - invoice_no is unique per company (e.g., INV-2025-000123)
   - booking_id is optional (manual invoice if NULL)
   - corporate_id optional for corporate billing
*/
CREATE TABLE IF NOT EXISTS invoices (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  company_id      INT UNSIGNED NOT NULL,
  invoice_no      VARCHAR(64)  NOT NULL,
  booking_id      INT UNSIGNED NULL,
  corporate_id    INT UNSIGNED NULL,

  bill_to_name    VARCHAR(255) NOT NULL,
  bill_to_email   VARCHAR(255) NULL,
  bill_to_address TEXT NULL,

  invoice_date    DATE NOT NULL,
  due_date        DATE NULL,

  currency        CHAR(3) NOT NULL DEFAULT 'GBP',

  subtotal_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  vat_rate        DECIMAL(5,2)  NOT NULL DEFAULT 0.00,   -- e.g. 20.00 for 20%
  vat_amount      DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  total_amount    DECIMAL(10,2) NOT NULL DEFAULT 0.00,

  paid_total      DECIMAL(10,2) NOT NULL DEFAULT 0.00,   -- cached sum of payments
  status          ENUM('draft','sent','paid','overdue','cancelled','void')
                  NOT NULL DEFAULT 'draft',

  notes           TEXT NULL,
  pdf_url         VARCHAR(255) NULL,

  created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  UNIQUE KEY uk_invoices_company_no (company_id, invoice_no),
  KEY idx_invoices_company_date (company_id, invoice_date),
  KEY idx_invoices_status (status),
  KEY idx_invoices_booking (booking_id),
  KEY idx_invoices_corporate (corporate_id),

  CONSTRAINT fk_invoices_company
    FOREIGN KEY (company_id) REFERENCES companies(id)
    ON DELETE CASCADE ON UPDATE CASCADE,

  CONSTRAINT fk_invoices_booking
    FOREIGN KEY (booking_id) REFERENCES bookings(id)
    ON DELETE SET NULL ON UPDATE CASCADE,

  CONSTRAINT fk_invoices_corporate
    FOREIGN KEY (corporate_id) REFERENCES corporates(id)
    ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


/* 2) invoice_lines
   - Standard invoice line items
*/
CREATE TABLE IF NOT EXISTS invoice_lines (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  invoice_id  INT UNSIGNED NOT NULL,
  description VARCHAR(255) NOT NULL,
  quantity    INT          NOT NULL DEFAULT 1,
  unit_price  DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  line_total  DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  sort_order  INT UNSIGNED  NOT NULL DEFAULT 1,

  KEY idx_lines_invoice (invoice_id, sort_order),

  CONSTRAINT fk_invoice_lines_invoice
    FOREIGN KEY (invoice_id) REFERENCES invoices(id)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


/* 3) invoice_email_logs (optional, useful for audit trail)
   - Track email sends (who/when/status)
*/
CREATE TABLE IF NOT EXISTS invoice_email_logs (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  invoice_id  INT UNSIGNED NOT NULL,
  to_email    VARCHAR(255) NOT NULL,
  subject     VARCHAR(255) NOT NULL,
  status      ENUM('queued','sent','failed') NOT NULL DEFAULT 'queued',
  error_msg   TEXT NULL,
  sent_at     DATETIME NULL DEFAULT NULL,
  created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

  KEY idx_email_logs_invoice (invoice_id, created_at),

  CONSTRAINT fk_invoice_email_logs_invoice
    FOREIGN KEY (invoice_id) REFERENCES invoices(id)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


/* 4) invoice_payments (optional but recommended)
   - Record partial/full payments and reconciliation
*/
CREATE TABLE IF NOT EXISTS invoice_payments (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  invoice_id  INT UNSIGNED NOT NULL,
  amount      DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  paid_at     DATETIME NOT NULL,
  method      VARCHAR(50) NULL,           -- e.g. Bank Transfer, Card, Cash
  reference   VARCHAR(100) NULL,          -- e.g. transaction id
  notes       TEXT NULL,
  created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

  KEY idx_payments_invoice_date (invoice_id, paid_at),

  CONSTRAINT fk_invoice_payments_invoice
    FOREIGN KEY (invoice_id) REFERENCES invoices(id)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


/* ------------------------------------------------------------
   7) COMMISSIONS (optional ledger per booking)
   ------------------------------------------------------------ */
CREATE TABLE IF NOT EXISTS commissions (
  id                INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  company_id        INT UNSIGNED NOT NULL,
  partner_id        INT UNSIGNED NOT NULL,
  booking_id        INT UNSIGNED NOT NULL,

  commission_type   ENUM('fixed','percent') NOT NULL,
  commission_value  DECIMAL(10,2) NOT NULL DEFAULT 0.00, -- rule value at time of calc
  commission_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00, -- actual amount for this booking

  status            ENUM('pending','settled','void') NOT NULL DEFAULT 'pending',
  notes             TEXT NULL,

  created_at        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

  KEY idx_comm_company_partner (company_id, partner_id),
  KEY idx_comm_booking         (booking_id),
  KEY idx_comm_status          (status),

  CONSTRAINT fk_comm_company
    FOREIGN KEY (company_id) REFERENCES companies(id)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_comm_partner
    FOREIGN KEY (partner_id) REFERENCES partners(id)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_comm_booking
    FOREIGN KEY (booking_id) REFERENCES bookings(id)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/* ------------------------------------------------------------
   8) SYSTEM SETTINGS (optional — store Quote T&Cs, etc.)
   ------------------------------------------------------------ */
CREATE TABLE IF NOT EXISTS system_settings (
  id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  company_id   INT UNSIGNED NOT NULL,
  setting_key  VARCHAR(100) NOT NULL,
  setting_value MEDIUMTEXT NULL,

  updated_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  UNIQUE KEY uk_settings_company_key (company_id, setting_key),
  KEY idx_settings_company (company_id),

  CONSTRAINT fk_settings_company
    FOREIGN KEY (company_id) REFERENCES companies(id)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/* ------------------------------------------------------------
   Seed handy defaults (optional)
   ------------------------------------------------------------ */
/* Example default T&Cs placeholder (safe to skip if you prefer)
INSERT INTO system_settings (company_id, setting_key, setting_value)
VALUES (1, 'quote_terms', 'Standard Terms & Conditions go here...')
ON DUPLICATE KEY UPDATE setting_value = VALUES(setting_value);
*/