-- LOANMETRIC clean MySQL schema + seed data
-- Deploy with: mysql -u USER -p DATABASE_NAME < loanmetric_clean_schema.sql
-- Default seeded logins: admin@loanmetric.test / password, staff@loanmetric.test / password
-- Change these passwords immediately after deployment.

SET SQL_MODE = 'NO_AUTO_VALUE_ON_ZERO';
SET time_zone = '+00:00';
SET FOREIGN_KEY_CHECKS = 0;

DROP TABLE IF EXISTS `activity_logs`;
DROP TABLE IF EXISTS `loan_payments`;
DROP TABLE IF EXISTS `loan_terms`;
DROP TABLE IF EXISTS `loan_applications`;
DROP TABLE IF EXISTS `fees`;
DROP TABLE IF EXISTS `settings`;
DROP TABLE IF EXISTS `customer_bank_accounts`;
DROP TABLE IF EXISTS `employment_infos`;
DROP TABLE IF EXISTS `customer_profiles`;
DROP TABLE IF EXISTS `otp_codes`;
DROP TABLE IF EXISTS `personal_access_tokens`;
DROP TABLE IF EXISTS `failed_jobs`;
DROP TABLE IF EXISTS `password_reset_tokens`;
DROP TABLE IF EXISTS `migrations`;
DROP TABLE IF EXISTS `users`;

