init.sql

CREATE DATABASE IF NOT EXISTS banking_auth CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE DATABASE IF NOT EXISTS banking_accounts CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE DATABASE IF NOT EXISTS banking_transactions CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE DATABASE IF NOT EXISTS banking_loans CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE DATABASE IF NOT EXISTS banking_admin CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE USER IF NOT EXISTS 'banking_user'@'localhost' IDENTIFIED BY 'Banking@123';

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, INDEX, ALTER, REFERENCES
    ON banking_auth.* TO 'banking_user'@'localhost';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, INDEX, ALTER, REFERENCES
    ON banking_accounts.* TO 'banking_user'@'localhost';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, INDEX, ALTER, REFERENCES
    ON banking_transactions.* TO 'banking_user'@'localhost';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, INDEX, ALTER, REFERENCES
    ON banking_loans.* TO 'banking_user'@'localhost';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, INDEX, ALTER, REFERENCES
    ON banking_admin.* TO 'banking_user'@'localhost';

FLUSH PRIVILEGES;

-- ============================================================
-- AUTH DATABASE SETUP
-- ============================================================
USE banking_auth;

CREATE TABLE IF NOT EXISTS users (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    phone_number VARCHAR(15) NOT NULL UNIQUE,
    date_of_birth DATE,
    address VARCHAR(500),
    pan_number VARCHAR(10) UNIQUE,
    aadhaar_number VARCHAR(12) UNIQUE,
    role ENUM('CUSTOMER','ADMIN','SUPER_ADMIN') NOT NULL DEFAULT 'CUSTOMER',
    is_active TINYINT(1) NOT NULL DEFAULT 0,
    is_email_verified TINYINT(1) NOT NULL DEFAULT 0,
    is_phone_verified TINYINT(1) NOT NULL DEFAULT 0,
    failed_login_attempts INT NOT NULL DEFAULT 0,
    account_locked TINYINT(1) NOT NULL DEFAULT 0,
    lock_time DATETIME,
    last_login DATETIME,
    last_login_ip VARCHAR(45),
    password_changed_at DATETIME,
    profile_picture_url VARCHAR(500),
    kyc_status VARCHAR(20) DEFAULT 'PENDING',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_by VARCHAR(50),
    INDEX idx_users_username (username),
    INDEX idx_users_email (email),
    INDEX idx_users_phone (phone_number),
    INDEX idx_users_role (role),
    INDEX idx_users_active (is_active)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS user_sessions (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT NOT NULL,
    session_token VARCHAR(255) NOT NULL UNIQUE,
    ip_address VARCHAR(45),
    user_agent VARCHAR(500),
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    expires_at DATETIME NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    last_accessed_at DATETIME,
    INDEX idx_session_token (session_token),
    INDEX idx_session_user_id (user_id),
    INDEX idx_session_active (is_active)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS audit_logs (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT,
    username VARCHAR(50),
    action VARCHAR(100) NOT NULL,
    entity_type VARCHAR(50),
    entity_id VARCHAR(50),
    description VARCHAR(1000),
    ip_address VARCHAR(45),
    user_agent VARCHAR(500),
    success TINYINT(1) NOT NULL DEFAULT 1,
    error_message VARCHAR(500),
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_audit_user_id (user_id),
    INDEX idx_audit_action (action),
    INDEX idx_audit_created_at (created_at)
) ENGINE=InnoDB;

-- View that NEVER exposes password_hash
CREATE OR REPLACE VIEW vw_users_safe AS
SELECT id, username, first_name, last_name, email, phone_number,
       date_of_birth, address, pan_number, role, is_active,
       is_email_verified, is_phone_verified, kyc_status,
       last_login, created_at, updated_at
FROM users;

-- Trigger: audit password changes
DELIMITER //
CREATE TRIGGER IF NOT EXISTS trg_audit_pwd_change
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
    IF OLD.password_hash != NEW.password_hash THEN
        INSERT INTO audit_logs(user_id, username, action, entity_type, entity_id, description, success)
        VALUES(NEW.id, NEW.username, 'PASSWORD_CHANGED', 'USER', CAST(NEW.id AS CHAR), 'Password hash changed', 1);
    END IF;
END //
DELIMITER ;

-- Default Super Admin  (password = Admin@123456)
INSERT IGNORE INTO users (username, password_hash, first_name, last_name, email,
    phone_number, role, is_active, is_email_verified, is_phone_verified, kyc_status, created_by)
VALUES ('superadmin',
    '$2a$12$LQv3c1yqBWVHxkd0LHAkCOYz6TtxMQyCgPGEG0jAQkFKkT4p4l5Wa',
    'Super', 'Admin', 'admin@banking.com', '9999999999',
    'SUPER_ADMIN', 1, 1, 1, 'VERIFIED', 'SYSTEM');

INSERT IGNORE INTO users (username, password_hash, first_name, last_name, email,
    phone_number, role, is_active, is_email_verified, is_phone_verified, kyc_status, created_by)
VALUES ('admin1',
    '$2a$12$LQv3c1yqBWVHxkd0LHAkCOYz6TtxMQyCgPGEG0jAQkFKkT4p4l5Wa',
    'Bank', 'Admin', 'admin1@banking.com', '9999999998',
    'ADMIN', 1, 1, 1, 'VERIFIED', 'SYSTEM');

-- ============================================================
-- ACCOUNTS DATABASE SETUP
-- ============================================================
USE banking_accounts;

CREATE TABLE IF NOT EXISTS bank_accounts (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    account_number VARCHAR(16) NOT NULL UNIQUE,
    user_id BIGINT NOT NULL,
    account_type VARCHAR(20) NOT NULL,
    status ENUM('ACTIVE','INACTIVE','SUSPENDED','CLOSED') NOT NULL DEFAULT 'ACTIVE',
    balance DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    available_balance DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    minimum_balance DECIMAL(15,2) NOT NULL DEFAULT 1000.00,
    daily_transfer_limit DECIMAL(15,2) DEFAULT 100000.00,
    daily_transferred_today DECIMAL(15,2) DEFAULT 0.00,
    last_transfer_date DATETIME,
    ifsc_code VARCHAR(11) NOT NULL DEFAULT 'BANK0001001',
    branch_name VARCHAR(100) DEFAULT 'Main Branch',
    nominee_name VARCHAR(100),
    interest_rate DECIMAL(5,2) DEFAULT 3.50,
    is_primary TINYINT(1) NOT NULL DEFAULT 0,
    kyc_verified TINYINT(1) NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    closed_at DATETIME,
    INDEX idx_account_number (account_number),
    INDEX idx_account_user_id (user_id),
    INDEX idx_account_status (status)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS beneficiaries (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT NOT NULL,
    beneficiary_name VARCHAR(100) NOT NULL,
    beneficiary_account_number VARCHAR(16) NOT NULL,
    beneficiary_bank_name VARCHAR(100),
    ifsc_code VARCHAR(11) NOT NULL,
    nickname VARCHAR(50),
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    is_verified TINYINT(1) NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_benef_user_id (user_id),
    INDEX idx_benef_account (beneficiary_account_number)
) ENGINE=InnoDB;

-- Trigger: prevent negative balance
DELIMITER //
CREATE TRIGGER IF NOT EXISTS trg_no_negative_balance
BEFORE UPDATE ON bank_accounts
FOR EACH ROW
BEGIN
    IF NEW.balance < 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Balance cannot be negative';
    END IF;
    IF NEW.available_balance < 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Available balance cannot be negative';
    END IF;
END //
DELIMITER ;

-- ============================================================
-- TRANSACTIONS DATABASE SETUP
-- ============================================================
USE banking_transactions;

CREATE TABLE IF NOT EXISTS transactions (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    transaction_reference VARCHAR(30) NOT NULL UNIQUE,
    user_id BIGINT NOT NULL,
    account_number VARCHAR(16) NOT NULL,
    to_account_number VARCHAR(16),
    transaction_type ENUM('CREDIT','DEBIT','TRANSFER','LOAN_DISBURSEMENT','LOAN_REPAYMENT','INTEREST') NOT NULL,
    amount DECIMAL(15,2) NOT NULL,
    balance_before DECIMAL(15,2),
    balance_after DECIMAL(15,2),
    description VARCHAR(500),
    status VARCHAR(20) NOT NULL DEFAULT 'SUCCESS',
    channel VARCHAR(30) DEFAULT 'INTERNET_BANKING',
    ip_address VARCHAR(45),
    remarks VARCHAR(500),
    beneficiary_name VARCHAR(100),
    loan_id BIGINT,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_txn_ref (transaction_reference),
    INDEX idx_txn_account (account_number),
    INDEX idx_txn_user (user_id),
    INDEX idx_txn_created_at (created_at),
    INDEX idx_txn_type (transaction_type),
    INDEX idx_txn_user_date (user_id, created_at)
) ENGINE=InnoDB;

-- Trigger: transactions are IMMUTABLE
DELIMITER //
CREATE TRIGGER IF NOT EXISTS trg_txn_no_update
BEFORE UPDATE ON transactions
FOR EACH ROW
BEGIN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Transactions are immutable';
END //

CREATE TRIGGER IF NOT EXISTS trg_txn_no_delete
BEFORE DELETE ON transactions
FOR EACH ROW
BEGIN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Transactions cannot be deleted';
END //
DELIMITER ;

-- ============================================================
-- LOANS DATABASE SETUP
-- ============================================================
USE banking_loans;

CREATE TABLE IF NOT EXISTS loans (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    loan_reference VARCHAR(20) NOT NULL UNIQUE,
    user_id BIGINT NOT NULL,
    account_number VARCHAR(16) NOT NULL,
    loan_type VARCHAR(30) NOT NULL,
    principal_amount DECIMAL(15,2) NOT NULL,
    approved_amount DECIMAL(15,2),
    outstanding_amount DECIMAL(15,2),
    interest_rate DECIMAL(5,2) NOT NULL,
    tenure_months INT NOT NULL,
    emi_amount DECIMAL(15,2),
    status ENUM('PENDING','UNDER_REVIEW','APPROVED','REJECTED','DISBURSED','ACTIVE','CLOSED','DEFAULTED') NOT NULL DEFAULT 'PENDING',
    purpose VARCHAR(500),
    rejection_reason VARCHAR(500),
    disbursement_date DATE,
    maturity_date DATE,
    next_emi_date DATE,
    emis_paid INT NOT NULL DEFAULT 0,
    emis_total INT,
    last_payment_date DATE,
    reviewed_by BIGINT,
    reviewed_at DATETIME,
    review_notes VARCHAR(1000),
    annual_income DECIMAL(15,2),
    employment_type VARCHAR(30),
    employer_name VARCHAR(100),
    applied_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_loan_user_id (user_id),
    INDEX idx_loan_status (status),
    INDEX idx_loan_account (account_number),
    INDEX idx_loan_reference (loan_reference),
    INDEX idx_loan_user_status (user_id, status)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS emi_schedule (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    loan_id BIGINT NOT NULL,
    emi_number INT NOT NULL,
    due_date DATE NOT NULL,
    emi_amount DECIMAL(15,2) NOT NULL,
    principal_component DECIMAL(15,2),
    interest_component DECIMAL(15,2),
    outstanding_after DECIMAL(15,2),
    payment_status VARCHAR(20) NOT NULL DEFAULT 'PENDING',
    paid_date DATE,
    paid_amount DECIMAL(15,2),
    late_fee DECIMAL(15,2) DEFAULT 0.00,
    transaction_reference VARCHAR(30),
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_emi_loan_id (loan_id),
    INDEX idx_emi_due_date (due_date),
    INDEX idx_emi_status (payment_status)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS loan_documents (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    loan_id BIGINT NOT NULL,
    user_id BIGINT NOT NULL,
    document_type VARCHAR(50) NOT NULL,
    file_name VARCHAR(255) NOT NULL,
    file_path VARCHAR(500) NOT NULL,
    file_size BIGINT,
    mime_type VARCHAR(100),
    verification_status VARCHAR(20) DEFAULT 'PENDING',
    verified_by BIGINT,
    uploaded_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_doc_loan_id (loan_id),
    INDEX idx_doc_user_id (user_id)
) ENGINE=InnoDB;

SELECT 'All databases, tables, views and triggers created successfully!' AS result;
SELECT 'Login: superadmin / Admin@123456' AS admin_credentials;
SELECT 'IMPORTANT: Change the admin password immediately after first login!' AS warning;