-- Create Database
CREATE DATABASE IF NOT EXISTS cash_lending_db;
USE cash_lending_db;

-- Users Table
CREATE TABLE IF NOT EXISTS users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    full_name VARCHAR(100) NOT NULL,
    role ENUM('admin', 'staff') NOT NULL,
    email VARCHAR(100),
    phone VARCHAR(20),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    is_active BOOLEAN DEFAULT TRUE
);

-- Settings Table
CREATE TABLE IF NOT EXISTS settings (
    id INT PRIMARY KEY AUTO_INCREMENT,
    company_name VARCHAR(100) NOT NULL,
    default_interest DECIMAL(5, 2) NOT NULL DEFAULT 10,
    penalty DECIMAL(5, 2) NOT NULL DEFAULT 2,
    logo VARCHAR(255),
    phone VARCHAR(20),
    address TEXT,
    email VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Borrowers Table
CREATE TABLE IF NOT EXISTS borrowers (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    address TEXT NOT NULL,
    contact VARCHAR(20) NOT NULL UNIQUE,
    id_type VARCHAR(50) NOT NULL,
    id_number VARCHAR(50) NOT NULL UNIQUE,
    date_of_birth DATE DEFAULT NULL,
    occupation VARCHAR(100),
    photo VARCHAR(255) DEFAULT NULL,
    id_photo VARCHAR(255) DEFAULT NULL,
    status ENUM('active', 'inactive') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- NOTE: If borrowers table already exists, run:
-- ALTER TABLE borrowers ADD COLUMN photo VARCHAR(255) DEFAULT NULL AFTER occupation;
-- ALTER TABLE borrowers ADD COLUMN id_photo VARCHAR(255) DEFAULT NULL AFTER photo;

-- Loans Table
CREATE TABLE IF NOT EXISTS loans (
    id INT PRIMARY KEY AUTO_INCREMENT,
    borrower_id INT NOT NULL,
    loan_amount DECIMAL(12, 2) NOT NULL,
    interest_rate DECIMAL(5, 2) NOT NULL,
    term INT NOT NULL,
    release_date DATE NOT NULL,
    due_date DATE NOT NULL,
    payment_mode ENUM('daily', 'weekly', 'monthly') NOT NULL,
    penalty DECIMAL(5, 2),
    total_interest DECIMAL(12, 2),
    total_payable DECIMAL(12, 2),
    installment_amount DECIMAL(12, 2),
    balance DECIMAL(12, 2),
    status ENUM('pending', 'approved', 'paid', 'overdue') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (borrower_id) REFERENCES borrowers(id) ON DELETE CASCADE,
    INDEX idx_borrower (borrower_id),
    INDEX idx_status (status),
    INDEX idx_due_date (due_date)
);

-- Payments Table
CREATE TABLE IF NOT EXISTS payments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    loan_id INT NOT NULL,
    payment_date DATE NOT NULL,
    amount DECIMAL(12, 2) NOT NULL,
    penalty_paid DECIMAL(12, 2) DEFAULT 0,
    notes TEXT,
    payment_method VARCHAR(50),
    receipt_number VARCHAR(50) UNIQUE,
    payment_type ENUM('regular','interest_only') DEFAULT 'regular',
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (loan_id) REFERENCES loans(id) ON DELETE CASCADE,
    FOREIGN KEY (created_by) REFERENCES users(id),
    INDEX idx_loan (loan_id),
    INDEX idx_payment_date (payment_date)
);


-- Activity Log Table
CREATE TABLE IF NOT EXISTS activity_log (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    action VARCHAR(100) NOT NULL,
    description TEXT,
    ip_address VARCHAR(45),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_user (user_id),
    INDEX idx_action (action),
    INDEX idx_created (created_at)
);

-- Alter notes for existing databases
-- ALTER TABLE borrowers ADD COLUMN date_of_birth DATE DEFAULT NULL AFTER occupation;

-- Insert default admin user (password: admin123)
INSERT INTO users (username, password, full_name, role, email, is_active) 
VALUES ('admin', '$2y$10$YourHashedPasswordHere', 'Admin User', 'admin', 'admin@cashending.local', TRUE)
ON DUPLICATE KEY UPDATE id=id;

-- Insert default settings
INSERT INTO settings (company_name, default_interest, penalty) 
VALUES ('Cash Lending System', 10, 2)
ON DUPLICATE KEY UPDATE id=id;

-- Create sample borrower (optional)
-- INSERT INTO borrowers (first_name, last_name, address, contact, id_type, id_number, occupation) 
-- VALUES ('John', 'Doe', '123 Main St', '09171234567', 'SSS', 'SS123456', 'Businessman');

-- Loan Renewal Support
ALTER TABLE loans ADD COLUMN IF NOT EXISTS renewed_from INT DEFAULT NULL;
ALTER TABLE loans ADD FOREIGN KEY IF NOT EXISTS (renewed_from) REFERENCES loans(id) ON DELETE SET NULL;
