-- Expand default chart of accounts with additional standard accounts
-- Safe to run multiple times (INSERT IGNORE skips duplicates on code+tenant_id)

-- ── Assets ───────────────────────────────────────────────────────────────────
-- Additional current assets (parent: 1000)
INSERT IGNORE INTO accounts (tenant_id, code, name, type, parent_id)
SELECT t.id, '1050', 'Short-term Investments', 'asset',
  (SELECT id FROM accounts a WHERE a.tenant_id = t.id AND a.code = '1000' LIMIT 1)
FROM tenants t;

INSERT IGNORE INTO accounts (tenant_id, code, name, type, parent_id)
SELECT t.id, '1060', 'Employee Advances', 'asset',
  (SELECT id FROM accounts a WHERE a.tenant_id = t.id AND a.code = '1000' LIMIT 1)
FROM tenants t;

INSERT IGNORE INTO accounts (tenant_id, code, name, type, parent_id)
SELECT t.id, '1070', 'Other Current Assets', 'asset',
  (SELECT id FROM accounts a WHERE a.tenant_id = t.id AND a.code = '1000' LIMIT 1)
FROM tenants t;

-- Additional fixed assets (parent: 1500)
INSERT IGNORE INTO accounts (tenant_id, code, name, type, parent_id)
SELECT t.id, '1530', 'Vehicles', 'asset',
  (SELECT id FROM accounts a WHERE a.tenant_id = t.id AND a.code = '1500' LIMIT 1)
FROM tenants t;

INSERT IGNORE INTO accounts (tenant_id, code, name, type, parent_id)
SELECT t.id, '1540', 'Furniture and Fixtures', 'asset',
  (SELECT id FROM accounts a WHERE a.tenant_id = t.id AND a.code = '1500' LIMIT 1)
FROM tenants t;

INSERT IGNORE INTO accounts (tenant_id, code, name, type, parent_id)
SELECT t.id, '1550', 'Computer Equipment', 'asset',
  (SELECT id FROM accounts a WHERE a.tenant_id = t.id AND a.code = '1500' LIMIT 1)
FROM tenants t;

INSERT IGNORE INTO accounts (tenant_id, code, name, type, parent_id)
SELECT t.id, '1560', 'Leasehold Improvements', 'asset',
  (SELECT id FROM accounts a WHERE a.tenant_id = t.id AND a.code = '1500' LIMIT 1)
FROM tenants t;

-- Other assets (no parent)
INSERT IGNORE INTO accounts (tenant_id, code, name, type, parent_id)
SELECT t.id, '1900', 'Other Assets', 'asset', NULL
FROM tenants t;

INSERT IGNORE INTO accounts (tenant_id, code, name, type, parent_id)
SELECT t.id, '1910', 'Intangible Assets', 'asset',
  (SELECT id FROM accounts a WHERE a.tenant_id = t.id AND a.code = '1900' LIMIT 1)
FROM tenants t;

INSERT IGNORE INTO accounts (tenant_id, code, name, type, parent_id)
SELECT t.id, '1920', 'Security Deposits', 'asset',
  (SELECT id FROM accounts a WHERE a.tenant_id = t.id AND a.code = '1900' LIMIT 1)
FROM tenants t;

-- ── Liabilities ──────────────────────────────────────────────────────────────
-- Additional current liabilities (parent: 2000)
INSERT IGNORE INTO accounts (tenant_id, code, name, type, parent_id)
SELECT t.id, '2040', 'Sales Tax Payable', 'liability',
  (SELECT id FROM accounts a WHERE a.tenant_id = t.id AND a.code = '2000' LIMIT 1)
FROM tenants t;

INSERT IGNORE INTO accounts (tenant_id, code, name, type, parent_id)
SELECT t.id, '2050', 'Payroll Liabilities', 'liability',
  (SELECT id FROM accounts a WHERE a.tenant_id = t.id AND a.code = '2000' LIMIT 1)
FROM tenants t;

INSERT IGNORE INTO accounts (tenant_id, code, name, type, parent_id)
SELECT t.id, '2060', 'Deferred Revenue', 'liability',
  (SELECT id FROM accounts a WHERE a.tenant_id = t.id AND a.code = '2000' LIMIT 1)
FROM tenants t;

INSERT IGNORE INTO accounts (tenant_id, code, name, type, parent_id)
SELECT t.id, '2070', 'Credit Card Payable', 'liability',
  (SELECT id FROM accounts a WHERE a.tenant_id = t.id AND a.code = '2000' LIMIT 1)
FROM tenants t;

INSERT IGNORE INTO accounts (tenant_id, code, name, type, parent_id)
SELECT t.id, '2080', 'Customer Deposits', 'liability',
  (SELECT id FROM accounts a WHERE a.tenant_id = t.id AND a.code = '2000' LIMIT 1)
FROM tenants t;

-- Additional long-term liabilities (parent: 2500)
INSERT IGNORE INTO accounts (tenant_id, code, name, type, parent_id)
SELECT t.id, '2520', 'Notes Payable', 'liability',
  (SELECT id FROM accounts a WHERE a.tenant_id = t.id AND a.code = '2500' LIMIT 1)
FROM tenants t;

