CREATE DATABASE IF NOT EXISTS double_entry_accounting_mvc CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE double_entry_accounting_mvc;

SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE IF EXISTS ledger;
DROP TABLE IF EXISTS voucher_details;
DROP TABLE IF EXISTS vouchers;
DROP TABLE IF EXISTS chart_of_accounts;
DROP TABLE IF EXISTS users;
SET FOREIGN_KEY_CHECKS = 1;

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    status ENUM('Active','Inactive') NOT NULL DEFAULT 'Active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE chart_of_accounts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    account_code VARCHAR(50) NOT NULL UNIQUE,
    account_name VARCHAR(150) NOT NULL,
    account_type ENUM('Asset','Liability','Equity','Income','Expense') NOT NULL,
    parent_id INT NULL,
    opening_balance DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    status ENUM('Active','Inactive') NOT NULL DEFAULT 'Active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_coa_parent FOREIGN KEY (parent_id) REFERENCES chart_of_accounts(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE vouchers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    voucher_no VARCHAR(50) NOT NULL UNIQUE,
    voucher_date DATE NOT NULL,
    reference VARCHAR(100) NULL,
    narration TEXT NULL,
    total_debit DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    total_credit DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    created_by INT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_voucher_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE voucher_details (
    id INT AUTO_INCREMENT PRIMARY KEY,
    voucher_id INT NOT NULL,
    account_id INT NOT NULL,
    debit DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    credit DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    remarks VARCHAR(255) NULL,
    CONSTRAINT fk_vd_voucher FOREIGN KEY (voucher_id) REFERENCES vouchers(id) ON DELETE CASCADE,
    CONSTRAINT fk_vd_account FOREIGN KEY (account_id) REFERENCES chart_of_accounts(id) ON DELETE RESTRICT
) ENGINE=InnoDB;

CREATE TABLE ledger (
    id INT AUTO_INCREMENT PRIMARY KEY,
    account_id INT NOT NULL,
    voucher_id INT NOT NULL,
    voucher_detail_id INT NOT NULL,
    posting_date DATE NOT NULL,
    voucher_no VARCHAR(50) NOT NULL,
    reference VARCHAR(100) NULL,
    narration TEXT NULL,
    debit DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    credit DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_ledger_account_date (account_id, posting_date),
    INDEX idx_ledger_voucher (voucher_id),
    CONSTRAINT fk_ledger_account FOREIGN KEY (account_id) REFERENCES chart_of_accounts(id) ON DELETE RESTRICT,
    CONSTRAINT fk_ledger_voucher FOREIGN KEY (voucher_id) REFERENCES vouchers(id) ON DELETE CASCADE,
    CONSTRAINT fk_ledger_detail FOREIGN KEY (voucher_detail_id) REFERENCES voucher_details(id) ON DELETE CASCADE
) ENGINE=InnoDB;

INSERT INTO users (name, email, password, status) VALUES
('Admin User', 'admin@example.com', '$2y$12$A0CGNRilAPFL8e/sJDcA3.lahFLUjc7M94RLc8N3GfeVe0k.mIMFS', 'Active');

INSERT INTO chart_of_accounts (id, account_code, account_name, account_type, parent_id, opening_balance, status) VALUES
(1, '1000', 'Assets', 'Asset', NULL, 0, 'Active'),
(2, '1100', 'Cash in Hand', 'Asset', 1, 5000, 'Active'),
(3, '1200', 'Bank Account', 'Asset', 1, 10000, 'Active'),
(4, '1300', 'Accounts Receivable', 'Asset', 1, 0, 'Active'),
(5, '2000', 'Liabilities', 'Liability', NULL, 0, 'Active'),
(6, '2100', 'Accounts Payable', 'Liability', 5, 0, 'Active'),
(7, '3000', 'Owner Equity', 'Equity', NULL, -15000, 'Active'),
(8, '4000', 'Income', 'Income', NULL, 0, 'Active'),
(9, '4100', 'Sales Revenue', 'Income', 8, 0, 'Active'),
(10, '5000', 'Expenses', 'Expense', NULL, 0, 'Active'),
(11, '5100', 'Rent Expense', 'Expense', 10, 0, 'Active'),
(12, '5200', 'Office Supplies Expense', 'Expense', 10, 0, 'Active');

INSERT INTO vouchers (id, voucher_no, voucher_date, reference, narration, total_debit, total_credit, created_by) VALUES
(1, 'JV-202605-0001', '2026-05-01', 'SALE-001', 'Cash sale recorded', 1200, 1200, 1),
(2, 'JV-202605-0002', '2026-05-03', 'RENT-001', 'Monthly office rent paid', 700, 700, 1);

INSERT INTO voucher_details (id, voucher_id, account_id, debit, credit, remarks) VALUES
(1, 1, 2, 1200, 0, 'Cash received'),
(2, 1, 9, 0, 1200, 'Sales revenue'),
(3, 2, 11, 700, 0, 'Rent expense'),
(4, 2, 2, 0, 700, 'Cash paid');

INSERT INTO ledger (account_id, voucher_id, voucher_detail_id, posting_date, voucher_no, reference, narration, debit, credit)
SELECT vd.account_id, v.id, vd.id, v.voucher_date, v.voucher_no, v.reference, v.narration, vd.debit, vd.credit
FROM voucher_details vd
INNER JOIN vouchers v ON v.id = vd.voucher_id;