-- =============================================================================
-- NHANCE DB changes: 2026-07-01 through 2026-08-12
-- Consolidated from app/Database/*.sql and Migrations (chronological).
-- Safe-ish to re-run where noted (IF NOT EXISTS / information_schema checks).
-- Demo seed (digit_motor_demo_seed.sql) omitted — schema only + retail reminder seed.
-- =============================================================================

-- -----------------------------------------------------------------------------
-- 2026-07-06 | Insurer claim form columns
-- Source: app/Database/insurers_add_claim_form_column.sql
-- -----------------------------------------------------------------------------
SET @col := (
    SELECT COUNT(1) FROM information_schema.columns
    WHERE table_schema = DATABASE()
      AND table_name = 'insurers'
      AND column_name = 'insurer_claim_form'
);
SET @sql := IF(@col = 0,
    'ALTER TABLE insurers ADD COLUMN insurer_claim_form VARCHAR(255) NULL DEFAULT NULL AFTER insurer_logo',
    'SELECT ''insurers.insurer_claim_form already exists'' AS info'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @col := (
    SELECT COUNT(1) FROM information_schema.columns
    WHERE table_schema = DATABASE()
      AND table_name = 'insurers'
      AND column_name = 'insurer_claim_form_original_name'
);
SET @sql := IF(@col = 0,
    'ALTER TABLE insurers ADD COLUMN insurer_claim_form_original_name VARCHAR(255) NULL DEFAULT NULL AFTER insurer_claim_form',
    'SELECT ''insurers.insurer_claim_form_original_name already exists'' AS info'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- -----------------------------------------------------------------------------
-- 2026-07-13 | BDS report performance indexes
-- Source: app/Database/bds_report_performance_indexes.sql
-- -----------------------------------------------------------------------------
SET @idx := (
    SELECT COUNT(1) FROM information_schema.statistics
    WHERE table_schema = DATABASE()
      AND table_name = 'policy_transaction'
      AND index_name = 'idx_pt_active_created'
);
SET @sql := IF(@idx = 0,
    'CREATE INDEX idx_pt_active_created ON policy_transaction (is_active, created_at)',
    'SELECT ''idx_pt_active_created already exists'' AS info'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @idx := (
    SELECT COUNT(1) FROM information_schema.statistics
    WHERE table_schema = DATABASE()
      AND table_name = 'pt_co_share_details'
      AND index_name = 'idx_pcsd_pt_active'
);
SET @sql := IF(@idx = 0,
    'CREATE INDEX idx_pcsd_pt_active ON pt_co_share_details (pt_id, is_active)',
    'SELECT ''idx_pcsd_pt_active already exists'' AS info'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @idx := (
    SELECT COUNT(1) FROM information_schema.statistics
    WHERE table_schema = DATABASE()
      AND table_name = 'co_share_stmt_details'
      AND index_name = 'idx_cssd_coshare_stmt_active'
);
SET @sql := IF(@idx = 0,
    'CREATE INDEX idx_cssd_coshare_stmt_active ON co_share_stmt_details (co_share_id, statement_id, is_active)',
    'SELECT ''idx_cssd_coshare_stmt_active already exists'' AS info'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @idx := (
    SELECT COUNT(1) FROM information_schema.statistics
    WHERE table_schema = DATABASE()
      AND table_name = 'insurer_statements'
      AND index_name = 'idx_insq_active_month_invoice'
);
SET @sql := IF(@idx = 0,
    'CREATE INDEX idx_insq_active_month_invoice ON insurer_statements (is_active, month, invoice_status)',
    'SELECT ''idx_insq_active_month_invoice already exists'' AS info'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- -----------------------------------------------------------------------------
-- 2026-07-21 / 2026-07-23 / 2026-07-30 | Digit Motor module tables (final schema)
-- Sources: digit_motor_tables.sql, digit_motor_master_tables.sql
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `motor_token` (
    `id`             BIGINT PRIMARY KEY AUTO_INCREMENT,
    `environment`    VARCHAR(20) NOT NULL DEFAULT 'staging',
    `access_token`   TEXT NOT NULL,
    `refresh_token`  TEXT,
    `expires_at`     TIMESTAMP NOT NULL,
    `created_at`     TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY `uq_motor_token_env` (`environment`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `motor_quote` (
    `id`                          BIGINT PRIMARY KEY AUTO_INCREMENT,
    `enquiry_id`                  VARCHAR(64) NOT NULL,
    `quote_number`                VARCHAR(32) DEFAULT NULL,
    `application_id`              VARCHAR(255) DEFAULT NULL,
    `policy_holder_type`          VARCHAR(20) NOT NULL DEFAULT 'INDIVIDUAL',
    `insurance_product_code`      VARCHAR(10) NOT NULL,
    `sub_insurance_product_code`  VARCHAR(10) NOT NULL DEFAULT 'PB',
    `previous_insurer_code`       SMALLINT DEFAULT NULL,
    `previous_policy_expiry_date` DATE DEFAULT NULL,
    `external_policy_number`      VARCHAR(32) DEFAULT NULL,
    `is_ncb_transfer`             TINYINT(1) DEFAULT 0,
    `start_date`                  DATE DEFAULT NULL,
    `end_date`                    DATE DEFAULT NULL,
    `pincode`                     VARCHAR(6) NOT NULL,
    `coverage_details`            JSON DEFAULT NULL,
    `policyholder_details`        JSON DEFAULT NULL,
    `premium`                     DECIMAL(12,2) DEFAULT NULL,
    `idv`                         DECIMAL(12,2) DEFAULT NULL,
    `status`                      VARCHAR(20) NOT NULL DEFAULT 'DRAFT',
    `created_by`                  INT DEFAULT NULL,
    `updated_by`                  INT DEFAULT NULL,
    `created_at`                  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`                  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY `uq_motor_quote_enquiry` (`enquiry_id`),
    KEY `idx_motor_quote_status` (`status`),
    KEY `idx_motor_quote_quote_number` (`quote_number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `motor_vehicle` (
    `id`                              BIGINT PRIMARY KEY AUTO_INCREMENT,
    `quote_id`                        BIGINT NOT NULL,
    `is_vehicle_new`                  TINYINT(1) NOT NULL DEFAULT 0,
    `vehicle_maincode`                VARCHAR(30) NOT NULL,
    `license_plate_number`            VARCHAR(12) NOT NULL,
    `vehicle_identification_number`   VARCHAR(30) DEFAULT NULL,
    `engine_number`                   VARCHAR(30) DEFAULT NULL,
    `manufacture_date`                DATE NOT NULL,
    `registration_date`               DATE NOT NULL,
    `registration_authority`          VARCHAR(10) DEFAULT NULL,
    `idv`                             DECIMAL(12,2) DEFAULT NULL,
    `default_idv`                     DECIMAL(12,2) DEFAULT NULL,
    `minimum_idv`                     DECIMAL(12,2) DEFAULT NULL,
    `maximum_idv`                     DECIMAL(12,2) DEFAULT NULL,
    UNIQUE KEY `uq_motor_vehicle_quote` (`quote_id`),
    CONSTRAINT `fk_motor_vehicle_quote` FOREIGN KEY (`quote_id`) REFERENCES `motor_quote`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `motor_kyc` (
    `id`                              BIGINT PRIMARY KEY AUTO_INCREMENT,
    `quote_id`                        BIGINT NOT NULL,
    `kyc_id`                          VARCHAR(64) DEFAULT NULL,
    `kyc_verification_status`         VARCHAR(20) DEFAULT NULL,
    `reference_id`                    VARCHAR(64) DEFAULT NULL,
    `link`                            VARCHAR(512) DEFAULT NULL,
    `mismatch_type`                   VARCHAR(40) DEFAULT NULL,
    `id_verification_doc_type`        VARCHAR(40) DEFAULT NULL,
    `address_verification_doc_type`   VARCHAR(40) DEFAULT NULL,
    `mode`                            CHAR(1) DEFAULT 'O',
    `checked_at`                      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY `idx_motor_kyc_quote` (`quote_id`),
    CONSTRAINT `fk_motor_kyc_quote` FOREIGN KEY (`quote_id`) REFERENCES `motor_quote`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `motor_payment` (
    `id`                    BIGINT PRIMARY KEY AUTO_INCREMENT,
    `quote_id`              BIGINT NOT NULL,
    `application_id`        VARCHAR(255) NOT NULL,
    `digit_payment_id`      VARCHAR(64) DEFAULT NULL,
    `request_reference`     VARCHAR(64) DEFAULT NULL,
    `payment_mode`          VARCHAR(5) DEFAULT 'EB',
    `cancel_return_url`     VARCHAR(512) DEFAULT NULL,
    `success_return_url`    VARCHAR(512) DEFAULT NULL,
    `dispatcher_response`   VARCHAR(512) DEFAULT NULL,
    `premium`               DECIMAL(12,2) DEFAULT NULL,
    `payment_status`        VARCHAR(20) DEFAULT NULL,
    `created_at`            TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY `idx_motor_payment_quote` (`quote_id`),
    CONSTRAINT `fk_motor_payment_quote` FOREIGN KEY (`quote_id`) REFERENCES `motor_quote`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `motor_policy` (
    `id`                BIGINT PRIMARY KEY AUTO_INCREMENT,
    `quote_id`          BIGINT NOT NULL,
    `policy_number`     VARCHAR(32) DEFAULT NULL,
    `policy_status`     VARCHAR(20) DEFAULT NULL,
    `schedule_path`     VARCHAR(512) DEFAULT NULL,
    `proposal_path`     VARCHAR(512) DEFAULT NULL,
    `response_code`     VARCHAR(10) DEFAULT NULL,
    `response_message`  VARCHAR(255) DEFAULT NULL,
    `updated_at`        TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY `uq_motor_policy_quote` (`quote_id`),
    CONSTRAINT `fk_motor_policy_quote` FOREIGN KEY (`quote_id`) REFERENCES `motor_quote`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `motor_api_log` (
    `id`              BIGINT PRIMARY KEY AUTO_INCREMENT,
    `quote_id`        BIGINT DEFAULT NULL,
    `integration_id`  VARCHAR(20) DEFAULT NULL,
    `endpoint`        VARCHAR(120) DEFAULT NULL,
    `request_body`    JSON DEFAULT NULL,
    `response_body`   JSON DEFAULT NULL,
    `http_status`     SMALLINT DEFAULT NULL,
    `error_code`      VARCHAR(10) DEFAULT NULL,
    `duration_ms`     INT DEFAULT NULL,
    `created_at`      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY `idx_motor_api_log_quote` (`quote_id`),
    KEY `idx_motor_api_log_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Digit Motor master / lookup tables
CREATE TABLE IF NOT EXISTS `motor_master_vehicle` (
    `vehicle_code`          VARCHAR(30)  NOT NULL,
    `make`                  VARCHAR(80)  NOT NULL,
    `model`                 VARCHAR(120) NOT NULL,
    `variant`               VARCHAR(120) DEFAULT NULL,
    `body_type`             VARCHAR(60)  DEFAULT NULL,
    `seating_capacity`      SMALLINT     DEFAULT NULL,
    `power`                 DECIMAL(10,2) DEFAULT NULL,
    `cubic_capacity`        DECIMAL(10,2) DEFAULT NULL,
    `gross_vehicle_weight`  DECIMAL(12,2) DEFAULT NULL,
    `fuel_type`             VARCHAR(40)  DEFAULT NULL,
    `no_of_wheels`          TINYINT      DEFAULT NULL,
    `abs`                   CHAR(1)      DEFAULT NULL,
    `air_bags`              SMALLINT     DEFAULT NULL,
    `length_m`              DECIMAL(10,3) DEFAULT NULL,
    `ex_showroom_price`     DECIMAL(14,2) DEFAULT NULL,
    `price_year`            SMALLINT     DEFAULT NULL,
    `production_status`     VARCHAR(60)  DEFAULT NULL,
    `manufacturing`         VARCHAR(40)  DEFAULT NULL,
    `vehicle_type`          VARCHAR(40)  DEFAULT NULL,
    `is_active`             TINYINT(1)   NOT NULL DEFAULT 1,
    `imported_at`           TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`vehicle_code`),
    KEY `idx_mmv_make` (`make`),
    KEY `idx_mmv_make_model` (`make`, `model`),
    KEY `idx_mmv_make_model_variant` (`make`, `model`, `variant`),
    KEY `idx_mmv_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `motor_master_previous_insurer` (
    `insurer_code`  VARCHAR(10)  NOT NULL,
    `insurer_name`  VARCHAR(180) NOT NULL,
    `is_active`     TINYINT(1)   NOT NULL DEFAULT 1,
    `imported_at`   TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`insurer_code`),
    KEY `idx_mmpi_name` (`insurer_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `motor_master_product` (
    `product_code`  VARCHAR(10)  NOT NULL,
    `product_name`  VARCHAR(120) NOT NULL,
    `vehicle_class` VARCHAR(10)  DEFAULT NULL COMMENT '2W / 4W / CV',
    `is_active`     TINYINT(1)   NOT NULL DEFAULT 1,
    `imported_at`   TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`product_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `motor_master_sub_product` (
    `id`               INT PRIMARY KEY AUTO_INCREMENT,
    `business_type`    VARCHAR(20) NOT NULL COMMENT 'NEW / ROLLOVER',
    `product_label`    VARCHAR(120) NOT NULL,
    `sub_product_code` VARCHAR(20) NOT NULL,
    `is_active`        TINYINT(1)  NOT NULL DEFAULT 1,
    `imported_at`      TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY `uq_mmsp` (`business_type`, `product_label`, `sub_product_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `motor_master_pincode` (
    `pincode`       VARCHAR(6)   NOT NULL,
    `city`          VARCHAR(120) DEFAULT NULL,
    `district`      VARCHAR(120) DEFAULT NULL,
    `street`        VARCHAR(180) DEFAULT NULL,
    `taluk`         VARCHAR(120) DEFAULT NULL,
    `state_code`    VARCHAR(10)  DEFAULT NULL,
    `segment`       VARCHAR(40)  DEFAULT NULL,
    `is_active`     TINYINT(1)   NOT NULL DEFAULT 1,
    `imported_at`   TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`pincode`),
    KEY `idx_mmp_city` (`city`),
    KEY `idx_mmp_state` (`state_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `motor_master_rto` (
    `rto_code`      VARCHAR(10)  NOT NULL,
    `city_state`    VARCHAR(180) DEFAULT NULL,
    `is_active`     TINYINT(1)   NOT NULL DEFAULT 1,
    `imported_at`   TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`rto_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `motor_master_ncb` (
    `ncb_code`      VARCHAR(30) NOT NULL,
    `sort_order`    SMALLINT    NOT NULL DEFAULT 0,
    `is_active`     TINYINT(1)  NOT NULL DEFAULT 1,
    `imported_at`   TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`ncb_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `motor_master_previous_policy_type` (
    `policy_type_code` VARCHAR(20)  NOT NULL,
    `description`      VARCHAR(120) DEFAULT NULL,
    `is_active`        TINYINT(1)   NOT NULL DEFAULT 1,
    `imported_at`      TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`policy_type_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `motor_master_voluntary_deductible` (
    `deductible_code` VARCHAR(40) NOT NULL,
    `sort_order`      SMALLINT    NOT NULL DEFAULT 0,
    `is_active`       TINYINT(1)  NOT NULL DEFAULT 1,
    `imported_at`     TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`deductible_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `motor_master_doc_type` (
    `doc_code`      VARCHAR(10)  NOT NULL,
    `doc_type`      VARCHAR(60)  NOT NULL,
    `is_active`     TINYINT(1)   NOT NULL DEFAULT 1,
    `imported_at`   TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`doc_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `motor_master_nominee_relation` (
    `relation_code` VARCHAR(40) NOT NULL,
    `is_active`     TINYINT(1)  NOT NULL DEFAULT 1,
    `imported_at`   TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`relation_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `motor_master_state` (
    `state_code`    VARCHAR(10)  NOT NULL,
    `state_name`    VARCHAR(120) NOT NULL,
    `is_active`     TINYINT(1)   NOT NULL DEFAULT 1,
    `imported_at`   TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`state_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `motor_master_addon_age_limit` (
    `id`            INT PRIMARY KEY AUTO_INCREMENT,
    `addon_name`    VARCHAR(120) NOT NULL,
    `age_limit_4w`  VARCHAR(120) DEFAULT NULL,
    `age_limit_2w`  VARCHAR(255) DEFAULT NULL,
    `is_active`     TINYINT(1)   NOT NULL DEFAULT 1,
    `imported_at`   TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY `uq_mmaal_addon` (`addon_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `motor_master_import_log` (
    `id`            BIGINT PRIMARY KEY AUTO_INCREMENT,
    `master_key`    VARCHAR(60)  NOT NULL,
    `source_file`   VARCHAR(255) DEFAULT NULL,
    `rows_upserted` INT          NOT NULL DEFAULT 0,
    `status`        VARCHAR(20)  NOT NULL DEFAULT 'OK',
    `message`       TEXT         DEFAULT NULL,
    `imported_at`   TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY `idx_mmil_master` (`master_key`),
    KEY `idx_mmil_imported` (`imported_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Align existing Digit Motor tables if created from an earlier draft schema
SET @col := (
    SELECT COUNT(1) FROM information_schema.columns
    WHERE table_schema = DATABASE()
      AND table_name = 'motor_quote'
      AND column_name = 'policyholder_details'
);
SET @sql := IF(@col = 0,
    'ALTER TABLE motor_quote ADD COLUMN policyholder_details JSON DEFAULT NULL AFTER coverage_details',
    'SELECT ''motor_quote.policyholder_details already exists'' AS info'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := (
    SELECT IF(
        EXISTS (
            SELECT 1 FROM information_schema.columns
            WHERE table_schema = DATABASE()
              AND table_name = 'motor_quote'
              AND column_name = 'application_id'
              AND character_maximum_length < 255
        ),
        'ALTER TABLE motor_quote MODIFY COLUMN application_id VARCHAR(255) DEFAULT NULL',
        'SELECT ''motor_quote.application_id already wide enough'' AS info'
    )
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := (
    SELECT IF(
        EXISTS (
            SELECT 1 FROM information_schema.columns
            WHERE table_schema = DATABASE()
              AND table_name = 'motor_payment'
              AND column_name = 'application_id'
              AND character_maximum_length < 255
        ),
        'ALTER TABLE motor_payment MODIFY COLUMN application_id VARCHAR(255) NOT NULL',
        'SELECT ''motor_payment.application_id already wide enough'' AS info'
    )
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- -----------------------------------------------------------------------------
-- 2026-07-28 | claim_report table
-- Sources: claim_report.sql / Migration 2026-07-28-090700_CreateClaimReportTable
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `claim_report` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `tpa_id` INT UNSIGNED NULL,
  `client_id` INT UNSIGNED NULL,
  `client_policy_id` INT UNSIGNED NOT NULL,
  `file_id` INT UNSIGNED NULL,
  `ticket_id` BIGINT UNSIGNED NULL,
  `source_table` VARCHAR(64) NULL,
  `source_row_id` BIGINT UNSIGNED NULL,
  `claim_number` VARCHAR(191) NOT NULL,
  `emp_code` VARCHAR(100) NULL,
  `tpa_no` VARCHAR(100) NULL,
  `emp_id` INT UNSIGNED NULL,
  `insured_emp_id` INT UNSIGNED NULL,
  `claim_amount` VARCHAR(50) NULL,
  `approved_amount` VARCHAR(50) NULL,
  `incurred_amount` VARCHAR(50) NULL,
  `si_amt` VARCHAR(50) NULL,
  `tpa_claim_status` VARCHAR(191) NULL,
  `claim_status_id` INT UNSIGNED NULL,
  `tpa_claim_type` VARCHAR(100) NULL,
  `tpa_ailments` TEXT NULL,
  `doa` DATE NULL,
  `dod` DATE NULL,
  `date_of_intimat` DATE NULL,
  `settled_date` DATE NULL,
  `approved_date` DATE NULL,
  `claim_dump_date` DATETIME NULL,
  `hospital_name` VARCHAR(255) NULL,
  `hospital_city` VARCHAR(150) NULL,
  `hospital_state` VARCHAR(150) NULL,
  `hospital_pin_code` VARCHAR(20) NULL,
  `hospital_address` TEXT NULL,
  `gender` VARCHAR(30) NULL,
  `age` VARCHAR(20) NULL,
  `relation` VARCHAR(50) NULL,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` DATETIME NULL,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_claim_report_policy_claim` (`client_policy_id`, `claim_number`),
  KEY `idx_claim_report_policy_active` (`client_policy_id`, `is_active`),
  KEY `idx_claim_report_ticket` (`ticket_id`),
  KEY `idx_claim_report_source` (`source_table`, `source_row_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- -----------------------------------------------------------------------------
-- 2026-08-01 / 2026-08-04 | Dependent add approval tracking
-- Migrations:
--   2026-08-01-043000_AddDependentApprovalTrackingColumns
--   2026-08-04-064500_AddRejectReasonToEmployeesAndEmployeePolices
--   2026-08-04-065000_RenameApprovedByToProcessedBy
-- -----------------------------------------------------------------------------

-- employees
SET @col := (
    SELECT COUNT(1) FROM information_schema.columns
    WHERE table_schema = DATABASE() AND table_name = 'employees' AND column_name = 'emp_created_by'
);
SET @sql := IF(@col = 0,
    'ALTER TABLE employees ADD COLUMN emp_created_by VARCHAR(50) NULL DEFAULT NULL COMMENT ''Creator role: HR / USER'' AFTER emp_status',
    'SELECT ''employees.emp_created_by already exists'' AS info'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Rename approved_by -> processed_by when needed
SET @sql := (
    SELECT IF(
        EXISTS (
            SELECT 1 FROM information_schema.columns
            WHERE table_schema = DATABASE() AND table_name = 'employees' AND column_name = 'approved_by'
        )
        AND NOT EXISTS (
            SELECT 1 FROM information_schema.columns
            WHERE table_schema = DATABASE() AND table_name = 'employees' AND column_name = 'processed_by'
        ),
        'ALTER TABLE employees CHANGE COLUMN approved_by processed_by VARCHAR(50) NULL DEFAULT NULL COMMENT ''Processor role: HR / ACM (approve or reject)''',
        'SELECT ''employees.processed_by rename skipped'' AS info'
    )
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @col := (
    SELECT COUNT(1) FROM information_schema.columns
    WHERE table_schema = DATABASE() AND table_name = 'employees' AND column_name = 'processed_by'
);
SET @sql := IF(@col = 0,
    'ALTER TABLE employees ADD COLUMN processed_by VARCHAR(50) NULL DEFAULT NULL COMMENT ''Processor role: HR / ACM (approve or reject)'' AFTER emp_created_by',
    'SELECT ''employees.processed_by already exists'' AS info'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @col := (
    SELECT COUNT(1) FROM information_schema.columns
    WHERE table_schema = DATABASE() AND table_name = 'employees' AND column_name = 'reject_reason'
);
SET @sql := IF(@col = 0,
    'ALTER TABLE employees ADD COLUMN reject_reason TEXT NULL DEFAULT NULL COMMENT ''Reason when dependent addition is rejected'' AFTER processed_by',
    'SELECT ''employees.reject_reason already exists'' AS info'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- employee_polices
SET @col := (
    SELECT COUNT(1) FROM information_schema.columns
    WHERE table_schema = DATABASE() AND table_name = 'employee_polices' AND column_name = 'emp_policy_created_by'
);
SET @sql := IF(@col = 0,
    'ALTER TABLE employee_polices ADD COLUMN emp_policy_created_by VARCHAR(50) NULL DEFAULT NULL COMMENT ''Creator role: HR / USER'' AFTER status',
    'SELECT ''employee_polices.emp_policy_created_by already exists'' AS info'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := (
    SELECT IF(
        EXISTS (
            SELECT 1 FROM information_schema.columns
            WHERE table_schema = DATABASE() AND table_name = 'employee_polices' AND column_name = 'approved_by'
        )
        AND NOT EXISTS (
            SELECT 1 FROM information_schema.columns
            WHERE table_schema = DATABASE() AND table_name = 'employee_polices' AND column_name = 'processed_by'
        ),
        'ALTER TABLE employee_polices CHANGE COLUMN approved_by processed_by VARCHAR(50) NULL DEFAULT NULL COMMENT ''Processor role: HR / ACM (approve or reject)''',
        'SELECT ''employee_polices.processed_by rename skipped'' AS info'
    )
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @col := (
    SELECT COUNT(1) FROM information_schema.columns
    WHERE table_schema = DATABASE() AND table_name = 'employee_polices' AND column_name = 'processed_by'
);
SET @sql := IF(@col = 0,
    'ALTER TABLE employee_polices ADD COLUMN processed_by VARCHAR(50) NULL DEFAULT NULL COMMENT ''Processor role: HR / ACM (approve or reject)'' AFTER emp_policy_created_by',
    'SELECT ''employee_polices.processed_by already exists'' AS info'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @col := (
    SELECT COUNT(1) FROM information_schema.columns
    WHERE table_schema = DATABASE() AND table_name = 'employee_polices' AND column_name = 'reject_reason'
);
SET @sql := IF(@col = 0,
    'ALTER TABLE employee_polices ADD COLUMN reject_reason TEXT NULL DEFAULT NULL COMMENT ''Reason when dependent addition is rejected'' AFTER processed_by',
    'SELECT ''employee_polices.reject_reason already exists'' AS info'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- -----------------------------------------------------------------------------
-- 2026-08-11 | Retail policy renewal reminder
-- Source: writable/sql/retail_policy_reminder_schema.sql
-- -----------------------------------------------------------------------------
SET @col := (
    SELECT COUNT(1) FROM information_schema.columns
    WHERE table_schema = DATABASE()
      AND table_name = 'policy_transaction'
      AND column_name = 'renewal_status'
);
SET @sql := IF(@col = 0,
    'ALTER TABLE policy_transaction ADD COLUMN renewal_status VARCHAR(50) NULL DEFAULT NULL AFTER renewal_date',
    'SELECT ''policy_transaction.renewal_status already exists'' AS info'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @col := (
    SELECT COUNT(1) FROM information_schema.columns
    WHERE table_schema = DATABASE()
      AND table_name = 'notifications'
      AND column_name = 'config_json'
);
SET @sql := IF(@col = 0,
    'ALTER TABLE notifications ADD COLUMN config_json LONGTEXT NULL AFTER common_mail',
    'SELECT ''notifications.config_json already exists'' AS info'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

INSERT INTO `notifications` (
  `client_id`,
  `template_name`,
  `subject`,
  `mail_content`,
  `enabled`,
  `config_json`
)
SELECT
  NULL,
  'retail_reminder_mail',
  'Policy Renewal Reminder',
  '',
  1,
  '{"enabled":true,"daily":true,"days":"mon,tue,wed,thu,fri","clientTypes":[2],"dateField":"renewal_date","daysBefore":30,"includeClientEmail":true,"includeOverdue":false,"toEmails":"","ccEmails":"","bccEmails":"","fromMail":""}'
FROM DUAL
WHERE NOT EXISTS (
  SELECT 1
  FROM `notifications`
  WHERE `template_name` = 'retail_reminder_mail'
    AND (`client_id` IS NULL OR `client_id` = 0)
);

-- =============================================================================
-- END
-- Optional demo data (not included): app/Database/digit_motor_demo_seed.sql
-- =============================================================================