-- ============================================================
-- Asia Asset Finance — Live CX Intelligence System
-- Complete Database Schema v2.0
-- ============================================================

CREATE DATABASE IF NOT EXISTS gravityc_aaf_cx CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE gravityc_aaf_cx;

-- ============================================================
-- CORE REFERENCE TABLES
-- ============================================================

CREATE TABLE regions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    code VARCHAR(20) NOT NULL UNIQUE,
    is_active TINYINT(1) DEFAULT 1
);

CREATE TABLE branches (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    code VARCHAR(20) NOT NULL UNIQUE,
    region_id INT NOT NULL,
    address TEXT,
    phone VARCHAR(20),
    is_active TINYINT(1) DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (region_id) REFERENCES regions(id)
);

CREATE TABLE touchpoint_types (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    code VARCHAR(50) NOT NULL UNIQUE,
    channel ENUM('branch','call_centre','digital','field','head_office') NOT NULL,
    icon VARCHAR(10) DEFAULT 'pin',
    is_active TINYINT(1) DEFAULT 1
);

CREATE TABLE touchpoints (
    id INT AUTO_INCREMENT PRIMARY KEY,
    branch_id INT NULL,
    touchpoint_type_id INT NOT NULL,
    label VARCHAR(150) NOT NULL,
    qr_token VARCHAR(64) NOT NULL UNIQUE,
    channel ENUM('branch','call_centre','digital','field','head_office') NOT NULL,
    department VARCHAR(100) NULL,
    is_active TINYINT(1) DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (branch_id) REFERENCES branches(id) ON DELETE SET NULL,
    FOREIGN KEY (touchpoint_type_id) REFERENCES touchpoint_types(id)
);

-- ============================================================
-- USERS & ROLES
-- ============================================================

CREATE TABLE departments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    code VARCHAR(50) NOT NULL UNIQUE,
    head_user_id INT NULL,
    is_active TINYINT(1) DEFAULT 1
);

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150) NOT NULL UNIQUE,
    phone VARCHAR(20) NULL,
    password_hash VARCHAR(255) NOT NULL,
    role ENUM('ceo','coo','head_office_admin','cx_officer','regional_manager','branch_manager',
              'product_head','call_centre_head','recoveries_head','compliance_head',
              'department_head','field_manager','compliance_officer','staff') NOT NULL,
    branch_id INT NULL,
    region_id INT NULL,
    department_id INT NULL,
    is_active TINYINT(1) DEFAULT 1,
    last_login DATETIME NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (branch_id) REFERENCES branches(id) ON DELETE SET NULL,
    FOREIGN KEY (region_id) REFERENCES regions(id) ON DELETE SET NULL
);

ALTER TABLE departments ADD FOREIGN KEY (head_user_id) REFERENCES users(id) ON DELETE SET NULL;

-- ============================================================
-- SURVEY & FEEDBACK
-- ============================================================

CREATE TABLE survey_categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    code VARCHAR(50) NOT NULL UNIQUE,
    type ENUM('product','service') NOT NULL,
    parent_id INT NULL,
    channel VARCHAR(50) NULL,
    is_active TINYINT(1) DEFAULT 1,
    FOREIGN KEY (parent_id) REFERENCES survey_categories(id) ON DELETE SET NULL
);

