-- ANILAD Cooperative Database Schema
-- Run this SQL in your MySQL database

CREATE DATABASE IF NOT EXISTS anilad_coop;
USE anilad_coop;

-- Virtual Accounts Table
CREATE TABLE IF NOT EXISTS virtual_accounts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    member_id INT NOT NULL,
    account_number VARCHAR(20) NOT NULL UNIQUE,
    bank_name VARCHAR(100) DEFAULT 'ANILAD COOP Bank',
    account_name VARCHAR(100) NOT NULL,
    savings_balance DECIMAL(15, 2) DEFAULT 0.00,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (member_id) REFERENCES members(id) ON DELETE CASCADE
);

-- Loans Table
CREATE TABLE IF NOT EXISTS loans (
    id INT AUTO_INCREMENT PRIMARY KEY,
    loan_reference VARCHAR(30) NOT NULL UNIQUE,
    member_id INT NOT NULL,
    amount DECIMAL(15, 2) NOT NULL,
    interest_rate DECIMAL(5, 2) NOT NULL,
    total_amount DECIMAL(15, 2) NOT NULL,
    repayment_plan INT NOT NULL COMMENT 'In months: 3, 6, or 12',
    monthly_repayment DECIMAL(15, 2) NOT NULL,
    amount_paid DECIMAL(15, 2) DEFAULT 0.00,
    amount_remaining DECIMAL(15, 2) NOT NULL,
    receiving_account_number VARCHAR(20) NOT NULL,
    receiving_bank_name VARCHAR(100) NOT NULL,
    receiving_account_name VARCHAR(100) NOT NULL,
    status ENUM('pending', 'approved', 'disbursed', 'repaying', 'completed', 'rejected', 'defaulted') DEFAULT 'pending',
    applied_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    approved_by INT DEFAULT NULL,
    approved_at TIMESTAMP NULL,
    disbursed_at TIMESTAMP NULL,
    completed_at TIMESTAMP NULL,
    FOREIGN KEY (member_id) REFERENCES members(id) ON DELETE CASCADE,
    FOREIGN KEY (approved_by) REFERENCES admins(id) ON DELETE SET NULL
);

-- Loan Guarantors Table
CREATE TABLE IF NOT EXISTS loan_guarantors (
    id INT AUTO_INCREMENT PRIMARY KEY,
    loan_id INT NOT NULL,
    guarantor_name VARCHAR(100) NOT NULL,
    guarantor_phone VARCHAR(20) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (loan_id) REFERENCES loans(id) ON DELETE CASCADE
);

-- Transactions Table
CREATE TABLE IF NOT EXISTS transactions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    transaction_reference VARCHAR(30) NOT NULL UNIQUE,
    member_id INT NOT NULL,
    virtual_account_id INT NOT NULL,
    type ENUM('deposit', 'loan_repayment', 'loan_disbursement', 'withdrawal') NOT NULL,
    amount DECIMAL(15, 2) NOT NULL,
    balance_after DECIMAL(15, 2) NOT NULL,
    description TEXT,
    loan_id INT DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (member_id) REFERENCES members(id) ON DELETE CASCADE,
    FOREIGN KEY (virtual_account_id) REFERENCES virtual_accounts(id) ON DELETE CASCADE,
    FOREIGN KEY (loan_id) REFERENCES loans(id) ON DELETE SET NULL
);

-- Loan Repayment History Table
CREATE TABLE IF NOT EXISTS loan_repayments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    loan_id INT NOT NULL,
    transaction_id INT NOT NULL,
    amount DECIMAL(15, 2) NOT NULL,
    principal_paid DECIMAL(15, 2) NOT NULL,
    interest_paid DECIMAL(15, 2) NOT NULL,
    balance_after DECIMAL(15, 2) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (loan_id) REFERENCES loans(id) ON DELETE CASCADE,
    FOREIGN KEY (transaction_id) REFERENCES transactions(id) ON DELETE CASCADE
);
CREATE INDEX idx_loans_status ON loans(status);
CREATE INDEX idx_loans_member ON loans(member_id);
CREATE INDEX idx_transactions_member ON transactions(member_id);
CREATE INDEX idx_transactions_type ON transactions(type);
