613 lines
28 KiB
SQL
613 lines
28 KiB
SQL
-- =============================================================================
|
|
-- 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
|
|
-- =============================================================================
|