CREATE TABLE feedback (
    id INT AUTO_INCREMENT PRIMARY KEY,
    ref_id VARCHAR(30) NOT NULL UNIQUE,
    touchpoint_id INT NOT NULL,
    branch_id INT NULL,
    channel ENUM('branch','call_centre','digital','field','head_office','sms') NOT NULL,
    issue_type ENUM('product','service') NULL,
    category_id INT NULL,
    subcategory_id INT NULL,

    -- Ratings
    rating_overall TINYINT NULL CHECK (rating_overall BETWEEN 1 AND 5),
    rating_staff TINYINT NULL CHECK (rating_staff BETWEEN 1 AND 5),
    rating_waiting TINYINT NULL CHECK (rating_waiting BETWEEN 1 AND 5),
    rating_resolution TINYINT NULL CHECK (rating_resolution BETWEEN 1 AND 5),
    rating_product TINYINT NULL CHECK (rating_product BETWEEN 1 AND 5),

    -- Content
    comments TEXT NULL,
    product_type ENUM('leasing','loans','gold_loan','fixed_deposit','savings','micro_mortgage','other') NULL,
    service_type VARCHAR(100) NULL,

    -- Contact
    mobile_number VARCHAR(20) NULL,
    is_anonymous TINYINT(1) DEFAULT 0,

    -- Classification
    is_red_alert TINYINT(1) DEFAULT 0,
    alert_level ENUM('green','amber','red','critical') DEFAULT 'green',
    sentiment_score DECIMAL(3,2) NULL,

    -- Meta
    language ENUM('en','si','ta') DEFAULT 'en',
    source ENUM('qr','sms','web','agent') DEFAULT 'qr',
    ip_address VARCHAR(45) NULL,
    submitted_at DATETIME DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (touchpoint_id) REFERENCES touchpoints(id),
    FOREIGN KEY (branch_id) REFERENCES branches(id) ON DELETE SET NULL,
    FOREIGN KEY (category_id) REFERENCES survey_categories(id) ON DELETE SET NULL
);

-- ============================================================
-- CASE MANAGEMENT
-- ============================================================

CREATE TABLE cases (
    id INT AUTO_INCREMENT PRIMARY KEY,
    case_number VARCHAR(30) NOT NULL UNIQUE,
    feedback_id INT NOT NULL UNIQUE,
    status ENUM('open','assigned','in_progress','escalated','pending_closure','closed') DEFAULT 'open',
    priority ENUM('low','medium','high','critical') DEFAULT 'medium',
    alert_level ENUM('green','amber','red','critical') DEFAULT 'red',
    issue_type ENUM('product','service') NOT NULL,
    channel VARCHAR(50) NOT NULL,

    -- Assignment
    primary_owner_id INT NULL,
    secondary_owner_id INT NULL,
    cx_officer_id INT NULL,

    -- SLA
    sla_hours INT NOT NULL DEFAULT 24,
    sla_deadline DATETIME NOT NULL,
    sla_breached TINYINT(1) DEFAULT 0,

    -- Resolution
    resolution_notes TEXT NULL,
    closure_action TEXT NULL,
    root_cause VARCHAR(255) NULL,
    resolved_at DATETIME NULL,
    closed_at DATETIME NULL,
    closed_by INT NULL,

    -- Repeat detection
    is_repeat TINYINT(1) DEFAULT 0,
    repeat_count INT DEFAULT 0,

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

    FOREIGN KEY (feedback_id) REFERENCES feedback(id),
    FOREIGN KEY (primary_owner_id) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (secondary_owner_id) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (cx_officer_id) REFERENCES users(id) ON DELETE SET NULL
);

CREATE TABLE case_timeline (
    id INT AUTO_INCREMENT PRIMARY KEY,
    case_id INT NOT NULL,
    user_id INT NULL,
    action VARCHAR(100) NOT NULL,
    notes TEXT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (case_id) REFERENCES cases(id),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
);

CREATE TABLE case_callbacks (
    id INT AUTO_INCREMENT PRIMARY KEY,
    case_id INT NOT NULL,
    logged_by INT NULL,
    customer_mobile VARCHAR(20) NULL,
    callback_time DATETIME NULL,
    outcome VARCHAR(255) NULL,
    notes TEXT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (case_id) REFERENCES cases(id)
);

-- ============================================================
-- ROUTING RULES
-- ============================================================

