-- ============================================================================
--  MEDMECS MIS REPORTING PLATFORM — Database Schema
--  Target:   MySQL 8.0+
--  Engine:   InnoDB
--  Charset:  utf8mb4 / utf8mb4_unicode_ci
--
--  Conventions
--  -----------
--  * Plural snake_case table names.
--  * BIGINT UNSIGNED surrogate primary keys (Laravel `id()`).
--  * created_at / updated_at on every business table.
--  * deleted_at (soft delete) on master tables only.
--  * JSON columns used liberally (MySQL 8 native JSON).
--  * Foreign keys: RESTRICT by default; CASCADE only on dependent child rows
--    where parent removal logically implies child removal.
--  * Indexes named idx_<table>_<cols>; uniques uq_<table>_<cols>; foreign
--    keys fk_<table>_<refs>.
--  * Audit / log tables: NO ON-DELETE cascade — historical evidence is
--    preserved even if related entities are removed (user_id may go NULL).
--
--  Import:  mysql -u root -p < schema.sql
--           (creates database `medmecs_mis` and all tables; idempotent)
-- ============================================================================

SET @OLD_FOREIGN_KEY_CHECKS = @@FOREIGN_KEY_CHECKS;
SET FOREIGN_KEY_CHECKS = 0;
SET @OLD_SQL_MODE = @@SQL_MODE;
SET SQL_MODE = 'STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION';

CREATE DATABASE IF NOT EXISTS `medmecs_mis`
  DEFAULT CHARACTER SET utf8mb4
  DEFAULT COLLATE utf8mb4_unicode_ci;

USE `medmecs_mis`;


-- ============================================================================
-- SECTION 1 — (Laravel framework tables removed 2026-05-12.  This stack uses
--             PHP native $_SESSION, no DB cache, no queue.)
-- ============================================================================


-- ============================================================================
-- SECTION 2 — ORG / MASTER DATA
-- ============================================================================

