-- Slice 8S-1 · Services Master Data
--
-- Ports the sitetracker16 3-layer pattern to rsflow.dspera:
--   asset_types          — class of equipment maintained (Generator, AC, UPS, etc.)
--   pm_templates         — versioned checklist per (asset_type × cadence × scope)
--   pm_template_items    — task rows (input types: CHECK / NUMBER / TEXT / PHOTO / SIGNATURE)
--   service_packs        — spares BOM per (asset_type × size range × interval)
--   service_pack_items   — parts per pack per interval
--   spare_parts          — SKU catalogue with cost, unit, category
--   callout_categories   — Emergency / Urgent / Routine with SLA target hours
--   callout_resolution_templates — dropdown of common resolutions
--
-- Setup is per-company (build once, reuse across all Service projects).

CREATE TABLE IF NOT EXISTS asset_types (
    c0d INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(32) NOT NULL UNIQUE,
    name VARCHAR(120) NOT NULL,
    description VARCHAR(500) NULL,
    icon VARCHAR(50) NULL COMMENT 'FontAwesome icon class',
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_by INT UNSIGNED NULL,
    created_on TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    KEY idx_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO asset_types (code, name, icon) VALUES
    ('GEN',       'Generator',              'fa-bolt'),
    ('AC',        'Air Conditioner',        'fa-wind'),
    ('UPS',       'UPS · Battery Backup',   'fa-plug'),
    ('BATTERY',   'Battery Bank',           'fa-car-battery'),
    ('RECTIFIER', 'Rectifier',              'fa-charging-station'),
    ('SHELTER',   'Shelter · Cabin',        'fa-warehouse'),
    ('TOWER',     'Tower · Mast',           'fa-broadcast-tower'),
    ('SOLAR',     'Solar Inverter',         'fa-sun'),
    ('CHILLER',   'Chiller',                'fa-snowflake'),
    ('PUMP',      'Pump',                   'fa-tint'),
    ('COMPRESSOR','Compressor',             'fa-cog'),
    ('FIRE',      'Fire Alarm · Panel',     'fa-fire-extinguisher'),
    ('CCTV',      'CCTV · Camera',          'fa-video');

CREATE TABLE IF NOT EXISTS pm_templates (
    c0d INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    asset_type_id INT UNSIGNED NULL COMMENT 'NULL for SITE-scoped templates',
    scope ENUM('ASSET','SITE') NOT NULL DEFAULT 'ASSET',
    cadence_type ENUM('NONE','DAYS','WEEKS','MONTHS','QUARTERS','HOURS_RUN') NOT NULL DEFAULT 'MONTHS',
    cadence_value SMALLINT UNSIGNED NULL COMMENT 'e.g. 3 for quarterly',
    expected_minutes SMALLINT UNSIGNED NULL,
    code VARCHAR(64) NOT NULL,
    name VARCHAR(191) NOT NULL,
    version SMALLINT UNSIGNED NOT NULL DEFAULT 1,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    reviewer_role VARCHAR(32) NULL COMMENT 'e.g. PM / PC to countersign',
    requires_manager_reconciliation TINYINT(1) NOT NULL DEFAULT 0,
    description TEXT NULL,
    created_by INT UNSIGNED NULL,
    created_on TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_code_ver (code, version),
    KEY idx_asset (asset_type_id),
    KEY idx_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS pm_template_items (
    c0d INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    template_id INT UNSIGNED NOT NULL,
    sort_order INT NOT NULL DEFAULT 0,
    section VARCHAR(64) NULL,
    task VARCHAR(500) NOT NULL,
    input_type ENUM('CHECK','NUMBER','TEXT','PHOTO','SIGNATURE') NOT NULL DEFAULT 'CHECK',
    unit VARCHAR(16) NULL,
    is_required TINYINT(1) NOT NULL DEFAULT 1,
    tracks_qty TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'Render qty_replaced input under result',
    depends_on_item_id INT UNSIGNED NULL,
    depends_on_value VARCHAR(60) NULL,
    KEY idx_template (template_id, sort_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS spare_parts (
    c0d INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    sku VARCHAR(64) NOT NULL UNIQUE,
    name VARCHAR(191) NOT NULL,
    description VARCHAR(500) NULL,
    uom VARCHAR(16) NOT NULL DEFAULT 'ea',
    category VARCHAR(60) NULL,
    asset_type_id INT UNSIGNED NULL,
    unit_cost DECIMAL(20,2) NULL,
    currency VARCHAR(10) NOT NULL DEFAULT 'TZS',
    default_markup_pct DECIMAL(5,2) NULL DEFAULT 25 COMMENT 'Sell price = cost × (1 + markup)',
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_by INT UNSIGNED NULL,
    created_on TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    edited_on TIMESTAMP NULL,
    KEY idx_active (is_active),
    KEY idx_asset (asset_type_id),
    KEY idx_cat (category)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS service_packs (
    c0d INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(64) NOT NULL UNIQUE,
    name VARCHAR(191) NOT NULL,
    description TEXT NULL,
    asset_type_id INT UNSIGNED NOT NULL,
    size_min INT UNSIGNED NULL COMMENT 'e.g. kVA min for generator packs',
    size_max INT UNSIGNED NULL COMMENT 'e.g. kVA max',
    size_unit VARCHAR(16) NULL DEFAULT 'kVA',
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_by INT UNSIGNED NULL,
    created_on TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    edited_on TIMESTAMP NULL,
    KEY idx_asset_size (asset_type_id, size_min, size_max),
    KEY idx_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS service_pack_items (
    c0d INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    pack_id INT UNSIGNED NOT NULL,
    interval_hrs INT UNSIGNED NULL COMMENT 'Service interval in run-hours (e.g. 250, 500, 1000)',
    interval_months SMALLINT UNSIGNED NULL COMMENT 'Alternative: months-based interval',
    part_id INT UNSIGNED NOT NULL,
    qty DECIMAL(12,4) NOT NULL DEFAULT 1,
    notes VARCHAR(300) NULL,
    KEY idx_pack (pack_id, interval_hrs),
    KEY idx_part (part_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS callout_categories (
    c0d INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(32) NOT NULL UNIQUE,
    name VARCHAR(120) NOT NULL,
    priority TINYINT UNSIGNED NOT NULL DEFAULT 3 COMMENT '1=highest',
    sla_response_hrs SMALLINT UNSIGNED NULL,
    sla_restoration_hrs SMALLINT UNSIGNED NULL,
    penalty_pct_per_hr DECIMAL(5,2) NULL COMMENT 'Contract penalty per hour beyond SLA',
    color_hex VARCHAR(10) NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO callout_categories (code, name, priority, sla_response_hrs, sla_restoration_hrs, color_hex) VALUES
    ('EMERGENCY','Emergency', 1, 4,  8,  '#C48579'),
    ('URGENT',   'Urgent',    2, 24, 48, '#8B6914'),
    ('ROUTINE',  'Routine',   3, 72, 120,'#7A8BA1'),
    ('SCHEDULED','Scheduled', 4, 168,240,'#3F5C43');

CREATE TABLE IF NOT EXISTS callout_resolution_templates (
    c0d INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    asset_type_id INT UNSIGNED NULL,
    category_id INT UNSIGNED NULL,
    label VARCHAR(191) NOT NULL,
    description TEXT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    sort_order INT NOT NULL DEFAULT 0,
    KEY idx_asset (asset_type_id),
    KEY idx_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Perms — full CRUD for Administrator + PM + PC (setup), read for FS/Procurement/Accountant
INSERT INTO user_groups_perms (user_group_id, controller_allowed, action_allowed, created_by, created_on)
SELECT g.c0d, ctrl, ac, 0, NOW()
FROM user_groups g
CROSS JOIN (
    SELECT 'AssetTypes' AS ctrl, ac FROM (SELECT 'index' AS ac UNION ALL SELECT 'saveAj' UNION ALL SELECT 'deleteAj') a
    UNION ALL SELECT 'PmTemplates', ac FROM (SELECT 'index' AS ac UNION ALL SELECT 'view' UNION ALL SELECT 'saveHeaderAj' UNION ALL SELECT 'saveItemAj' UNION ALL SELECT 'deleteItemAj' UNION ALL SELECT 'cloneAj') b
    UNION ALL SELECT 'SpareParts', ac FROM (SELECT 'index' AS ac UNION ALL SELECT 'saveAj' UNION ALL SELECT 'deleteAj') c
    UNION ALL SELECT 'ServicePacks', ac FROM (SELECT 'index' AS ac UNION ALL SELECT 'view' UNION ALL SELECT 'saveHeaderAj' UNION ALL SELECT 'saveItemAj' UNION ALL SELECT 'deleteItemAj') d
    UNION ALL SELECT 'CalloutCategories', ac FROM (SELECT 'index' AS ac UNION ALL SELECT 'saveAj' UNION ALL SELECT 'deleteAj') e
) ac_map
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 = ac_map.ctrl AND p.action_allowed = ac_map.ac);

-- Read-only index for Field Supervisor + Procurement + Accountant
INSERT INTO user_groups_perms (user_group_id, controller_allowed, action_allowed, created_by, created_on)
SELECT g.c0d, ctrl, 'index', 0, NOW()
FROM user_groups g
CROSS JOIN (SELECT 'AssetTypes' AS ctrl UNION ALL SELECT 'PmTemplates' UNION ALL SELECT 'SpareParts' UNION ALL SELECT 'ServicePacks' UNION ALL SELECT 'CalloutCategories') c
WHERE g.user_group_name IN ('Field Supervisor','Procurement','Accountant','Finance Manager')
  AND NOT EXISTS (SELECT 1 FROM user_groups_perms p WHERE p.user_group_id = g.c0d AND p.controller_allowed = c.ctrl AND p.action_allowed = 'index');