CREATE TABLE routing_rules (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    channel VARCHAR(50) NULL,
    issue_type ENUM('product','service','both') DEFAULT 'both',
    product_type VARCHAR(50) NULL,
    alert_level VARCHAR(20) NULL,
    primary_owner_role VARCHAR(50) NULL,
    primary_owner_id INT NULL,
    secondary_owner_role VARCHAR(50) NULL,
    secondary_owner_id INT NULL,
    cx_officer_always TINYINT(1) DEFAULT 1,
    sla_hours INT DEFAULT 24,
    is_active TINYINT(1) DEFAULT 1,
    priority_order INT DEFAULT 10,
    FOREIGN KEY (primary_owner_id) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (secondary_owner_id) REFERENCES users(id) ON DELETE SET NULL
);

-- ============================================================
-- SMS SURVEY
-- ============================================================

CREATE TABLE sms_surveys (
    id INT AUTO_INCREMENT PRIMARY KEY,
    mobile_number VARCHAR(20) NOT NULL,
    touchpoint_id INT NULL,
    branch_id INT NULL,
    agent_id INT NULL,
    call_ref VARCHAR(50) NULL,
    token VARCHAR(64) NOT NULL UNIQUE,
    status ENUM('sent','opened','completed','expired') DEFAULT 'sent',
    sent_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    completed_at DATETIME NULL,
    expires_at DATETIME NOT NULL,
    FOREIGN KEY (touchpoint_id) REFERENCES touchpoints(id) ON DELETE SET NULL,
    FOREIGN KEY (branch_id) REFERENCES branches(id) ON DELETE SET NULL
);

-- ============================================================
-- SYSTEM CONFIG & AUDIT
-- ============================================================

