-- Create database
CREATE DATABASE IF NOT EXISTS sacco_db;
USE sacco_db;

-- Members table
CREATE TABLE members (
    id INT PRIMARY KEY AUTO_INCREMENT,
    membership_no VARCHAR(20) UNIQUE NOT NULL,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    id_number VARCHAR(20) UNIQUE NOT NULL,
    phone VARCHAR(15) NOT NULL,
    email VARCHAR(100),
    county VARCHAR(50),
    constituency VARCHAR(50),
    ward VARCHAR(50),
    occupation VARCHAR(50),
    date_joined DATE NOT NULL,
    status ENUM('active','inactive','suspended') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- FOSA Accounts (Front Office Savings Account)
CREATE TABLE fosa_accounts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    member_id INT NOT NULL,
    account_no VARCHAR(20) UNIQUE NOT NULL,
    balance DECIMAL(15,2) DEFAULT 0,
    status ENUM('active','dormant','closed') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (member_id) REFERENCES members(id) ON DELETE CASCADE
);

-- FOSA Transactions
CREATE TABLE fosa_transactions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    account_id INT NOT NULL,
    transaction_type ENUM('deposit','withdrawal','transfer') NOT NULL,
    amount DECIMAL(15,2) NOT NULL,
    description TEXT,
    reference_no VARCHAR(50) UNIQUE,
    transaction_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status ENUM('pending','completed','failed') DEFAULT 'completed',
    FOREIGN KEY (account_id) REFERENCES fosa_accounts(id) ON DELETE CASCADE
);

-- BOSA Accounts (Back Office Savings Account / Loans)
CREATE TABLE bosa_accounts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    member_id INT NOT NULL,
    account_no VARCHAR(20) UNIQUE NOT NULL,
    balance DECIMAL(15,2) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (member_id) REFERENCES members(id) ON DELETE CASCADE
);

-- Loan Products
CREATE TABLE loan_products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    product_name VARCHAR(100) NOT NULL,
    interest_rate DECIMAL(5,2) NOT NULL,
    max_amount DECIMAL(15,2) NOT NULL,
    min_amount DECIMAL(15,2) NOT NULL,
    duration_months INT NOT NULL,
    status ENUM('active','inactive') DEFAULT 'active'
);

-- Loan Applications
CREATE TABLE loan_applications (
    id INT PRIMARY KEY AUTO_INCREMENT,
    member_id INT NOT NULL,
    product_id INT NOT NULL,
    amount DECIMAL(15,2) NOT NULL,
    interest_rate DECIMAL(5,2) NOT NULL,
    duration_months INT NOT NULL,
    monthly_installment DECIMAL(15,2),
    purpose TEXT,
    application_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status ENUM('pending','approved','rejected','disbursed','completed') DEFAULT 'pending',
    approved_by INT,
    approval_date DATE,
    FOREIGN KEY (member_id) REFERENCES members(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES loan_products(id)
);

-- Mobile Banking Users
CREATE TABLE mobile_banking_users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    member_id INT NOT NULL,
    pin VARCHAR(255) NOT NULL,
    device_id VARCHAR(100),
    last_login TIMESTAMP,
    is_active BOOLEAN DEFAULT TRUE,
    FOREIGN KEY (member_id) REFERENCES members(id) ON DELETE CASCADE
);

-- Mobile Banking Transactions
CREATE TABLE mobile_transactions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    transaction_type ENUM('deposit','withdrawal','transfer','loan_payment') NOT NULL,
    amount DECIMAL(15,2) NOT NULL,
    recipient VARCHAR(50),
    reference_no VARCHAR(50),
    status ENUM('pending','completed','failed') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES mobile_banking_users(id)
);

-- Insert sample loan products
INSERT INTO loan_products (product_name, interest_rate, max_amount, min_amount, duration_months) VALUES
('Normal Loan', 12.00, 500000, 10000, 12),
('Emergency Loan', 10.00, 100000, 5000, 6),
('Development Loan', 8.00, 1000000, 50000, 24);