CREATE TABLE IF NOT EXISTS `reimbursement_claims` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `org_id` varchar(50) NOT NULL,
  `employee_id` int(11) NOT NULL,
  `claim_month` date NOT NULL,
  `status` enum('pending','approved','rejected') NOT NULL DEFAULT 'pending',
  `total_claim_amount` decimal(12,2) NOT NULL DEFAULT 0.00,
  `total_approved_amount` decimal(12,2) NOT NULL DEFAULT 0.00,
  `notes` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_claim_employee` (`employee_id`),
  KEY `idx_claim_month` (`claim_month`),
  KEY `idx_claim_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `reimbursement_items` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `claim_id` int(11) NOT NULL,
  `reimbursement_type` varchar(100) NOT NULL,
  `bill_date` date DEFAULT NULL,
  `bill_number` varchar(80) DEFAULT NULL,
  `attachment_path` varchar(255) DEFAULT NULL,
  `claim_amount` decimal(12,2) NOT NULL DEFAULT 0.00,
  `approved_amount` decimal(12,2) NOT NULL DEFAULT 0.00,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_item_claim` (`claim_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `reimbursement_imports` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `org_id` varchar(50) NOT NULL,
  `file_name` varchar(255) NOT NULL,
  `file_path` varchar(255) DEFAULT NULL,
  `file_format` varchar(20) NOT NULL,
  `encoding_name` varchar(80) NOT NULL DEFAULT 'UTF-8 (Unicode)',
  `imported_rows` int(11) NOT NULL DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_import_org` (`org_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE `reimbursement_claims`
  ADD COLUMN `notes` text DEFAULT NULL,
  ADD COLUMN `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp();
