-- Sample Test Data for Cash Lending System
-- Run this AFTER setup.sql to populate test data

USE cash_lending_db;

-- Reset admin password to 'admin123'
UPDATE users SET password = '$2y$10$k15tVPRMcQcWBpYhPvnJLeMuv2RUIHxk.TdOwG8F3JK5S8u0v5W8y' WHERE username = 'admin';

-- Add sample staff user (password: staff123)
INSERT INTO users (username, password, full_name, role, email, phone, is_active) VALUES
('staff1', '$2y$10$WKqF5p0VzCPVZK7hA8uQ.eBnTyGYPbWvHrxVpjKZ2jALmKp0Zh3lO', 'Juan Santos', 'staff', 'juan@example.com', '09171234567', TRUE),
('staff2', '$2y$10$WKqF5p0VzCPVZK7hA8uQ.eBnTyGYPbWvHrxVpjKZ2jALmKp0Zh3lO', 'Maria Cruz', 'staff', 'maria@example.com', '09188765432', TRUE);

-- Add sample borrowers
INSERT INTO borrowers (first_name, last_name, address, contact, id_type, id_number, occupation) VALUES
('Jose', 'Reyes', '123 Main Street, Barangay 1, City', '09171234567', 'SSS', 'SS-123456789', 'Businessman'),
('Maria', 'Santos', '456 Secondary Avenue, Barangay 2, City', '09188765432', 'GSIS', 'GSIS-987654321', 'Government Employee'),
('Antonio', 'Dela Cruz', '789 Tertiary Road, Barangay 3, City', '09159876543', 'Driver\'s License', 'DL-1234567', 'Jeepney Driver'),
('Rosa', 'Garcia', '321 Residential Lane, Barangay 4, City', '09167654321', 'PhilHealth', 'PH-456789012', 'Housewife'),
('Carlos', 'Martinez', '654 Commercial Street, Barangay 5, City', '09179876543', 'Passport', 'PA-123456', 'OFW'),
('Elena', 'Lopez', '987 Industrial Road, Barangay 6, City', '09184567890', 'National ID', 'NID-789012', 'Nurse'),
('Francisco', 'Gonzales', '147 Residential Court, Barangay 7, City', '09192345678', 'SSS', 'SS-234567890', 'Tricycle Driver'),
('Isabel', 'Ramos', '258 Business Park, Barangay 8, City', '09180123456', 'PRC', 'PRC-345678', 'Accountant');

-- Add sample loans
INSERT INTO loans (borrower_id, loan_amount, interest_rate, term, release_date, due_date, payment_mode, penalty, total_interest, total_payable, installment_amount, balance, status) VALUES
-- Jose Reyes - Active Loan
(1, 50000.00, 12.00, 180, '2024-12-01', '2025-05-30', 'daily', 2.00, 6000.00, 56000.00, 311.11, 45000.00, 'approved'),

-- Maria Santos - Pending Loan
(2, 30000.00, 10.00, 120, '2025-01-10', '2025-04-09', 'weekly', 2.00, 3000.00, 33000.00, 578.95, 33000.00, 'pending'),

-- Antonio Dela Cruz - Active Loan with some payments
(3, 20000.00, 15.00, 90, '2024-10-15', '2025-01-13', 'monthly', 2.00, 3000.00, 23000.00, 2555.56, 8000.00, 'approved'),

-- Rosa Garcia - Paid Loan
(4, 15000.00, 10.00, 60, '2024-08-01', '2024-09-30', 'monthly', 2.00, 1500.00, 16500.00, 2750.00, 0.00, 'paid'),

-- Carlos Martinez - Overdue Loan
(5, 40000.00, 12.00, 150, '2024-09-01', '2024-12-30', 'daily', 2.00, 4800.00, 44800.00, 298.67, 25000.00, 'overdue'),

-- Elena Lopez - Active Loan
(6, 25000.00, 11.00, 120, '2024-11-15', '2025-03-15', 'weekly', 2.00, 2750.00, 27750.00, 485.42, 20000.00, 'approved'),

-- Francisco Gonzales - Pending Loan
(7, 18000.00, 13.00, 90, '2025-01-20', '2025-04-20', 'daily', 2.00, 2340.00, 20340.00, 226.00, 20340.00, 'pending'),

-- Isabel Ramos - Active Loan with Payments
(8, 35000.00, 10.00, 180, '2024-10-01', '2025-03-31', 'monthly', 2.00, 3500.00, 38500.00, 213.89, 12000.00, 'approved');

-- Add sample payments
INSERT INTO payments (loan_id, payment_date, amount, penalty_paid, notes, payment_method, receipt_number, created_by) VALUES
-- Payment for Antonio (Loan 3)
(3, '2025-01-05', 5000.00, 0.00, 'First payment received', 'Cash', 'RCP-20250105001', 1),
(3, '2025-01-15', 5000.00, 0.00, 'Second payment received', 'Cash', 'RCP-20250115001', 2),
(3, '2025-02-01', 5000.00, 0.00, 'Third payment received', 'Check', 'RCP-20250201001', 1),

-- Payment for Rosa (Loan 4) - Paid Loan
(4, '2024-09-15', 8250.00, 0.00, 'First installment', 'Cash', 'RCP-20240915001', 1),
(4, '2024-10-15', 8250.00, 0.00, 'Final payment', 'Cash', 'RCP-20241015001', 2),

-- Payment for Jose (Loan 1)
(1, '2025-01-10', 10000.00, 0.00, 'Partial payment', 'Bank Transfer', 'RCP-20250110001', 1),

-- Payment for Isabel (Loan 8)
(8, '2024-11-15', 9000.00, 0.00, 'First installment', 'Cash', 'RCP-20241115001', 1),
(8, '2024-12-15', 9000.00, 0.00, 'Second installment', 'Online', 'RCP-20241215001', 2),
(8, '2025-01-15', 8000.00, 0.00, 'Third installment', 'Check', 'RCP-20250115002', 1);

-- Verify data
SELECT 'Total Borrowers:' AS stat, COUNT(*) AS count FROM borrowers;
SELECT 'Total Loans:' AS stat, COUNT(*) AS count FROM loans;
SELECT 'Total Payments:' AS stat, COUNT(*) AS count FROM payments;
SELECT 'Active Users:' AS stat, COUNT(*) AS count FROM users WHERE is_active = TRUE;
