CREATE TABLE IF NOT EXISTS `loan_types` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `org_id` varchar(50) NOT NULL,
  `loan_name` varchar(120) NOT NULL,
  `perquisite_rate` decimal(8,2) NOT NULL DEFAULT 0.00,
  `description` text DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `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_loan_type_org` (`org_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `employee_loans` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `org_id` varchar(50) NOT NULL,
  `employee_id` int(11) NOT NULL,
  `loan_type_id` int(11) NOT NULL,
  `loan_amount` decimal(12,2) NOT NULL DEFAULT 0.00,
  `disbursement_date` date NOT NULL,
  `paid_through_account` varchar(150) NOT NULL,
  `reference_number` varchar(80) DEFAULT NULL,
  `reason` text NOT NULL,
  `exempt_perquisite` tinyint(1) NOT NULL DEFAULT 0,
  `installment_amount` decimal(12,2) NOT NULL DEFAULT 0.00,
  `total_installments` int(11) NOT NULL DEFAULT 1,
  `repayment_start_month` date DEFAULT NULL,
  `status` enum('open','paused','closed') NOT NULL DEFAULT 'open',
  `outstanding_amount` decimal(12,2) NOT NULL DEFAULT 0.00,
  `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_employee_loan_org` (`org_id`),
  KEY `idx_employee_loan_employee` (`employee_id`),
  KEY `idx_employee_loan_status` (`status`),
  KEY `idx_employee_loan_type` (`loan_type_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `loan_repayments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `loan_id` int(11) NOT NULL,
  `employee_id` int(11) NOT NULL,
  `repayment_month` date NOT NULL,
  `repayment_type` varchar(100) NOT NULL DEFAULT 'Loan Repayment Method',
  `installment_amount` decimal(12,2) NOT NULL DEFAULT 0.00,
  `principal_amount` decimal(12,2) NOT NULL DEFAULT 0.00,
  `interest_amount` decimal(12,2) NOT NULL DEFAULT 0.00,
  `remarks` varchar(255) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_loan_repayment_loan` (`loan_id`),
  KEY `idx_loan_repayment_employee` (`employee_id`),
  KEY `idx_loan_repayment_month` (`repayment_month`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `giving_campaigns` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `org_id` varchar(50) NOT NULL,
  `campaign_name` varchar(160) NOT NULL,
  `about_campaign` text NOT NULL,
  `exemption_type` varchar(40) NOT NULL,
  `campaign_ends_on` date NOT NULL,
  `show_in_portal` tinyint(1) NOT NULL DEFAULT 0,
  `status` enum('active','completed') NOT NULL DEFAULT 'active',
  `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_giving_campaign_org` (`org_id`),
  KEY `idx_giving_campaign_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `document_folders` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `org_id` varchar(50) NOT NULL,
  `folder_scope` enum('org','employee') NOT NULL DEFAULT 'org',
  `folder_name` varchar(160) NOT NULL,
  `description` text NOT NULL,
  `employee_id` int(11) DEFAULT NULL,
  `employee_status` enum('active','exited','all') NOT NULL DEFAULT 'all',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_document_folder_org` (`org_id`),
  KEY `idx_document_folder_scope` (`folder_scope`),
  KEY `idx_document_folder_employee` (`employee_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `document_files` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `folder_id` int(11) NOT NULL,
  `org_id` varchar(50) NOT NULL,
  `employee_id` int(11) DEFAULT NULL,
  `document_name` varchar(180) NOT NULL,
  `file_path` varchar(255) DEFAULT NULL,
  `uploaded_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_document_files_folder` (`folder_id`),
  KEY `idx_document_files_org` (`org_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `payroll_report_summaries` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `org_id` varchar(50) NOT NULL,
  `org_name` varchar(150) DEFAULT NULL,
  `period_key` varchar(40) NOT NULL,
  `range_start` date NOT NULL,
  `range_end` date NOT NULL,
  `earnings_amount` decimal(14,2) NOT NULL DEFAULT 0.00,
  `benefits_amount` decimal(14,2) NOT NULL DEFAULT 0.00,
  `donations_amount` decimal(14,2) NOT NULL DEFAULT 0.00,
  `deductions_amount` decimal(14,2) NOT NULL DEFAULT 0.00,
  `taxes_amount` decimal(14,2) NOT NULL DEFAULT 0.00,
  `reimbursements_amount` decimal(14,2) NOT NULL DEFAULT 0.00,
  `net_pay_amount` decimal(14,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`),
  UNIQUE KEY `uniq_payroll_report_period` (`org_id`,`period_key`,`range_start`,`range_end`),
  KEY `idx_payroll_report_org` (`org_id`),
  KEY `idx_payroll_report_range` (`range_start`,`range_end`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
