-- claim_report: normalized claims analytics table (UNIQUE client_policy_id + claim_number)
-- Prefer: php spark migrate
-- Manual fallback if migrations are not used in this environment.

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;