CREATE TABLE IF NOT EXISTS acc_accounts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(20) UNIQUE NOT NULL,
    name_en VARCHAR(100) NOT NULL,
    name_ar VARCHAR(100) NOT NULL,
    type ENUM('asset', 'liability', 'equity', 'revenue', 'expense') NOT NULL,
    parent_id INT NULL,
    FOREIGN KEY (parent_id) REFERENCES acc_accounts(id)
);

CREATE TABLE IF NOT EXISTS acc_journal_entries (
    id INT AUTO_INCREMENT PRIMARY KEY,
    entry_date DATE NOT NULL,
    description TEXT,
    reference VARCHAR(100),
    source_type VARCHAR(50),
    source_id INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS acc_ledger (
    id INT AUTO_INCREMENT PRIMARY KEY,
    journal_entry_id INT NOT NULL,
    account_id INT NOT NULL,
    debit DECIMAL(15, 3) DEFAULT 0,
    credit DECIMAL(15, 3) DEFAULT 0,
    FOREIGN KEY (journal_entry_id) REFERENCES acc_journal_entries(id) ON DELETE CASCADE,
    FOREIGN KEY (account_id) REFERENCES acc_accounts(id)
);

INSERT IGNORE INTO acc_accounts (code, name_en, name_ar, type) VALUES 
('1000', 'Assets', 'الأصول', 'asset'),
('1100', 'Cash on Hand', 'النقدية', 'asset'),
('1200', 'Bank Account', 'حساب البنك', 'asset'),
('1300', 'Accounts Receivable', 'حسابات العملاء', 'asset'),
('1400', 'Inventory', 'المخزون', 'asset'),
('2000', 'Liabilities', 'الالتزامات', 'liability'),
('2100', 'Accounts Payable', 'حسابات الموردين', 'liability'),
('3000', 'Equity', 'حقوق الملكية', 'equity'),
('4000', 'Revenue', 'الإيرادات', 'revenue'),
('4100', 'Sales Revenue', 'إيرادات المبيعات', 'revenue'),
('5000', 'Expenses', 'المصروفات', 'expense'),
('5100', 'Cost of Goods Sold', 'تكلفة البضاعة المباعة', 'expense'),
('5200', 'Operating Expenses', 'مصاريف تشغيلية', 'expense');

UPDATE acc_accounts SET parent_id = (SELECT id FROM (SELECT id FROM acc_accounts AS x WHERE x.code='1000') AS t) WHERE code IN ('1100', '1200', '1300', '1400');
UPDATE acc_accounts SET parent_id = (SELECT id FROM (SELECT id FROM acc_accounts AS x WHERE x.code='2000') AS t) WHERE code IN ('2100');
UPDATE acc_accounts SET parent_id = (SELECT id FROM (SELECT id FROM acc_accounts AS x WHERE x.code='4000') AS t) WHERE code IN ('4100');
UPDATE acc_accounts SET parent_id = (SELECT id FROM (SELECT id FROM acc_accounts AS x WHERE x.code='5000') AS t) WHERE code IN ('5100', '5200');