-- Loan Applications Table for TrustBeam Credit
-- This table stores customer loan applications

CREATE TABLE IF NOT EXISTS `loan_applications` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `loan_type` varchar(100) NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  `purpose` text NOT NULL,
  `repayment_period` int(11) NOT NULL,
  `monthly_income` decimal(10,2) NOT NULL,
  `employment_status` varchar(50) DEFAULT NULL,
  `monthly_payment` decimal(10,2) NOT NULL,
  `interest_rate` decimal(5,2) NOT NULL,
  `total_amount` decimal(10,2) NOT NULL,
  `status` enum('Pending','Approved','Rejected','Under Review','Disbursed') NOT NULL DEFAULT 'Pending',
  `admin_notes` text DEFAULT NULL,
  `approved_by` int(11) DEFAULT NULL,
  `approved_at` timestamp NULL DEFAULT NULL,
  `disbursed_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `status` (`status`),
  KEY `created_at` (`created_at`),
  CONSTRAINT `loan_applications_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Sample data (optional)
INSERT INTO `loan_applications` (`user_id`, `loan_type`, `amount`, `purpose`, `repayment_period`, `monthly_income`, `employment_status`, `monthly_payment`, `interest_rate`, `total_amount`, `status`) VALUES
(1, 'Personal Loan', 5000.00, 'Home renovation and furniture purchase', 24, 4500.00, 'Full-time', 235.42, 12.00, 5650.00, 'Pending'),
(1, 'Business Loan', 15000.00, 'Expanding small business operations and inventory', 36, 6000.00, 'Business Owner', 523.61, 12.00, 18850.00, 'Approved'),
(2, 'Education Loan', 8000.00, 'University tuition and course materials', 18, 3500.00, 'Student', 470.83, 12.00, 8475.00, 'Under Review');
