-- Slice 8S-2 · Service Contracts + Sites + Assets + PM Schedule
--
-- Once a Services project is approved by Exec, PC sets up:
--   1. service_contracts       — customer, project, dates, contract value, spare policy, invoice cadence
--   2. service_contract_sites  — locations we service (banks: branches; telcos: base stations; etc.)
--   3. service_contract_assets — assets per site (each with asset_type + optional size for pack matching)
--   4. service_contract_slas   — per-category SLA overrides (contract may tighten/loosen defaults)
--   5. service_contract_pm_schedule — auto-generated from templates × assets × cadence
--
-- Layer 3 (execution) — pm_services / callouts — lands in Slice S3.

CREATE TABLE IF NOT EXISTS service_contracts (
    c0d INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    contract_no VARCHAR(30) NOT NULL UNIQUE,
    project_id INT UNSIGNED NOT NULL,
    customer_id INT UNSIGNED NULL,
    customer_name VARCHAR(200) NULL,
    start_date DATE NULL,
    end_date DATE NULL,
    contract_value DECIMAL(20,2) NOT NULL DEFAULT 0,
    currency VARCHAR(10) NOT NULL DEFAULT 'TZS',
    monthly_retainer DECIMAL(20,2) NULL,
    spare_policy ENUM('COMPANY','CLIENT','CONSIGNMENT','FIXED_PACK') NOT NULL DEFAULT 'COMPANY',
    invoice_cadence ENUM('MONTHLY_RETAINER','PER_EVENT','MILESTONE','CONSUMPTION') NOT NULL DEFAULT 'MONTHLY_RETAINER',
    payment_terms_days SMALLINT UNSIGNED NULL DEFAULT 30,
    labour_rate_per_hr DECIMAL(20,2) NULL,
    callout_fee DECIMAL(20,2) NULL,
    emergency_multiplier DECIMAL(5,2) NULL DEFAULT 2.00,
    parts_markup_pct DECIMAL(5,2) NULL DEFAULT 25.00,
    mileage_rate_per_km DECIMAL(20,2) NULL,
    free_callouts_per_month SMALLINT UNSIGNED NULL DEFAULT 0,
    report_cadence ENUM('MONTHLY','QUARTERLY','SEMI_ANNUAL','ANNUAL','NONE') NOT NULL DEFAULT 'MONTHLY',
    status ENUM('DRAFT','ACTIVE','SUSPENDED','EXPIRED','TERMINATED') NOT NULL DEFAULT 'DRAFT',
    activated_at DATETIME NULL,
    notes TEXT NULL,
    contract_pdf_path VARCHAR(500) NULL,
    created_by INT UNSIGNED NULL,
    created_by_name VARCHAR(160) NULL,
    created_on TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    edited_on TIMESTAMP NULL,
    deleted TINYINT(1) NOT NULL DEFAULT 0,
    KEY idx_project (project_id),
    KEY idx_status (status),
    KEY idx_dates (start_date, end_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS service_contract_sites (
    c0d INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    contract_id INT UNSIGNED NOT NULL,
    site_code VARCHAR(60) NULL COMMENT 'Client-side site code (branch code, BTS code)',
    name VARCHAR(191) NOT NULL,
    physical_address TEXT NULL,
    region VARCHAR(60) NULL,
    district VARCHAR(60) NULL,
    gps_lat DECIMAL(10,7) NULL,
    gps_lng DECIMAL(10,7) NULL,
    site_contact_name VARCHAR(160) NULL,
    site_contact_phone VARCHAR(40) NULL,
    access_notes TEXT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    KEY idx_contract (contract_id),
    KEY idx_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS service_contract_assets (
    c0d INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    contract_id INT UNSIGNED NOT NULL,
    site_id INT UNSIGNED NOT NULL,
    asset_type_id INT UNSIGNED NOT NULL,
    service_pack_id_override INT UNSIGNED NULL COMMENT 'Manual override; else auto-match by size',
    asset_no VARCHAR(80) NULL COMMENT 'Client tag or internal serial',
    serial_no VARCHAR(120) NULL,
    make VARCHAR(120) NULL,
    model VARCHAR(120) NULL,
    size_value INT UNSIGNED NULL COMMENT 'e.g. kVA for generator, tons for AC',
    size_unit VARCHAR(16) NULL,
    install_date DATE NULL,
    warranty_end DATE NULL,
    hours_run INT UNSIGNED NULL COMMENT 'Current run-hours (for HOURS_RUN cadence)',
    meta_json TEXT NULL COMMENT 'Free-form additional attributes',
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    KEY idx_contract (contract_id),
    KEY idx_site (site_id),
    KEY idx_type (asset_type_id),
    KEY idx_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS service_contract_slas (
    c0d INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    contract_id INT UNSIGNED NOT NULL,
    category_id INT UNSIGNED NOT NULL,
    sla_response_hrs SMALLINT UNSIGNED NULL,
    sla_restoration_hrs SMALLINT UNSIGNED NULL,
    penalty_pct_per_hr DECIMAL(5,2) NULL,
    notes VARCHAR(500) NULL,
    UNIQUE KEY uq_contract_cat (contract_id, category_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS service_contract_pm_schedule (
    c0d INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    contract_id INT UNSIGNED NOT NULL,
    contract_asset_id INT UNSIGNED NOT NULL,
    template_id INT UNSIGNED NOT NULL,
    next_due_on DATE NULL,
    last_done_on DATE NULL,
    interval_months SMALLINT UNSIGNED NULL,
    interval_hrs INT UNSIGNED NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_on TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    KEY idx_contract (contract_id),
    KEY idx_asset (contract_asset_id),
    KEY idx_next_due (next_due_on)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Perms
INSERT INTO user_groups_perms (user_group_id, controller_allowed, action_allowed, created_by, created_on)
SELECT g.c0d, 'ServiceContracts', ac, 0, NOW()
FROM user_groups g
CROSS JOIN (SELECT 'index' AS ac
            UNION ALL SELECT 'view'
            UNION ALL SELECT 'saveHeaderAj'
            UNION ALL SELECT 'activateAj'
            UNION ALL SELECT 'saveSiteAj'
            UNION ALL SELECT 'deleteSiteAj'
            UNION ALL SELECT 'saveAssetAj'
            UNION ALL SELECT 'deleteAssetAj'
            UNION ALL SELECT 'saveSlaAj'
            UNION ALL SELECT 'generateScheduleAj') actions
WHERE g.user_group_name IN ('Administrator','Project Manager','Projects Coordinator')
  AND NOT EXISTS (SELECT 1 FROM user_groups_perms p WHERE p.user_group_id = g.c0d AND p.controller_allowed = 'ServiceContracts' AND p.action_allowed = actions.ac);

-- Read-only for FS/Procurement/Accountant/FM/Exec
INSERT INTO user_groups_perms (user_group_id, controller_allowed, action_allowed, created_by, created_on)
SELECT g.c0d, 'ServiceContracts', ac, 0, NOW()
FROM user_groups g
CROSS JOIN (SELECT 'index' AS ac UNION ALL SELECT 'view') actions
WHERE g.user_group_name IN ('Field Supervisor','Procurement','Accountant','Finance Manager','Executive')
  AND NOT EXISTS (SELECT 1 FROM user_groups_perms p WHERE p.user_group_id = g.c0d AND p.controller_allowed = 'ServiceContracts' AND p.action_allowed = actions.ac);
