-- =========================================================================
-- SPMS — Skema MySQL (untuk phpMyAdmin)
-- Import fail ini terus melalui phpMyAdmin > pilih database > Import.
-- Tiada Composer/artisan diperlukan — semua struktur & trigger audit
-- ditulis sebagai SQL tulen.
-- =========================================================================

SET FOREIGN_KEY_CHECKS = 0;

-- ---------- Anak Syarikat ----------
CREATE TABLE subsidiaries (
    subsidiary_id     CHAR(36) PRIMARY KEY,
    code              VARCHAR(20) NOT NULL UNIQUE,
    name              VARCHAR(150) NOT NULL,
    is_active         TINYINT(1) NOT NULL DEFAULT 1,
    created_at        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- Pengguna & Peranan (RBAC) ----------
CREATE TABLE app_users (
    user_id           CHAR(36) PRIMARY KEY,
    username          VARCHAR(50) NOT NULL UNIQUE,
    full_name         VARCHAR(150) NOT NULL,
    email             VARCHAR(150) NOT NULL UNIQUE,
    password_hash     VARCHAR(255) NOT NULL,
    security_question VARCHAR(255) NOT NULL,
    security_answer_hash VARCHAR(255) NOT NULL,
    role              ENUM('super_admin','group_finance','subsidiary_finance','board_viewer') NOT NULL,
    subsidiary_id     CHAR(36) NULL,
    is_active         TINYINT(1) NOT NULL DEFAULT 1,
    failed_logins     INT NOT NULL DEFAULT 0,
    locked_until      DATETIME NULL,
    created_at        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (subsidiary_id) REFERENCES subsidiaries(subsidiary_id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- Financial: Master line items ----------
CREATE TABLE financial_line_items (
    line_item_id      INT AUTO_INCREMENT PRIMARY KEY,
    category          ENUM('income_statement','balance_sheet','dividend') NOT NULL,
    label             VARCHAR(100) NOT NULL,
    display_order     SMALLINT NOT NULL,
    is_calculated     TINYINT(1) NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO financial_line_items (category, label, display_order, is_calculated) VALUES
('income_statement','Revenue',1,0),
('income_statement','Cost of Sales',2,0),
('income_statement','Gross Profit',3,1),
('income_statement','EBITDA',4,0),
('income_statement','PBT/(LBT)',5,0),
('income_statement','PAT/(LAT)',6,0),
('balance_sheet','Total Assets',7,0),
('balance_sheet','Total Liabilities',8,0),
('balance_sheet','Net Assets',9,1),
('balance_sheet','Cash and Bank Balances',10,0),
('balance_sheet','Borrowings',11,0),
('dividend','Dividend Pay-out',12,0);

-- ---------- Financial: Transactional records ----------
CREATE TABLE financial_records (
    record_id         CHAR(36) PRIMARY KEY,
    subsidiary_id     CHAR(36) NOT NULL,
    line_item_id      INT NOT NULL,
    fiscal_year       SMALLINT NOT NULL,
    quarter           ENUM('Q1','Q2','Q3','Q4') NULL,
    period_type       ENUM('YTD','FY') NOT NULL DEFAULT 'YTD',
    value_type        ENUM('ACTUAL','BUDGET') NOT NULL,
    amount_rm000      DECIMAL(18,2) NOT NULL DEFAULT 0,
    note              TEXT NULL,
    status            ENUM('draft','submitted','approved','rejected') NOT NULL DEFAULT 'draft',
    created_by        CHAR(36) NULL,
    approved_by       CHAR(36) NULL,
    created_at        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_financial_record (subsidiary_id, line_item_id, fiscal_year, quarter, period_type, value_type),
    FOREIGN KEY (subsidiary_id) REFERENCES subsidiaries(subsidiary_id) ON DELETE CASCADE,
    FOREIGN KEY (line_item_id) REFERENCES financial_line_items(line_item_id),
    FOREIGN KEY (created_by) REFERENCES app_users(user_id),
    FOREIGN KEY (approved_by) REFERENCES app_users(user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- Audit Trail ----------
CREATE TABLE audit_logs (
    audit_id          BIGINT AUTO_INCREMENT PRIMARY KEY,
    table_name        VARCHAR(50) NOT NULL,
    record_id         CHAR(36) NOT NULL,
    action            ENUM('INSERT','UPDATE','DELETE') NOT NULL,
    old_value         JSON NULL,
    new_value         JSON NULL,
    changed_by        CHAR(36) NULL,
    changed_at        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- MySQL triggers can't read a PHP session, so the application layer
-- (includes/db.php) sets a session variable @app_user_id before every
-- write — the trigger below picks it up. This keeps audit logging
-- automatic at the database layer, same principle as the Postgres version.
DELIMITER $$

CREATE TRIGGER trg_financial_records_insert
AFTER INSERT ON financial_records
FOR EACH ROW
BEGIN
    INSERT INTO audit_logs (table_name, record_id, action, new_value, changed_by)
    VALUES ('financial_records', NEW.record_id, 'INSERT',
            JSON_OBJECT('amount_rm000', NEW.amount_rm000, 'status', NEW.status),
            @app_user_id);
END$$

CREATE TRIGGER trg_financial_records_update
AFTER UPDATE ON financial_records
FOR EACH ROW
BEGIN
    INSERT INTO audit_logs (table_name, record_id, action, old_value, new_value, changed_by)
    VALUES ('financial_records', NEW.record_id, 'UPDATE',
            JSON_OBJECT('amount_rm000', OLD.amount_rm000, 'status', OLD.status),
            JSON_OBJECT('amount_rm000', NEW.amount_rm000, 'status', NEW.status),
            @app_user_id);
END$$

CREATE TRIGGER trg_financial_records_delete
AFTER DELETE ON financial_records
FOR EACH ROW
BEGIN
    INSERT INTO audit_logs (table_name, record_id, action, old_value, changed_by)
    VALUES ('financial_records', OLD.record_id, 'DELETE',
            JSON_OBJECT('amount_rm000', OLD.amount_rm000, 'status', OLD.status),
            @app_user_id);
END$$

DELIMITER ;

-- ---------- Rate limiting / login lockout tracking ----------
CREATE TABLE rate_limit_events (
    event_id          BIGINT AUTO_INCREMENT PRIMARY KEY,
    bucket_key        VARCHAR(150) NOT NULL,   -- e.g. 'login-email:x@y.com' or 'write:<user_id>'
    created_at        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_bucket_time (bucket_key, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- API Keys (Power BI) ----------
CREATE TABLE api_keys (
    api_key_id        CHAR(36) PRIMARY KEY,
    name              VARCHAR(100) NOT NULL,
    key_hash          VARCHAR(64) NOT NULL UNIQUE,   -- SHA-256 hex
    key_prefix        VARCHAR(12) NOT NULL,
    scopes            VARCHAR(255) NOT NULL DEFAULT 'read:financial,read:hr,read:kpi',
    is_active         TINYINT(1) NOT NULL DEFAULT 1,
    last_used_at      DATETIME NULL,
    expires_at        DATETIME NULL,
    created_by        CHAR(36) NULL,
    created_at        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SET FOREIGN_KEY_CHECKS = 1;

-- =========================================================================
-- ROADMAP: HR / KPI / Business Plan / Notes akan ditambah dalam pusingan
-- seterusnya — ikut corak jadual yang sama (line-item master + records +
-- trigger audit) supaya konsisten dengan financial_records di atas.
-- =========================================================================