-- ============================================================
-- Run this on an EXISTING FULL EDITION company database that
-- is getting HTTP 500 errors on: Item Master, Purchase Payments,
-- Payments, Accounts (Bank Accounts / Expenses / Salaries /
-- Journal). Those pages need tables that were missing from the
-- original schema - this creates them. Safe to re-run, and does
-- not touch any of your existing data in other tables.
-- ============================================================

CREATE TABLE IF NOT EXISTS account_groups (
    id INT AUTO_INCREMENT PRIMARY KEY,
    group_name VARCHAR(100) NOT NULL UNIQUE,
    group_type ENUM('asset','liability','equity','income','expense') NOT NULL,
    sort_order INT DEFAULT 0
);

CREATE TABLE IF NOT EXISTS accounts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    account_code VARCHAR(20) UNIQUE NOT NULL,
    account_name VARCHAR(150) NOT NULL,
    account_type ENUM('asset','liability','equity','income','expense') NOT NULL,
    account_subtype VARCHAR(30) NULL,
    group_id INT NULL,
    bank_name VARCHAR(100) NULL,
    account_number VARCHAR(50) NULL,
    ifsc_code VARCHAR(20) NULL,
    opening_balance DECIMAL(14,2) DEFAULT 0,
    current_balance DECIMAL(14,2) DEFAULT 0,
    description TEXT NULL,
    is_system TINYINT DEFAULT 0,
    is_active TINYINT DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (group_id) REFERENCES account_groups(id)
);

CREATE TABLE IF NOT EXISTS journal_entries (
    id INT AUTO_INCREMENT PRIMARY KEY,
    entry_no VARCHAR(30) UNIQUE NOT NULL,
    entry_date DATE NOT NULL,
    entry_type VARCHAR(30) NOT NULL,
    reference_type VARCHAR(30) NULL,
    reference_id INT NULL,
    narration TEXT NULL,
    total_debit DECIMAL(14,2) DEFAULT 0,
    total_credit DECIMAL(14,2) DEFAULT 0,
    created_by INT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS journal_lines (
    id INT AUTO_INCREMENT PRIMARY KEY,
    journal_id INT NOT NULL,
    account_id INT NOT NULL,
    debit DECIMAL(14,2) DEFAULT 0,
    credit DECIMAL(14,2) DEFAULT 0,
    narration VARCHAR(255) NULL,
    FOREIGN KEY (journal_id) REFERENCES journal_entries(id) ON DELETE CASCADE,
    FOREIGN KEY (account_id) REFERENCES accounts(id)
);

CREATE TABLE IF NOT EXISTS expenses (
    id INT AUTO_INCREMENT PRIMARY KEY,
    expense_date DATE NOT NULL,
    category VARCHAR(100) NOT NULL,
    description VARCHAR(255) NOT NULL,
    amount DECIMAL(12,2) NOT NULL,
    paid_from INT NOT NULL,
    vendor_name VARCHAR(150) NULL,
    reference_no VARCHAR(50) NULL,
    journal_id INT NULL,
    created_by INT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (paid_from) REFERENCES accounts(id),
    FOREIGN KEY (journal_id) REFERENCES journal_entries(id)
);

CREATE TABLE IF NOT EXISTS salaries (
    id INT AUTO_INCREMENT PRIMARY KEY,
    salary_month VARCHAR(7) NOT NULL,
    employee_name VARCHAR(150) NOT NULL,
    designation VARCHAR(100) NULL,
    basic_salary DECIMAL(12,2) DEFAULT 0,
    allowances DECIMAL(12,2) DEFAULT 0,
    deductions DECIMAL(12,2) DEFAULT 0,
    net_salary DECIMAL(12,2) DEFAULT 0,
    status ENUM('pending','paid') DEFAULT 'pending',
    paid_from INT NULL,
    paid_date DATE NULL,
    payment_mode VARCHAR(30) NULL,
    journal_id INT NULL,
    created_by INT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (paid_from) REFERENCES accounts(id),
    FOREIGN KEY (journal_id) REFERENCES journal_entries(id)
);

CREATE TABLE IF NOT EXISTS item_configurations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    category_id INT NULL,
    config_name VARCHAR(100) NOT NULL,
    status TINYINT DEFAULT 1,
    sort_order INT DEFAULT 0,
    UNIQUE KEY uniq_cat_config (category_id, config_name)
);
-- Fixes an earlier version of this migration that created the table
-- without these two columns - safe/no-op if they're already there.
ALTER TABLE item_configurations
    ADD COLUMN IF NOT EXISTS status TINYINT DEFAULT 1,
    ADD COLUMN IF NOT EXISTS sort_order INT DEFAULT 0;

