357 lines
20 KiB
Markdown
357 lines
20 KiB
Markdown
# Non-EB Claims Module - Database Changes
|
|
|
|
## 1. New Table: `non_eb_ticket_master`
|
|
|
|
```sql
|
|
CREATE TABLE `non_eb_ticket_master` (
|
|
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
-- Section 1: Policy & Account
|
|
`acm_id` INT UNSIGNED DEFAULT NULL,
|
|
`client_id` INT UNSIGNED DEFAULT NULL,
|
|
`branch_id` INT UNSIGNED DEFAULT NULL,
|
|
`client_policy_id` INT UNSIGNED DEFAULT NULL,
|
|
`policy_type_id` INT UNSIGNED DEFAULT NULL,
|
|
`policy_no` VARCHAR(100) DEFAULT NULL,
|
|
`policy_section` VARCHAR(255) DEFAULT NULL,
|
|
`policy_period` VARCHAR(50) DEFAULT NULL,
|
|
`insurer_id` INT UNSIGNED DEFAULT NULL,
|
|
-- Section 2: Loss / Incident
|
|
`nature_of_loss` VARCHAR(255) DEFAULT NULL,
|
|
`loss_description` TEXT DEFAULT NULL,
|
|
`loss_location` VARCHAR(255) DEFAULT NULL,
|
|
`loss_date` DATE DEFAULT NULL,
|
|
`loss_estimate` DECIMAL(15,2) DEFAULT NULL,
|
|
-- Section 3: Intimation & Ref
|
|
`intimation_recd_date` DATE DEFAULT NULL,
|
|
`intimated_to_insurer_date` DATE DEFAULT NULL,
|
|
`nhance_claim_ref_no` VARCHAR(100) DEFAULT NULL,
|
|
`claim_number` VARCHAR(100) DEFAULT NULL,
|
|
-- Section 4: Status
|
|
`claim_status_id` INT UNSIGNED DEFAULT NULL,
|
|
`surveyor_file_ref_no` VARCHAR(100) DEFAULT NULL,
|
|
-- Section 5: Surveyor
|
|
`surveyor_name` VARCHAR(255) DEFAULT NULL,
|
|
`surveyor_contact_person` VARCHAR(255) DEFAULT NULL,
|
|
`surveyor_contact_number` VARCHAR(20) DEFAULT NULL,
|
|
`surveyor_email` VARCHAR(255) DEFAULT NULL,
|
|
`lor` TEXT DEFAULT NULL,
|
|
`surveyor_remarks` TEXT DEFAULT NULL,
|
|
-- Section 6: Insured Contact
|
|
`insured_contact_name` VARCHAR(255) DEFAULT NULL,
|
|
`insured_contact_number` VARCHAR(20) DEFAULT NULL,
|
|
`insured_contact_email` VARCHAR(255) DEFAULT NULL,
|
|
-- Section 7: Documents
|
|
`google_drive_links` TEXT DEFAULT NULL,
|
|
`documents_required` TEXT DEFAULT NULL,
|
|
`documents_submitted` TEXT DEFAULT NULL,
|
|
`pending_documents` TEXT DEFAULT NULL,
|
|
`eta_for_documents` DATE DEFAULT NULL,
|
|
-- Section 8: Settlement
|
|
`loss_assessed_value` DECIMAL(15,2) DEFAULT NULL,
|
|
`settled_amount` DECIMAL(15,2) DEFAULT NULL,
|
|
`settlement_utr` VARCHAR(100) DEFAULT NULL,
|
|
-- Asset File (uploaded alternative to manual asset rows)
|
|
`asset_file` VARCHAR(255) DEFAULT NULL,
|
|
-- System
|
|
`priority` TINYINT UNSIGNED DEFAULT NULL,
|
|
`is_active` TINYINT(1) NOT NULL DEFAULT 1,
|
|
`created_by` INT UNSIGNED DEFAULT NULL,
|
|
`updated_by` INT UNSIGNED DEFAULT NULL,
|
|
`last_updated_by` INT UNSIGNED DEFAULT NULL,
|
|
`created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
`updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`id`),
|
|
KEY `idx_claim_status` (`claim_status_id`),
|
|
KEY `idx_client` (`client_id`),
|
|
KEY `idx_branch` (`branch_id`),
|
|
KEY `idx_acm` (`acm_id`),
|
|
KEY `idx_insurer` (`insurer_id`),
|
|
KEY `idx_policy_type` (`policy_type_id`),
|
|
KEY `idx_is_active` (`is_active`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
```
|
|
|
|
## 2. New Table: `non_eb_claim_assets`
|
|
|
|
```sql
|
|
CREATE TABLE `non_eb_claim_assets` (
|
|
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`non_eb_ticket_id` INT UNSIGNED NOT NULL,
|
|
`asset_id_code` VARCHAR(100) DEFAULT NULL,
|
|
`serial_no` VARCHAR(100) DEFAULT NULL,
|
|
`vehicle_no` VARCHAR(50) DEFAULT NULL,
|
|
`asset_description` VARCHAR(255) DEFAULT NULL,
|
|
`is_active` TINYINT(1) NOT NULL DEFAULT 1,
|
|
`created_by` INT UNSIGNED DEFAULT NULL,
|
|
`updated_by` INT UNSIGNED DEFAULT NULL,
|
|
`created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
`updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`id`),
|
|
KEY `idx_ticket` (`non_eb_ticket_id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
```
|
|
|
|
## 3. Reused Tables (No Changes Needed)
|
|
|
|
- `ticket_claim_status` - Add Non-EB sub-statuses with `ticket_type` = `policy_type.id`
|
|
- `ticket_history` - Reuse for field change tracking
|
|
- `ticket_notes` - Reuse for notes
|
|
- `ticket_messages` - Reuse for conversation/replies
|
|
- `ticket_mail_template` - Reuse for email templates
|
|
- `ticket_check_list` - Reuse for document checklists
|
|
|
|
## 4. Status Seed Data (per policy type)
|
|
|
|
For each Non-EB policy type (from `policy_type` table where `allocg IN ('Non-EB', 'Marine')`), insert these sub-statuses into `ticket_claim_status`:
|
|
|
|
| Sub Status (claim_status) | Display Name (Status for user) | allowed_status |
|
|
|---|---|---|
|
|
| Claim Intimation - Insured | Claim Intimation Received | [next_id] |
|
|
| Claim Intimation - Insurer | Claim Intimated | [next_id] |
|
|
| Survey Appointment - Awaited | Insurer Pending | [next_id] |
|
|
| Surveyor Appointed | Surveyor Pending | [next_id, lor_awaited_id] |
|
|
| LOR Awaited - Surveyor | Surveyor Pending | [next_id] |
|
|
| Documents Awaited - Insured | Insured Pending | [next_id, partial_docs_id] |
|
|
| Partial Documents Awaited - Insured | Insured Pending | [next_id] |
|
|
| Documents Submitted - Awaiting Loss Assessment | Insurer / Surveyor Pending | [next_id, discrepancy_id] |
|
|
| Loss Assessment - Discrepancy | Insurer / Surveyor Pending | [next_id] |
|
|
| Loss Assessment - Consent Awaited - Insured | Insured Pending | [next_id] |
|
|
| Consent Agreed | Insurer / Surveyor Pending | [approved_id] |
|
|
| Claim Approved | Claim Approved | [settled_id] |
|
|
| Claim Settled | Claim Settled | [] |
|
|
| Claim Closed | Claim Closed | [] |
|
|
| Claim Rejected | Claim Rejected | [] |
|
|
| Claim Withdrawn | Claim Withdrawn | [] |
|
|
| Workshop Pending | Workshop Pending | [next_id] |
|
|
|
|
> Note: `allowed_status` JSON should contain the IDs of the next valid statuses after insert.
|
|
|
|
---
|
|
|
|
## 5. ALTER TABLE Queries (for existing databases)
|
|
|
|
Run these if the tables already exist and need the new columns:
|
|
|
|
```sql
|
|
-- Add branch_id column
|
|
ALTER TABLE `non_eb_ticket_master` ADD COLUMN `branch_id` INT UNSIGNED DEFAULT NULL AFTER `client_id`;
|
|
ALTER TABLE `non_eb_ticket_master` ADD KEY `idx_branch` (`branch_id`);
|
|
|
|
-- Add asset_file column
|
|
ALTER TABLE `non_eb_ticket_master` ADD COLUMN `asset_file` VARCHAR(255) DEFAULT NULL AFTER `settlement_utr`;
|
|
|
|
-- Add closure_remark column
|
|
ALTER TABLE `non_eb_ticket_master` ADD COLUMN `closure_remark` TEXT DEFAULT NULL AFTER `asset_file`;
|
|
|
|
-- Add policy date columns
|
|
ALTER TABLE `non_eb_ticket_master` ADD COLUMN `policy_start_date` VARCHAR(20) DEFAULT NULL AFTER `policy_period`;
|
|
ALTER TABLE `non_eb_ticket_master` ADD COLUMN `policy_end_date` VARCHAR(20) DEFAULT NULL AFTER `policy_start_date`;
|
|
|
|
-- Optional: Drop legacy columns (only after confirming no existing data needed)
|
|
-- ALTER TABLE `non_eb_ticket_master` DROP COLUMN `policy_period`;
|
|
-- ALTER TABLE `non_eb_ticket_master` DROP COLUMN `policy_section`;
|
|
|
|
-- Add required_docs column for IR Documents checklist (Claim Files tab)
|
|
ALTER TABLE `non_eb_ticket_master` ADD COLUMN `required_docs` TEXT DEFAULT NULL AFTER `closure_remark`;
|
|
|
|
-- Add ticket_type flag to claim_files to distinguish EB (1) vs Non-EB (2) records
|
|
ALTER TABLE `claim_files` ADD COLUMN `ticket_type` TINYINT(1) NOT NULL DEFAULT 1 COMMENT '1=EB, 2=Non-EB' AFTER `ticket_id`;
|
|
ALTER TABLE `claim_files` ADD KEY `idx_ticket_type` (`ticket_type`);
|
|
```
|
|
|
|
---
|
|
|
|
## 6. Trigger: `non_eb_ticket_master_after_update`
|
|
|
|
Tracks field-level changes into `ticket_history` on every UPDATE.
|
|
|
|
```sql
|
|
DELIMITER $$
|
|
|
|
CREATE TRIGGER `non_eb_ticket_master_after_update`
|
|
AFTER UPDATE ON `non_eb_ticket_master`
|
|
FOR EACH ROW
|
|
BEGIN
|
|
|
|
-- Claim Status
|
|
IF (OLD.claim_status_id IS NULL OR OLD.claim_status_id != NEW.claim_status_id) AND NEW.claim_status_id IS NOT NULL THEN
|
|
INSERT INTO ticket_history (ticket_id, field_name, display_name, old_value, new_value, created_at, created_by, updated_by)
|
|
VALUES (OLD.id, 'claim_status_id', 'Claim Status', OLD.claim_status_id, NEW.claim_status_id, NEW.updated_at, NEW.updated_by, IF(NEW.last_updated_by = 'API', 0, NULL));
|
|
END IF;
|
|
|
|
-- Priority
|
|
IF (OLD.priority IS NULL OR OLD.priority != NEW.priority) AND NEW.priority IS NOT NULL THEN
|
|
INSERT INTO ticket_history (ticket_id, field_name, display_name, old_value, new_value, created_at, created_by, updated_by)
|
|
VALUES (OLD.id, 'priority', 'Priority', OLD.priority, NEW.priority, NEW.updated_at, NEW.updated_by, IF(NEW.last_updated_by = 'API', 0, NULL));
|
|
END IF;
|
|
|
|
-- Account Manager
|
|
IF (OLD.acm_id IS NULL OR OLD.acm_id != NEW.acm_id) AND NEW.acm_id IS NOT NULL THEN
|
|
INSERT INTO ticket_history (ticket_id, field_name, display_name, old_value, new_value, created_at, created_by, updated_by)
|
|
VALUES (OLD.id, 'acm_id', 'Account Manager', OLD.acm_id, NEW.acm_id, NEW.updated_at, NEW.updated_by, IF(NEW.last_updated_by = 'API', 0, NULL));
|
|
END IF;
|
|
|
|
-- Nature of Loss
|
|
IF (OLD.nature_of_loss IS NULL OR OLD.nature_of_loss != NEW.nature_of_loss) AND NEW.nature_of_loss IS NOT NULL THEN
|
|
INSERT INTO ticket_history (ticket_id, field_name, display_name, old_value, new_value, created_at, created_by, updated_by)
|
|
VALUES (OLD.id, 'nature_of_loss', 'Nature of Loss', OLD.nature_of_loss, NEW.nature_of_loss, NEW.updated_at, NEW.updated_by, IF(NEW.last_updated_by = 'API', 0, NULL));
|
|
END IF;
|
|
|
|
-- Loss Location
|
|
IF (OLD.loss_location IS NULL OR OLD.loss_location != NEW.loss_location) AND NEW.loss_location IS NOT NULL THEN
|
|
INSERT INTO ticket_history (ticket_id, field_name, display_name, old_value, new_value, created_at, created_by, updated_by)
|
|
VALUES (OLD.id, 'loss_location', 'Loss Location', OLD.loss_location, NEW.loss_location, NEW.updated_at, NEW.updated_by, IF(NEW.last_updated_by = 'API', 0, NULL));
|
|
END IF;
|
|
|
|
-- Loss Date
|
|
IF (OLD.loss_date IS NULL OR OLD.loss_date != NEW.loss_date) AND NEW.loss_date IS NOT NULL THEN
|
|
INSERT INTO ticket_history (ticket_id, field_name, display_name, old_value, new_value, created_at, created_by, updated_by)
|
|
VALUES (OLD.id, 'loss_date', 'Loss Date', OLD.loss_date, NEW.loss_date, NEW.updated_at, NEW.updated_by, IF(NEW.last_updated_by = 'API', 0, NULL));
|
|
END IF;
|
|
|
|
-- Loss Estimate
|
|
IF (OLD.loss_estimate IS NULL OR OLD.loss_estimate != NEW.loss_estimate) AND NEW.loss_estimate IS NOT NULL THEN
|
|
INSERT INTO ticket_history (ticket_id, field_name, display_name, old_value, new_value, created_at, created_by, updated_by)
|
|
VALUES (OLD.id, 'loss_estimate', 'Loss Estimate', OLD.loss_estimate, NEW.loss_estimate, NEW.updated_at, NEW.updated_by, IF(NEW.last_updated_by = 'API', 0, NULL));
|
|
END IF;
|
|
|
|
-- Loss Description (truncated to 500 chars)
|
|
IF (OLD.loss_description IS NULL OR OLD.loss_description != NEW.loss_description) AND NEW.loss_description IS NOT NULL THEN
|
|
INSERT INTO ticket_history (ticket_id, field_name, display_name, old_value, new_value, created_at, created_by, updated_by)
|
|
VALUES (OLD.id, 'loss_description', 'Loss Description', LEFT(OLD.loss_description, 500), LEFT(NEW.loss_description, 500), NEW.updated_at, NEW.updated_by, IF(NEW.last_updated_by = 'API', 0, NULL));
|
|
END IF;
|
|
|
|
-- Intimation Recd Date
|
|
IF (OLD.intimation_recd_date IS NULL OR OLD.intimation_recd_date != NEW.intimation_recd_date) AND NEW.intimation_recd_date IS NOT NULL THEN
|
|
INSERT INTO ticket_history (ticket_id, field_name, display_name, old_value, new_value, created_at, created_by, updated_by)
|
|
VALUES (OLD.id, 'intimation_recd_date', 'Intimation Recd Date', OLD.intimation_recd_date, NEW.intimation_recd_date, NEW.updated_at, NEW.updated_by, IF(NEW.last_updated_by = 'API', 0, NULL));
|
|
END IF;
|
|
|
|
-- Intimated to Insurer Date
|
|
IF (OLD.intimated_to_insurer_date IS NULL OR OLD.intimated_to_insurer_date != NEW.intimated_to_insurer_date) AND NEW.intimated_to_insurer_date IS NOT NULL THEN
|
|
INSERT INTO ticket_history (ticket_id, field_name, display_name, old_value, new_value, created_at, created_by, updated_by)
|
|
VALUES (OLD.id, 'intimated_to_insurer_date', 'Intimated to Insurer Date', OLD.intimated_to_insurer_date, NEW.intimated_to_insurer_date, NEW.updated_at, NEW.updated_by, IF(NEW.last_updated_by = 'API', 0, NULL));
|
|
END IF;
|
|
|
|
-- Nhance Claim Ref No
|
|
IF (OLD.nhance_claim_ref_no IS NULL OR OLD.nhance_claim_ref_no != NEW.nhance_claim_ref_no) AND NEW.nhance_claim_ref_no IS NOT NULL THEN
|
|
INSERT INTO ticket_history (ticket_id, field_name, display_name, old_value, new_value, created_at, created_by, updated_by)
|
|
VALUES (OLD.id, 'nhance_claim_ref_no', 'Nhance Claim Ref No', OLD.nhance_claim_ref_no, NEW.nhance_claim_ref_no, NEW.updated_at, NEW.updated_by, IF(NEW.last_updated_by = 'API', 0, NULL));
|
|
END IF;
|
|
|
|
-- Claim Number
|
|
IF (OLD.claim_number IS NULL OR OLD.claim_number != NEW.claim_number) AND NEW.claim_number IS NOT NULL THEN
|
|
INSERT INTO ticket_history (ticket_id, field_name, display_name, old_value, new_value, created_at, created_by, updated_by)
|
|
VALUES (OLD.id, 'claim_number', 'Claim Number', OLD.claim_number, NEW.claim_number, NEW.updated_at, NEW.updated_by, IF(NEW.last_updated_by = 'API', 0, NULL));
|
|
END IF;
|
|
|
|
-- Surveyor File Ref No
|
|
IF (OLD.surveyor_file_ref_no IS NULL OR OLD.surveyor_file_ref_no != NEW.surveyor_file_ref_no) AND NEW.surveyor_file_ref_no IS NOT NULL THEN
|
|
INSERT INTO ticket_history (ticket_id, field_name, display_name, old_value, new_value, created_at, created_by, updated_by)
|
|
VALUES (OLD.id, 'surveyor_file_ref_no', 'Surveyor File Ref No', OLD.surveyor_file_ref_no, NEW.surveyor_file_ref_no, NEW.updated_at, NEW.updated_by, IF(NEW.last_updated_by = 'API', 0, NULL));
|
|
END IF;
|
|
|
|
-- Surveyor Name
|
|
IF (OLD.surveyor_name IS NULL OR OLD.surveyor_name != NEW.surveyor_name) AND NEW.surveyor_name IS NOT NULL THEN
|
|
INSERT INTO ticket_history (ticket_id, field_name, display_name, old_value, new_value, created_at, created_by, updated_by)
|
|
VALUES (OLD.id, 'surveyor_name', 'Surveyor Name', OLD.surveyor_name, NEW.surveyor_name, NEW.updated_at, NEW.updated_by, IF(NEW.last_updated_by = 'API', 0, NULL));
|
|
END IF;
|
|
|
|
-- Surveyor Contact Person
|
|
IF (OLD.surveyor_contact_person IS NULL OR OLD.surveyor_contact_person != NEW.surveyor_contact_person) AND NEW.surveyor_contact_person IS NOT NULL THEN
|
|
INSERT INTO ticket_history (ticket_id, field_name, display_name, old_value, new_value, created_at, created_by, updated_by)
|
|
VALUES (OLD.id, 'surveyor_contact_person', 'Surveyor Contact Person', OLD.surveyor_contact_person, NEW.surveyor_contact_person, NEW.updated_at, NEW.updated_by, IF(NEW.last_updated_by = 'API', 0, NULL));
|
|
END IF;
|
|
|
|
-- Surveyor Contact Number
|
|
IF (OLD.surveyor_contact_number IS NULL OR OLD.surveyor_contact_number != NEW.surveyor_contact_number) AND NEW.surveyor_contact_number IS NOT NULL THEN
|
|
INSERT INTO ticket_history (ticket_id, field_name, display_name, old_value, new_value, created_at, created_by, updated_by)
|
|
VALUES (OLD.id, 'surveyor_contact_number', 'Surveyor Contact Number', OLD.surveyor_contact_number, NEW.surveyor_contact_number, NEW.updated_at, NEW.updated_by, IF(NEW.last_updated_by = 'API', 0, NULL));
|
|
END IF;
|
|
|
|
-- Surveyor Email
|
|
IF (OLD.surveyor_email IS NULL OR OLD.surveyor_email != NEW.surveyor_email) AND NEW.surveyor_email IS NOT NULL THEN
|
|
INSERT INTO ticket_history (ticket_id, field_name, display_name, old_value, new_value, created_at, created_by, updated_by)
|
|
VALUES (OLD.id, 'surveyor_email', 'Surveyor Email', OLD.surveyor_email, NEW.surveyor_email, NEW.updated_at, NEW.updated_by, IF(NEW.last_updated_by = 'API', 0, NULL));
|
|
END IF;
|
|
|
|
-- LOR (truncated to 500 chars)
|
|
IF (OLD.lor IS NULL OR OLD.lor != NEW.lor) AND NEW.lor IS NOT NULL THEN
|
|
INSERT INTO ticket_history (ticket_id, field_name, display_name, old_value, new_value, created_at, created_by, updated_by)
|
|
VALUES (OLD.id, 'lor', 'LOR', LEFT(OLD.lor, 500), LEFT(NEW.lor, 500), NEW.updated_at, NEW.updated_by, IF(NEW.last_updated_by = 'API', 0, NULL));
|
|
END IF;
|
|
|
|
-- Surveyor Remarks (truncated to 500 chars)
|
|
IF (OLD.surveyor_remarks IS NULL OR OLD.surveyor_remarks != NEW.surveyor_remarks) AND NEW.surveyor_remarks IS NOT NULL THEN
|
|
INSERT INTO ticket_history (ticket_id, field_name, display_name, old_value, new_value, created_at, created_by, updated_by)
|
|
VALUES (OLD.id, 'surveyor_remarks', 'Surveyor Remarks', LEFT(OLD.surveyor_remarks, 500), LEFT(NEW.surveyor_remarks, 500), NEW.updated_at, NEW.updated_by, IF(NEW.last_updated_by = 'API', 0, NULL));
|
|
END IF;
|
|
|
|
-- Loss Assessed Value
|
|
IF (OLD.loss_assessed_value IS NULL OR OLD.loss_assessed_value != NEW.loss_assessed_value) AND NEW.loss_assessed_value IS NOT NULL THEN
|
|
INSERT INTO ticket_history (ticket_id, field_name, display_name, old_value, new_value, created_at, created_by, updated_by)
|
|
VALUES (OLD.id, 'loss_assessed_value', 'Loss Assessed Value', OLD.loss_assessed_value, NEW.loss_assessed_value, NEW.updated_at, NEW.updated_by, IF(NEW.last_updated_by = 'API', 0, NULL));
|
|
END IF;
|
|
|
|
-- Settled Amount
|
|
IF (OLD.settled_amount IS NULL OR OLD.settled_amount != NEW.settled_amount) AND NEW.settled_amount IS NOT NULL THEN
|
|
INSERT INTO ticket_history (ticket_id, field_name, display_name, old_value, new_value, created_at, created_by, updated_by)
|
|
VALUES (OLD.id, 'settled_amount', 'Settled Amount', OLD.settled_amount, NEW.settled_amount, NEW.updated_at, NEW.updated_by, IF(NEW.last_updated_by = 'API', 0, NULL));
|
|
END IF;
|
|
|
|
-- Settlement UTR
|
|
IF (OLD.settlement_utr IS NULL OR OLD.settlement_utr != NEW.settlement_utr) AND NEW.settlement_utr IS NOT NULL THEN
|
|
INSERT INTO ticket_history (ticket_id, field_name, display_name, old_value, new_value, created_at, created_by, updated_by)
|
|
VALUES (OLD.id, 'settlement_utr', 'Settlement UTR', OLD.settlement_utr, NEW.settlement_utr, NEW.updated_at, NEW.updated_by, IF(NEW.last_updated_by = 'API', 0, NULL));
|
|
END IF;
|
|
|
|
-- Closure Remark (truncated to 500 chars)
|
|
IF (OLD.closure_remark IS NULL OR OLD.closure_remark != NEW.closure_remark) AND NEW.closure_remark IS NOT NULL THEN
|
|
INSERT INTO ticket_history (ticket_id, field_name, display_name, old_value, new_value, created_at, created_by, updated_by)
|
|
VALUES (OLD.id, 'closure_remark', 'Closure Remark', LEFT(OLD.closure_remark, 500), LEFT(NEW.closure_remark, 500), NEW.updated_at, NEW.updated_by, IF(NEW.last_updated_by = 'API', 0, NULL));
|
|
END IF;
|
|
|
|
-- Policy No
|
|
IF (OLD.policy_no IS NULL OR OLD.policy_no != NEW.policy_no) AND NEW.policy_no IS NOT NULL THEN
|
|
INSERT INTO ticket_history (ticket_id, field_name, display_name, old_value, new_value, created_at, created_by, updated_by)
|
|
VALUES (OLD.id, 'policy_no', 'Policy No', OLD.policy_no, NEW.policy_no, NEW.updated_at, NEW.updated_by, IF(NEW.last_updated_by = 'API', 0, NULL));
|
|
END IF;
|
|
|
|
-- Policy Start Date
|
|
IF (OLD.policy_start_date IS NULL OR OLD.policy_start_date != NEW.policy_start_date) AND NEW.policy_start_date IS NOT NULL THEN
|
|
INSERT INTO ticket_history (ticket_id, field_name, display_name, old_value, new_value, created_at, created_by, updated_by)
|
|
VALUES (OLD.id, 'policy_start_date', 'Policy Start Date', OLD.policy_start_date, NEW.policy_start_date, NEW.updated_at, NEW.updated_by, IF(NEW.last_updated_by = 'API', 0, NULL));
|
|
END IF;
|
|
|
|
-- Policy Expiry Date
|
|
IF (OLD.policy_end_date IS NULL OR OLD.policy_end_date != NEW.policy_end_date) AND NEW.policy_end_date IS NOT NULL THEN
|
|
INSERT INTO ticket_history (ticket_id, field_name, display_name, old_value, new_value, created_at, created_by, updated_by)
|
|
VALUES (OLD.id, 'policy_end_date', 'Policy Expiry Date', OLD.policy_end_date, NEW.policy_end_date, NEW.updated_at, NEW.updated_by, IF(NEW.last_updated_by = 'API', 0, NULL));
|
|
END IF;
|
|
|
|
-- ETA for Documents
|
|
IF (OLD.eta_for_documents IS NULL OR OLD.eta_for_documents != NEW.eta_for_documents) AND NEW.eta_for_documents IS NOT NULL THEN
|
|
INSERT INTO ticket_history (ticket_id, field_name, display_name, old_value, new_value, created_at, created_by, updated_by)
|
|
VALUES (OLD.id, 'eta_for_documents', 'ETA for Documents', OLD.eta_for_documents, NEW.eta_for_documents, NEW.updated_at, NEW.updated_by, IF(NEW.last_updated_by = 'API', 0, NULL));
|
|
END IF;
|
|
|
|
END$$
|
|
|
|
DELIMITER ;
|
|
```
|
|
|
|
---
|
|
|
|
# Digit Motor Module - Database Tables
|
|
|
|
Apply: `app/Database/digit_motor_tables.sql`
|
|
|
|
Tables:
|
|
- `motor_token` — cached Digit OneAPI bearer tokens
|
|
- `motor_quote` — quote header (quick + create quote)
|
|
- `motor_vehicle` — vehicle 1:1 with quote
|
|
- `motor_kyc` — KYC poll results
|
|
- `motor_payment` — payment link records
|
|
- `motor_policy` — policy status + PDF paths
|
|
- `motor_api_log` — redacted request/response audit log
|