-- Pacific Logistics · slice 27 · Financial Reports (Iter 5 Turn 1)
--
-- New endpoints:
--   FinancialReports/index          — landing menu
--   FinancialReports/trialBalance   — TB for a date range
--   FinancialReports/pnl            — P&L for a date range
--   FinancialReports/balanceSheet   — BS as of a date
--   FinancialReports/ledger/{acc}   — per-account drill-down
--
-- Grants: Admin + Accountant + Finance Manager see all four.


-- Composite index that speeds the report queries — accountId + status join
-- is the hot path (journal_lines JOIN journal_entries ON status=POSTED).
-- Idempotent via a stored proc so re-running the slice is safe.

DELIMITER $$
DROP PROCEDURE IF EXISTS pl_add_index_if_missing_27 $$
CREATE PROCEDURE pl_add_index_if_missing_27(IN tbl VARCHAR(64), IN idx VARCHAR(64), IN spec VARCHAR(500))
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.STATISTICS
         WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = tbl AND INDEX_NAME = idx
    ) THEN
        SET @s = CONCAT('CREATE INDEX ', idx, ' ON ', tbl, ' ', spec);
        PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;
    END IF;
END $$
DELIMITER ;

CALL pl_add_index_if_missing_27('journal_entries', 'idx_je_date_status', '(entry_date, status, deleted)');

DROP PROCEDURE pl_add_index_if_missing_27;


-- Grants — Executive (1), Finance Manager (2), Accountant (3), Administrator (10)
INSERT IGNORE INTO user_groups_perms (user_group_id, controller_allowed, action_allowed, created_by, created_on) VALUES
(1,  'FinancialReports', 'index',        1, NOW()),
(1,  'FinancialReports', 'trialBalance', 1, NOW()),
(1,  'FinancialReports', 'pnl',          1, NOW()),
(1,  'FinancialReports', 'balanceSheet', 1, NOW()),
(1,  'FinancialReports', 'ledger',       1, NOW()),
(2,  'FinancialReports', 'index',        1, NOW()),
(2,  'FinancialReports', 'trialBalance', 1, NOW()),
(2,  'FinancialReports', 'pnl',          1, NOW()),
(2,  'FinancialReports', 'balanceSheet', 1, NOW()),
(2,  'FinancialReports', 'ledger',       1, NOW()),
(3,  'FinancialReports', 'index',        1, NOW()),
(3,  'FinancialReports', 'trialBalance', 1, NOW()),
(3,  'FinancialReports', 'pnl',          1, NOW()),
(3,  'FinancialReports', 'balanceSheet', 1, NOW()),
(3,  'FinancialReports', 'ledger',       1, NOW()),
(10, 'FinancialReports', 'index',        1, NOW()),
(10, 'FinancialReports', 'trialBalance', 1, NOW()),
(10, 'FinancialReports', 'pnl',          1, NOW()),
(10, 'FinancialReports', 'balanceSheet', 1, NOW()),
(10, 'FinancialReports', 'ledger',       1, NOW());


-- Sanity: report which user_groups now have Financial Reports access
SELECT ug.user_group_name AS user_group, GROUP_CONCAT(ugp.action_allowed ORDER BY ugp.action_allowed) AS actions
  FROM user_groups_perms ugp
  JOIN user_groups ug ON ug.c0d = ugp.user_group_id
 WHERE ugp.controller_allowed = 'FinancialReports'
 GROUP BY ug.c0d, ug.user_group_name;