-- Fixes item_brands (Item Master "Add Brand" was missing category_id,
-- causing HTTP 500 on the Item Master page). Safe/no-op if already there.
ALTER TABLE item_brands
    ADD COLUMN IF NOT EXISTS category_id INT NULL;

-- Fixes item_brands.brand_name being globally UNIQUE, which silently broke
-- "Add Brand" for any brand name that already existed under a different
-- category (e.g. the pre-seeded "Dell", "HP", etc.) and could cascade into
-- a 500 error. Brand names should only be unique per-category.
-- NOTE: the next two statements are expected to show an error the SECOND
-- time you run this migration (index already dropped / already added) -
-- that is normal and can be ignored; the Super Admin "Run Migration"
-- button already treats each statement independently so one statement
-- erroring never stops the rest of the migration from running.
ALTER TABLE item_brands DROP INDEX brand_name;
ALTER TABLE item_brands ADD UNIQUE KEY uniq_brand_category (brand_name, category_id);

-- Fixes items table: the app's code expects brand_id/model_id (linking to
-- item_brands/item_models), but the original table only had free-text
-- brand/model columns. This was the actual cause of "Add Item" failing
-- with a 500 error. The old brand/model text columns are left in place
-- (unused) so no existing data is touched.
ALTER TABLE items
    ADD COLUMN IF NOT EXISTS brand_id INT NULL,
    ADD COLUMN IF NOT EXISTS model_id INT NULL;

CREATE TABLE IF NOT EXISTS item_configuration_values (
    id INT AUTO_INCREMENT PRIMARY KEY,
    config_id INT NOT NULL,
    value VARCHAR(150) NOT NULL,
    status TINYINT DEFAULT 1,
    UNIQUE KEY uniq_config_value (config_id, value),
    FOREIGN KEY (config_id) REFERENCES item_configurations(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS item_specs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    item_id INT NOT NULL,
    config_id INT NOT NULL,
    config_value_id INT NOT NULL,
    UNIQUE KEY uniq_item_config (item_id, config_id),
    FOREIGN KEY (item_id) REFERENCES items(id) ON DELETE CASCADE,
    FOREIGN KEY (config_id) REFERENCES item_configurations(id),
    FOREIGN KEY (config_value_id) REFERENCES item_configuration_values(id)
);

INSERT IGNORE INTO account_groups (group_name, group_type, sort_order) VALUES
('Bank Accounts', 'asset', 1),
('Cash in Hand', 'asset', 2),
('Credit Cards', 'liability', 3),
('Receivables', 'asset', 4),
('Inventory', 'asset', 5),
('Payables', 'liability', 6),
('Sales Revenue', 'income', 7),
('Operating Expenses', 'expense', 8),
('Payroll', 'expense', 9);

INSERT IGNORE INTO accounts (account_code, account_name, account_type, account_subtype, group_id, is_system, is_active) VALUES
('1100', 'Accounts Receivable', 'asset', 'receivable', (SELECT id FROM account_groups WHERE group_name='Receivables'), 1, 1),
('1200', 'Stock / Inventory', 'asset', 'inventory', (SELECT id FROM account_groups WHERE group_name='Inventory'), 1, 1),
('2100', 'Accounts Payable', 'liability', 'payable', (SELECT id FROM account_groups WHERE group_name='Payables'), 1, 1),
('4001', 'Sales Revenue', 'income', 'income', (SELECT id FROM account_groups WHERE group_name='Sales Revenue'), 1, 1),
('5105', 'Miscellaneous Expense', 'expense', 'expense', (SELECT id FROM account_groups WHERE group_name='Operating Expenses'), 1, 1),
('5200', 'Salary Expense', 'expense', 'expense', (SELECT id FROM account_groups WHERE group_name='Payroll'), 1, 1);