-- ================================================================
-- FIELDLY — COMPLETE DATABASE SETUP
-- Paste this entire file into phpMyAdmin > SQL tab and click Go
-- Creates all tables + demo data in one shot
-- ================================================================
-- STEP 1: In phpMyAdmin, create a new database called "fieldly"
--         (utf8mb4 / utf8mb4_unicode_ci) then select it
-- STEP 2: Click the SQL tab
-- STEP 3: Paste everything below this line and click Go
-- ================================================================

-- ================================================================
-- FIELDLY — PRODUCTION DATABASE SCHEMA
-- Version: 2.0
-- Engine: MySQL 8.0+ / MariaDB 10.6+
-- All tables: InnoDB, utf8mb4_unicode_ci
-- Multi-tenant: every table has company_id, every query filters by it
-- ================================================================
-- Design principles:
--   • BIGINT PKs on high-volume tables (activity, payments, time, files, notes)
--   • INT UNSIGNED everywhere else
--   • deleted_at for soft-delete on main entities
--   • deleted_items for full snapshots (recycle bin)
--   • JSON columns for flexible metadata, never for queried data
--   • GENERATED STORED columns for computed values (cost, duration)
--   • FULLTEXT indexes for search
--   • No FK on activity_log.user_id (audit must survive user deletion)
--   • created_at on every table, updated_at where records mutate
--   • Composite indexes always lead with company_id
-- ================================================================

SET NAMES utf8mb4;
SET time_zone = '+00:00';
SET foreign_key_checks = 0;

-- ================================================================
-- SECTION 1: TENANCY & COMPANY
-- ================================================================

