START TRANSACTION;

CREATE TABLE roles (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50) NOT NULL UNIQUE,
  description VARCHAR(255) NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO roles (name, description) VALUES
('admin', 'System administrator'),
('manager', 'Branch/loan manager'),
('cashier', 'Cashier / payment collector'),
('recovery_officer', 'Recovery / field officer');

CREATE TABLE branches (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  code VARCHAR(20) NOT NULL UNIQUE,
  name VARCHAR(100) NOT NULL,
  address TEXT NULL,
  phone VARCHAR(30) NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO branches (code, name, address, phone) VALUES
('BR001', 'Main Branch', 'Colombo', '0110000000');

CREATE TABLE areas (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  branch_id INT UNSIGNED NOT NULL,
  code VARCHAR(20) NOT NULL,
  name VARCHAR(100) NOT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_areas_branch FOREIGN KEY (branch_id) REFERENCES branches(id) ON DELETE CASCADE,
  UNIQUE KEY uq_area_branch_code (branch_id, code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO areas (branch_id, code, name) VALUES
(1, 'AR001', 'Pettah Area');
-- password: admin123

CREATE TABLE clients (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  branch_id INT UNSIGNED NOT NULL,
  area_id INT UNSIGNED NULL,
  client_code VARCHAR(30) NOT NULL UNIQUE,
  full_name VARCHAR(150) NOT NULL,
  nic_no VARCHAR(20) NULL,
  mobile_no VARCHAR(30) NULL,
  alternate_no VARCHAR(30) NULL,
  address_line1 VARCHAR(255) NULL,
  address_line2 VARCHAR(255) NULL,
  city VARCHAR(100) NULL,
  occupation VARCHAR(100) NULL,
  monthly_income DECIMAL(12,2) NOT NULL DEFAULT 0,
  guarantor_name VARCHAR(150) NULL,
  guarantor_phone VARCHAR(30) NULL,
  status ENUM('active','inactive','blacklisted','deceased') NOT NULL DEFAULT 'active',
  notes TEXT NULL,
  created_by INT UNSIGNED NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at DATETIME NULL,
  CONSTRAINT fk_clients_branch FOREIGN KEY (branch_id) REFERENCES branches(id) ON DELETE RESTRICT,
  CONSTRAINT fk_clients_area FOREIGN KEY (area_id) REFERENCES areas(id) ON DELETE SET NULL,
  CONSTRAINT fk_clients_created_by FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
  KEY idx_clients_name (full_name),
  KEY idx_clients_nic (nic_no),
  KEY idx_clients_mobile (mobile_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE client_documents (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  client_id INT UNSIGNED NOT NULL,
  document_type VARCHAR(50) NOT NULL,
  file_name VARCHAR(255) NOT NULL,
  file_path VARCHAR(255) NOT NULL,
  uploaded_by INT UNSIGNED NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_client_documents_client FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE,
  CONSTRAINT fk_client_documents_user FOREIGN KEY (uploaded_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE loan_products (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  loan_type ENUM('daily','monthly','mortgage','cheque_secured') NOT NULL,
  interest_method ENUM('flat','reducing') NOT NULL DEFAULT 'flat',
  installment_frequency ENUM('daily','weekly','monthly') NOT NULL,
  min_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
  max_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
  default_interest_rate DECIMAL(8,2) NOT NULL DEFAULT 0,
  penalty_interest_rate DECIMAL(8,2) NOT NULL DEFAULT 0,
  default_term_count INT NOT NULL DEFAULT 30,
  processing_fee DECIMAL(12,2) NOT NULL DEFAULT 0,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO loan_products (name, loan_type, interest_method, installment_frequency, min_amount, max_amount, default_interest_rate, penalty_interest_rate, default_term_count, processing_fee) VALUES
('Daily Loan Basic', 'daily', 'flat', 'daily', 5000, 100000, 20.00, 2.00, 30, 500),
('Monthly Loan Standard', 'monthly', 'flat', 'monthly', 10000, 500000, 18.00, 2.50, 12, 1000),
('Mortgage Loan', 'mortgage', 'reducing', 'monthly', 50000, 5000000, 16.00, 3.00, 24, 2500),
('Cheque Secured Loan', 'cheque_secured', 'flat', 'monthly', 10000, 300000, 22.00, 3.00, 6, 750);

CREATE TABLE loans (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  loan_no VARCHAR(30) NOT NULL UNIQUE,
  branch_id INT UNSIGNED NOT NULL,
  area_id INT UNSIGNED NULL,
  client_id INT UNSIGNED NOT NULL,
  product_id INT UNSIGNED NOT NULL,
  created_by INT UNSIGNED NULL,
  approved_by INT UNSIGNED NULL,
  principal_amount DECIMAL(12,2) NOT NULL,
  interest_rate DECIMAL(8,2) NOT NULL,
  penalty_interest_rate DECIMAL(8,2) NOT NULL DEFAULT 0,
  processing_fee DECIMAL(12,2) NOT NULL DEFAULT 0,
  disbursed_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
  installment_frequency ENUM('daily','weekly','monthly') NOT NULL,
  term_count INT NOT NULL,
  installment_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
  start_date DATE NOT NULL,
  maturity_date DATE NULL,
  approved_at DATETIME NULL,
  disbursed_at DATETIME NULL,
  security_type ENUM('none','mortgage','cheque','guarantor','other') NOT NULL DEFAULT 'none',
  security_reference VARCHAR(255) NULL,
  loan_status ENUM('draft','pending','approved','rejected','disbursed','active','overdue','closed','written_off') NOT NULL DEFAULT 'pending',
  notes TEXT NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at DATETIME NULL,
  CONSTRAINT fk_loans_branch FOREIGN KEY (branch_id) REFERENCES branches(id) ON DELETE RESTRICT,
  CONSTRAINT fk_loans_area FOREIGN KEY (area_id) REFERENCES areas(id) ON DELETE SET NULL,
  CONSTRAINT fk_loans_client FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE RESTRICT,
  CONSTRAINT fk_loans_product FOREIGN KEY (product_id) REFERENCES loan_products(id) ON DELETE RESTRICT,
  CONSTRAINT fk_loans_created_by FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
  CONSTRAINT fk_loans_approved_by FOREIGN KEY (approved_by) REFERENCES users(id) ON DELETE SET NULL,
  KEY idx_loans_status (loan_status),
  KEY idx_loans_client (client_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE loan_installments (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  loan_id INT UNSIGNED NOT NULL,
  installment_no INT NOT NULL,
  due_date DATE NOT NULL,
  opening_balance DECIMAL(12,2) NOT NULL DEFAULT 0,
  principal_due DECIMAL(12,2) NOT NULL DEFAULT 0,
  interest_due DECIMAL(12,2) NOT NULL DEFAULT 0,
  penalty_due DECIMAL(12,2) NOT NULL DEFAULT 0,
  total_due DECIMAL(12,2) NOT NULL DEFAULT 0,
  amount_paid DECIMAL(12,2) NOT NULL DEFAULT 0,
  status ENUM('pending','partial','paid','overdue') NOT NULL DEFAULT 'pending',
  paid_at DATETIME NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_installments_loan FOREIGN KEY (loan_id) REFERENCES loans(id) ON DELETE CASCADE,
  UNIQUE KEY uq_installment_per_loan (loan_id, installment_no),
  KEY idx_installments_due_date (due_date),
  KEY idx_installments_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE payments (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  payment_no VARCHAR(30) NOT NULL UNIQUE,
  loan_id INT UNSIGNED NOT NULL,
  installment_id INT UNSIGNED NULL,
  client_id INT UNSIGNED NOT NULL,
  branch_id INT UNSIGNED NOT NULL,
  collected_by INT UNSIGNED NULL,
  payment_date DATE NOT NULL,
  payment_method ENUM('cash','bank','cheque','online') NOT NULL DEFAULT 'cash',
  principal_paid DECIMAL(12,2) NOT NULL DEFAULT 0,
  interest_paid DECIMAL(12,2) NOT NULL DEFAULT 0,
  penalty_paid DECIMAL(12,2) NOT NULL DEFAULT 0,
  total_paid DECIMAL(12,2) NOT NULL DEFAULT 0,
  remarks TEXT NULL,
  is_reversed TINYINT(1) NOT NULL DEFAULT 0,
  reversed_by INT UNSIGNED NULL,
  reversed_at DATETIME NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_payments_loan FOREIGN KEY (loan_id) REFERENCES loans(id) ON DELETE RESTRICT,
  CONSTRAINT fk_payments_installment FOREIGN KEY (installment_id) REFERENCES loan_installments(id) ON DELETE SET NULL,
  CONSTRAINT fk_payments_client FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE RESTRICT,
  CONSTRAINT fk_payments_branch FOREIGN KEY (branch_id) REFERENCES branches(id) ON DELETE RESTRICT,
  CONSTRAINT fk_payments_collected_by FOREIGN KEY (collected_by) REFERENCES users(id) ON DELETE SET NULL,
  CONSTRAINT fk_payments_reversed_by FOREIGN KEY (reversed_by) REFERENCES users(id) ON DELETE SET NULL,
  KEY idx_payments_date (payment_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE expenses (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  branch_id INT UNSIGNED NOT NULL,
  expense_date DATE NOT NULL,
  category VARCHAR(100) NOT NULL,
  description VARCHAR(255) NOT NULL,
  amount DECIMAL(12,2) NOT NULL DEFAULT 0,
  created_by INT UNSIGNED NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_expenses_branch FOREIGN KEY (branch_id) REFERENCES branches(id) ON DELETE RESTRICT,
  CONSTRAINT fk_expenses_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE audit_logs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id INT UNSIGNED NULL,
  module VARCHAR(50) NOT NULL,
  action VARCHAR(50) NOT NULL,
  record_id BIGINT NULL,
  details TEXT NULL,
  ip_address VARCHAR(45) NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_audit_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
  KEY idx_audit_module (module),
  KEY idx_audit_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

COMMIT;
