CREATE TABLE IF NOT EXISTS `settings` (
  `name` varchar(100) NOT NULL,
  `value` text,
  `updated_by` bigint unsigned DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `loan_applications` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint unsigned NOT NULL,
  `amount` int unsigned NOT NULL,
  `frequency` enum('weekly','monthly') NOT NULL,
  `extra_amount` int unsigned NOT NULL,
  `total_repayment` int unsigned NOT NULL,
  `installment_amount` int unsigned NOT NULL,
  `installment_count` smallint unsigned NOT NULL,
  `purpose` varchar(255) NOT NULL,
  `monthly_income` int unsigned NOT NULL,
  `address` text NOT NULL,
  `nid_number` varchar(20) NOT NULL,
  `nid_front` varchar(255) DEFAULT NULL,
  `nid_back` varchar(255) DEFAULT NULL,
  `selfie` varchar(255) DEFAULT NULL,
  `status` enum('pending','under_review','approved','rejected','cancelled','disbursed') NOT NULL DEFAULT 'pending',
  `admin_note` text,
  `reviewed_by` bigint unsigned DEFAULT NULL,
  `decided_at` timestamp NULL DEFAULT NULL,
  `consent_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `loan_applications_nid_number_index` (`nid_number`),
  KEY `loan_applications_status_index` (`status`),
  KEY `loan_applications_user_id_foreign` (`user_id`),
  CONSTRAINT `loan_applications_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `loans` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `application_id` bigint unsigned NOT NULL,
  `user_id` bigint unsigned NOT NULL,
  `principal` int unsigned NOT NULL,
  `extra_amount` int unsigned NOT NULL,
  `total_repayment` int unsigned NOT NULL,
  `installment_amount` int unsigned NOT NULL,
  `installment_count` smallint unsigned NOT NULL,
  `frequency` enum('weekly','monthly') NOT NULL,
  `status` enum('active','closed') NOT NULL DEFAULT 'active',
  `disbursed_on` date NOT NULL,
  `first_due_date` date NOT NULL,
  `paid_total` int unsigned NOT NULL DEFAULT 0,
  `closed_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `loans_application_id_unique` (`application_id`),
  KEY `loans_user_id_index` (`user_id`),
  KEY `loans_status_index` (`status`),
  CONSTRAINT `loans_application_id_foreign` FOREIGN KEY (`application_id`) REFERENCES `loan_applications` (`id`),
  CONSTRAINT `loans_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `loan_disbursements` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `loan_id` bigint unsigned NOT NULL,
  `amount` int unsigned NOT NULL,
  `disbursed_on` date NOT NULL,
  `method` varchar(30) NOT NULL,
  `reference` varchar(100) DEFAULT NULL,
  `disbursed_by` bigint unsigned NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `loan_disbursements_loan_id_unique` (`loan_id`),
  UNIQUE KEY `loan_disbursements_reference_unique` (`reference`),
  CONSTRAINT `loan_disbursements_loan_id_foreign` FOREIGN KEY (`loan_id`) REFERENCES `loans` (`id`),
  CONSTRAINT `loan_disbursements_by_foreign` FOREIGN KEY (`disbursed_by`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `loan_installments` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `loan_id` bigint unsigned NOT NULL,
  `number` smallint unsigned NOT NULL,
  `due_date` date NOT NULL,
  `amount_due` int unsigned NOT NULL,
  `amount_paid` int unsigned NOT NULL DEFAULT 0,
  `status` enum('pending','partial','paid') NOT NULL DEFAULT 'pending',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `loan_installments_loan_number_unique` (`loan_id`,`number`),
  KEY `loan_installments_due_status_index` (`due_date`,`status`),
  CONSTRAINT `loan_installments_loan_id_foreign` FOREIGN KEY (`loan_id`) REFERENCES `loans` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `repayments` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `loan_id` bigint unsigned NOT NULL,
  `amount` int unsigned NOT NULL,
  `paid_on` date NOT NULL,
  `method` varchar(30) NOT NULL,
  `reference` varchar(100) DEFAULT NULL,
  `status` enum('valid','reversed') NOT NULL DEFAULT 'valid',
  `recorded_by` bigint unsigned NOT NULL,
  `reversed_by` bigint unsigned DEFAULT NULL,
  `reversal_reason` varchar(255) DEFAULT NULL,
  `reversed_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `repayments_reference_unique` (`reference`),
  KEY `repayments_loan_id_index` (`loan_id`),
  KEY `repayments_paid_on_index` (`paid_on`),
  CONSTRAINT `repayments_loan_id_foreign` FOREIGN KEY (`loan_id`) REFERENCES `loans` (`id`),
  CONSTRAINT `repayments_recorded_by_foreign` FOREIGN KEY (`recorded_by`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `repayment_allocations` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `repayment_id` bigint unsigned NOT NULL,
  `installment_id` bigint unsigned NOT NULL,
  `amount` int unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `repayment_allocations_repayment_id_index` (`repayment_id`),
  KEY `repayment_allocations_installment_id_index` (`installment_id`),
  CONSTRAINT `repayment_allocations_repayment_foreign` FOREIGN KEY (`repayment_id`) REFERENCES `repayments` (`id`),
  CONSTRAINT `repayment_allocations_installment_foreign` FOREIGN KEY (`installment_id`) REFERENCES `loan_installments` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `audit_logs` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `actor_id` bigint unsigned DEFAULT NULL,
  `action` varchar(60) NOT NULL,
  `subject_type` varchar(40) NOT NULL,
  `subject_id` bigint unsigned DEFAULT NULL,
  `details` text,
  `prev_hash` char(64) NOT NULL,
  `hash` char(64) NOT NULL,
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `audit_logs_subject_index` (`subject_type`,`subject_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `notifications` (
  `id` char(36) NOT NULL,
  `type` varchar(255) NOT NULL,
  `notifiable_type` varchar(255) NOT NULL,
  `notifiable_id` bigint unsigned NOT NULL,
  `data` text NOT NULL,
  `read_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `notifications_notifiable_index` (`notifiable_type`,`notifiable_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;