-- create database bhishi_management;

-- use bhishi_management;

CREATE TABLE users (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    email_verified_at TIMESTAMP NULL,
    password VARCHAR(255) NOT NULL,
    remember_token VARCHAR(100),
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL
);

CREATE TABLE password_reset_tokens (
    email VARCHAR(255) PRIMARY KEY,
    token VARCHAR(255) NOT NULL,
    created_at TIMESTAMP NULL
);

CREATE TABLE sessions (
    id VARCHAR(255) PRIMARY KEY,
    user_id BIGINT UNSIGNED NULL,
    ip_address VARCHAR(45) NULL,
    user_agent TEXT NULL,
    payload LONGTEXT NOT NULL,
    last_activity INT NOT NULL,

    INDEX sessions_user_id_index(user_id),
    INDEX sessions_last_activity_index(last_activity)
);

CREATE TABLE cache (
    `key` VARCHAR(255) PRIMARY KEY,
    value MEDIUMTEXT NOT NULL,
    expiration INT NOT NULL,

    INDEX cache_expiration_index(expiration)
);

CREATE TABLE cache_locks (
    `key` VARCHAR(255) PRIMARY KEY,
    owner VARCHAR(255) NOT NULL,
    expiration INT NOT NULL,

    INDEX cache_locks_expiration_index(expiration)
);

CREATE TABLE jobs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    queue VARCHAR(255) NOT NULL,
    payload LONGTEXT NOT NULL,
    attempts TINYINT UNSIGNED NOT NULL,
    reserved_at INT UNSIGNED NULL,
    available_at INT UNSIGNED NOT NULL,
    created_at INT UNSIGNED NOT NULL,

    INDEX jobs_queue_index(queue)
);

CREATE TABLE job_batches (
    id VARCHAR(255) PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    total_jobs INT NOT NULL,
    pending_jobs INT NOT NULL,
    failed_jobs INT NOT NULL,
    failed_job_ids LONGTEXT NOT NULL,
    options MEDIUMTEXT NULL,
    cancelled_at INT NULL,
    created_at INT NOT NULL,
    finished_at INT NULL
);



CREATE TABLE failed_jobs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    uuid VARCHAR(255) NOT NULL UNIQUE,
    connection TEXT NOT NULL,
    queue TEXT NOT NULL,
    payload LONGTEXT NOT NULL,
    exception LONGTEXT NOT NULL,
    failed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);


CREATE TABLE migrations (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    migration VARCHAR(255) NOT NULL,
    batch INT NOT NULL
);


CREATE TABLE accounts (

    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    account_no VARCHAR(30) NOT NULL UNIQUE,

    customer_name VARCHAR(150) NOT NULL,

    mobile VARCHAR(20) NOT NULL,

    email VARCHAR(100) NULL,

    opening_date DATE NOT NULL,

    collection_type ENUM(
        'Daily',
        'Weekly',
        'Monthly'
    ) NOT NULL,

    installment_amount DECIMAL(12,2) NOT NULL,

    total_installments INT NOT NULL,

    status ENUM(
        'Active',
        'Closed'
    ) DEFAULT 'Active',

    remarks TEXT NULL,

    created_at TIMESTAMP NULL DEFAULT NULL,

    updated_at TIMESTAMP NULL DEFAULT NULL

);

CREATE TABLE collections (

    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    receipt_no VARCHAR(30) NOT NULL UNIQUE,

    account_id BIGINT UNSIGNED NOT NULL,

    collection_date DATE NOT NULL,

    amount DECIMAL(12,2) NOT NULL,

    payment_mode ENUM(
        'Cash',
        'UPI',
        'Cheque',
        'Online'
    ) DEFAULT 'Cash',

    remarks TEXT NULL,

    created_at TIMESTAMP NULL DEFAULT NULL,

    updated_at TIMESTAMP NULL DEFAULT NULL,

    CONSTRAINT fk_collection_account
    FOREIGN KEY (account_id)
    REFERENCES accounts(id)
    ON DELETE CASCADE

);

ALTER TABLE accounts
MODIFY total_installments INT NULL DEFAULT NULL;

CREATE TABLE loans (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    account_id BIGINT UNSIGNED NOT NULL,

    loan_amount DECIMAL(12,2) NOT NULL,
    interest_rate DECIMAL(5,2) NOT NULL, 
    loan_start_date DATE NOT NULL,
    tenure_months INT NULL,

    total_interest DECIMAL(12,2) DEFAULT 0,
    total_payable DECIMAL(12,2) DEFAULT 0,

    status ENUM('Active','Closed') DEFAULT 'Active',

    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,

    FOREIGN KEY (account_id) REFERENCES accounts(id) ON DELETE CASCADE
);

ALTER TABLE collections
ADD COLUMN loan_id BIGINT UNSIGNED NULL AFTER account_id,
ADD COLUMN principal_amount DECIMAL(12,2) DEFAULT 0 AFTER amount,
ADD COLUMN interest_amount DECIMAL(12,2) DEFAULT 0 AFTER principal_amount;

ALTER TABLE collections
ADD CONSTRAINT fk_collection_loan
FOREIGN KEY (loan_id)
REFERENCES loans(id)
ON DELETE CASCADE;