INSERT IGNORE INTO accounts (tenant_id, code, name, type, parent_id)
SELECT t.id, '2530', 'Equipment Financing', 'liability',
  (SELECT id FROM accounts a WHERE a.tenant_id = t.id AND a.code = '2500' LIMIT 1)
FROM tenants t;

-- ── Equity ───────────────────────────────────────────────────────────────────
INSERT IGNORE INTO accounts (tenant_id, code, name, type, parent_id)
SELECT t.id, '3040', 'Owner Contribution', 'equity',
  (SELECT id FROM accounts a WHERE a.tenant_id = t.id AND a.code = '3000' LIMIT 1)
FROM tenants t;

INSERT IGNORE INTO accounts (tenant_id, code, name, type, parent_id)
SELECT t.id, '3050', 'Additional Paid-in Capital', 'equity',
  (SELECT id FROM accounts a WHERE a.tenant_id = t.id AND a.code = '3000' LIMIT 1)
FROM tenants t;

-- ── Revenue ──────────────────────────────────────────────────────────────────
INSERT IGNORE INTO accounts (tenant_id, code, name, type, parent_id)
SELECT t.id, '4040', 'Interest Income', 'revenue',
  (SELECT id FROM accounts a WHERE a.tenant_id = t.id AND a.code = '4000' LIMIT 1)
FROM tenants t;

INSERT IGNORE INTO accounts (tenant_id, code, name, type, parent_id)
SELECT t.id, '4050', 'Consulting Revenue', 'revenue',
  (SELECT id FROM accounts a WHERE a.tenant_id = t.id AND a.code = '4000' LIMIT 1)
FROM tenants t;

INSERT IGNORE INTO accounts (tenant_id, code, name, type, parent_id)
SELECT t.id, '4060', 'Subscription Revenue', 'revenue',
  (SELECT id FROM accounts a WHERE a.tenant_id = t.id AND a.code = '4000' LIMIT 1)
FROM tenants t;

-- ── Expenses ─────────────────────────────────────────────────────────────────
INSERT IGNORE INTO accounts (tenant_id, code, name, type, parent_id)
SELECT t.id, '5025', 'Payroll Taxes', 'expense',
  (SELECT id FROM accounts a WHERE a.tenant_id = t.id AND a.code = '5000' LIMIT 1)
FROM tenants t;

INSERT IGNORE INTO accounts (tenant_id, code, name, type, parent_id)
SELECT t.id, '5075', 'Legal and Accounting', 'expense',
  (SELECT id FROM accounts a WHERE a.tenant_id = t.id AND a.code = '5000' LIMIT 1)
FROM tenants t;

INSERT IGNORE INTO accounts (tenant_id, code, name, type, parent_id)
SELECT t.id, '5110', 'Travel and Entertainment', 'expense',
  (SELECT id FROM accounts a WHERE a.tenant_id = t.id AND a.code = '5000' LIMIT 1)
FROM tenants t;

INSERT IGNORE INTO accounts (tenant_id, code, name, type, parent_id)
SELECT t.id, '5120', 'Meals and Entertainment', 'expense',
  (SELECT id FROM accounts a WHERE a.tenant_id = t.id AND a.code = '5000' LIMIT 1)
FROM tenants t;

INSERT IGNORE INTO accounts (tenant_id, code, name, type, parent_id)
SELECT t.id, '5130', 'Software and Subscriptions', 'expense',
  (SELECT id FROM accounts a WHERE a.tenant_id = t.id AND a.code = '5000' LIMIT 1)
FROM tenants t;

INSERT IGNORE INTO accounts (tenant_id, code, name, type, parent_id)
SELECT t.id, '5140', 'Bank Fees', 'expense',
  (SELECT id FROM accounts a WHERE a.tenant_id = t.id AND a.code = '5000' LIMIT 1)
FROM tenants t;

INSERT IGNORE INTO accounts (tenant_id, code, name, type, parent_id)
SELECT t.id, '5150', 'Repairs and Maintenance', 'expense',
  (SELECT id FROM accounts a WHERE a.tenant_id = t.id AND a.code = '5000' LIMIT 1)
FROM tenants t;

INSERT IGNORE INTO accounts (tenant_id, code, name, type, parent_id)
SELECT t.id, '5160', 'Shipping and Postage', 'expense',
  (SELECT id FROM accounts a WHERE a.tenant_id = t.id AND a.code = '5000' LIMIT 1)
FROM tenants t;

INSERT IGNORE INTO accounts (tenant_id, code, name, type, parent_id)
SELECT t.id, '5170', 'Vehicle Expense', 'expense',
  (SELECT id FROM accounts a WHERE a.tenant_id = t.id AND a.code = '5000' LIMIT 1)
FROM tenants t;

INSERT IGNORE INTO accounts (tenant_id, code, name, type, parent_id)
SELECT t.id, '5180', 'Training and Education', 'expense',
  (SELECT id FROM accounts a WHERE a.tenant_id = t.id AND a.code = '5000' LIMIT 1)
FROM tenants t;

INSERT IGNORE INTO accounts (tenant_id, code, name, type, parent_id)
SELECT t.id, '5190', 'Charitable Contributions', 'expense',
  (SELECT id FROM accounts a WHERE a.tenant_id = t.id AND a.code = '5000' LIMIT 1)
FROM tenants t;
