-- ============================================================
--  PERMISSION SYNC SCHEMA — Ticto Group CRM
--  Run this once on tictogroupuser_db (the Ticto master DB)
-- ============================================================

-- ── 1. Global Module Map ────────────────────────────────────
--  Maps a human-readable global feature key to the domain-
--  specific local module ID in each subsidiary's module_master.
--
--  global_key  = snake_case derived from module_master.name
--  domain_slug = ticto | floorsathi | chakraplus | revobin | solardharti
--
--  NOTE: Seed data is auto-generated by permissionSyncAutoSeed.php
--        which reads live module_master from every domain DB.
--        Run: php base/permissionSyncAutoSeed.php
--
CREATE TABLE IF NOT EXISTS `global_module_map` (
    `id`              INT UNSIGNED      NOT NULL AUTO_INCREMENT,
    `global_key`      VARCHAR(80)       NOT NULL COMMENT 'Snake-case feature key derived from module name, e.g. lead_module, what_to_say',
    `domain_slug`     VARCHAR(40)       NOT NULL COMMENT 'Domain slug: ticto | floorsathi | chakraplus | revobin | solardharti',
    `local_module_id` SMALLINT UNSIGNED NOT NULL COMMENT 'module_master.id in that domain DB',
    `module_label`    VARCHAR(120)      NOT NULL DEFAULT '' COMMENT 'Human-readable label (= module_master.name)',
    `updated_at`      DATETIME          NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_key_domain` (`global_key`, `domain_slug`),
    KEY `idx_domain`     (`domain_slug`),
    KEY `idx_global_key` (`global_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='Global feature key → domain-local module ID mapping for cross-domain permission sync';

-- ── 2. Permission Sync Log ──────────────────────────────────
--  Full audit trail of every cross-domain sync operation.
--
CREATE TABLE IF NOT EXISTS `permission_sync_log` (
    `id`             INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    `role_id`        SMALLINT UNSIGNED NOT NULL COMMENT 'role_master.id that was synced',
    `global_keys`    TEXT          NOT NULL COMMENT 'JSON array of global keys that were synced',
    `domain_results` MEDIUMTEXT    NOT NULL COMMENT 'JSON: domain_slug => {ok,msg,ids}',
    `initiated_by`   INT UNSIGNED  NOT NULL DEFAULT 0 COMMENT 'master_user_details.id of CRM user who triggered sync',
    `synced_at`      DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_role`   (`role_id`),
    KEY `idx_synced` (`synced_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='Audit log for cross-domain module permission sync operations';

-- ── After creating the tables, populate with live data: ────
-- php base/permissionSyncAutoSeed.php
--
-- OR call the in-UI "Re-sync Map from DB" button on globalPermissionSync.php
-- which calls backendFilesFolders/permissionSync/reSeedMap.php

-- ── Reference: auto-discovered module list (38 modules, 5 domains = 190 rows) ─
-- Modules discovered from module_master on 2026-09-29:
-- add_employee, assign_to_expert, for_assign_lead, generate_lead,
-- lead_dashboard, lead_module, lead_process_log, lead_source,
-- location_management, main_menu_master, module_master, nature_of_business,
-- organic_lead, pending_lead_count, positive_lead_count, process_lead,
-- reassign_lead, report_date_wise, report_details, report_lead_wise,
-- setting, specialization, state_management, sub_location, sub_menu_master,
-- unassigned_lead, upload_lead, website, what_to_say,
-- about, add_employee, banner, designation_management, designation_master,
-- district_management, edit_employee, edit_permission, employee_master