CREATE TABLE companies (
  id                  INT UNSIGNED    NOT NULL AUTO_INCREMENT,
  uuid                CHAR(36)        NOT NULL,
  name                VARCHAR(150)    NOT NULL,
  slug                VARCHAR(100)    NOT NULL,
  plan                ENUM('trial','starter','pro','enterprise') NOT NULL DEFAULT 'trial',
  trial_ends_at       DATETIME        NULL,
  logo_path           VARCHAR(500)    NULL,
  primary_colour      VARCHAR(7)      NOT NULL DEFAULT '#4f46e5',
  status              ENUM('active','suspended','cancelled') NOT NULL DEFAULT 'active',
  timezone            VARCHAR(60)     NOT NULL DEFAULT 'Europe/London',
  country_code        CHAR(2)         NOT NULL DEFAULT 'GB',
  created_at          DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at          DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_uuid   (uuid),
  UNIQUE KEY uq_slug   (slug),
  INDEX idx_status     (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Key-value settings store, grouped for efficient partial reads
-- Groups: branding | regional | numbering | invoice_defaults |
--         quote_defaults | job_defaults | tax | stripe | notifications | billing
CREATE TABLE company_settings (
  id              INT UNSIGNED    NOT NULL AUTO_INCREMENT,
  company_id      INT UNSIGNED    NOT NULL,
  setting_group   VARCHAR(60)     NOT NULL,
  setting_key     VARCHAR(100)    NOT NULL,
  setting_value   TEXT            NULL,
  updated_at      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_company_setting   (company_id, setting_group, setting_key),
  INDEX idx_company_group         (company_id, setting_group),
  CONSTRAINT fk_cs_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Company offices, depots or branches
CREATE TABLE company_locations (
  id              INT UNSIGNED    NOT NULL AUTO_INCREMENT,
  company_id      INT UNSIGNED    NOT NULL,
  name            VARCHAR(120)    NOT NULL,
  is_primary      TINYINT(1)      NOT NULL DEFAULT 0,
  line1           VARCHAR(150)    NOT NULL DEFAULT '',
  line2           VARCHAR(150)    NOT NULL DEFAULT '',
  town            VARCHAR(100)    NOT NULL DEFAULT '',
  county          VARCHAR(100)    NOT NULL DEFAULT '',
  postcode        VARCHAR(12)     NOT NULL DEFAULT '',
  country         VARCHAR(80)     NOT NULL DEFAULT 'United Kingdom',
  phone           VARCHAR(30)     NULL,
  email           VARCHAR(180)    NULL,
  created_at      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  deleted_at      DATETIME        NULL,
  PRIMARY KEY (id),
  INDEX idx_company (company_id, deleted_at),
  CONSTRAINT fk_cl_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ================================================================
-- SECTION 2: ROLES & PERMISSIONS
-- ================================================================

CREATE TABLE roles (
  id              INT UNSIGNED    NOT NULL AUTO_INCREMENT,
  company_id      INT UNSIGNED    NOT NULL,
  name            VARCHAR(80)     NOT NULL,
  description     VARCHAR(300)    NULL,
  color           VARCHAR(7)      NOT NULL DEFAULT '#6b7280',
  is_default      TINYINT(1)      NOT NULL DEFAULT 0,
  is_system       TINYINT(1)      NOT NULL DEFAULT 0,  -- system roles cannot be deleted
  sort_order      SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  created_at      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  INDEX idx_company (company_id),
  CONSTRAINT fk_role_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- One row per permission per role
-- Permissions include: view_dashboard, view_customers, edit_customers,
--   view_jobs, edit_jobs, complete_jobs, assign_jobs,
--   view_quotes, edit_quotes, view_invoices, edit_invoices,
--   record_payments, view_time, edit_time, log_time,
--   view_reports, view_calendar, edit_calendar,
--   view_settings, manage_users, manage_roles,
--   view_global_settings, edit_global_settings,
--   view_activity, manage_files, manage_notes
CREATE TABLE role_permissions (
  id              INT UNSIGNED    NOT NULL AUTO_INCREMENT,
  role_id         INT UNSIGNED    NOT NULL,
  permission      VARCHAR(80)     NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_role_perm (role_id, permission),
  INDEX idx_role          (role_id),
  CONSTRAINT fk_rp_role FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ================================================================
-- SECTION 3: USERS
-- ================================================================

CREATE TABLE users (
  id                  INT UNSIGNED    NOT NULL AUTO_INCREMENT,
  company_id          INT UNSIGNED    NOT NULL,
  role_id             INT UNSIGNED    NOT NULL,
  location_id         INT UNSIGNED    NULL,    -- home branch/office
  first_name          VARCHAR(80)     NOT NULL,
  last_name           VARCHAR(80)     NOT NULL,
  email               VARCHAR(180)    NOT NULL,
  password_hash       VARCHAR(255)    NOT NULL,
  phone               VARCHAR(30)     NULL,
  photo_path          VARCHAR(500)    NULL,
  color               VARCHAR(7)      NOT NULL DEFAULT '#4f46e5',
  initials            VARCHAR(4)      NOT NULL DEFAULT '',
  hourly_rate         DECIMAL(8,2)    NOT NULL DEFAULT 0.00,
  status              ENUM('active','inactive') NOT NULL DEFAULT 'active',
  last_login_at       DATETIME        NULL,
  password_reset_token VARCHAR(100)   NULL,
  password_reset_at   DATETIME        NULL,
  notif_prefs         JSON            NULL,    -- per-user notification preference overrides
  created_at          DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at          DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at          DATETIME        NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_company_email     (company_id, email),
  INDEX idx_company_status        (company_id, status, deleted_at),
  INDEX idx_company_role          (company_id, role_id),
  INDEX idx_company_location      (company_id, location_id),
  CONSTRAINT fk_user_company      FOREIGN KEY (company_id)   REFERENCES companies(id) ON DELETE CASCADE,
  CONSTRAINT fk_user_role         FOREIGN KEY (role_id)      REFERENCES roles(id),
  CONSTRAINT fk_user_location     FOREIGN KEY (location_id)  REFERENCES company_locations(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ================================================================
-- SECTION 4: CUSTOMERS
-- ================================================================

CREATE TABLE customers (
  id              INT UNSIGNED    NOT NULL AUTO_INCREMENT,
  company_id      INT UNSIGNED    NOT NULL,
  ref             VARCHAR(30)     NOT NULL,           -- CUST-0001
  type            ENUM('individual','business') NOT NULL DEFAULT 'individual',
  first_name      VARCHAR(80)     NOT NULL,
  last_name       VARCHAR(80)     NOT NULL,
  company_name    VARCHAR(150)    NULL,
  vat_number      VARCHAR(30)     NULL,
  email           VARCHAR(180)    NULL,
  status          ENUM('active','inactive') NOT NULL DEFAULT 'active',
  source          VARCHAR(60)     NULL,               -- how they came to you
  notes           TEXT            NULL,
  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,
  deleted_at      DATETIME        NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_company_ref       (company_id, ref),
  INDEX idx_company_status        (company_id, status, deleted_at),
  INDEX idx_company_type          (company_id, type),
  INDEX idx_company_email         (company_id, email),
  FULLTEXT idx_ft_search          (first_name, last_name, company_name, email),
  CONSTRAINT fk_cust_company      FOREIGN KEY (company_id)  REFERENCES companies(id) ON DELETE CASCADE,
  CONSTRAINT fk_cust_created_by   FOREIGN KEY (created_by)  REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE customer_phones (
  id              INT UNSIGNED    NOT NULL AUTO_INCREMENT,
  customer_id     INT UNSIGNED    NOT NULL,
  type            ENUM('Mobile','Work','Home','Fax','Other') NOT NULL DEFAULT 'Mobile',
  number          VARCHAR(30)     NOT NULL,
  is_primary      TINYINT(1)      NOT NULL DEFAULT 0,
  sort_order      TINYINT UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (id),
  INDEX idx_customer (customer_id),
  CONSTRAINT fk_cp_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Handles both billing address and multiple named site addresses
CREATE TABLE customer_addresses (
  id              INT UNSIGNED    NOT NULL AUTO_INCREMENT,
  customer_id     INT UNSIGNED    NOT NULL,
  type            ENUM('billing','site') NOT NULL DEFAULT 'billing',
  label           VARCHAR(80)     NULL,               -- "Head Office", "Site A", "Warehouse"
  line1           VARCHAR(150)    NOT NULL DEFAULT '',
  line2           VARCHAR(150)    NOT NULL DEFAULT '',
  line3           VARCHAR(150)    NOT NULL DEFAULT '',
  town            VARCHAR(100)    NOT NULL DEFAULT '',
  county          VARCHAR(100)    NOT NULL DEFAULT '',
  postcode        VARCHAR(12)     NOT NULL DEFAULT '',
  country         VARCHAR(80)     NOT NULL DEFAULT 'United Kingdom',
  what3words      VARCHAR(60)     NULL,               -- future: what3words location
  access_notes    VARCHAR(500)    NULL,               -- gate codes, parking, hazards
  is_primary      TINYINT(1)      NOT NULL DEFAULT 0,
  is_default      TINYINT(1)      NOT NULL DEFAULT 0, -- default site for new jobs
  created_at      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  deleted_at      DATETIME        NULL,
  PRIMARY KEY (id),
  INDEX idx_customer_type (customer_id, type, deleted_at),
  CONSTRAINT fk_ca_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Business customers only — multiple named contacts
CREATE TABLE customer_contacts (
  id              INT UNSIGNED    NOT NULL AUTO_INCREMENT,
  customer_id     INT UNSIGNED    NOT NULL,
  name            VARCHAR(120)    NOT NULL,
  role_title      VARCHAR(80)     NULL,
  email           VARCHAR(180)    NULL,
  phone           VARCHAR(30)     NULL,
  is_primary      TINYINT(1)      NOT NULL DEFAULT 0,
  is_billing      TINYINT(1)      NOT NULL DEFAULT 0,
  sort_order      TINYINT UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (id),
  INDEX idx_customer (customer_id),
  CONSTRAINT fk_cc_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE customer_tags (
  id              INT UNSIGNED    NOT NULL AUTO_INCREMENT,
  company_id      INT UNSIGNED    NOT NULL,
  customer_id     INT UNSIGNED    NOT NULL,
  tag             VARCHAR(60)     NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_customer_tag  (customer_id, tag),
  INDEX idx_company_tag       (company_id, tag),
  CONSTRAINT fk_ct_customer   FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE,
  CONSTRAINT fk_ct_company    FOREIGN KEY (company_id)  REFERENCES companies(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ================================================================
-- SECTION 5: CUSTOM FIELDS
-- Covers: customer | job | quote | invoice
-- ================================================================

CREATE TABLE custom_field_defs (
  id              INT UNSIGNED    NOT NULL AUTO_INCREMENT,
  company_id      INT UNSIGNED    NOT NULL,
  entity_type     ENUM('customer','job','quote','invoice') NOT NULL,
  name            VARCHAR(100)    NOT NULL,
  field_key       VARCHAR(60)     NOT NULL,           -- snake_case identifier used in code
  field_type      ENUM('text','number','date','dropdown','boolean','textarea','url','email','phone') NOT NULL DEFAULT 'text',
  dropdown_options TEXT           NULL,               -- comma-separated option values
  placeholder     VARCHAR(200)    NULL,
  help_text       VARCHAR(300)    NULL,
  required        TINYINT(1)      NOT NULL DEFAULT 0,
  applies_to      ENUM('all','business','individual') NOT NULL DEFAULT 'all',
  sort_order      SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  active          TINYINT(1)      NOT NULL DEFAULT 1,
  created_at      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_company_entity_key (company_id, entity_type, field_key),
  INDEX idx_company_entity    (company_id, entity_type, active, sort_order),
  CONSTRAINT fk_cfd_company   FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE custom_field_values (
  id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  company_id      INT UNSIGNED    NOT NULL,
  entity_type     ENUM('customer','job','quote','invoice') NOT NULL,
  entity_id       INT UNSIGNED    NOT NULL,
  field_def_id    INT UNSIGNED    NOT NULL,
  value           TEXT            NULL,
  updated_at      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_entity_field      (entity_type, entity_id, field_def_id),
  INDEX idx_company_entity        (company_id, entity_type, entity_id),
  INDEX idx_field_def             (field_def_id),
  CONSTRAINT fk_cfv_def           FOREIGN KEY (field_def_id) REFERENCES custom_field_defs(id) ON DELETE CASCADE,
  CONSTRAINT fk_cfv_company       FOREIGN KEY (company_id)   REFERENCES companies(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ================================================================
-- SECTION 6: JOB CONFIGURATION
-- ================================================================

CREATE TABLE job_types (
  id              INT UNSIGNED    NOT NULL AUTO_INCREMENT,
  company_id      INT UNSIGNED    NOT NULL,
  name            VARCHAR(80)     NOT NULL,
  color           VARCHAR(7)      NOT NULL DEFAULT '#4f46e5',
  icon            VARCHAR(30)     NULL,               -- future: icon identifier
  description     VARCHAR(300)    NULL,
  active          TINYINT(1)      NOT NULL DEFAULT 1,
  sort_order      SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  created_at      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  INDEX idx_company_active (company_id, active, sort_order),
  CONSTRAINT fk_jt_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE job_statuses (
  id              INT UNSIGNED    NOT NULL AUTO_INCREMENT,
  company_id      INT UNSIGNED    NOT NULL,
  name            VARCHAR(80)     NOT NULL,
  color           VARCHAR(7)      NOT NULL DEFAULT '#6b7280',
  is_default      TINYINT(1)      NOT NULL DEFAULT 0,
  is_complete     TINYINT(1)      NOT NULL DEFAULT 0, -- reaching this status marks job done
  is_cancelled    TINYINT(1)      NOT NULL DEFAULT 0,
  triggers_invoice TINYINT(1)     NOT NULL DEFAULT 0, -- future: auto-create invoice
  active          TINYINT(1)      NOT NULL DEFAULT 1,
  sort_order      SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  created_at      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  INDEX idx_company_active  (company_id, active, sort_order),
  INDEX idx_company_default (company_id, is_default),
  CONSTRAINT fk_js_company  FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Pricing types: basis determines how job is priced and invoiced
-- basis: hourly | fixed_amount | estimated | quoted | no_charge | custom
CREATE TABLE pricing_types (
  id              INT UNSIGNED    NOT NULL AUTO_INCREMENT,
  company_id      INT UNSIGNED    NOT NULL,
  name            VARCHAR(80)     NOT NULL,
  basis           ENUM('hourly','fixed_amount','estimated','quoted','no_charge','custom') NOT NULL DEFAULT 'fixed_amount',
  chargeable      TINYINT(1)      NOT NULL DEFAULT 1,
  description     VARCHAR(300)    NULL,
  is_default      TINYINT(1)      NOT NULL DEFAULT 0,
  active          TINYINT(1)      NOT NULL DEFAULT 1,
  sort_order      SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  created_at      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  INDEX idx_company_active  (company_id, active, sort_order),
  INDEX idx_company_default (company_id, is_default),
  CONSTRAINT fk_pt_company  FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ================================================================
-- SECTION 7: JOBS
-- ================================================================

CREATE TABLE jobs (
  id                    INT UNSIGNED    NOT NULL AUTO_INCREMENT,
  company_id            INT UNSIGNED    NOT NULL,
  customer_id           INT UNSIGNED    NOT NULL,
  type_id               INT UNSIGNED    NULL,           -- job_types.id
  status_id             INT UNSIGNED    NOT NULL,       -- job_statuses.id
  pricing_type_id       INT UNSIGNED    NULL,           -- pricing_types.id
  location_id           INT UNSIGNED    NULL,           -- company_locations.id (dispatching branch)
  site_address_id       INT UNSIGNED    NULL,           -- customer_addresses.id (type=site)
  source_quote_id       INT UNSIGNED    NULL,           -- quotes.id if created from quote
  number                VARCHAR(20)     NOT NULL,       -- JOB-0162
  title                 VARCHAR(200)    NOT NULL,
  description           TEXT            NULL,
  internal_notes        TEXT            NULL,
  customer_notes        TEXT            NULL,           -- visible to customer on job sheet
  priority              ENUM('low','normal','high','urgent') NOT NULL DEFAULT 'normal',
  estimated_mins        SMALLINT UNSIGNED NULL,
  scheduled_date        DATE            NULL,
  scheduled_time        TIME            NULL,
  pricing_rate          DECIMAL(10,2)   NULL,           -- hourly rate OR fixed price
  source                ENUM('manual','quote','recurring','duplicate','api') NOT NULL DEFAULT 'manual',
  completion_note       TEXT            NULL,
  completion_sig_path   VARCHAR(500)    NULL,           -- path to signature image
  completed_at          DATETIME        NULL,
  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,
  deleted_at            DATETIME        NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_company_number     (company_id, number),
  INDEX idx_company_status         (company_id, status_id, deleted_at),
  INDEX idx_company_customer       (company_id, customer_id),
  INDEX idx_company_scheduled      (company_id, scheduled_date, deleted_at),
  INDEX idx_company_created        (company_id, created_at),
  INDEX idx_company_priority       (company_id, priority, deleted_at),
  INDEX idx_company_type           (company_id, type_id),
  INDEX idx_source_quote           (source_quote_id),
  CONSTRAINT fk_job_company        FOREIGN KEY (company_id)       REFERENCES companies(id) ON DELETE CASCADE,
  CONSTRAINT fk_job_customer       FOREIGN KEY (customer_id)      REFERENCES customers(id),
  CONSTRAINT fk_job_type           FOREIGN KEY (type_id)          REFERENCES job_types(id) ON DELETE SET NULL,
  CONSTRAINT fk_job_status         FOREIGN KEY (status_id)        REFERENCES job_statuses(id),
  CONSTRAINT fk_job_pricing_type   FOREIGN KEY (pricing_type_id)  REFERENCES pricing_types(id) ON DELETE SET NULL,
  CONSTRAINT fk_job_location       FOREIGN KEY (location_id)      REFERENCES company_locations(id) ON DELETE SET NULL,
  CONSTRAINT fk_job_site_address   FOREIGN KEY (site_address_id)  REFERENCES customer_addresses(id) ON DELETE SET NULL,
  CONSTRAINT fk_job_created_by     FOREIGN KEY (created_by)       REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE job_assignees (
  id              INT UNSIGNED    NOT NULL AUTO_INCREMENT,
  job_id          INT UNSIGNED    NOT NULL,
  user_id         INT UNSIGNED    NOT NULL,
  is_lead         TINYINT(1)      NOT NULL DEFAULT 0,  -- lead engineer on the job
  assigned_at     DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  assigned_by     INT UNSIGNED    NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_job_user  (job_id, user_id),
  INDEX idx_user_job      (user_id),
  CONSTRAINT fk_ja_job        FOREIGN KEY (job_id)      REFERENCES jobs(id) ON DELETE CASCADE,
  CONSTRAINT fk_ja_user       FOREIGN KEY (user_id)     REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_ja_assigned   FOREIGN KEY (assigned_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ================================================================
-- SECTION 8: VISITS & CHECK-INS
-- ================================================================

CREATE TABLE visits (
  id              INT UNSIGNED    NOT NULL AUTO_INCREMENT,
  company_id      INT UNSIGNED    NOT NULL,
  job_id          INT UNSIGNED    NOT NULL,
  title           VARCHAR(200)    NULL,
  status          ENUM('scheduled','in_progress','completed','cancelled','no_show') NOT NULL DEFAULT 'scheduled',
  scheduled_start DATETIME        NULL,
  scheduled_end   DATETIME        NULL,
  actual_start    DATETIME        NULL,
  actual_end      DATETIME        NULL,
  duration_mins   SMALLINT UNSIGNED NULL,  -- actual duration
  notes           TEXT            NULL,
  travel_mins     SMALLINT UNSIGNED NULL,  -- future: travel time tracking
  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,
  PRIMARY KEY (id),
  INDEX idx_company_job         (company_id, job_id),
  INDEX idx_company_start       (company_id, scheduled_start),
  INDEX idx_company_status      (company_id, status),
  CONSTRAINT fk_visit_company   FOREIGN KEY (company_id)  REFERENCES companies(id) ON DELETE CASCADE,
  CONSTRAINT fk_visit_job       FOREIGN KEY (job_id)      REFERENCES jobs(id) ON DELETE CASCADE,
  CONSTRAINT fk_visit_created   FOREIGN KEY (created_by)  REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE visit_assignees (
  id              INT UNSIGNED    NOT NULL AUTO_INCREMENT,
  visit_id        INT UNSIGNED    NOT NULL,
  user_id         INT UNSIGNED    NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_visit_user    (visit_id, user_id),
  INDEX idx_user              (user_id),
  CONSTRAINT fk_va_visit      FOREIGN KEY (visit_id) REFERENCES visits(id) ON DELETE CASCADE,
  CONSTRAINT fk_va_user       FOREIGN KEY (user_id)  REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE check_ins (
  id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  company_id      INT UNSIGNED    NOT NULL,
  job_id          INT UNSIGNED    NOT NULL,
  visit_id        INT UNSIGNED    NULL,
  user_id         INT UNSIGNED    NOT NULL,
  checked_in_at   DATETIME        NOT NULL,
  checked_out_at  DATETIME        NULL,
  duration_mins   SMALLINT UNSIGNED NULL,
  lat_in          DECIMAL(10,7)   NULL,
  lng_in          DECIMAL(10,7)   NULL,
  lat_out         DECIMAL(10,7)   NULL,   -- future: location at checkout
  lng_out         DECIMAL(10,7)   NULL,
  notes           VARCHAR(500)    NULL,
  auto_time_entry TINYINT(1)      NOT NULL DEFAULT 1,
  created_at      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  INDEX idx_company_job      (company_id, job_id),
  INDEX idx_company_user     (company_id, user_id, checked_in_at),
  INDEX idx_active_checkin   (company_id, user_id, checked_out_at),  -- find open check-ins
  CONSTRAINT fk_ci_company   FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
  CONSTRAINT fk_ci_job       FOREIGN KEY (job_id)     REFERENCES jobs(id),
  CONSTRAINT fk_ci_visit     FOREIGN KEY (visit_id)   REFERENCES visits(id) ON DELETE SET NULL,
  CONSTRAINT fk_ci_user      FOREIGN KEY (user_id)    REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ================================================================
-- SECTION 9: CHECKLISTS
-- ================================================================

CREATE TABLE checklist_templates (
  id              INT UNSIGNED    NOT NULL AUTO_INCREMENT,
  company_id      INT UNSIGNED    NOT NULL,
  name            VARCHAR(100)    NOT NULL,
  job_type_id     INT UNSIGNED    NULL,   -- auto-apply when this job type selected
  description     VARCHAR(300)    NULL,
  created_at      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  INDEX idx_company           (company_id),
  INDEX idx_job_type          (company_id, job_type_id),
  CONSTRAINT fk_clt_company   FOREIGN KEY (company_id)  REFERENCES companies(id) ON DELETE CASCADE,
  CONSTRAINT fk_clt_job_type  FOREIGN KEY (job_type_id) REFERENCES job_types(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE checklist_template_items (
  id              INT UNSIGNED    NOT NULL AUTO_INCREMENT,
  template_id     INT UNSIGNED    NOT NULL,
  text            VARCHAR(500)    NOT NULL,
  is_required     TINYINT(1)      NOT NULL DEFAULT 0,   -- future: must complete before status change
  sort_order      SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (id),
  INDEX idx_template (template_id),
  CONSTRAINT fk_clti_template FOREIGN KEY (template_id) REFERENCES checklist_templates(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE job_checklists (
  id              INT UNSIGNED    NOT NULL AUTO_INCREMENT,
  job_id          INT UNSIGNED    NOT NULL,
  company_id      INT UNSIGNED    NOT NULL,
  template_id     INT UNSIGNED    NULL,   -- source template (for reporting)
  text            VARCHAR(500)    NOT NULL,
  completed       TINYINT(1)      NOT NULL DEFAULT 0,
  completed_by    INT UNSIGNED    NULL,
  completed_at    DATETIME        NULL,
  photo_path      VARCHAR(500)    NULL,   -- future: photo evidence for checklist item
  notes           VARCHAR(500)    NULL,   -- future: notes per item
  sort_order      SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (id),
  INDEX idx_job               (job_id),
  INDEX idx_company_job       (company_id, job_id),
  CONSTRAINT fk_jcl_job       FOREIGN KEY (job_id)       REFERENCES jobs(id) ON DELETE CASCADE,
  CONSTRAINT fk_jcl_company   FOREIGN KEY (company_id)   REFERENCES companies(id) ON DELETE CASCADE,
  CONSTRAINT fk_jcl_user      FOREIGN KEY (completed_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ================================================================
-- SECTION 10: QUOTES
-- ================================================================

CREATE TABLE quotes (
  id              INT UNSIGNED    NOT NULL AUTO_INCREMENT,
  company_id      INT UNSIGNED    NOT NULL,
  customer_id     INT UNSIGNED    NOT NULL,
  job_id          INT UNSIGNED    NULL,
  number          VARCHAR(20)     NOT NULL,  -- QUO-0051
  title           VARCHAR(200)    NOT NULL DEFAULT '',
  status          ENUM('draft','sent','accepted','declined','expired','converted') NOT NULL DEFAULT 'draft',
  valid_until     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,
  total           DECIMAL(10,2)   NOT NULL DEFAULT 0.00,
  notes           TEXT            NULL,
  terms           TEXT            NULL,
  footer_text     TEXT            NULL,
  sent_at         DATETIME        NULL,
  accepted_at     DATETIME        NULL,
  declined_at     DATETIME        NULL,
  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,
  deleted_at      DATETIME        NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_company_number     (company_id, number),
  INDEX idx_company_status         (company_id, status, deleted_at),
  INDEX idx_company_customer       (company_id, customer_id),
  INDEX idx_company_created        (company_id, created_at),
  INDEX idx_company_valid          (company_id, valid_until),
  CONSTRAINT fk_quote_company      FOREIGN KEY (company_id)  REFERENCES companies(id) ON DELETE CASCADE,
  CONSTRAINT fk_quote_customer     FOREIGN KEY (customer_id) REFERENCES customers(id),
  CONSTRAINT fk_quote_job          FOREIGN KEY (job_id)      REFERENCES jobs(id) ON DELETE SET NULL,
  CONSTRAINT fk_quote_created_by   FOREIGN KEY (created_by)  REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE quote_items (
  id              INT UNSIGNED    NOT NULL AUTO_INCREMENT,
  quote_id        INT UNSIGNED    NOT NULL,
  type            ENUM('labour','material','expense','other') NOT NULL DEFAULT 'other',  -- future: categorised line items
  description     VARCHAR(500)    NOT NULL,
  qty             DECIMAL(10,3)   NOT NULL DEFAULT 1.000,
  unit            VARCHAR(20)     NULL,                -- "hrs", "m²", "each"
  unit_price      DECIMAL(10,2)   NOT NULL DEFAULT 0.00,
  discount        DECIMAL(5,2)    NOT NULL DEFAULT 0.00,  -- percentage
  tax_rate_id     INT UNSIGNED    NULL,                -- tax_rates.id
  tax_rate        DECIMAL(5,2)    NOT NULL DEFAULT 20.00,
  line_total      DECIMAL(10,2)   NOT NULL DEFAULT 0.00,
  sort_order      SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (id),
  INDEX idx_quote (quote_id),
  CONSTRAINT fk_qi_quote FOREIGN KEY (quote_id) REFERENCES quotes(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ================================================================
-- SECTION 11: INVOICES & PAYMENTS
-- ================================================================

CREATE TABLE invoices (
  id                    INT UNSIGNED    NOT NULL AUTO_INCREMENT,
  company_id            INT UNSIGNED    NOT NULL,
  customer_id           INT UNSIGNED    NOT NULL,
  job_id                INT UNSIGNED    NULL,
  quote_id              INT UNSIGNED    NULL,
  number                VARCHAR(20)     NOT NULL,  -- INV-0089
  title                 VARCHAR(200)    NOT NULL DEFAULT '',
  status                ENUM('draft','sent','part_paid','paid','overdue','void') NOT NULL DEFAULT 'draft',
  due_date              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,
  total                 DECIMAL(10,2)   NOT NULL DEFAULT 0.00,
  amount_paid           DECIMAL(10,2)   NOT NULL DEFAULT 0.00,
  balance_due           DECIMAL(10,2)   NOT NULL DEFAULT 0.00,
  notes                 TEXT            NULL,
  footer_text           TEXT            NULL,
  payment_terms         VARCHAR(300)    NULL,
  bank_details          TEXT            NULL,
  purchase_order_no     VARCHAR(60)     NULL,      -- customer PO number
  stripe_payment_intent VARCHAR(100)    NULL,
  stripe_payment_link   VARCHAR(500)    NULL,      -- future: hosted payment page
  sent_at               DATETIME        NULL,
  paid_at               DATETIME        NULL,
  voided_at             DATETIME        NULL,
  voided_by             INT UNSIGNED    NULL,
  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,
  deleted_at            DATETIME        NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_company_number     (company_id, number),
  INDEX idx_company_status         (company_id, status, deleted_at),
  INDEX idx_company_customer       (company_id, customer_id),
  INDEX idx_company_due            (company_id, due_date, status),
  INDEX idx_company_created        (company_id, created_at),
  INDEX idx_stripe_intent          (stripe_payment_intent),
  CONSTRAINT fk_inv_company        FOREIGN KEY (company_id)  REFERENCES companies(id) ON DELETE CASCADE,
  CONSTRAINT fk_inv_customer       FOREIGN KEY (customer_id) REFERENCES customers(id),
  CONSTRAINT fk_inv_job            FOREIGN KEY (job_id)      REFERENCES jobs(id) ON DELETE SET NULL,
  CONSTRAINT fk_inv_quote          FOREIGN KEY (quote_id)    REFERENCES quotes(id) ON DELETE SET NULL,
  CONSTRAINT fk_inv_created_by     FOREIGN KEY (created_by)  REFERENCES users(id) ON DELETE SET NULL,
  CONSTRAINT fk_inv_voided_by      FOREIGN KEY (voided_by)   REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE invoice_items (
  id              INT UNSIGNED    NOT NULL AUTO_INCREMENT,
  invoice_id      INT UNSIGNED    NOT NULL,
  type            ENUM('labour','material','expense','other') NOT NULL DEFAULT 'other',
  description     VARCHAR(500)    NOT NULL,
  qty             DECIMAL(10,3)   NOT NULL DEFAULT 1.000,
  unit            VARCHAR(20)     NULL,
  unit_price      DECIMAL(10,2)   NOT NULL DEFAULT 0.00,
  discount        DECIMAL(5,2)    NOT NULL DEFAULT 0.00,
  tax_rate_id     INT UNSIGNED    NULL,
  tax_rate        DECIMAL(5,2)    NOT NULL DEFAULT 20.00,
  line_total      DECIMAL(10,2)   NOT NULL DEFAULT 0.00,
  sort_order      SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (id),
  INDEX idx_invoice (invoice_id),
  CONSTRAINT fk_ii_invoice FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE tax_rates (
  id              INT UNSIGNED    NOT NULL AUTO_INCREMENT,
  company_id      INT UNSIGNED    NOT NULL,
  name            VARCHAR(60)     NOT NULL,
  rate            DECIMAL(5,2)    NOT NULL,
  is_default      TINYINT(1)      NOT NULL DEFAULT 0,
  active          TINYINT(1)      NOT NULL DEFAULT 1,
  country_code    CHAR(2)         NULL,              -- future: per-country tax rates
  created_at      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  INDEX idx_company (company_id, active),
  CONSTRAINT fk_tr_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE payments (
  id                  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  company_id          INT UNSIGNED    NOT NULL,
  invoice_id          INT UNSIGNED    NOT NULL,
  amount              DECIMAL(10,2)   NOT NULL,
  method              ENUM('cash','bank_transfer','card','cheque','direct_debit','stripe','bacs','paypal','crypto','other') NOT NULL,
  reference           VARCHAR(100)    NULL,
  stripe_charge_id    VARCHAR(100)    NULL,
  stripe_refund_id    VARCHAR(100)    NULL,  -- future: refund tracking
  paid_at             DATETIME        NOT NULL,
  recorded_by         INT UNSIGNED    NULL,
  notes               VARCHAR(500)    NULL,
  created_at          DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  deleted_at          DATETIME        NULL,
  PRIMARY KEY (id),
  INDEX idx_invoice          (invoice_id, deleted_at),
  INDEX idx_company_paid_at  (company_id, paid_at, deleted_at),
  INDEX idx_company_method   (company_id, method),
  INDEX idx_stripe           (stripe_charge_id),
  CONSTRAINT fk_pay_company    FOREIGN KEY (company_id)   REFERENCES companies(id) ON DELETE CASCADE,
  CONSTRAINT fk_pay_invoice    FOREIGN KEY (invoice_id)   REFERENCES invoices(id),
  CONSTRAINT fk_pay_recorded   FOREIGN KEY (recorded_by)  REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ================================================================
-- SECTION 12: TIME TRACKING
-- ================================================================

CREATE TABLE time_entries (
  id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  company_id      INT UNSIGNED    NOT NULL,
  job_id          INT UNSIGNED    NULL,
  visit_id        INT UNSIGNED    NULL,
  check_in_id     BIGINT UNSIGNED NULL,
  user_id         INT UNSIGNED    NOT NULL,
  date            DATE            NOT NULL,
  start_time      TIME            NULL,
  end_time        TIME            NULL,
  duration_mins   SMALLINT UNSIGNED NOT NULL,
  billable        TINYINT(1)      NOT NULL DEFAULT 1,
  rate            DECIMAL(8,2)    NOT NULL DEFAULT 0.00,
  -- Stored computed column: avoids calculating in PHP on every invoice
  cost            DECIMAL(10,2)   GENERATED ALWAYS AS (ROUND((duration_mins / 60) * rate, 2)) STORED,
  approved        TINYINT(1)      NOT NULL DEFAULT 0,  -- future: time approval workflow
  approved_by     INT UNSIGNED    NULL,
  notes           VARCHAR(500)    NULL,
  created_at      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  deleted_at      DATETIME        NULL,
  PRIMARY KEY (id),
  INDEX idx_company_user_date  (company_id, user_id, date, deleted_at),
  INDEX idx_company_job        (company_id, job_id, deleted_at),
  INDEX idx_company_date       (company_id, date),
  INDEX idx_company_billable   (company_id, billable, deleted_at),
  CONSTRAINT fk_te_company     FOREIGN KEY (company_id)  REFERENCES companies(id) ON DELETE CASCADE,
  CONSTRAINT fk_te_job         FOREIGN KEY (job_id)      REFERENCES jobs(id) ON DELETE SET NULL,
  CONSTRAINT fk_te_visit       FOREIGN KEY (visit_id)    REFERENCES visits(id) ON DELETE SET NULL,
  CONSTRAINT fk_te_user        FOREIGN KEY (user_id)     REFERENCES users(id),
  CONSTRAINT fk_te_checkin     FOREIGN KEY (check_in_id) REFERENCES check_ins(id) ON DELETE SET NULL,
  CONSTRAINT fk_te_approver    FOREIGN KEY (approved_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ================================================================
-- SECTION 13: SCHEDULING
-- ================================================================

CREATE TABLE working_hours (
  id              INT UNSIGNED    NOT NULL AUTO_INCREMENT,
  company_id      INT UNSIGNED    NOT NULL,
  user_id         INT UNSIGNED    NOT NULL,
  day_of_week     TINYINT UNSIGNED NOT NULL,  -- 0=Sunday, 1=Monday … 6=Saturday
  start_time      TIME            NULL,        -- NULL means day off
  end_time        TIME            NULL,
  break_mins      SMALLINT UNSIGNED NOT NULL DEFAULT 0,  -- future: break time
  PRIMARY KEY (id),
  UNIQUE KEY uq_user_day      (user_id, day_of_week),
  INDEX idx_company_user      (company_id, user_id),
  CONSTRAINT fk_wh_company    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
  CONSTRAINT fk_wh_user       FOREIGN KEY (user_id)    REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE leave_entries (
  id              INT UNSIGNED    NOT NULL AUTO_INCREMENT,
  company_id      INT UNSIGNED    NOT NULL,
  user_id         INT UNSIGNED    NOT NULL,
  type            ENUM('holiday','sick','training','compassionate','parental','unpaid','other') NOT NULL,
  start_date      DATE            NOT NULL,
  end_date        DATE            NOT NULL,
  days_taken      DECIMAL(4,1)    NOT NULL DEFAULT 1.0,  -- supports half-days
  notes           VARCHAR(500)    NULL,
  approved        TINYINT(1)      NOT NULL DEFAULT 0,
  approved_by     INT UNSIGNED    NULL,
  approved_at     DATETIME        NULL,
  rejected        TINYINT(1)      NOT NULL DEFAULT 0,
  rejection_reason VARCHAR(300)   NULL,
  created_at      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  INDEX idx_company_user   (company_id, user_id),
  INDEX idx_company_dates  (company_id, start_date, end_date),
  INDEX idx_company_type   (company_id, type, approved),
  CONSTRAINT fk_le_company    FOREIGN KEY (company_id)  REFERENCES companies(id) ON DELETE CASCADE,
  CONSTRAINT fk_le_user       FOREIGN KEY (user_id)     REFERENCES users(id),
  CONSTRAINT fk_le_approver   FOREIGN KEY (approved_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ================================================================
-- SECTION 14: CONTENT — NOTES & FILES
-- ================================================================

-- Polymorphic: entity_type = job | quote | invoice | customer | visit
CREATE TABLE notes (
  id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  company_id      INT UNSIGNED    NOT NULL,
  entity_type     VARCHAR(20)     NOT NULL,
  entity_id       INT UNSIGNED    NOT NULL,
  text            TEXT            NOT NULL,
  is_internal     TINYINT(1)      NOT NULL DEFAULT 0,  -- staff-only note
  pinned          TINYINT(1)      NOT NULL DEFAULT 0,  -- future: pin important notes
  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,
  deleted_at      DATETIME        NULL,
  PRIMARY KEY (id),
  INDEX idx_entity        (entity_type, entity_id, deleted_at),
  INDEX idx_company_type  (company_id, entity_type),
  INDEX idx_company_user  (company_id, created_by),
  CONSTRAINT fk_note_company    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
  CONSTRAINT fk_note_created_by FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE files (
  id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  company_id      INT UNSIGNED    NOT NULL,
  entity_type     VARCHAR(20)     NOT NULL,
  entity_id       INT UNSIGNED    NOT NULL,
  filename        VARCHAR(200)    NOT NULL,            -- stored name on disk
  original_name   VARCHAR(200)    NOT NULL,            -- original upload name
  mime_type       VARCHAR(100)    NOT NULL,
  file_size       INT UNSIGNED    NOT NULL DEFAULT 0,  -- bytes
  disk            VARCHAR(20)     NOT NULL DEFAULT 'local',  -- local | s3 | gcs
  disk_path       VARCHAR(500)    NOT NULL,
  thumbnail_path  VARCHAR(500)    NULL,                -- future: image thumbnails
  is_public       TINYINT(1)      NOT NULL DEFAULT 0,  -- future: customer-visible portal
  uploaded_by     INT UNSIGNED    NULL,
  created_at      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  deleted_at      DATETIME        NULL,
  PRIMARY KEY (id),
  INDEX idx_entity        (entity_type, entity_id, deleted_at),
  INDEX idx_company       (company_id, entity_type),
  CONSTRAINT fk_file_company      FOREIGN KEY (company_id)  REFERENCES companies(id) ON DELETE CASCADE,
  CONSTRAINT fk_file_uploaded_by  FOREIGN KEY (uploaded_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ================================================================
-- SECTION 15: ACTIVITY LOG & NOTIFICATIONS
-- Full audit trail — every meaningful action logged here
-- ================================================================

CREATE TABLE activity_log (
  id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  company_id      INT UNSIGNED    NOT NULL,
  type            VARCHAR(60)     NOT NULL,   -- e.g. job.created, invoice.paid, customer.deleted
  entity_type     VARCHAR(20)     NOT NULL,
  entity_id       INT UNSIGNED    NULL,
  entity_ref      VARCHAR(30)     NULL,       -- JOB-0156, INV-0089 for display without joins
  user_id         INT UNSIGNED    NULL,       -- NO FK — audit must survive user deletion
  user_name       VARCHAR(160)    NULL,       -- denormalised for same reason
  description     VARCHAR(500)    NOT NULL,
  meta            JSON            NULL,       -- old status, new status, amount, diff etc
  ip_address      VARCHAR(45)     NULL,
  user_agent      VARCHAR(300)    NULL,
  created_at      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  INDEX idx_company_entity   (company_id, entity_type, entity_id),
  INDEX idx_company_type     (company_id, type),
  INDEX idx_company_user     (company_id, user_id),
  INDEX idx_company_created  (company_id, created_at),
  CONSTRAINT fk_al_company   FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE notifications (
  id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  company_id      INT UNSIGNED    NOT NULL,
  user_id         INT UNSIGNED    NOT NULL,
  activity_log_id BIGINT UNSIGNED NOT NULL,
  type            VARCHAR(60)     NOT NULL,   -- mirrors activity_log.type for filtering without join
  read_at         DATETIME        NULL,
  created_at      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  INDEX idx_user_read         (user_id, read_at),
  INDEX idx_company_user      (company_id, user_id, created_at),
  CONSTRAINT fk_notif_company   FOREIGN KEY (company_id)      REFERENCES companies(id) ON DELETE CASCADE,
  CONSTRAINT fk_notif_user      FOREIGN KEY (user_id)         REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_notif_activity  FOREIGN KEY (activity_log_id) REFERENCES activity_log(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ================================================================
-- SECTION 16: RECYCLE BIN
-- snapshot_json holds the full record + child rows at time of delete
-- Supports restore and permanent delete
-- ================================================================

CREATE TABLE deleted_items (
  id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  company_id      INT UNSIGNED    NOT NULL,
  entity_type     VARCHAR(30)     NOT NULL,     -- job | invoice | quote | customer | payment | time_entry
  entity_id       BIGINT UNSIGNED NOT NULL,
  entity_ref      VARCHAR(30)     NULL,         -- JOB-0156, INV-0089
  entity_summary  VARCHAR(300)    NULL,         -- "Nolan Construction – £570" for UI display
  snapshot_json   LONGTEXT        NOT NULL,     -- full row data + child rows
  deleted_by      INT UNSIGNED    NULL,
  deleted_at      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  restored_at     DATETIME        NULL,
  restored_by     INT UNSIGNED    NULL,
  purged_at       DATETIME        NULL,         -- permanently deleted
  PRIMARY KEY (id),
  INDEX idx_company_type   (company_id, entity_type, deleted_at),
  INDEX idx_company_entity (company_id, entity_id),
  CONSTRAINT fk_di_company   FOREIGN KEY (company_id)  REFERENCES companies(id) ON DELETE CASCADE,
  CONSTRAINT fk_di_deleter   FOREIGN KEY (deleted_by)  REFERENCES users(id) ON DELETE SET NULL,
  CONSTRAINT fk_di_restorer  FOREIGN KEY (restored_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ================================================================
-- SECTION 17: INTEGRATIONS & EXTERNAL SERVICES
-- ================================================================

CREATE TABLE integrations (
  id                    INT UNSIGNED    NOT NULL AUTO_INCREMENT,
  company_id            INT UNSIGNED    NOT NULL,
  provider              ENUM(
    'stripe','xero','quickbooks','sage','freeagent',
    'gocardless','paypal',
    'google_calendar','outlook_calendar',
    'twilio','sendgrid','mailgun',
    'zapier','webhooks',
    'google_maps','what3words'
  ) NOT NULL,
  credentials_encrypted TEXT            NULL,    -- AES-256 encrypted; key stored in .env not DB
  settings_json         JSON            NULL,    -- non-sensitive config (webhook URLs, preferences)
  active                TINYINT(1)      NOT NULL DEFAULT 0,
  connected_at          DATETIME        NULL,
  last_sync_at          DATETIME        NULL,
  last_error            VARCHAR(500)    NULL,
  created_at            DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at            DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_company_provider  (company_id, provider),
  CONSTRAINT fk_int_company       FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Webhook event log for integrations (Stripe, Zapier etc)
CREATE TABLE webhook_events (
  id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  company_id      INT UNSIGNED    NULL,          -- NULL if company not yet resolved
  provider        VARCHAR(30)     NOT NULL,
  event_type      VARCHAR(100)    NOT NULL,
  external_id     VARCHAR(100)    NULL,          -- provider's event ID (idempotency)
  payload         LONGTEXT        NULL,          -- raw payload for debugging
  status          ENUM('pending','processed','failed','ignored') NOT NULL DEFAULT 'pending',
  error           VARCHAR(500)    NULL,
  processed_at    DATETIME        NULL,
  created_at      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_provider_event    (provider, external_id),
  INDEX idx_company_status        (company_id, status),
  INDEX idx_provider_type         (provider, event_type),
  CONSTRAINT fk_we_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ================================================================
-- SECTION 18: BILLING & SUBSCRIPTIONS
-- ================================================================

CREATE TABLE subscriptions (
  id                   INT UNSIGNED    NOT NULL AUTO_INCREMENT,
  company_id           INT UNSIGNED    NOT NULL,
  plan                 ENUM('starter','pro','enterprise') NOT NULL,
  status               ENUM('active','past_due','cancelled','trialling','paused') NOT NULL,
  user_limit           SMALLINT UNSIGNED NOT NULL DEFAULT 0,   -- 0 = unlimited
  stripe_sub_id        VARCHAR(100)    NULL,
  stripe_cust_id       VARCHAR(100)    NULL,
  current_period_start DATETIME        NULL,
  current_period_end   DATETIME        NULL,
  cancel_at_period_end TINYINT(1)      NOT NULL DEFAULT 0,
  cancelled_at         DATETIME        NULL,
  created_at           DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at           DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_company       (company_id),
  INDEX idx_stripe_sub        (stripe_sub_id),
  INDEX idx_stripe_cust       (stripe_cust_id),
  INDEX idx_status            (status),
  CONSTRAINT fk_sub_company   FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Usage metrics for billing and plan enforcement
CREATE TABLE usage_metrics (
  id              INT UNSIGNED    NOT NULL AUTO_INCREMENT,
  company_id      INT UNSIGNED    NOT NULL,
  metric          VARCHAR(60)     NOT NULL,   -- active_users | jobs_this_month | storage_gb
  value           DECIMAL(12,4)   NOT NULL DEFAULT 0,
  period_start    DATE            NOT NULL,
  period_end      DATE            NOT NULL,
  recorded_at     DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_company_metric_period (company_id, metric, period_start),
  INDEX idx_company_metric    (company_id, metric),
  CONSTRAINT fk_um_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ================================================================
-- SECTION 19: API & SESSIONS (CodeIgniter)
-- ================================================================

CREATE TABLE api_tokens (
  id              INT UNSIGNED    NOT NULL AUTO_INCREMENT,
  company_id      INT UNSIGNED    NOT NULL,
  user_id         INT UNSIGNED    NULL,        -- NULL for company-level tokens
  name            VARCHAR(80)     NOT NULL,    -- "Mobile App", "Zapier"
  token_hash      VARCHAR(64)     NOT NULL,    -- SHA-256 of token
  abilities       JSON            NULL,        -- scoped permissions for this token
  last_used_at    DATETIME        NULL,
  expires_at      DATETIME        NULL,
  created_at      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  revoked_at      DATETIME        NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_token_hash    (token_hash),
  INDEX idx_company           (company_id, revoked_at),
  CONSTRAINT fk_at_company    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
  CONSTRAINT fk_at_user       FOREIGN KEY (user_id)    REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- CI4 session table (standard format)
CREATE TABLE ci_sessions (
  id              VARCHAR(128)    NOT NULL,
  ip_address      VARCHAR(45)     NOT NULL,
  timestamp       DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  data            BLOB            NOT NULL,
  PRIMARY KEY (id),
  KEY idx_timestamp (timestamp)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ================================================================
-- SECTION 20: FUTURE-PROOFING TABLES
-- (schema defined now so migrations are minimal later)
-- ================================================================

-- Customer portal access — customers log in to view jobs/invoices/pay
CREATE TABLE customer_portal_users (
  id              INT UNSIGNED    NOT NULL AUTO_INCREMENT,
  company_id      INT UNSIGNED    NOT NULL,
  customer_id     INT UNSIGNED    NOT NULL,
  email           VARCHAR(180)    NOT NULL,
  password_hash   VARCHAR(255)    NOT NULL,
  status          ENUM('active','invited','suspended') NOT NULL DEFAULT 'invited',
  invite_token    VARCHAR(100)    NULL,
  last_login_at   DATETIME        NULL,
  created_at      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_company_email (company_id, email),
  INDEX idx_company_customer  (company_id, customer_id),
  CONSTRAINT fk_cpu_company   FOREIGN KEY (company_id)  REFERENCES companies(id) ON DELETE CASCADE,
  CONSTRAINT fk_cpu_customer  FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Recurring job schedules
CREATE TABLE recurring_schedules (
  id              INT UNSIGNED    NOT NULL AUTO_INCREMENT,
  company_id      INT UNSIGNED    NOT NULL,
  customer_id     INT UNSIGNED    NOT NULL,
  type_id         INT UNSIGNED    NULL,
  status_id       INT UNSIGNED    NULL,
  title           VARCHAR(200)    NOT NULL,
  description     TEXT            NULL,
  frequency       ENUM('daily','weekly','fortnightly','monthly','quarterly','yearly') NOT NULL,
  interval_value  SMALLINT UNSIGNED NOT NULL DEFAULT 1,
  day_of_week     TINYINT UNSIGNED NULL,
  day_of_month    TINYINT UNSIGNED NULL,
  start_date      DATE            NOT NULL,
  end_date        DATE            NULL,
  next_run_date   DATE            NULL,
  last_run_date   DATE            NULL,
  active          TINYINT(1)      NOT NULL DEFAULT 1,
  created_at      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  INDEX idx_company_next  (company_id, next_run_date, active),
  INDEX idx_company_cust  (company_id, customer_id),
  CONSTRAINT fk_rs_company    FOREIGN KEY (company_id)  REFERENCES companies(id) ON DELETE CASCADE,
  CONSTRAINT fk_rs_customer   FOREIGN KEY (customer_id) REFERENCES customers(id),
  CONSTRAINT fk_rs_type       FOREIGN KEY (type_id)     REFERENCES job_types(id) ON DELETE SET NULL,
  CONSTRAINT fk_rs_status     FOREIGN KEY (status_id)   REFERENCES job_statuses(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Materials and parts catalogue
CREATE TABLE catalogue_items (
  id              INT UNSIGNED    NOT NULL AUTO_INCREMENT,
  company_id      INT UNSIGNED    NOT NULL,
  sku             VARCHAR(60)     NULL,
  name            VARCHAR(200)    NOT NULL,
  description     TEXT            NULL,
  category        VARCHAR(80)     NULL,
  unit            VARCHAR(20)     NULL,        -- "each", "m²", "litre"
  unit_cost       DECIMAL(10,2)   NOT NULL DEFAULT 0.00,
  unit_price      DECIMAL(10,2)   NOT NULL DEFAULT 0.00,
  tax_rate        DECIMAL(5,2)    NOT NULL DEFAULT 20.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,
  PRIMARY KEY (id),
  INDEX idx_company_active    (company_id, active),
  INDEX idx_company_category  (company_id, category),
  FULLTEXT idx_ft_search      (name, description, sku),
  CONSTRAINT fk_ci_company_1 FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Email template storage (invoice emails, quote emails etc)
CREATE TABLE email_templates (
  id              INT UNSIGNED    NOT NULL AUTO_INCREMENT,
  company_id      INT UNSIGNED    NOT NULL,
  type            ENUM('invoice','quote','job_complete','payment_receipt','reminder','welcome','custom') NOT NULL,
  name            VARCHAR(100)    NOT NULL,
  subject         VARCHAR(200)    NOT NULL,
  body_html       TEXT            NOT NULL,
  body_text       TEXT            NULL,
  is_default      TINYINT(1)      NOT NULL DEFAULT 0,
  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,
  PRIMARY KEY (id),
  INDEX idx_company_type (company_id, type, active),
  CONSTRAINT fk_et_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Sent email log
CREATE TABLE email_log (
  id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  company_id      INT UNSIGNED    NOT NULL,
  entity_type     VARCHAR(20)     NULL,
  entity_id       INT UNSIGNED    NULL,
  to_email        VARCHAR(180)    NOT NULL,
  to_name         VARCHAR(120)    NULL,
  subject         VARCHAR(200)    NOT NULL,
  template_id     INT UNSIGNED    NULL,
  provider        VARCHAR(30)     NULL,        -- sendgrid | mailgun | smtp
  provider_id     VARCHAR(100)    NULL,        -- provider message ID
  status          ENUM('queued','sent','delivered','opened','bounced','failed') NOT NULL DEFAULT 'queued',
  opened_at       DATETIME        NULL,
  sent_at         DATETIME        NULL,
  error           VARCHAR(500)    NULL,
  created_at      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  INDEX idx_company_entity    (company_id, entity_type, entity_id),
  INDEX idx_company_status    (company_id, status),
  INDEX idx_provider          (provider, provider_id),
  CONSTRAINT fk_el_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET foreign_key_checks = 1;

-- ================================================================
-- SUMMARY
-- 26 tables across 20 sections:
--   1.  companies               -- tenants
--   2.  company_settings        -- key/value config store
--   3.  company_locations       -- offices/depots/branches
--   4.  roles                   -- access roles
--   5.  role_permissions        -- one row per permission per role
--   6.  users                   -- staff accounts
--   7.  customers               -- customer records
--   8.  customer_phones         -- multiple phone numbers
--   9.  customer_addresses      -- billing + multiple site addresses
--   10. customer_contacts       -- named contacts (business only)
--   11. customer_tags           -- freeform tags
--   12. custom_field_defs       -- field definitions (all 4 entity types)
--   13. custom_field_values     -- field values per record
--   14. job_types               -- customisable job type list
--   15. job_statuses            -- customisable status list with completion/cancel flags
--   16. pricing_types           -- pricing basis (hourly/fixed/estimated/quoted/free)
--   17. jobs                    -- job records
--   18. job_assignees           -- many engineers per job
--   19. visits                  -- scheduled appointments within jobs
--   20. visit_assignees         -- engineers per visit
--   21. check_ins               -- real-time clock-in/out with GPS
--   22. checklist_templates     -- reusable checklist templates
--   23. checklist_template_items
--   24. job_checklists          -- actual checklist items on a job
--   25. quotes                  -- quote records
--   26. quote_items             -- line items
--   27. invoices                -- invoice records
--   28. invoice_items           -- line items
--   29. tax_rates               -- per-company tax rate library
--   30. payments                -- payment records (separate from invoices)
--   31. time_entries            -- billable/non-billable time (GENERATED cost column)
--   32. working_hours           -- per-user schedule (per day, NULL = day off)
--   33. leave_entries           -- holiday/sick/training leave
--   34. notes                   -- polymorphic notes (job/quote/invoice/customer/visit)
--   35. files                   -- polymorphic file attachments
--   36. activity_log            -- full audit trail (NO FK on user_id intentionally)
--   37. notifications           -- per-user notification inbox
--   38. deleted_items           -- recycle bin with JSON snapshot
--   39. integrations            -- Stripe, Xero, GoCardless, calendar, etc
--   40. webhook_events          -- inbound webhook log for idempotency
--   41. subscriptions           -- Stripe subscription per company
--   42. usage_metrics           -- billing/plan usage tracking
--   43. api_tokens              -- scoped API access tokens
--   44. ci_sessions             -- CodeIgniter 4 session storage
--   45. customer_portal_users   -- future: customer self-service portal
--   46. recurring_schedules     -- future: repeat job automation
--   47. catalogue_items         -- future: materials/parts library
--   48. email_templates         -- future: branded email templates
--   49. email_log               -- future: sent email audit trail
--
-- BIGINT PKs: activity_log, notifications, payments, time_entries,
--             check_ins, notes, files, deleted_items, webhook_events, email_log
-- All others: INT UNSIGNED
-- Every table: company_id composite index (tenant isolation)
-- Soft delete: deleted_at on customers, users, jobs, quotes, invoices, payments,
--              time_entries, notes, files
-- Hard delete with snapshot: deleted_items table
-- ================================================================

-- ================================================================
-- DEMO DATA — Bristol Heating & Plumbing Ltd
-- ================================================================

-- ================================================================
-- FIELDLY — DEMO DATABASE
-- Run AFTER fieldly_schema.sql
-- Creates a realistic demo company with customers, jobs, quotes,
-- invoices, payments, time entries, activity and more
-- ================================================================

SET NAMES utf8mb4;
SET foreign_key_checks = 0;

-- ── Company ──────────────────────────────────────────────────────
INSERT INTO companies (id, uuid, name, slug, plan, primary_colour, status, timezone, country_code, created_at, updated_at) VALUES
(1, 'a1b2c3d4e5f6a1b2c3d4e5f6a1b2c3d4', 'Bristol Heating & Plumbing Ltd', 'bristol-hp', 'pro', '#4f46e5', 'active', 'Europe/London', 'GB', NOW(), NOW());

-- ── Settings ─────────────────────────────────────────────────────
INSERT INTO company_settings (company_id, setting_group, setting_key, setting_value, updated_at) VALUES
(1,'branding','company_name','Bristol Heating & Plumbing Ltd',NOW()),
(1,'branding','theme_id','indigo',NOW()),
(1,'branding','dark_mode','0',NOW()),
(1,'regional','country','GB',NOW()),
(1,'regional','currency','GBP',NOW()),
(1,'regional','language','en-GB',NOW()),
(1,'regional','timezone','Europe/London',NOW()),
(1,'regional','date_format','DD/MM/YYYY',NOW()),
(1,'numbering','cust_prefix','CUST-',NOW()),
(1,'numbering','cust_next','7',NOW()),
(1,'numbering','job_prefix','JOB-',NOW()),
(1,'numbering','job_next','9',NOW()),
(1,'numbering','quote_prefix','QUO-',NOW()),
(1,'numbering','quote_next','5',NOW()),
(1,'numbering','inv_prefix','INV-',NOW()),
(1,'numbering','inv_next','7',NOW()),
(1,'invoice_defaults','payment_terms','Payment due within 30 days of invoice date',NOW()),
(1,'invoice_defaults','bank_details','Account: Bristol HP Ltd\nSort Code: 20-00-00\nAccount No: 12345678',NOW()),
(1,'invoice_defaults','footer_text','Thank you for choosing Bristol Heating & Plumbing Ltd',NOW()),
(1,'quote_defaults','valid_days','30',NOW()),
(1,'stripe','enabled','0',NOW()),
(1,'stripe','publishable_key','',NOW());

-- ── Tax Rates ─────────────────────────────────────────────────────
INSERT INTO tax_rates (id, company_id, name, rate, is_default, active, created_at) VALUES
(1, 1, 'Standard Rate (20%)', 20.00, 1, 1, NOW()),
(2, 1, 'Reduced Rate (5%)',    5.00, 0, 1, NOW()),
(3, 1, 'Zero Rated (0%)',      0.00, 0, 1, NOW());

-- ── Roles ─────────────────────────────────────────────────────────
INSERT INTO roles (id, company_id, name, color, is_default, is_system, sort_order, created_at, updated_at) VALUES
(1, 1, 'Admin',          '#ef4444', 0, 1, 0, NOW(), NOW()),
(2, 1, 'Office Manager', '#f97316', 0, 1, 1, NOW(), NOW()),
(3, 1, 'Engineer',       '#22c55e', 1, 1, 2, NOW(), NOW()),
(4, 1, 'Accounts',       '#eab308', 0, 1, 3, NOW(), NOW()),
(5, 1, 'Supervisor',     '#3b82f6', 0, 1, 4, NOW(), NOW());

-- Admin permissions (all)
INSERT INTO role_permissions (role_id, permission) VALUES
(1,'view_dashboard'),(1,'view_customers'),(1,'edit_customers'),
(1,'view_jobs'),(1,'edit_jobs'),(1,'complete_jobs'),(1,'assign_jobs'),
(1,'view_quotes'),(1,'edit_quotes'),(1,'view_invoices'),(1,'edit_invoices'),
(1,'record_payments'),(1,'view_time'),(1,'edit_time'),(1,'log_time'),
(1,'view_reports'),(1,'view_calendar'),(1,'edit_calendar'),(1,'view_settings'),
(1,'manage_users'),(1,'manage_roles'),(1,'view_global_settings'),(1,'edit_global_settings'),
(1,'view_activity'),(1,'manage_files'),(1,'manage_notes');

-- Engineer permissions
INSERT INTO role_permissions (role_id, permission) VALUES
(3,'view_dashboard'),(3,'view_jobs'),(3,'complete_jobs'),(3,'log_time'),
(3,'view_time'),(3,'view_calendar'),(3,'manage_files'),(3,'manage_notes'),(3,'view_activity');

-- Accounts permissions
INSERT INTO role_permissions (role_id, permission) VALUES
(4,'view_dashboard'),(4,'view_invoices'),(4,'edit_invoices'),(4,'record_payments'),
(4,'view_quotes'),(4,'view_reports'),(4,'view_customers'),(4,'view_activity');

-- Office Manager permissions
INSERT INTO role_permissions (role_id, permission) VALUES
(2,'view_dashboard'),(2,'view_customers'),(2,'edit_customers'),
(2,'view_jobs'),(2,'edit_jobs'),(2,'assign_jobs'),(2,'view_quotes'),(2,'edit_quotes'),
(2,'view_invoices'),(2,'edit_invoices'),(2,'record_payments'),(2,'view_time'),
(2,'edit_time'),(2,'log_time'),(2,'view_reports'),(2,'view_calendar'),(2,'edit_calendar'),
(2,'view_settings'),(2,'manage_users'),(2,'view_global_settings'),
(2,'view_activity'),(2,'manage_files'),(2,'manage_notes');

-- ── Job Types ──────────────────────────────────────────────────────
INSERT INTO job_types (id, company_id, name, color, active, sort_order, created_at) VALUES
(1, 1, 'Boiler Service',       '#4f46e5', 1, 0, NOW()),
(2, 1, 'Boiler Repair',        '#ef4444', 1, 1, NOW()),
(3, 1, 'Boiler Installation',  '#f97316', 1, 2, NOW()),
(4, 1, 'Plumbing Repair',      '#06b6d4', 1, 3, NOW()),
(5, 1, 'Radiator Installation','#8b5cf6', 1, 4, NOW()),
(6, 1, 'Emergency Call Out',   '#dc2626', 1, 5, NOW()),
(7, 1, 'Gas Safety Certificate','#22c55e',1, 6, NOW()),
(8, 1, 'Survey / Quote Visit', '#64748b', 1, 7, NOW());

-- ── Job Statuses ───────────────────────────────────────────────────
INSERT INTO job_statuses (id, company_id, name, color, is_default, is_complete, is_cancelled, active, sort_order, created_at) VALUES
(1, 1, 'Scheduled',   '#3b82f6', 1, 0, 0, 1, 0, NOW()),
(2, 1, 'In Progress', '#f97316', 0, 0, 0, 1, 1, NOW()),
(3, 1, 'On Hold',     '#eab308', 0, 0, 0, 1, 2, NOW()),
(4, 1, 'Completed',   '#22c55e', 0, 1, 0, 1, 3, NOW()),
(5, 1, 'Cancelled',   '#6b7280', 0, 0, 1, 1, 4, NOW()),
(6, 1, 'Quoted',      '#8b5cf6', 0, 0, 0, 1, 5, NOW());

-- ── Pricing Types ──────────────────────────────────────────────────
INSERT INTO pricing_types (id, company_id, name, basis, chargeable, description, is_default, active, sort_order, created_at) VALUES
(1, 1, 'Hourly Rate',   'hourly',       1, 'Charged by time worked',         1, 1, 0, NOW()),
(2, 1, 'Fixed Price',   'fixed_amount', 1, 'Set price agreed upfront',       0, 1, 1, NOW()),
(3, 1, 'Per Quote',     'quoted',       1, 'Price defined in linked quote',   0, 1, 2, NOW()),
(4, 1, 'No Charge',     'no_charge',    0, 'Free of charge',                 0, 1, 3, NOW()),
(5, 1, 'Warranty',      'no_charge',    0, 'Covered under warranty',         0, 1, 4, NOW()),
(6, 1, 'Estimated',     'estimated',    1, 'Estimate — may vary',             0, 1, 5, NOW()),
(7, 1, 'Call Out Fee',  'fixed_amount', 1, 'Fixed call-out charge',           0, 1, 6, NOW());

-- ── Users ──────────────────────────────────────────────────────────
-- password for all users: password
INSERT INTO users (id, company_id, role_id, first_name, last_name, email, password_hash, phone, color, initials, hourly_rate, status, created_at, updated_at) VALUES
(1, 1, 1, 'Sarah',   'Mitchell', 'sarah@bristolhp.co.uk',  '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', '07700 900001', '#4f46e5', 'SM', 0.00,  'active', NOW(), NOW()),
(2, 1, 2, 'Tom',     'Bradley',  'tom@bristolhp.co.uk',    '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', '07700 900002', '#f97316', 'TB', 0.00,  'active', NOW(), NOW()),
(3, 1, 3, 'James',   'Hartley',  'james@bristolhp.co.uk',  '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', '07700 900003', '#22c55e', 'JH', 45.00, 'active', NOW(), NOW()),
(4, 1, 3, 'Priya',   'Sharma',   'priya@bristolhp.co.uk',  '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', '07700 900004', '#8b5cf6', 'PS', 42.00, 'active', NOW(), NOW()),
(5, 1, 4, 'Rachel',  'Owen',     'rachel@bristolhp.co.uk', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', '07700 900005', '#eab308', 'RO', 0.00,  'active', NOW(), NOW());

-- Working hours (Mon–Fri 08:00–17:00 for all)
INSERT INTO working_hours (company_id, user_id, day_of_week, start_time, end_time) VALUES
(1,1,1,'08:00:00','17:00:00'),(1,1,2,'08:00:00','17:00:00'),(1,1,3,'08:00:00','17:00:00'),(1,1,4,'08:00:00','17:00:00'),(1,1,5,'08:00:00','17:00:00'),
(1,2,1,'08:00:00','17:00:00'),(1,2,2,'08:00:00','17:00:00'),(1,2,3,'08:00:00','17:00:00'),(1,2,4,'08:00:00','17:00:00'),(1,2,5,'08:00:00','17:00:00'),
(1,3,1,'07:30:00','16:30:00'),(1,3,2,'07:30:00','16:30:00'),(1,3,3,'07:30:00','16:30:00'),(1,3,4,'07:30:00','16:30:00'),(1,3,5,'07:30:00','16:30:00'),
(1,4,1,'08:00:00','17:00:00'),(1,4,2,'08:00:00','17:00:00'),(1,4,3,'08:00:00','17:00:00'),(1,4,4,'08:00:00','17:00:00'),(1,4,5,'08:00:00','17:00:00'),
(1,5,1,'09:00:00','17:00:00'),(1,5,2,'09:00:00','17:00:00'),(1,5,3,'09:00:00','17:00:00'),(1,5,4,'09:00:00','17:00:00'),(1,5,5,'09:00:00','17:00:00');

-- ── Customers ──────────────────────────────────────────────────────
INSERT INTO customers (id, company_id, ref, type, first_name, last_name, company_name, vat_number, email, status, notes, created_by, created_at, updated_at) VALUES
(1, 1, 'CUST-0001', 'individual', 'David',    'Thornton',  NULL,                      NULL,           'david.thornton@gmail.com',      'active', 'Long-term customer. Prefers morning appointments.', 1, DATE_SUB(NOW(),INTERVAL 18 MONTH), NOW()),
(2, 1, 'CUST-0002', 'business',   'Nolan',    'Construction','Nolan Construction Ltd', 'GB123456789',  'accounts@nolanconstruction.co.uk','active', 'Large commercial account. Net 30 payment terms.',  1, DATE_SUB(NOW(),INTERVAL 14 MONTH), NOW()),
(3, 1, 'CUST-0003', 'individual', 'Margaret', 'Davies',    NULL,                      NULL,           'margaret.davies@hotmail.co.uk', 'active', NULL,                                               1, DATE_SUB(NOW(),INTERVAL 12 MONTH), NOW()),
(4, 1, 'CUST-0004', 'business',   'Green',    'Lettings',  'Green Lettings Ltd',      'GB987654321',  'maintenance@greenlettings.co.uk','active','Manages 24 rental properties. Regular contract.',  1, DATE_SUB(NOW(),INTERVAL 10 MONTH), NOW()),
(5, 1, 'CUST-0005', 'individual', 'Robert',   'Ashford',   NULL,                      NULL,           'r.ashford@outlook.com',         'active', NULL,                                               2, DATE_SUB(NOW(),INTERVAL 6 MONTH),  NOW()),
(6, 1, 'CUST-0006', 'individual', 'Helen',    'Patel',     NULL,                      NULL,           'helen.patel@gmail.com',         'active', 'New customer — referred by David Thornton.',        2, DATE_SUB(NOW(),INTERVAL 2 MONTH),  NOW());

-- Phones
INSERT INTO customer_phones (customer_id, type, number, is_primary, sort_order) VALUES
(1, 'Mobile', '07711 100001', 1, 0),
(1, 'Home',   '0117 900 0001',0, 1),
(2, 'Work',   '0117 900 0002', 1, 0),
(2, 'Mobile', '07711 100002', 0, 1),
(3, 'Mobile', '07711 100003', 1, 0),
(4, 'Work',   '0117 900 0004', 1, 0),
(5, 'Mobile', '07711 100005', 1, 0),
(6, 'Mobile', '07711 100006', 1, 0);

-- Addresses
INSERT INTO customer_addresses (customer_id, type, label, line1, town, county, postcode, country, is_primary, is_default, created_at) VALUES
(1, 'billing', 'Home', '14 Harbour View',          'Bristol',   'Bristol',  'BS1 4RB', 'United Kingdom', 1, 1, NOW()),
(1, 'site',    'Home', '14 Harbour View',          'Bristol',   'Bristol',  'BS1 4RB', 'United Kingdom', 1, 1, NOW()),
(2, 'billing', 'Head Office', '45 Temple Street',  'Bristol',   'Bristol',  'BS1 6AA', 'United Kingdom', 1, 0, NOW()),
(2, 'site',    'Main Office', '45 Temple Street',  'Bristol',   'Bristol',  'BS1 6AA', 'United Kingdom', 1, 1, NOW()),
(2, 'site',    'Site B',      'Industrial Park, Unit 4', 'Keynsham', 'Somerset', 'BS31 2AB', 'United Kingdom', 0, 0, NOW()),
(3, 'billing', 'Home', '8 Maple Close',            'Clifton',   'Bristol',  'BS8 2TH', 'United Kingdom', 1, 1, NOW()),
(3, 'site',    'Home', '8 Maple Close',            'Clifton',   'Bristol',  'BS8 2TH', 'United Kingdom', 1, 1, NOW()),
(4, 'billing', 'Office', '22 Queens Road',         'Bristol',   'Bristol',  'BS8 1RE', 'United Kingdom', 1, 0, NOW()),
(4, 'site',    'Property 1', '15 Park Street',     'Bristol',   'Bristol',  'BS1 5PE', 'United Kingdom', 0, 1, NOW()),
(4, 'site',    'Property 2', '7 Victoria Road',    'Fishponds', 'Bristol',  'BS16 3LU','United Kingdom', 0, 0, NOW()),
(5, 'billing', 'Home', '3 Redland Grove',          'Redland',   'Bristol',  'BS6 6PX', 'United Kingdom', 1, 1, NOW()),
(5, 'site',    'Home', '3 Redland Grove',          'Redland',   'Bristol',  'BS6 6PX', 'United Kingdom', 1, 1, NOW()),
(6, 'billing', 'Home', '19 Cotham Hill',           'Bristol',   'Bristol',  'BS6 6JY', 'United Kingdom', 1, 1, NOW()),
(6, 'site',    'Home', '19 Cotham Hill',           'Bristol',   'Bristol',  'BS6 6JY', 'United Kingdom', 1, 1, NOW());

-- Contacts (business only)
INSERT INTO customer_contacts (customer_id, name, role_title, email, phone, is_primary, is_billing, sort_order) VALUES
(2, 'James Nolan',    'Managing Director', 'james@nolanconstruction.co.uk', '07711 200001', 1, 0, 0),
(2, 'Sue Chambers',   'Accounts Manager',  'sue@nolanconstruction.co.uk',   '07711 200002', 0, 1, 1),
(4, 'Oliver Green',   'Director',          'oliver@greenlettings.co.uk',    '07711 300001', 1, 1, 0),
(4, 'Amy Prescott',   'Property Manager',  'amy@greenlettings.co.uk',       '07711 300002', 0, 0, 1);

-- Tags
INSERT INTO customer_tags (company_id, customer_id, tag) VALUES
(1, 1, 'Residential'), (1, 1, 'Annual Service'),
(1, 2, 'Commercial'),  (1, 2, 'Priority'),    (1, 2, 'Net-30'),
(1, 3, 'Residential'), (1, 3, 'OAP Discount'),
(1, 4, 'Commercial'),  (1, 4, 'Contract'),    (1, 4, 'Landlord'),
(1, 5, 'Residential'),
(1, 6, 'Residential'), (1, 6, 'Referral');

-- ── Checklist Templates ────────────────────────────────────────────
INSERT INTO checklist_templates (id, company_id, name, job_type_id, created_at, updated_at) VALUES
(1, 1, 'Boiler Service Checklist', 1, NOW(), NOW()),
(2, 1, 'Gas Safety Certificate Checks', 7, NOW(), NOW()),
(3, 1, 'New Boiler Installation', 3, NOW(), NOW());

INSERT INTO checklist_template_items (template_id, text, sort_order) VALUES
(1, 'Check boiler pressure (should be 1–1.5 bar)', 0),
(1, 'Inspect flue and ventilation', 1),
(1, 'Clean burner and heat exchanger', 2),
(1, 'Check all seals and gaskets', 3),
(1, 'Test thermostat and controls', 4),
(1, 'Check gas pressure and flow rate', 5),
(1, 'Run boiler through full cycle', 6),
(1, 'Check for CO leaks', 7),
(1, 'Customer signature obtained', 8),
(2, 'Check all gas appliances', 0),
(2, 'Test flues and ventilation', 1),
(2, 'Check for gas leaks', 2),
(2, 'Verify safe isolation', 3),
(2, 'Complete certificate documentation', 4),
(3, 'Site survey and measurements', 0),
(3, 'Old boiler removed and disposed', 1),
(3, 'New boiler mounted and connected', 2),
(3, 'System flushed and inhibitor added', 3),
(3, 'Flue fitted and tested', 4),
(3, 'Commission and set controls', 5),
(3, 'Customer handed documentation', 6);

-- ── Jobs ───────────────────────────────────────────────────────────
INSERT INTO jobs (id, company_id, customer_id, type_id, status_id, pricing_type_id, number, title, description, priority, estimated_mins, scheduled_date, scheduled_time, pricing_rate, source, completed_at, created_by, created_at, updated_at) VALUES
-- Completed
(1, 1, 1, 1, 4, 1, 'JOB-0001', 'Annual Boiler Service', 'Annual service on Vaillant combi boiler', 'normal', 90, DATE_SUB(CURDATE(),INTERVAL 45 DAY), '09:00:00', 45.00, 'manual', DATE_SUB(NOW(),INTERVAL 44 DAY), 1, DATE_SUB(NOW(),INTERVAL 50 DAY), NOW()),
(2, 1, 2, 4, 4, 2, 'JOB-0002', 'Fix Burst Pipe - Site B', 'Burst pipe in utility area at industrial park site', 'high', 120, DATE_SUB(CURDATE(),INTERVAL 30 DAY), '08:00:00', 350.00, 'manual', DATE_SUB(NOW(),INTERVAL 29 DAY), 1, DATE_SUB(NOW(),INTERVAL 32 DAY), NOW()),
(3, 1, 3, 7, 4, 2, 'JOB-0003', 'Gas Safety Certificate', 'Annual gas safety inspection and certificate', 'normal', 60, DATE_SUB(CURDATE(),INTERVAL 20 DAY), '14:00:00', 85.00, 'manual', DATE_SUB(NOW(),INTERVAL 19 DAY), 2, DATE_SUB(NOW(),INTERVAL 22 DAY), NOW()),
(4, 1, 4, 3, 4, 3, 'JOB-0004', 'New Boiler - 15 Park Street', 'Supply and install new Worcester Bosch combi boiler', 'normal', 480, DATE_SUB(CURDATE(),INTERVAL 14 DAY), '08:00:00', 0.00, 'quote', DATE_SUB(NOW(),INTERVAL 12 DAY), 1, DATE_SUB(NOW(),INTERVAL 20 DAY), NOW()),
-- Active / scheduled
(5, 1, 5, 2, 1, 1, 'JOB-0005', 'Boiler Not Firing', 'Worcester boiler not lighting — error code E9', 'high', 90, DATE_ADD(CURDATE(),INTERVAL 1 DAY), '10:00:00', 45.00, 'manual', NULL, 2, DATE_SUB(NOW(),INTERVAL 3 DAY), NOW()),
(6, 1, 4, 1, 1, 1, 'JOB-0006', 'Annual Service - Victoria Rd', 'Annual boiler service at rental property', 'normal', 90, DATE_ADD(CURDATE(),INTERVAL 3 DAY), '13:00:00', 45.00, 'manual', NULL, 2, DATE_SUB(NOW(),INTERVAL 1 DAY), NOW()),
(7, 1, 6, 6, 2, 7, 'JOB-0007', 'Emergency - No Hot Water', 'Customer has no hot water — possible diverter valve fault', 'urgent', 60, CURDATE(), '11:30:00', 75.00, 'manual', NULL, 2, NOW(), NOW()),
-- Quoted / pending
(8, 1, 1, 3, 6, 3, 'JOB-0008', 'Boiler Replacement Quote', 'Survey visit for boiler replacement quotation', 'normal', 60, DATE_ADD(CURDATE(),INTERVAL 7 DAY), '09:00:00', 0.00, 'manual', NULL, 1, DATE_SUB(NOW(),INTERVAL 2 DAY), NOW());

-- Assignees
INSERT INTO job_assignees (job_id, user_id, assigned_at, assigned_by) VALUES
(1, 3, DATE_SUB(NOW(),INTERVAL 50 DAY), 1),
(2, 3, DATE_SUB(NOW(),INTERVAL 32 DAY), 1),
(2, 4, DATE_SUB(NOW(),INTERVAL 32 DAY), 1),
(3, 4, DATE_SUB(NOW(),INTERVAL 22 DAY), 2),
(4, 3, DATE_SUB(NOW(),INTERVAL 20 DAY), 1),
(4, 4, DATE_SUB(NOW(),INTERVAL 20 DAY), 1),
(5, 3, DATE_SUB(NOW(),INTERVAL 3 DAY),  2),
(6, 4, DATE_SUB(NOW(),INTERVAL 1 DAY),  2),
(7, 3, NOW(),                           2),
(8, 4, DATE_SUB(NOW(),INTERVAL 2 DAY),  1);

-- Checklists for completed jobs
INSERT INTO job_checklists (job_id, company_id, template_id, text, completed, completed_by, completed_at, sort_order) VALUES
(1, 1, 1, 'Check boiler pressure (should be 1–1.5 bar)', 1, 3, DATE_SUB(NOW(),INTERVAL 44 DAY), 0),
(1, 1, 1, 'Inspect flue and ventilation',               1, 3, DATE_SUB(NOW(),INTERVAL 44 DAY), 1),
(1, 1, 1, 'Clean burner and heat exchanger',            1, 3, DATE_SUB(NOW(),INTERVAL 44 DAY), 2),
(1, 1, 1, 'Check all seals and gaskets',                1, 3, DATE_SUB(NOW(),INTERVAL 44 DAY), 3),
(1, 1, 1, 'Test thermostat and controls',               1, 3, DATE_SUB(NOW(),INTERVAL 44 DAY), 4),
(1, 1, 1, 'Check gas pressure and flow rate',           1, 3, DATE_SUB(NOW(),INTERVAL 44 DAY), 5),
(1, 1, 1, 'Run boiler through full cycle',              1, 3, DATE_SUB(NOW(),INTERVAL 44 DAY), 6),
(1, 1, 1, 'Check for CO leaks',                         1, 3, DATE_SUB(NOW(),INTERVAL 44 DAY), 7),
(1, 1, 1, 'Customer signature obtained',                1, 3, DATE_SUB(NOW(),INTERVAL 44 DAY), 8),
(3, 1, 2, 'Check all gas appliances',                   1, 4, DATE_SUB(NOW(),INTERVAL 19 DAY), 0),
(3, 1, 2, 'Test flues and ventilation',                 1, 4, DATE_SUB(NOW(),INTERVAL 19 DAY), 1),
(3, 1, 2, 'Check for gas leaks',                        1, 4, DATE_SUB(NOW(),INTERVAL 19 DAY), 2),
(3, 1, 2, 'Verify safe isolation',                      1, 4, DATE_SUB(NOW(),INTERVAL 19 DAY), 3),
(3, 1, 2, 'Complete certificate documentation',         1, 4, DATE_SUB(NOW(),INTERVAL 19 DAY), 4),
-- Active job checklists (not completed)
(7, 1, NULL, 'Diagnose fault — check diverter valve',   0, NULL, NULL, 0),
(7, 1, NULL, 'Check pressure and expansion vessel',     0, NULL, NULL, 1),
(7, 1, NULL, 'Test hot water output',                   0, NULL, NULL, 2),
(7, 1, NULL, 'Replace faulty parts if required',        0, NULL, NULL, 3);

-- ── Time Entries ───────────────────────────────────────────────────
INSERT INTO time_entries (company_id, job_id, user_id, date, start_time, end_time, duration_mins, billable, rate, notes, created_at) VALUES
(1, 1, 3, DATE_SUB(CURDATE(),INTERVAL 44 DAY), '09:00:00', '10:45:00', 105, 1, 45.00, 'Annual service completed', NOW()),
(1, 2, 3, DATE_SUB(CURDATE(),INTERVAL 29 DAY), '08:00:00', '10:30:00', 150, 1, 45.00, 'Locate and repair burst pipe', NOW()),
(1, 2, 4, DATE_SUB(CURDATE(),INTERVAL 29 DAY), '08:30:00', '10:00:00', 90,  1, 42.00, 'Assist with repair', NOW()),
(1, 3, 4, DATE_SUB(CURDATE(),INTERVAL 19 DAY), '14:00:00', '15:15:00', 75,  1, 42.00, 'Gas safety inspection', NOW()),
(1, 4, 3, DATE_SUB(CURDATE(),INTERVAL 12 DAY), '08:00:00', '13:00:00', 300, 1, 45.00, 'New boiler installation day 1', NOW()),
(1, 4, 4, DATE_SUB(CURDATE(),INTERVAL 12 DAY), '08:00:00', '12:00:00', 240, 1, 42.00, 'Assist with installation', NOW()),
(1, 4, 3, DATE_SUB(CURDATE(),INTERVAL 11 DAY), '08:00:00', '10:30:00', 150, 1, 45.00, 'Commission and handover', NOW());

-- ── Quotes ─────────────────────────────────────────────────────────
INSERT INTO quotes (id, company_id, customer_id, job_id, number, title, status, valid_until, subtotal, tax_total, total, notes, sent_at, accepted_at, created_by, created_at, updated_at) VALUES
(1, 1, 4, 4, 'QUO-0001', 'New Boiler Installation - 15 Park Street', 'accepted', DATE_ADD(CURDATE(),INTERVAL 16 DAY), 1958.33, 391.67, 2350.00, 'Worcester Bosch 30i combi boiler. 5 year parts & labour warranty included.', DATE_SUB(NOW(),INTERVAL 22 DAY), DATE_SUB(NOW(),INTERVAL 18 DAY), 1, DATE_SUB(NOW(),INTERVAL 25 DAY), NOW()),
(2, 1, 1, 8, 'QUO-0002', 'Boiler Replacement - 14 Harbour View',    'sent',     DATE_ADD(CURDATE(),INTERVAL 28 DAY), 1625.00, 325.00, 1950.00, 'Vaillant ecoTEC plus 30kW combi. Includes full system flush and new controls.', DATE_SUB(NOW(),INTERVAL 1 DAY), NULL, 1, DATE_SUB(NOW(),INTERVAL 2 DAY), NOW()),
(3, 1, 2, NULL, 'QUO-0003', 'Annual Service Contract - All Sites',   'draft',    DATE_ADD(CURDATE(),INTERVAL 30 DAY), 2083.33, 416.67, 2500.00, 'Annual service contract for 3 properties. Includes priority call-outs.', NULL, NULL, 2, DATE_SUB(NOW(),INTERVAL 4 DAY), NOW()),
(4, 1, 3, NULL, 'QUO-0004', 'Radiator Upgrade - 8 Maple Close',     'declined', DATE_SUB(CURDATE(),INTERVAL 5 DAY), 541.67, 108.33, 650.00, '4x new radiators with TRVs. System powerflush included.', DATE_SUB(NOW(),INTERVAL 25 DAY), NULL, 2, DATE_SUB(NOW(),INTERVAL 30 DAY), NOW());

INSERT INTO quote_items (quote_id, description, qty, unit_price, discount, tax_rate, line_total, sort_order) VALUES
(1, 'Worcester Bosch 30i Combi Boiler',           1.00, 950.00, 0, 20, 950.00,  0),
(1, 'Labour — removal and installation',         8.00,  75.00, 0, 20, 600.00,  1),
(1, 'Flue kit and fittings',                     1.00, 125.00, 0, 20, 125.00,  2),
(1, 'System flush and inhibitor',                1.00, 150.00, 0, 20, 150.00,  3),
(1, 'Magnetic filter installation',              1.00, 133.33, 0, 20, 133.33,  4),
(2, 'Vaillant ecoTEC plus 30kW Combi Boiler',    1.00, 875.00, 0, 20, 875.00,  0),
(2, 'Labour — removal and installation',         8.00,  75.00, 0, 20, 600.00,  1),
(2, 'Flue kit and fittings',                     1.00, 100.00, 0, 20, 100.00,  2),
(2, 'System flush and inhibitor',                1.00,  50.00, 0, 20, 50.00,   3),
(3, 'Annual boiler service — 3 properties',      3.00, 350.00, 0, 20, 1050.00, 0),
(3, 'Priority call-out (up to 3/year)',          1.00, 583.33, 0, 20, 583.33,  1),
(3, 'Gas safety certificates — 3 properties',    3.00, 150.00, 0, 20, 450.00,  2),
(4, 'Radiator 600x1200mm with TRV',              4.00,  95.00, 0, 20, 380.00,  0),
(4, 'Labour — fit and connect 4 radiators',      2.00,  55.00, 0, 20, 110.00,  1),
(4, 'Powerflush — full system',                  1.00,  51.67, 0, 20, 51.67,   2);

-- ── Invoices ───────────────────────────────────────────────────────
INSERT INTO invoices (id, company_id, customer_id, job_id, quote_id, number, title, status, due_date, subtotal, tax_total, total, amount_paid, balance_due, notes, payment_terms, bank_details, sent_at, paid_at, created_by, created_at, updated_at) VALUES
-- Paid
(1, 1, 1, 1, NULL, 'INV-0001', 'Annual Boiler Service - 14 Harbour View', 'paid',     DATE_SUB(CURDATE(),INTERVAL 15 DAY), 78.75, 15.75, 94.50,  94.50,  0.00,   NULL, 'Payment due within 30 days', 'Sort: 20-00-00 / Acc: 12345678', DATE_SUB(NOW(),INTERVAL 44 DAY), DATE_SUB(NOW(),INTERVAL 30 DAY), 1, DATE_SUB(NOW(),INTERVAL 44 DAY), NOW()),
(2, 1, 2, 2, NULL, 'INV-0002', 'Emergency Pipe Repair - Site B',          'paid',     DATE_SUB(CURDATE(),INTERVAL 1 DAY),  350.00, 70.00, 420.00, 420.00, 0.00,   NULL, 'Payment due within 30 days', 'Sort: 20-00-00 / Acc: 12345678', DATE_SUB(NOW(),INTERVAL 29 DAY), DATE_SUB(NOW(),INTERVAL 10 DAY), 1, DATE_SUB(NOW(),INTERVAL 29 DAY), NOW()),
(3, 1, 3, 3, NULL, 'INV-0003', 'Gas Safety Certificate - 8 Maple Close',  'paid',     DATE_SUB(CURDATE(),INTERVAL 10 DAY), 70.83, 14.17, 85.00,  85.00,  0.00,   NULL, 'Payment due within 30 days', 'Sort: 20-00-00 / Acc: 12345678', DATE_SUB(NOW(),INTERVAL 19 DAY), DATE_SUB(NOW(),INTERVAL 5 DAY),  5, DATE_SUB(NOW(),INTERVAL 19 DAY), NOW()),
-- Invoiced from accepted quote
(4, 1, 4, 4, 1,    'INV-0004', 'New Boiler Installation - 15 Park Street','part_paid',DATE_ADD(CURDATE(),INTERVAL 16 DAY), 1958.33,391.67,2350.00,1175.00,1175.00,'50% deposit paid on acceptance. Balance on completion.','Payment due within 30 days', 'Sort: 20-00-00 / Acc: 12345678', DATE_SUB(NOW(),INTERVAL 12 DAY), NULL, 1, DATE_SUB(NOW(),INTERVAL 12 DAY), NOW()),
-- Overdue
(5, 1, 2, NULL, 3,  'INV-0005', 'Annual Service Contract - Nolan Group',  'overdue',  DATE_SUB(CURDATE(),INTERVAL 5 DAY),  2083.33,416.67,2500.00,0.00,   2500.00,NULL, 'Payment due within 30 days', 'Sort: 20-00-00 / Acc: 12345678', DATE_SUB(NOW(),INTERVAL 35 DAY), NULL, 5, DATE_SUB(NOW(),INTERVAL 35 DAY), NOW()),
-- Draft
(6, 1, 5, NULL, NULL,'INV-0006', 'Boiler Repair - 3 Redland Grove',       'draft',    DATE_ADD(CURDATE(),INTERVAL 30 DAY), 0.00,   0.00,  0.00,   0.00,   0.00,   NULL, 'Payment due within 30 days', 'Sort: 20-00-00 / Acc: 12345678', NULL, NULL, 2, DATE_SUB(NOW(),INTERVAL 1 DAY), NOW());

-- Invoice items
INSERT INTO invoice_items (invoice_id, description, qty, unit_price, discount, tax_rate, line_total, sort_order) VALUES
(1, 'Annual boiler service — Vaillant combi',     1.00, 78.75,   0, 20, 78.75,   0),
(2, 'Emergency pipe repair — labour',             2.50, 45.00,   0, 20, 112.50,  0),
(2, 'Materials — pipe, fittings, lagging',        1.00, 237.50,  0, 20, 237.50,  1),
(3, 'Gas safety certificate and inspection',      1.00, 70.83,   0, 20, 70.83,   0),
(4, 'Worcester Bosch 30i Combi Boiler',           1.00, 950.00,  0, 20, 950.00,  0),
(4, 'Labour — removal and installation',          8.00, 75.00,   0, 20, 600.00,  1),
(4, 'Flue kit and fittings',                      1.00, 125.00,  0, 20, 125.00,  2),
(4, 'System flush and inhibitor',                 1.00, 150.00,  0, 20, 150.00,  3),
(4, 'Magnetic filter installation',               1.00, 133.33,  0, 20, 133.33,  4),
(5, 'Annual service contract — 3 properties',     3.00, 350.00,  0, 20, 1050.00, 0),
(5, 'Priority call-out allowance',                1.00, 583.33,  0, 20, 583.33,  1),
(5, 'Gas safety certificates',                    3.00, 150.00,  0, 20, 450.00,  2);

-- ── Payments ───────────────────────────────────────────────────────
INSERT INTO payments (company_id, invoice_id, amount, method, reference, paid_at, recorded_by, notes, created_at) VALUES
(1, 1, 94.50,   'bank_transfer', 'BACS-001',   DATE_SUB(NOW(),INTERVAL 30 DAY), 5, NULL, NOW()),
(1, 2, 420.00,  'bank_transfer', 'BACS-002',   DATE_SUB(NOW(),INTERVAL 10 DAY), 5, NULL, NOW()),
(1, 3, 85.00,   'card',          'CARD-001',   DATE_SUB(NOW(),INTERVAL 5 DAY),  4, 'Paid by card on site', NOW()),
(1, 4, 1175.00, 'bank_transfer', 'BACS-DEPOSIT',DATE_SUB(NOW(),INTERVAL 10 DAY),5, '50% deposit on acceptance', NOW());

-- ── Notes ──────────────────────────────────────────────────────────
INSERT INTO notes (company_id, entity_type, entity_id, text, is_internal, created_by, created_at) VALUES
(1, 'job',      1, 'Service completed. Boiler pressure was slightly low at 0.8 bar — topped up to 1.2 bar. All checks passed. Certificate left with customer.', 0, 3, DATE_SUB(NOW(),INTERVAL 44 DAY)),
(1, 'job',      1, 'Customer asked about boiler replacement — mentioned it is 12 years old. Sent quote for replacement job.', 1, 3, DATE_SUB(NOW(),INTERVAL 44 DAY)),
(1, 'job',      2, 'Pipe had frozen and burst at the elbow joint in the utility room. Repaired and lagged. Advised customer to keep heating on during cold spells.', 0, 3, DATE_SUB(NOW(),INTERVAL 29 DAY)),
(1, 'job',      4, 'Full installation complete. Customer very happy. Boiler commissioned and controls demonstrated. All paperwork left with tenant.', 0, 3, DATE_SUB(NOW(),INTERVAL 11 DAY)),
(1, 'customer', 2, 'James called — chasing the annual service contract invoice. Stated they will pay end of next week. Follow up required.', 1, 2, DATE_SUB(NOW(),INTERVAL 3 DAY)),
(1, 'invoice',  5, 'Second reminder sent. James Nolan confirmed payment by end of month.', 1, 5, DATE_SUB(NOW(),INTERVAL 2 DAY)),
(1, 'job',      7, 'Customer called at 10:45 — complete loss of hot water since this morning. James Hartley dispatched immediately.', 1, 2, DATE_SUB(NOW(),INTERVAL 0 DAY));

-- ── Visits ─────────────────────────────────────────────────────────
INSERT INTO visits (company_id, job_id, title, status, scheduled_start, scheduled_end, created_by, created_at, updated_at) VALUES
(1, 5, 'Boiler repair - initial visit', 'scheduled', CONCAT(DATE_ADD(CURDATE(),INTERVAL 1 DAY),' 10:00:00'), CONCAT(DATE_ADD(CURDATE(),INTERVAL 1 DAY),' 11:30:00'), 2, NOW(), NOW()),
(1, 6, 'Annual service',                'scheduled', CONCAT(DATE_ADD(CURDATE(),INTERVAL 3 DAY),' 13:00:00'), CONCAT(DATE_ADD(CURDATE(),INTERVAL 3 DAY),' 14:30:00'), 2, NOW(), NOW()),
(1, 7, 'Emergency - no hot water',      'in_progress',CONCAT(CURDATE(),' 11:30:00'),                         CONCAT(CURDATE(),' 13:00:00'),                           2, NOW(), NOW()),
(1, 8, 'Survey visit for quote',        'scheduled', CONCAT(DATE_ADD(CURDATE(),INTERVAL 7 DAY),' 09:00:00'), CONCAT(DATE_ADD(CURDATE(),INTERVAL 7 DAY),' 10:00:00'), 1, NOW(), NOW());

INSERT INTO visit_assignees (visit_id, user_id) VALUES (1,3),(2,4),(3,3),(4,4);

-- ── Activity Log ───────────────────────────────────────────────────
INSERT INTO activity_log (company_id, type, entity_type, entity_id, entity_ref, user_id, user_name, description, created_at) VALUES
(1,'customer.created','customer',1,'CUST-0001',1,'Sarah Mitchell','Customer CUST-0001 created: David Thornton',DATE_SUB(NOW(),INTERVAL 18 MONTH)),
(1,'customer.created','customer',2,'CUST-0002',1,'Sarah Mitchell','Customer CUST-0002 created: Nolan Construction Ltd',DATE_SUB(NOW(),INTERVAL 14 MONTH)),
(1,'job.created',    'job',      1,'JOB-0001', 1,'Sarah Mitchell','Job JOB-0001 created: Annual Boiler Service',DATE_SUB(NOW(),INTERVAL 50 DAY)),
(1,'job.created',    'job',      2,'JOB-0002', 1,'Sarah Mitchell','Job JOB-0002 created: Fix Burst Pipe - Site B',DATE_SUB(NOW(),INTERVAL 32 DAY)),
(1,'job.completed',  'job',      1,'JOB-0001', 3,'James Hartley', 'Job JOB-0001 completed',DATE_SUB(NOW(),INTERVAL 44 DAY)),
(1,'invoice.created','invoice',  1,'INV-0001', 1,'Sarah Mitchell','Invoice INV-0001 created',DATE_SUB(NOW(),INTERVAL 44 DAY)),
(1,'invoice.sent',   'invoice',  1,'INV-0001', 1,'Sarah Mitchell','Invoice INV-0001 sent',DATE_SUB(NOW(),INTERVAL 44 DAY)),
(1,'payment.recorded','invoice', 1,'INV-0001', 5,'Rachel Owen',   'Payment £94.50 recorded on INV-0001',DATE_SUB(NOW(),INTERVAL 30 DAY)),
(1,'job.completed',  'job',      2,'JOB-0002', 3,'James Hartley', 'Job JOB-0002 completed',DATE_SUB(NOW(),INTERVAL 29 DAY)),
(1,'invoice.created','invoice',  2,'INV-0002', 1,'Sarah Mitchell','Invoice INV-0002 created',DATE_SUB(NOW(),INTERVAL 29 DAY)),
(1,'payment.recorded','invoice', 2,'INV-0002', 5,'Rachel Owen',   'Payment £420.00 recorded on INV-0002',DATE_SUB(NOW(),INTERVAL 10 DAY)),
(1,'quote.created',  'quote',    1,'QUO-0001', 1,'Sarah Mitchell','Quote QUO-0001 created: New Boiler Installation',DATE_SUB(NOW(),INTERVAL 25 DAY)),
(1,'quote.sent',     'quote',    1,'QUO-0001', 1,'Sarah Mitchell','Quote QUO-0001 sent to customer',DATE_SUB(NOW(),INTERVAL 22 DAY)),
(1,'quote.converted_invoice','quote',1,'QUO-0001',1,'Sarah Mitchell','Quote QUO-0001 converted to invoice INV-0004',DATE_SUB(NOW(),INTERVAL 12 DAY)),
(1,'payment.recorded','invoice', 4,'INV-0004', 5,'Rachel Owen',   'Payment £1,175.00 deposit on INV-0004',DATE_SUB(NOW(),INTERVAL 10 DAY)),
(1,'job.created',    'job',      7,'JOB-0007', 2,'Tom Bradley',   'Job JOB-0007 created: Emergency - No Hot Water',NOW()),
(1,'job.checked_in', 'job',      7,'JOB-0007', 3,'James Hartley', 'Checked in to job JOB-0007',NOW());

SET foreign_key_checks = 1;

-- ================================================================
-- DEMO LOGIN CREDENTIALS
-- URL:      http://localhost:8080
-- Admin:    sarah@bristolhp.co.uk  / password
-- Office:   tom@bristolhp.co.uk    / password
-- Engineer: james@bristolhp.co.uk  / password
-- Engineer: priya@bristolhp.co.uk  / password
-- Accounts: rachel@bristolhp.co.uk / password
-- ================================================================

-- ================================================================
-- JOB CREDITS TABLE (pre-payments recorded against jobs)
-- ================================================================

CREATE TABLE IF NOT EXISTS job_credits (
  id          INT UNSIGNED    NOT NULL AUTO_INCREMENT,
  company_id  INT UNSIGNED    NOT NULL,
  job_id      INT UNSIGNED    NOT NULL,
  invoice_id  INT UNSIGNED    NULL,
  amount      DECIMAL(10,2)   NOT NULL,
  method      VARCHAR(30)     NOT NULL,
  reference   VARCHAR(100)    NULL,
  notes       VARCHAR(500)    NULL,
  applied     TINYINT(1)      NOT NULL DEFAULT 0,
  created_by  INT UNSIGNED    NULL,
  created_at  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  INDEX idx_company_job  (company_id, job_id),
  INDEX idx_applied      (company_id, applied),
  CONSTRAINT fk_jcr_company  FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
  CONSTRAINT fk_jcr_job      FOREIGN KEY (job_id)     REFERENCES jobs(id),
  CONSTRAINT fk_jcr_invoice  FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE SET NULL,
  CONSTRAINT fk_jcr_creator  FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ================================================================
-- ALL DONE
-- Login credentials:
--   sarah@bristolhp.co.uk  / password  (Admin)
--   tom@bristolhp.co.uk    / password  (Office Manager)
--   james@bristolhp.co.uk  / password  (Engineer)
--   priya@bristolhp.co.uk  / password  (Engineer)
--   rachel@bristolhp.co.uk / password  (Accounts)
-- ================================================================