-- 2.1 verticals (Coding / Billing / AR / ...)
CREATE TABLE IF NOT EXISTS `verticals` (
  `id`          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name`        VARCHAR(150) NOT NULL,
  `code`        VARCHAR(50)  NOT NULL,
  `description` TEXT NULL,
  `status`      ENUM('active','inactive') NOT NULL DEFAULT 'active',
  `created_at`  TIMESTAMP NULL DEFAULT NULL,
  `updated_at`  TIMESTAMP NULL DEFAULT NULL,
  `deleted_at`  TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_verticals_code` (`code`),
  KEY `idx_verticals_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2.2 clients
CREATE TABLE IF NOT EXISTS `clients` (
  `id`          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `vertical_id` BIGINT UNSIGNED NOT NULL,
  `name`        VARCHAR(200) NOT NULL,
  `code`        VARCHAR(50)  NOT NULL,
  `spoc_name`   VARCHAR(150) NULL,
  `spoc_email`  VARCHAR(191) NULL,
  `spoc_phone`  VARCHAR(50)  NULL,
  `sla_notes`   TEXT NULL,
  `timezone`    VARCHAR(64)  NOT NULL DEFAULT 'Asia/Kolkata',
  `status`      ENUM('active','inactive') NOT NULL DEFAULT 'active',
  `created_at`  TIMESTAMP NULL DEFAULT NULL,
  `updated_at`  TIMESTAMP NULL DEFAULT NULL,
  `deleted_at`  TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_clients_code` (`code`),
  KEY `idx_clients_vertical` (`vertical_id`),
  KEY `idx_clients_status` (`status`),
  CONSTRAINT `fk_clients_vertical`
    FOREIGN KEY (`vertical_id`) REFERENCES `verticals`(`id`)
    ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2.3 client_branding (1:1 with clients)
CREATE TABLE IF NOT EXISTS `client_branding` (
  `client_id`                  BIGINT UNSIGNED NOT NULL,
  `logo_path`                  VARCHAR(255) NULL,
  `favicon_path`               VARCHAR(255) NULL,
  `primary_color`              VARCHAR(20)  NULL DEFAULT '#206bc4',
  `login_slug`                 VARCHAR(80)  NOT NULL,
  `login_message`              TEXT NULL,
  `show_historical_to_client`  TINYINT(1) NOT NULL DEFAULT 1,
  `allow_client_upload`        TINYINT(1) NOT NULL DEFAULT 0,
  `allow_raw_data`             TINYINT(1) NOT NULL DEFAULT 0,
  `updated_by`                 BIGINT UNSIGNED NULL,
  `updated_at`                 TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`client_id`),
  UNIQUE KEY `uq_client_branding_login_slug` (`login_slug`),
  CONSTRAINT `fk_client_branding_client`
    FOREIGN KEY (`client_id`) REFERENCES `clients`(`id`)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2.4 processes
CREATE TABLE IF NOT EXISTS `processes` (
  `id`          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `client_id`   BIGINT UNSIGNED NOT NULL,
  `name`        VARCHAR(200) NOT NULL,
  `code`        VARCHAR(50)  NOT NULL,
  `description` TEXT NULL,
  `status`      ENUM('active','inactive') NOT NULL DEFAULT 'active',
  `created_at`  TIMESTAMP NULL DEFAULT NULL,
  `updated_at`  TIMESTAMP NULL DEFAULT NULL,
  `deleted_at`  TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_processes_client_code` (`client_id`,`code`),
  KEY `idx_processes_client` (`client_id`),
  KEY `idx_processes_status` (`status`),
  CONSTRAINT `fk_processes_client`
    FOREIGN KEY (`client_id`) REFERENCES `clients`(`id`)
    ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ============================================================================
-- SECTION 3 — USERS, ROLES, PERMISSIONS, SCOPES
-- ============================================================================

-- 3.1 users (internal + client, single table, hard-isolated by user_type)
CREATE TABLE IF NOT EXISTS `users` (
  `id`                    BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name`                  VARCHAR(150) NOT NULL,
  `email`                 VARCHAR(191) NOT NULL,
  `email_verified_at`     TIMESTAMP NULL DEFAULT NULL,
  `password`              VARCHAR(255) NOT NULL,
  `remember_token`        VARCHAR(100) NULL,
  `user_type`             ENUM('internal','client') NOT NULL,
  `client_id`             BIGINT UNSIGNED NULL,
  `status`                ENUM('active','inactive','locked') NOT NULL DEFAULT 'active',
  `two_factor_enabled`    TINYINT(1) NOT NULL DEFAULT 0,
  `two_factor_secret`     VARCHAR(255) NULL,
  `must_change_password`  TINYINT(1) NOT NULL DEFAULT 0,
  `last_login_at`         TIMESTAMP NULL DEFAULT NULL,
  `last_login_ip`         VARCHAR(45) NULL,
  `login_attempts`        SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  `locked_until`          TIMESTAMP NULL DEFAULT NULL,
  `password_changed_at`   TIMESTAMP NULL DEFAULT NULL,
  `created_at`            TIMESTAMP NULL DEFAULT NULL,
  `updated_at`            TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_users_email` (`email`),
  KEY `idx_users_user_type` (`user_type`),
  KEY `idx_users_client` (`client_id`),
  KEY `idx_users_status` (`status`),
  CONSTRAINT `fk_users_client`
    FOREIGN KEY (`client_id`) REFERENCES `clients`(`id`)
    ON DELETE RESTRICT ON UPDATE CASCADE,
  -- Enforce isolation: client users MUST have a client_id; internal users MUST NOT.
  CONSTRAINT `chk_users_client_isolation`
    CHECK ((`user_type` = 'client' AND `client_id` IS NOT NULL)
        OR (`user_type` = 'internal' AND `client_id` IS NULL))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 3.2 roles
CREATE TABLE IF NOT EXISTS `roles` (
  `id`          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name`        VARCHAR(120) NOT NULL,
  `code`        VARCHAR(60)  NOT NULL,
  `description` TEXT NULL,
  `is_system`   TINYINT(1) NOT NULL DEFAULT 0,
  `scope`       ENUM('internal','client','both') NOT NULL DEFAULT 'internal',
  `created_at`  TIMESTAMP NULL DEFAULT NULL,
  `updated_at`  TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_roles_code` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 3.3 permissions
CREATE TABLE IF NOT EXISTS `permissions` (
  `id`          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `code`        VARCHAR(120) NOT NULL,
  `label`       VARCHAR(150) NOT NULL,
  `group_name`  VARCHAR(80)  NOT NULL,
  `description` TEXT NULL,
  `created_at`  TIMESTAMP NULL DEFAULT NULL,
  `updated_at`  TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_permissions_code` (`code`),
  KEY `idx_permissions_group` (`group_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 3.4 role_permissions (many-to-many)
CREATE TABLE IF NOT EXISTS `role_permissions` (
  `role_id`       BIGINT UNSIGNED NOT NULL,
  `permission_id` BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY (`role_id`,`permission_id`),
  KEY `idx_role_permissions_permission` (`permission_id`),
  CONSTRAINT `fk_role_permissions_role`
    FOREIGN KEY (`role_id`) REFERENCES `roles`(`id`)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_role_permissions_permission`
    FOREIGN KEY (`permission_id`) REFERENCES `permissions`(`id`)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 3.5 user_roles (many-to-many)
CREATE TABLE IF NOT EXISTS `user_roles` (
  `user_id` BIGINT UNSIGNED NOT NULL,
  `role_id` BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY (`user_id`,`role_id`),
  KEY `idx_user_roles_role` (`role_id`),
  CONSTRAINT `fk_user_roles_user`
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_user_roles_role`
    FOREIGN KEY (`role_id`) REFERENCES `roles`(`id`)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;





-- ============================================================================
-- SECTION 4 — REPORT DEFINITIONS
-- ============================================================================

-- 4.1 reports
CREATE TABLE IF NOT EXISTS `reports` (
  `id`                    BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `process_id`            BIGINT UNSIGNED NOT NULL,
  `name`                  VARCHAR(200) NOT NULL,
  `code`                  VARCHAR(80)  NOT NULL,
  `description`           TEXT NULL,
  `report_type`           ENUM('standard','custom') NOT NULL DEFAULT 'standard',
  `handler`               VARCHAR(64) NULL COMMENT 'Viewer routing key. NULL = generic report-view.php. collection_dashboard = admin/collection.php',
  `upload_strategy`       ENUM('append','replace_all','replace_by_period','upsert')
                            NOT NULL DEFAULT 'replace_all',
  `period_column`         VARCHAR(120) NULL,
  `unique_key_columns`    JSON NULL,
  `custom_handler_class`  VARCHAR(255) NULL,
  `status`                ENUM('draft','active','archived') NOT NULL DEFAULT 'draft',
  `created_by`            BIGINT UNSIGNED NULL,
  `deployed_at`           TIMESTAMP NULL DEFAULT NULL,
  `created_at`            TIMESTAMP NULL DEFAULT NULL,
  `updated_at`            TIMESTAMP NULL DEFAULT NULL,
  `deleted_at`            TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_reports_process_code` (`process_id`,`code`),
  KEY `idx_reports_process` (`process_id`),
  KEY `idx_reports_status` (`status`),
  KEY `idx_reports_created_by` (`created_by`),
  KEY `idx_reports_handler` (`handler`),
  CONSTRAINT `fk_reports_process`
    FOREIGN KEY (`process_id`) REFERENCES `processes`(`id`)
    ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT `fk_reports_created_by`
    FOREIGN KEY (`created_by`) REFERENCES `users`(`id`)
    ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 4.2 report_columns
CREATE TABLE IF NOT EXISTS `report_columns` (
  `id`                BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `report_id`         BIGINT UNSIGNED NOT NULL,
  `col_order`         SMALLINT UNSIGNED NOT NULL,
  `col_name`          VARCHAR(120) NOT NULL,
  `col_label`         VARCHAR(200) NOT NULL,
  `data_type`         ENUM('text','int','decimal','date','datetime','bool')
                        NOT NULL DEFAULT 'text',
  `is_required`       TINYINT(1) NOT NULL DEFAULT 0,
  `validation_rule`   JSON NULL,
  `is_groupable`      TINYINT(1) NOT NULL DEFAULT 0,
  `is_aggregatable`   TINYINT(1) NOT NULL DEFAULT 0,
  `default_agg`       ENUM('sum','count','avg','min','max','none')
                        NOT NULL DEFAULT 'none',
  `is_period_col`     TINYINT(1) NOT NULL DEFAULT 0,
  `is_key_col`        TINYINT(1) NOT NULL DEFAULT 0,
  `is_displayed`      TINYINT(1) NOT NULL DEFAULT 1,
  `created_at`        TIMESTAMP NULL DEFAULT NULL,
  `updated_at`        TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_report_columns_report_col` (`report_id`,`col_name`),
  KEY `idx_report_columns_report_order` (`report_id`,`col_order`),
  CONSTRAINT `fk_report_columns_report`
    FOREIGN KEY (`report_id`) REFERENCES `reports`(`id`)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 4.3 report_outputs
CREATE TABLE IF NOT EXISTS `report_outputs` (
  `id`             BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `report_id`      BIGINT UNSIGNED NOT NULL,
  `output_name`    VARCHAR(200) NOT NULL,
  `output_type`    ENUM('dashboard','summary','pivot','chart','kpi','detail')
                     NOT NULL,
  `group_by_cols`  JSON NULL,
  `agg_cols`       JSON NULL,
  `filter_rules`   JSON NULL,
  `sort_rules`     JSON NULL,
  `chart_config`   JSON NULL,
  `display_order`  SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  `created_at`     TIMESTAMP NULL DEFAULT NULL,
  `updated_at`     TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_report_outputs_report_type` (`report_id`,`output_type`),
  KEY `idx_report_outputs_display_order` (`report_id`,`display_order`),
  CONSTRAINT `fk_report_outputs_report`
    FOREIGN KEY (`report_id`) REFERENCES `reports`(`id`)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ============================================================================
-- SECTION 5 — DATA STORAGE (schema-agnostic via JSON)
-- ============================================================================

-- 5.1 report_uploads
CREATE TABLE IF NOT EXISTS `report_uploads` (
  `id`                 BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `report_id`          BIGINT UNSIGNED NOT NULL,
  `file_name`          VARCHAR(255) NOT NULL,
  `file_path`          VARCHAR(500) NOT NULL,
  `file_size`          BIGINT UNSIGNED NOT NULL DEFAULT 0,
  `file_hash`          CHAR(64) NULL,
  `uploaded_by`        BIGINT UNSIGNED NULL,
  `upload_mode_used`   ENUM('append','replace_all','replace_by_period','upsert')
                         NOT NULL,
  `period_label`       VARCHAR(50) NULL,   -- canonical label or comma-list
  `total_rows`         INT UNSIGNED NOT NULL DEFAULT 0,
  `valid_rows`         INT UNSIGNED NOT NULL DEFAULT 0,
  `error_rows`         INT UNSIGNED NOT NULL DEFAULT 0,
  `replaced_row_count` INT UNSIGNED NOT NULL DEFAULT 0,
  `status`             ENUM('pending','processing','completed','failed','archived')
                         NOT NULL DEFAULT 'pending',
  `started_at`         TIMESTAMP NULL DEFAULT NULL,
  `completed_at`       TIMESTAMP NULL DEFAULT NULL,
  `notes`              TEXT NULL,
  `created_at`         TIMESTAMP NULL DEFAULT NULL,
  `updated_at`         TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_report_uploads_report_status` (`report_id`,`status`),
  KEY `idx_report_uploads_report_created` (`report_id`,`created_at`),
  KEY `idx_report_uploads_uploaded_by` (`uploaded_by`),
  CONSTRAINT `fk_report_uploads_report`
    FOREIGN KEY (`report_id`) REFERENCES `reports`(`id`)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_report_uploads_user`
    FOREIGN KEY (`uploaded_by`) REFERENCES `users`(`id`)
    ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 5.2 report_data — generic JSON-typed row store
CREATE TABLE IF NOT EXISTS `report_data` (
  `id`            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `report_id`     BIGINT UNSIGNED NOT NULL,
  `upload_id`     BIGINT UNSIGNED NOT NULL,
  `row_index`     INT UNSIGNED NOT NULL,        -- 1-based source row in upload
  `period_label`  VARCHAR(50) NULL,
  `key_hash`      CHAR(32) NULL,                 -- MD5 of unique_key_columns concat
  `data_json`     JSON NOT NULL,
  `is_active`     TINYINT(1) NOT NULL DEFAULT 1,
  `created_at`    TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_report_data_report_period_active` (`report_id`,`period_label`,`is_active`),
  KEY `idx_report_data_report_keyhash` (`report_id`,`key_hash`),
  KEY `idx_report_data_upload` (`upload_id`),
  CONSTRAINT `fk_report_data_report`
    FOREIGN KEY (`report_id`) REFERENCES `reports`(`id`)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_report_data_upload`
    FOREIGN KEY (`upload_id`) REFERENCES `report_uploads`(`id`)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 5.3 report_data_archive — same shape as report_data + archive metadata
CREATE TABLE IF NOT EXISTS `report_data_archive` (
  `id`                  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `report_id`           BIGINT UNSIGNED NOT NULL,
  `upload_id`           BIGINT UNSIGNED NULL,
  `original_upload_id`  BIGINT UNSIGNED NULL,
  `row_index`           INT UNSIGNED NOT NULL,
  `period_label`        VARCHAR(50) NULL,
  `key_hash`            CHAR(32) NULL,
  `data_json`           JSON NOT NULL,
  `is_active`           TINYINT(1) NOT NULL DEFAULT 0,
  `archived_at`         TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `archived_reason`     VARCHAR(120) NULL,
  `created_at`          TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_report_data_archive_report_period` (`report_id`,`period_label`),
  KEY `idx_report_data_archive_original` (`original_upload_id`),
  CONSTRAINT `fk_rda_report`
    FOREIGN KEY (`report_id`) REFERENCES `reports`(`id`)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 5.4 upload_errors
CREATE TABLE IF NOT EXISTS `upload_errors` (
  `id`            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `upload_id`     BIGINT UNSIGNED NOT NULL,
  `row_index`     INT UNSIGNED NOT NULL,
  `col_name`      VARCHAR(120) NULL,
  `error_code`    VARCHAR(60)  NOT NULL,
  `error_message` TEXT NOT NULL,
  `created_at`    TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_upload_errors_upload` (`upload_id`),
  CONSTRAINT `fk_upload_errors_upload`
    FOREIGN KEY (`upload_id`) REFERENCES `report_uploads`(`id`)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 5.5 generated_outputs (materialized result cache)
CREATE TABLE IF NOT EXISTS `generated_outputs` (
  `id`           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `report_id`    BIGINT UNSIGNED NOT NULL,
  `output_id`    BIGINT UNSIGNED NOT NULL,
  `upload_id`    BIGINT UNSIGNED NOT NULL,
  `generated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `data_json`    LONGTEXT NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_generated_outputs_lookup` (`report_id`,`output_id`,`upload_id`),
  CONSTRAINT `fk_generated_outputs_report`
    FOREIGN KEY (`report_id`) REFERENCES `reports`(`id`)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_generated_outputs_output`
    FOREIGN KEY (`output_id`) REFERENCES `report_outputs`(`id`)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_generated_outputs_upload`
    FOREIGN KEY (`upload_id`) REFERENCES `report_uploads`(`id`)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ============================================================================
-- SECTION 6 — REPORT-LEVEL ACCESS OVERRIDES
-- ============================================================================

CREATE TABLE IF NOT EXISTS `report_access` (
  `id`            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `report_id`     BIGINT UNSIGNED NOT NULL,
  `user_id`       BIGINT UNSIGNED NOT NULL,
  `can_view`      TINYINT(1) NOT NULL DEFAULT 0,
  `can_upload`    TINYINT(1) NOT NULL DEFAULT 0,
  `can_download`  TINYINT(1) NOT NULL DEFAULT 0,
  `can_delete`    TINYINT(1) NOT NULL DEFAULT 0,
  `can_replace`   TINYINT(1) NOT NULL DEFAULT 0,
  `granted_by`    BIGINT UNSIGNED NULL,
  `granted_at`    TIMESTAMP NULL DEFAULT NULL,
  `expires_at`    TIMESTAMP NULL DEFAULT NULL,
  `created_at`    TIMESTAMP NULL DEFAULT NULL,
  `updated_at`    TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_report_access_report_user` (`report_id`,`user_id`),
  KEY `idx_report_access_user` (`user_id`),
  CONSTRAINT `fk_report_access_report`
    FOREIGN KEY (`report_id`) REFERENCES `reports`(`id`)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_report_access_user`
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_report_access_granted_by`
    FOREIGN KEY (`granted_by`) REFERENCES `users`(`id`)
    ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ============================================================================
-- SECTION 7 — AUDIT, LOGS, OPS
-- ============================================================================

-- 7.1 audit_logs
CREATE TABLE IF NOT EXISTS `audit_logs` (
  `id`           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id`      BIGINT UNSIGNED NULL,
  `action`       VARCHAR(80)  NOT NULL,
  `entity_type`  VARCHAR(120) NOT NULL,
  `entity_id`    BIGINT UNSIGNED NULL,
  `before_json`  JSON NULL,
  `after_json`   JSON NULL,
  `ip`           VARCHAR(45) NULL,
  `user_agent`   VARCHAR(255) NULL,
  `created_at`   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_audit_logs_user_created` (`user_id`,`created_at`),
  KEY `idx_audit_logs_entity` (`entity_type`,`entity_id`),
  KEY `idx_audit_logs_action` (`action`),
  CONSTRAINT `fk_audit_logs_user`
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`)
    ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 7.2 login_logs
CREATE TABLE IF NOT EXISTS `login_logs` (
  `id`              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id`         BIGINT UNSIGNED NULL,
  `email_attempted` VARCHAR(191) NOT NULL,
  `ip`              VARCHAR(45) NULL,
  `user_agent`      VARCHAR(255) NULL,
  `status`          ENUM('success','failed','locked','2fa_required','2fa_failed')
                      NOT NULL,
  `failure_reason`  VARCHAR(150) NULL,
  `created_at`      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_login_logs_email_created` (`email_attempted`,`created_at`),
  KEY `idx_login_logs_user_created` (`user_id`,`created_at`),
  KEY `idx_login_logs_status` (`status`),
  CONSTRAINT `fk_login_logs_user`
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`)
    ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 7.3 download_logs
CREATE TABLE IF NOT EXISTS `download_logs` (
  `id`           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id`      BIGINT UNSIGNED NULL,
  `report_id`    BIGINT UNSIGNED NULL,
  `output_id`    BIGINT UNSIGNED NULL,
  `file_name`    VARCHAR(255) NOT NULL,
  `format`       VARCHAR(20)  NOT NULL,    -- xlsx, csv, pdf
  `filter_json`  JSON NULL,
  `ip`           VARCHAR(45) NULL,
  `created_at`   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_download_logs_user_created` (`user_id`,`created_at`),
  KEY `idx_download_logs_report_created` (`report_id`,`created_at`),
  CONSTRAINT `fk_download_logs_user`
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`)
    ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `fk_download_logs_report`
    FOREIGN KEY (`report_id`) REFERENCES `reports`(`id`)
    ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `fk_download_logs_output`
    FOREIGN KEY (`output_id`) REFERENCES `report_outputs`(`id`)
    ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 7.4 upload_logs
CREATE TABLE IF NOT EXISTS `upload_logs` (
  `id`         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `upload_id`  BIGINT UNSIGNED NOT NULL,
  `action`     VARCHAR(60)  NOT NULL,
  `message`    TEXT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_upload_logs_upload_created` (`upload_id`,`created_at`),
  CONSTRAINT `fk_upload_logs_upload`
    FOREIGN KEY (`upload_id`) REFERENCES `report_uploads`(`id`)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ============================================================================
-- SECTION 8 — SCHEDULED REPORTS, NOTIFICATIONS, SETTINGS
-- ============================================================================

-- 8.1 scheduled_reports
CREATE TABLE IF NOT EXISTS `scheduled_reports` (
  `id`                    BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `report_id`             BIGINT UNSIGNED NOT NULL,
  `output_id`             BIGINT UNSIGNED NULL,
  `name`                  VARCHAR(200) NOT NULL,
  `cron_expression`       VARCHAR(100) NOT NULL,
  `recipients`            JSON NOT NULL,
  `output_formats`        JSON NOT NULL,
  `subject_template`      VARCHAR(255) NOT NULL,
  `body_template`         TEXT NOT NULL,
  `last_run_at`           TIMESTAMP NULL DEFAULT NULL,
  `last_run_status`       ENUM('success','failed','skipped','running') NULL,
  `next_run_at`           TIMESTAMP NULL DEFAULT NULL,
  `consecutive_failures`  SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  `status`                ENUM('active','paused','disabled') NOT NULL DEFAULT 'active',
  `created_by`            BIGINT UNSIGNED NULL,
  `created_at`            TIMESTAMP NULL DEFAULT NULL,
  `updated_at`            TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_scheduled_reports_next_run` (`status`,`next_run_at`),
  KEY `idx_scheduled_reports_report` (`report_id`),
  CONSTRAINT `fk_scheduled_reports_report`
    FOREIGN KEY (`report_id`) REFERENCES `reports`(`id`)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_scheduled_reports_output`
    FOREIGN KEY (`output_id`) REFERENCES `report_outputs`(`id`)
    ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `fk_scheduled_reports_created_by`
    FOREIGN KEY (`created_by`) REFERENCES `users`(`id`)
    ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 8.2 notifications
CREATE TABLE IF NOT EXISTS `notifications` (
  `id`         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id`    BIGINT UNSIGNED NOT NULL,
  `type`       VARCHAR(80)  NOT NULL,
  `title`      VARCHAR(200) NOT NULL,
  `message`    TEXT NOT NULL,
  `link`       VARCHAR(500) NULL,
  `is_read`    TINYINT(1) NOT NULL DEFAULT 0,
  `read_at`    TIMESTAMP NULL DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_notifications_user_unread` (`user_id`,`is_read`,`created_at`),
  CONSTRAINT `fk_notifications_user`
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 8.3 settings (key/value with typed value)
CREATE TABLE IF NOT EXISTS `settings` (
  `key`         VARCHAR(120) NOT NULL,
  `value`       LONGTEXT NULL,
  `value_type`  ENUM('string','int','bool','json','text') NOT NULL DEFAULT 'string',
  `group_name`  VARCHAR(60)  NULL,
  `is_secret`   TINYINT(1) NOT NULL DEFAULT 0,
  `updated_by`  BIGINT UNSIGNED NULL,
  `updated_at`  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
                  ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`key`),
  KEY `idx_settings_group` (`group_name`),
  CONSTRAINT `fk_settings_user`
    FOREIGN KEY (`updated_by`) REFERENCES `users`(`id`)
    ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ============================================================================
-- SECTION 9 — SYSTEM REFERENCE INDEX (auto-populated by reference:scan)
-- ============================================================================

-- 9.1 system_reference_pages
CREATE TABLE IF NOT EXISTS `system_reference_pages` (
  `id`              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `module`          VARCHAR(80)  NOT NULL,
  `page_name`       VARCHAR(150) NOT NULL,
  `file_path`       VARCHAR(500) NOT NULL,
  `description`     TEXT NULL,
  `roles_allowed`   JSON NULL,
  `last_scanned_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_srp_file_path` (`file_path`),
  KEY `idx_srp_module` (`module`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 9.2 system_reference_changelog
CREATE TABLE IF NOT EXISTS `system_reference_changelog` (
  `id`               BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `version`          VARCHAR(40) NOT NULL,
  `change_type`      ENUM('FIX','FEATURE','UPDATE','BREAKING') NOT NULL,
  `title`            VARCHAR(200) NOT NULL,
  `description`      TEXT NULL,
  `files_affected`   JSON NULL,
  `tables_affected`  JSON NULL,
  `changed_by`       BIGINT UNSIGNED NULL,
  `changed_at`       TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_src_changelog_version` (`version`),
  KEY `idx_src_changelog_type` (`change_type`),
  CONSTRAINT `fk_src_changelog_user`
    FOREIGN KEY (`changed_by`) REFERENCES `users`(`id`)
    ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 9.5 system_reference_errors
CREATE TABLE IF NOT EXISTS `system_reference_errors` (
  `id`           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `error_level`  ENUM('debug','info','notice','warning','error','critical','alert','emergency')
                   NOT NULL DEFAULT 'error',
  `message`      TEXT NOT NULL,
  `file`         VARCHAR(500) NULL,
  `line`         INT UNSIGNED NULL,
  `trace`        LONGTEXT NULL,
  `user_id`      BIGINT UNSIGNED NULL,
  `ip`           VARCHAR(45) NULL,
  `url`          VARCHAR(500) NULL,
  `method`       VARCHAR(10)  NULL,
  `occurred_at`  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `resolved`     TINYINT(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `idx_src_errors_occurred`     (`occurred_at`),
  KEY `idx_src_errors_resolved`     (`resolved`),
  KEY `idx_src_errors_level_resolved` (`error_level`,`resolved`),
  CONSTRAINT `fk_src_errors_user`
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`)
    ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ============================================================================
-- SECTION 10 — SEED DATA
--   * Default permissions, roles, role-permission mapping
--   * Default platform settings
--   * Default sysadmin user
--   * Default verticals (Billing)
--
--   NOTE: client / process / report seeds for the live customer are in
--   sql/collection-dashboard.sql (PY-Medical SNF + Collection Dashboard).
-- ============================================================================

-- 10.1 PERMISSIONS
INSERT INTO `permissions` (`code`,`label`,`group_name`,`description`,`created_at`,`updated_at`) VALUES
-- master data
('vertical.view',           'View Verticals',           'master',  'View vertical list & details', NOW(), NOW()),
('vertical.manage',         'Manage Verticals',         'master',  'Create, edit, archive verticals', NOW(), NOW()),
('client.view',             'View Clients',             'master',  'View client list & details', NOW(), NOW()),
('client.manage',           'Manage Clients',           'master',  'Create, edit, archive clients', NOW(), NOW()),
('client.branding.manage',  'Manage Client Branding',   'master',  'Edit branded login pages and colors', NOW(), NOW()),
('process.view',            'View Processes',           'master',  'View process list & details', NOW(), NOW()),
('process.manage',          'Manage Processes',         'master',  'Create, edit, archive processes', NOW(), NOW()),
-- users / access
('user.internal.view',      'View Internal Users',      'users',   'See internal user list', NOW(), NOW()),
('user.internal.manage',    'Manage Internal Users',    'users',   'CRUD on internal users', NOW(), NOW()),
('user.client.view',        'View Client Users',        'users',   'See client user list', NOW(), NOW()),
('user.client.manage',      'Manage Client Users',      'users',   'CRUD on client users', NOW(), NOW()),
('role.manage',             'Manage Roles',             'users',   'Create custom roles, edit permissions', NOW(), NOW()),
('user.scope.assign',       'Assign User Scopes',       'users',   'Grant vertical/client/process access', NOW(), NOW()),
-- reports
('report.definition.view',  'View Report Definitions',  'reports', 'View report builder definitions', NOW(), NOW()),
('report.definition.manage','Manage Report Definitions','reports', 'Create / edit reports & schemas', NOW(), NOW()),
('report.view',             'View Reports',             'reports', 'View report outputs', NOW(), NOW()),
('report.view_detail',      'View Detail Tab',          'reports', 'Access raw row-level data', NOW(), NOW()),
('report.upload',           'Upload Data',              'reports', 'Upload data to a report', NOW(), NOW()),
('report.replace_all',      'Replace All Data',         'reports', 'Wipe-and-load uploads', NOW(), NOW()),
('report.download',         'Download Reports',         'reports', 'Excel / CSV / PDF export', NOW(), NOW()),
('report.delete',           'Delete Report Data',       'reports', 'Delete uploaded data', NOW(), NOW()),
('report.restore',          'Restore Snapshots',        'reports', 'Restore historical uploads', NOW(), NOW()),
-- access control
('report.access.view',      'View Report Access',       'access',  'View access matrix', NOW(), NOW()),
('report.access.grant',     'Grant Report Access',      'access',  'Grant or revoke report access', NOW(), NOW()),
('report.access.audit',     'Audit Report Access',      'access',  'View access change history', NOW(), NOW()),
-- ops
('schedule.manage',         'Manage Scheduled Reports', 'ops',     'Create / edit scheduled reports', NOW(), NOW()),
('audit.view',              'View Audit Logs',          'ops',     'Read audit / login / download logs', NOW(), NOW()),
('settings.view',           'View Settings',            'ops',     'View system settings', NOW(), NOW()),
('settings.manage',         'Manage Settings',          'ops',     'Edit system settings (SMTP, branding, ...)', NOW(), NOW()),
('backup.manage',           'Manage Backups',           'ops',     'Run / restore backups', NOW(), NOW()),
('reference.view',          'View System Reference',    'ops',     'Access System Reference Index', NOW(), NOW())
ON DUPLICATE KEY UPDATE `label`=VALUES(`label`),`group_name`=VALUES(`group_name`),`description`=VALUES(`description`),`updated_at`=NOW();

-- 10.2 ROLES
INSERT INTO `roles` (`code`,`name`,`description`,`is_system`,`scope`,`created_at`,`updated_at`) VALUES
('sysadmin',          'SysAdmin',          'Full platform access — all permissions',                       1,'internal',NOW(),NOW()),
('developer',         'Developer',         'Build report definitions and custom handlers',                 1,'internal',NOW(),NOW()),
('vertical_manager',  'Vertical Manager',  'Manage clients/processes within assigned verticals',           1,'internal',NOW(),NOW()),
('client_manager',    'Client Manager',    'Manage processes & reports for assigned clients',              1,'internal',NOW(),NOW()),
('process_lead',      'Process Lead',      'Run reports & uploads within assigned processes',              1,'internal',NOW(),NOW()),
('analyst',           'Analyst',           'View reports, upload data, download outputs',                  1,'internal',NOW(),NOW()),
('auditor',           'Auditor',           'Read-only access to reports + full audit trail',               1,'internal',NOW(),NOW()),
('report_controller', 'Report Controller', 'Manage report access (grant/revoke/audit) only',               1,'internal',NOW(),NOW()),
('client_admin',      'Client Admin',      'Manage own client portal, view all client reports',            1,'client',  NOW(),NOW()),
('client_viewer',     'Client Viewer',     'View dashboards / summaries for assigned client',              1,'client',  NOW(),NOW())
ON DUPLICATE KEY UPDATE `name`=VALUES(`name`),`description`=VALUES(`description`),`updated_at`=NOW();

-- 10.3 ROLE -> PERMISSIONS
-- SysAdmin gets everything
INSERT IGNORE INTO `role_permissions` (`role_id`,`permission_id`)
  SELECT r.id, p.id FROM `roles` r CROSS JOIN `permissions` p
  WHERE r.code = 'sysadmin';

-- Developer
INSERT IGNORE INTO `role_permissions` (`role_id`,`permission_id`)
  SELECT r.id, p.id FROM `roles` r JOIN `permissions` p
    ON p.code IN (
      'vertical.view','client.view','process.view',
      'report.definition.view','report.definition.manage',
      'report.view','report.view_detail','report.upload','report.download',
      'reference.view','audit.view'
    )
  WHERE r.code = 'developer';

-- Vertical Manager
INSERT IGNORE INTO `role_permissions` (`role_id`,`permission_id`)
  SELECT r.id, p.id FROM `roles` r JOIN `permissions` p
    ON p.code IN (
      'vertical.view','client.view','client.manage','process.view','process.manage',
      'user.internal.view','user.client.view',
      'report.definition.view','report.view','report.view_detail',
      'report.upload','report.download','report.access.view','audit.view'
    )
  WHERE r.code = 'vertical_manager';

-- Client Manager
INSERT IGNORE INTO `role_permissions` (`role_id`,`permission_id`)
  SELECT r.id, p.id FROM `roles` r JOIN `permissions` p
    ON p.code IN (
      'client.view','process.view','process.manage',
      'user.client.view','user.client.manage',
      'report.definition.view','report.view','report.view_detail',
      'report.upload','report.download','report.access.view','audit.view'
    )
  WHERE r.code = 'client_manager';

-- Process Lead
INSERT IGNORE INTO `role_permissions` (`role_id`,`permission_id`)
  SELECT r.id, p.id FROM `roles` r JOIN `permissions` p
    ON p.code IN (
      'process.view','report.definition.view',
      'report.view','report.view_detail','report.upload','report.download'
    )
  WHERE r.code = 'process_lead';

-- Analyst
INSERT IGNORE INTO `role_permissions` (`role_id`,`permission_id`)
  SELECT r.id, p.id FROM `roles` r JOIN `permissions` p
    ON p.code IN (
      'report.view','report.view_detail','report.upload','report.download'
    )
  WHERE r.code = 'analyst';

-- Auditor
INSERT IGNORE INTO `role_permissions` (`role_id`,`permission_id`)
  SELECT r.id, p.id FROM `roles` r JOIN `permissions` p
    ON p.code IN (
      'vertical.view','client.view','process.view',
      'report.definition.view','report.view','report.view_detail',
      'audit.view','report.access.view','report.access.audit','reference.view'
    )
  WHERE r.code = 'auditor';

-- Report Controller
INSERT IGNORE INTO `role_permissions` (`role_id`,`permission_id`)
  SELECT r.id, p.id FROM `roles` r JOIN `permissions` p
    ON p.code IN (
      'report.access.view','report.access.grant','report.access.audit','audit.view'
    )
  WHERE r.code = 'report_controller';

-- Client Admin
INSERT IGNORE INTO `role_permissions` (`role_id`,`permission_id`)
  SELECT r.id, p.id FROM `roles` r JOIN `permissions` p
    ON p.code IN (
      'report.view','report.download','user.client.view','user.client.manage'
    )
  WHERE r.code = 'client_admin';

-- Client Viewer
INSERT IGNORE INTO `role_permissions` (`role_id`,`permission_id`)
  SELECT r.id, p.id FROM `roles` r JOIN `permissions` p
    ON p.code IN ('report.view','report.download')
  WHERE r.code = 'client_viewer';

-- 10.4 SETTINGS
INSERT INTO `settings` (`key`,`value`,`value_type`,`group_name`,`is_secret`) VALUES
  ('platform.name',           'Medmecs MIS',  'string','branding',0),
  ('platform.primary_color',  '#206bc4',      'string','branding',0),
  ('platform.logo_path',      '/img/logo.svg','string','branding',0),
  ('platform.favicon_path',   '/img/favicon.png','string','branding',0),
  ('security.password_min_length', '10',      'int',   'security',0),
  ('security.password_expiry_days','90',      'int',   'security',0),
  ('security.login_max_attempts',  '5',       'int',   'security',0),
  ('security.login_lockout_minutes','15',     'int',   'security',0),
  ('security.2fa_required_clients','1',       'bool',  'security',0),
  ('security.2fa_required_internal','0',      'bool',  'security',0),
  ('session.timeout_internal_min', '30',      'int',   'session', 0),
  ('session.timeout_client_min',   '15',      'int',   'session', 0),
  ('upload.max_bytes',        '104857600',    'int',   'upload',  0),
  ('upload.allowed_ext',      '["xlsx","xls","csv"]','json','upload',0),
  ('upload.sync_row_threshold','1000',        'int',   'upload',  0),
  ('audit.retention_days',    '730',          'int',   'audit',   0),
  ('archive.retention_days',  '90',           'int',   'archive', 0),
  ('backup.retention_days',   '30',           'int',   'backup',  0)
ON DUPLICATE KEY UPDATE `value`=VALUES(`value`),`updated_at`=NOW();

-- 10.5 DEFAULT SYSADMIN USER
--   email:    admin@medmecs.local
--   password: ChangeMe@2026   (bcrypt below — change on first login)
INSERT INTO `users` (`name`,`email`,`password`,`user_type`,`status`,`must_change_password`,`password_changed_at`,`created_at`,`updated_at`)
VALUES (
  'Platform Administrator',
  'admin@medmecs.local',
  '$2b$12$dmGm2D.M5q5pExEJofNjkek0UAJTjfYUJwGZRaXLF1gntCafWiOwu',  -- bcrypt of ChangeMe@2026 (verified with PHP password_verify)
  'internal','active',1,NOW(),NOW(),NOW()
)
ON DUPLICATE KEY UPDATE `updated_at` = NOW();

-- Assign sysadmin role
INSERT IGNORE INTO `user_roles` (`user_id`,`role_id`)
  SELECT u.id, r.id FROM `users` u, `roles` r
  WHERE u.email = 'admin@medmecs.local' AND r.code = 'sysadmin';

-- 10.6 SAMPLE VERTICAL / CLIENT / PROCESS
INSERT INTO `verticals` (`name`,`code`,`description`,`status`,`created_at`,`updated_at`) VALUES
  ('Billing','BILLING','Revenue cycle billing operations','active',NOW(),NOW())
ON DUPLICATE KEY UPDATE `updated_at`=NOW();

-- (Demo client / branding / process / reports / columns / outputs for
--  "Blue Central Healthcare" were removed on 2026-05-11.  The report
--  template code (admin/report-view.php, includes/report-engine.php,
--  generic report-columns/outputs schema) is fully retained so the
--  same dashboard format can be used for any new report created via
--  Reports → Report builder.)


-- 10.8 INITIAL CHANGELOG ENTRY
INSERT INTO `system_reference_changelog`
  (`version`,`change_type`,`title`,`description`,`files_affected`,`tables_affected`,`changed_at`)
VALUES (
  '0.1.0','FEATURE','Initial schema & seed data',
  'Created 40-table schema, default roles + permissions, sysadmin user. Demo data removed; live client seeds in sql/collection-dashboard.sql.',
  JSON_ARRAY('sql/schema.sql','database/migrations/*','database/seeders/*'),
  JSON_ARRAY('verticals','clients','processes','reports','report_columns','report_outputs','users','roles','permissions','settings'),
  NOW()
);


-- ============================================================================
-- Restore session vars
-- ============================================================================
SET FOREIGN_KEY_CHECKS = @OLD_FOREIGN_KEY_CHECKS;
SET SQL_MODE = @OLD_SQL_MODE;

-- =====  END OF schema.sql  ==================================================