CREATE TABLE system_config (
    id INT AUTO_INCREMENT PRIMARY KEY,
    config_key VARCHAR(100) NOT NULL UNIQUE,
    config_value TEXT NOT NULL,
    label VARCHAR(150) NULL,
    updated_by INT NULL,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE audit_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NULL,
    action VARCHAR(100) NOT NULL,
    target_type VARCHAR(50) NULL,
    target_id INT NULL,
    details TEXT NULL,
    ip_address VARCHAR(45) NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- ============================================================
-- INDEXES
-- ============================================================

CREATE INDEX idx_feedback_touchpoint ON feedback(touchpoint_id);
CREATE INDEX idx_feedback_branch ON feedback(branch_id);
CREATE INDEX idx_feedback_submitted ON feedback(submitted_at);
CREATE INDEX idx_feedback_alert ON feedback(is_red_alert, alert_level);
CREATE INDEX idx_cases_status ON cases(status);
CREATE INDEX idx_cases_sla ON cases(sla_deadline, sla_breached);
CREATE INDEX idx_cases_priority ON cases(priority, alert_level);

-- ============================================================
-- SEED DATA
-- ============================================================

INSERT INTO regions (name, code) VALUES
('Western','WES'),('Central','CEN'),('Southern','SOU'),
('Northern','NOR'),('North Western','NWE'),('Eastern','EAS'),
('Sabaragamuwa','SAB'),('Uva','UVA');

INSERT INTO branches (name, code, region_id, address, phone) VALUES
('Colombo Head Office','COL-001',1,'45 Galle Road, Colombo 03','011-222-1234'),
('Kandy Main Branch','KDY-001',2,'12 Dalada Veediya, Kandy','081-222-5678'),
('Galle Branch','GAL-001',3,'78 Matara Road, Galle','091-222-9012'),
('Jaffna Branch','JFN-001',4,'23 Hospital Road, Jaffna','021-222-3456'),
('Kurunegala Branch','KRL-001',5,'5 Colombo Road, Kurunegala','037-222-7890'),
('Negombo Branch','NGO-001',1,'14 St. Joseph Street, Negombo','031-222-1111');

INSERT INTO touchpoint_types (name, code, channel, icon) VALUES
('Branch Counter','branch_counter','branch','pin'),
('Customer Service Desk','cs_desk','branch','pin'),
('Gold Loan Desk','gold_desk','branch','pin'),
('Leasing Desk','leasing_desk','branch','pin'),
('Loans Desk','loans_desk','branch','pin'),
('Cash Counter','cash_counter','branch','pin'),
('Waiting Area','waiting_area','branch','pin'),
('Recoveries Counter','recoveries_counter','branch','pin'),
('Relationship Officer Desk','ro_desk','branch','pin'),
('Call Centre','call_centre','call_centre','pin'),
('Digital Channel','digital','digital','pin'),
('Field Operations','field','field','pin'),
('Head Office','head_office','head_office','pin');

-- Generate touchpoints for each branch
INSERT INTO touchpoints (branch_id, touchpoint_type_id, label, qr_token, channel)
SELECT b.id, t.id,
    CONCAT(b.name, ' — ', t.name),
    SHA2(CONCAT(b.code, '-', t.code, '-2026-', RAND()), 256),
    t.channel
FROM branches b
CROSS JOIN touchpoint_types t
WHERE t.channel = 'branch';

-- Call centre touchpoint
INSERT INTO touchpoints (branch_id, touchpoint_type_id, label, qr_token, channel)
VALUES (1, 10, 'AAF Call Centre', SHA2(CONCAT('CALLCENTRE-2026-', RAND()), 256), 'call_centre');

INSERT INTO departments (name, code) VALUES
('Leasing','leasing'),('Loans & Micro Mortgage','loans'),
('Gold Loans','gold_loans'),('Fixed Deposits','fixed_deposits'),
('Recoveries','recoveries'),('Compliance','compliance'),
('Call Centre','call_centre'),('Field Operations','field_ops'),
('Customer Experience','cx');

INSERT INTO survey_categories (name, code, type, channel) VALUES
('Leasing Product','cat_leasing','product',NULL),
('Loans / Micro Mortgage','cat_loans','product',NULL),
('Gold Loan Product','cat_gold','product',NULL),
('Fixed Deposit Product','cat_fd','product',NULL),
('Staff Behaviour','cat_staff','service',NULL),
('Waiting Time','cat_wait','service',NULL),
('Process / Documentation','cat_process','service',NULL),
('Call Centre Service','cat_call','service','call_centre'),
('Recoveries Conduct','cat_recovery','service','branch'),
('Digital Experience','cat_digital','service','digital'),
('Branch Facilities','cat_facility','service','branch'),
('Resolution Quality','cat_resolution','service',NULL);

INSERT INTO system_config (config_key, config_value, label) VALUES
('red_alert_threshold','2','Rating threshold to trigger Red Alert (1-5)'),
('sla_hours_red','4','SLA hours for Red Alert cases'),
('sla_hours_amber','24','SLA hours for Amber cases'),
('sla_hours_green','72','SLA hours for Green cases'),
('sms_survey_expiry_hours','48','SMS survey link expiry in hours'),
('site_url','http://localhost/cx-system','System base URL'),
('org_name','Asia Asset Finance PLC','Organisation name'),
('cx_officer_email','cx@asiaasset.lk','CX Officer email for alerts');

-- Default admin (password: Admin@2026)
INSERT INTO users (name, email, password_hash, role) VALUES
('CX Head Admin','admin@asiaasset.lk','$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi','head_office_admin'),
('CEO Dashboard','ceo@asiaasset.lk','$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi','ceo');

INSERT INTO routing_rules (name, channel, issue_type, product_type, alert_level, primary_owner_role, sla_hours, priority_order) VALUES
('Red Alert — Any Channel','','both',NULL,'red','cx_officer',4,1),
('Critical Alert — Any','','both',NULL,'critical','cx_officer',2,1),
('Product — Leasing','branch','product','leasing',NULL,'product_head',24,5),
('Product — Loans','branch','product','loans',NULL,'product_head',24,5),
('Product — Gold Loan','branch','product','gold_loan',NULL,'product_head',24,5),
('Service — Branch','branch','service',NULL,NULL,'branch_manager',24,10),
('Service — Call Centre','call_centre','service',NULL,NULL,'call_centre_head',12,10),
('Service — Recoveries','branch','service',NULL,NULL,'recoveries_head',8,8),
('Service — Compliance','head_office','service',NULL,NULL,'compliance_head',24,10);