SET @has_current_balance := (
  SELECT COUNT(*)
  FROM information_schema.columns
  WHERE table_schema = DATABASE()
    AND table_name = 'employee_loans'
    AND column_name = 'current_balance'
);
SET @sql_current_balance := IF(
  @has_current_balance = 0,
  'ALTER TABLE employee_loans ADD COLUMN current_balance DECIMAL(14,2) NOT NULL DEFAULT 0.00 AFTER amount_due_this_period',
  'SELECT 1'
);
PREPARE stmt_current_balance FROM @sql_current_balance;
EXECUTE stmt_current_balance;
DEALLOCATE PREPARE stmt_current_balance;

SET @has_status := (
  SELECT COUNT(*)
  FROM information_schema.columns
  WHERE table_schema = DATABASE()
    AND table_name = 'employee_loans'
    AND column_name = 'status'
);
SET @sql_status := IF(
  @has_status = 0,
  'ALTER TABLE employee_loans ADD COLUMN status VARCHAR(20) NOT NULL DEFAULT ''active'' AFTER current_balance',
  'SELECT 1'
);
PREPARE stmt_status FROM @sql_status;
EXECUTE stmt_status;
DEALLOCATE PREPARE stmt_status;

UPDATE employee_loans
SET installment_amount = ROUND(principal_amount / tenor, 2)
WHERE UPPER(COALESCE(repayment_type, 'DIRECT')) = 'INSTALLMENT'
  AND tenor IS NOT NULL
  AND tenor > 0
  AND (installment_amount IS NULL OR installment_amount <= 0);

UPDATE employee_loans
SET amount_due_this_period = principal_amount
WHERE UPPER(COALESCE(repayment_type, 'DIRECT')) = 'DIRECT'
  AND COALESCE(amount_due_this_period, 0) <= 0;

UPDATE employee_loans
SET current_balance = CASE
    WHEN COALESCE(current_balance, 0) > 0 THEN current_balance
    WHEN COALESCE(principal_amount, 0) > 0 THEN principal_amount
    ELSE 0
  END,
  status = CASE
    WHEN COALESCE(is_active, 1) = 0 THEN 'cancelled'
    WHEN COALESCE(principal_amount, 0) <= 0 THEN 'paid'
    ELSE 'active'
  END;

CREATE TABLE IF NOT EXISTS employee_loan_periods (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  loan_id BIGINT UNSIGNED NOT NULL,
  payroll_period_id BIGINT UNSIGNED NOT NULL,
  installment_number INT NOT NULL DEFAULT 1,
  scheduled_amount DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  company_subsidy_amount DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  employee_deduction_amount DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  remaining_balance_after DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  status VARCHAR(20) NOT NULL DEFAULT 'pending',
  processed_at DATETIME NULL,
  reviewed_at DATETIME NULL,
  note TEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_employee_loan_periods_loan_period (loan_id, payroll_period_id),
  KEY idx_employee_loan_periods_period (payroll_period_id, status),
  KEY idx_employee_loan_periods_loan (loan_id, installment_number, status),
  CONSTRAINT fk_employee_loan_periods_loan FOREIGN KEY (loan_id) REFERENCES employee_loans(id) ON DELETE CASCADE,
  CONSTRAINT fk_employee_loan_periods_period FOREIGN KEY (payroll_period_id) REFERENCES payroll_periods(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SET @has_reviewed_at := (
  SELECT COUNT(*)
  FROM information_schema.columns
  WHERE table_schema = DATABASE()
    AND table_name = 'employee_loan_periods'
    AND column_name = 'reviewed_at'
);
SET @sql_reviewed_at := IF(
  @has_reviewed_at = 0,
  'ALTER TABLE employee_loan_periods ADD COLUMN reviewed_at DATETIME NULL AFTER processed_at',
  'SELECT 1'
);
PREPARE stmt_reviewed_at FROM @sql_reviewed_at;
EXECUTE stmt_reviewed_at;
DEALLOCATE PREPARE stmt_reviewed_at;