CREATE TABLE `users` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `role` ENUM('customer','admin','staff') NOT NULL DEFAULT 'customer',
  `name` VARCHAR(150) DEFAULT NULL,
  `phone` VARCHAR(30) DEFAULT NULL,
  `email` VARCHAR(191) DEFAULT NULL,
  `date_of_birth` DATE DEFAULT NULL,
  `password` VARCHAR(191) DEFAULT NULL,
  `status` ENUM('active','inactive','blocked') NOT NULL DEFAULT 'active',
  `phone_verified_at` TIMESTAMP NULL DEFAULT NULL,
  `email_verified_at` TIMESTAMP NULL DEFAULT NULL,
  `last_login_at` TIMESTAMP NULL DEFAULT NULL,
  `remember_token` VARCHAR(100) DEFAULT NULL,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  `deleted_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `users_phone_unique` (`phone`),
  UNIQUE KEY `users_email_unique` (`email`),
  KEY `users_role_status_index` (`role`,`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `migrations` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `migration` VARCHAR(255) NOT NULL,
  `batch` INT NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `password_reset_tokens` (
  `email` VARCHAR(255) NOT NULL,
  `token` VARCHAR(255) NOT NULL,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `failed_jobs` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `uuid` VARCHAR(255) NOT NULL,
  `connection` TEXT NOT NULL,
  `queue` TEXT NOT NULL,
  `payload` LONGTEXT NOT NULL,
  `exception` LONGTEXT NOT NULL,
  `failed_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `failed_jobs_uuid_unique` (`uuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `personal_access_tokens` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `tokenable_type` VARCHAR(191) NOT NULL,
  `tokenable_id` BIGINT UNSIGNED NOT NULL,
  `name` VARCHAR(191) NOT NULL,
  `token` VARCHAR(64) NOT NULL,
  `abilities` TEXT DEFAULT NULL,
  `last_used_at` TIMESTAMP NULL DEFAULT NULL,
  `expires_at` TIMESTAMP NULL DEFAULT NULL,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `personal_access_tokens_token_unique` (`token`),
  KEY `personal_access_tokens_tokenable_type_tokenable_id_index` (`tokenable_type`,`tokenable_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `otp_codes` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `phone` VARCHAR(30) NOT NULL,
  `purpose` ENUM('customer_login','phone_verification') NOT NULL DEFAULT 'customer_login',
  `code_hash` VARCHAR(191) NOT NULL,
  `attempts` TINYINT UNSIGNED NOT NULL DEFAULT 0,
  `expires_at` TIMESTAMP NOT NULL,
  `verified_at` TIMESTAMP NULL DEFAULT NULL,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `otp_codes_phone_purpose_index` (`phone`,`purpose`),
  KEY `otp_codes_expires_at_index` (`expires_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `customer_profiles` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `full_name` VARCHAR(150) NOT NULL,
  `email` VARCHAR(191) DEFAULT NULL,
  `gender` ENUM('male','female','other') NOT NULL,
  `date_of_birth` DATE NOT NULL,
  `id_type` VARCHAR(80) NOT NULL,
  `id_number` VARCHAR(100) NOT NULL,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `customer_profiles_user_id_unique` (`user_id`),
  KEY `customer_profiles_id_number_index` (`id_number`),
  CONSTRAINT `customer_profiles_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `employment_infos` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `employment_type` VARCHAR(100) NOT NULL,
  `position` VARCHAR(150) NOT NULL,
  `monthly_gross_income` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `monthly_net_income` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `employment_infos_user_id_unique` (`user_id`),
  CONSTRAINT `employment_infos_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `customer_bank_accounts` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `bank_name` VARCHAR(150) NOT NULL,
  `account_holder_name` VARCHAR(150) NOT NULL,
  `account_number` VARCHAR(100) NOT NULL,
  `branch_name` VARCHAR(150) DEFAULT NULL,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `customer_bank_accounts_user_id_unique` (`user_id`),
  CONSTRAINT `customer_bank_accounts_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `settings` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `setting_key` VARCHAR(100) NOT NULL,
  `setting_value` JSON DEFAULT NULL,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `settings_setting_key_unique` (`setting_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `fees` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `fee_key` VARCHAR(120) NOT NULL,
  `title` VARCHAR(150) NOT NULL,
  `description` TEXT DEFAULT NULL,
  `amount_type` ENUM('fixed','percentage','formula') NOT NULL DEFAULT 'fixed',
  `amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `percentage_rate` DECIMAL(8,5) DEFAULT NULL,
  `formula_key` VARCHAR(100) DEFAULT NULL,
  `amount_formula` VARCHAR(500) DEFAULT NULL,
  `sort_order` INT UNSIGNED NOT NULL DEFAULT 0,
  `is_required` TINYINT(1) NOT NULL DEFAULT 1,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `bank_name` VARCHAR(150) DEFAULT NULL,
  `bank_account_name` VARCHAR(150) DEFAULT NULL,
  `bank_account_number` VARCHAR(100) DEFAULT NULL,
  `bank_notes` TEXT DEFAULT NULL,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `fees_fee_key_unique` (`fee_key`),
  KEY `fees_active_order_index` (`is_active`,`sort_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `loan_applications` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `application_no` VARCHAR(50) NOT NULL,
  `status` ENUM('draft','basic_info_completed','employment_info_completed','bank_info_completed','review_pending','application_received','loan_term_selected','handling_fee_pending','receipt_uploaded','loan_approved','payment_pending','completed','rejected') NOT NULL DEFAULT 'draft',
  `current_step` VARCHAR(100) DEFAULT NULL,
  `requested_amount` DECIMAL(12,2) DEFAULT NULL,
  `approved_amount` DECIMAL(12,2) DEFAULT NULL,
  `current_fee_id` BIGINT UNSIGNED DEFAULT NULL,
  `application_received_confirmed` TINYINT(1) NOT NULL DEFAULT 0,
  `review_started_at` TIMESTAMP NULL DEFAULT NULL,
  `review_completed_at` TIMESTAMP NULL DEFAULT NULL,
  `approved_by` BIGINT UNSIGNED DEFAULT NULL,
  `approved_at` TIMESTAMP NULL DEFAULT NULL,
  `rejected_by` BIGINT UNSIGNED DEFAULT NULL,
  `rejected_at` TIMESTAMP NULL DEFAULT NULL,
  `rejection_reason` TEXT DEFAULT NULL,
  `completed_at` TIMESTAMP NULL DEFAULT NULL,
  `completed_by` BIGINT UNSIGNED DEFAULT NULL,
  `completion_note` TEXT DEFAULT NULL,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `loan_applications_application_no_unique` (`application_no`),
  KEY `loan_applications_user_status_index` (`user_id`,`status`),
  KEY `loan_applications_current_fee_id_index` (`current_fee_id`),
  KEY `loan_applications_approved_by_foreign` (`approved_by`),
  KEY `loan_applications_rejected_by_foreign` (`rejected_by`),
  KEY `loan_applications_completed_by_foreign` (`completed_by`),
  CONSTRAINT `loan_applications_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `loan_applications_current_fee_id_foreign` FOREIGN KEY (`current_fee_id`) REFERENCES `fees` (`id`) ON DELETE SET NULL,
  CONSTRAINT `loan_applications_approved_by_foreign` FOREIGN KEY (`approved_by`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `loan_applications_rejected_by_foreign` FOREIGN KEY (`rejected_by`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `loan_applications_completed_by_foreign` FOREIGN KEY (`completed_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `loan_terms` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `loan_application_id` BIGINT UNSIGNED NOT NULL,
  `loan_amount` DECIMAL(12,2) NOT NULL,
  `term_months` SMALLINT UNSIGNED NOT NULL,
  `original_interest_rate` DECIMAL(8,5) NOT NULL DEFAULT 0.05000,
  `discounted_interest_rate` DECIMAL(8,5) NOT NULL DEFAULT 0.02500,
  `original_interest_amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `discounted_interest_amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `discount_amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `total_payable` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `monthly_repayment` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `handling_fee_rate` DECIMAL(8,5) NOT NULL DEFAULT 0.00800,
  `handling_fee_amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `loan_terms_application_unique` (`loan_application_id`),
  CONSTRAINT `loan_terms_application_foreign` FOREIGN KEY (`loan_application_id`) REFERENCES `loan_applications` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `loan_payments` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `loan_application_id` BIGINT UNSIGNED NOT NULL,
  `fee_id` BIGINT UNSIGNED NOT NULL,
  `fee_key` VARCHAR(120) DEFAULT NULL,
  `fee_title` VARCHAR(191) DEFAULT NULL,
  `fee_description` TEXT DEFAULT NULL,
  `amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `status` ENUM('not_started','pending_upload','receipt_uploaded','pending_review','approved','rejected','refunded') NOT NULL DEFAULT 'not_started',
  `payment_stage` ENUM('confirm_payment','pay_now','upload_receipt','pending_review','approved','rejected') NOT NULL DEFAULT 'confirm_payment',
  `receipt_file_path` VARCHAR(255) DEFAULT NULL,
  `receipt_original_name` VARCHAR(191) DEFAULT NULL,
  `receipt_mime_type` VARCHAR(100) DEFAULT NULL,
  `receipt_size` INT UNSIGNED DEFAULT NULL,
  `customer_note` TEXT DEFAULT NULL,
  `admin_note` TEXT DEFAULT NULL,
  `rejection_reason` TEXT DEFAULT NULL,
  `uploaded_at` TIMESTAMP NULL DEFAULT NULL,
  `reviewed_by` BIGINT UNSIGNED DEFAULT NULL,
  `reviewed_at` TIMESTAMP NULL DEFAULT NULL,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `loan_payments_application_fee_unique` (`loan_application_id`,`fee_id`),
  KEY `loan_payments_status_index` (`status`),
  KEY `loan_payments_fee_id_index` (`fee_id`),
  KEY `loan_payments_reviewed_by_index` (`reviewed_by`),
  CONSTRAINT `loan_payments_application_foreign` FOREIGN KEY (`loan_application_id`) REFERENCES `loan_applications` (`id`) ON DELETE CASCADE,
  CONSTRAINT `loan_payments_fee_id_foreign` FOREIGN KEY (`fee_id`) REFERENCES `fees` (`id`),
  CONSTRAINT `loan_payments_reviewed_by_foreign` FOREIGN KEY (`reviewed_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `activity_logs` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `actor_id` BIGINT UNSIGNED DEFAULT NULL,
  `actor_role` VARCHAR(30) DEFAULT NULL,
  `action` VARCHAR(120) NOT NULL,
  `entity_type` VARCHAR(120) DEFAULT NULL,
  `entity_id` BIGINT UNSIGNED DEFAULT NULL,
  `description` TEXT DEFAULT NULL,
  `metadata` JSON DEFAULT NULL,
  `ip_address` VARCHAR(45) DEFAULT NULL,
  `user_agent` TEXT DEFAULT NULL,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `activity_logs_actor_index` (`actor_id`,`actor_role`),
  KEY `activity_logs_entity_index` (`entity_type`,`entity_id`),
  KEY `activity_logs_action_index` (`action`),
  CONSTRAINT `activity_logs_actor_id_foreign` FOREIGN KEY (`actor_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `migrations` (`migration`, `batch`) VALUES
('2014_10_12_000000_create_users_table', 1),
('2014_10_12_100000_create_password_reset_tokens_table', 1),
('2019_08_19_000000_create_failed_jobs_table', 1),
('2019_12_14_000001_create_personal_access_tokens_table', 1),
('2026_06_01_000000_create_loanmetric_domain_tables', 1);

INSERT INTO `users` (`role`, `name`, `phone`, `email`, `password`, `status`, `created_at`, `updated_at`) VALUES
('admin', 'System Admin', NULL, 'admin@loanmetric.test', '$2y$10$33K9PYDGpTPG7Rjfy.uI1.u/TP/NF5vOkaSbXN5QYg2MFhQYTAhru', 'active', NOW(), NOW()),
('staff', 'Review Staff', NULL, 'staff@loanmetric.test', '$2y$10$K3GQShZCCeY/pi8juwgH1OiU/45ZapN7PnlqX4UzNZKRRe4znH6iu', 'active', NOW(), NOW());

INSERT INTO `settings` (`setting_key`, `setting_value`, `created_at`, `updated_at`) VALUES
('app_config', JSON_OBJECT('name', 'LOANMETRIC'), NOW(), NOW()),
('main_bank_details', JSON_OBJECT('bank_name', 'Demo Bank', 'account_name', 'LoanMetric Sdn Bhd', 'account_number', '0000000000', 'notes', 'Use phone number as reference.'), NOW(), NOW()),
('maintenance_mode', JSON_OBJECT('enabled', false, 'frontend_behavior', 'calculator_only'), NOW(), NOW()),
('loan_config', JSON_OBJECT('min_amount', 5000, 'max_amount', 100000, 'terms', JSON_ARRAY(6,12,18,24,36), 'original_interest_rate', 0.05, 'discounted_interest_rate', 0.025, 'handling_fee_rate', 0.008), NOW(), NOW());

INSERT INTO `fees` (`fee_key`, `title`, `description`, `amount_type`, `amount`, `percentage_rate`, `formula_key`, `amount_formula`, `sort_order`, `is_required`, `is_active`, `created_at`, `updated_at`) VALUES
('handling-fee', 'Handling Fee', 'Required handling fee based on selected loan amount. This payment will be refunded upon loan disbursement.', 'formula', 0.00, NULL, NULL, '{loanAmount} * 0.008', 1, 1, 1, NOW(), NOW()),
('takaful-fee', 'Takaful Fee', 'Required takaful payment. This payment will be refunded upon loan disbursement.', 'fixed', 500.00, NULL, NULL, NULL, 2, 1, 1, NOW(), NOW()),
('processing-fee', 'Processing Fee', 'Required processing fee. This payment will be refunded upon loan disbursement.', 'fixed', 500.00, NULL, NULL, NULL, 3, 1, 1, NOW(), NOW()),
('agreement-stamping-fee', 'Agreement Stamping Fee', 'Required agreement stamping fee. This payment will be refunded upon loan disbursement.', 'fixed', 500.00, NULL, NULL, NULL, 4, 1, 1, NOW(), NOW()),
('disbursement-fee', 'Disbursement Fee', 'Required disbursement fee. This payment will be refunded upon loan disbursement.', 'fixed', 500.00, NULL, NULL, NULL, 5, 1, 1, NOW(), NOW()),
('administration-fee', 'Administration Fee', 'Required administration fee. This payment will be refunded upon loan disbursement.', 'fixed', 500.00, NULL, NULL, NULL, 6, 1, 1, NOW(), NOW());

SET FOREIGN_KEY_CHECKS = 1;