-- =========================================================
-- Real studio chart of accounts + opening balances
-- =========================================================

-- 1. Full chart of accounts (existing codes untouched)
INSERT INTO public.accounts (code, name, account_type_id, description)
SELECT v.code, v.name, t.id, v.descr
FROM (VALUES
  -- Cash & bank
  ('1030','Mobile Wallet (bKash/Nagad)','CASH','Mobile financial service wallet'),
  ('1110','Bank - Current Account','CASH','Primary operating bank account'),
  ('1120','Bank - Savings Account','CASH','Reserve savings account'),
  -- Other assets
  ('1250','Advances to Staff','OTHER_ASSET','Salary and expense advances'),
  ('1260','Prepaid Rent','OTHER_ASSET','Rent paid in advance'),
  ('1265','Prepaid Insurance','OTHER_ASSET','Insurance paid in advance'),
  ('1270','Security Deposits','OTHER_ASSET','Refundable deposits held by others'),
  ('1280','Inventory - Merchandise','OTHER_ASSET','Prints, albums and resale stock'),
  -- Fixed assets
  ('1320','Cameras & Lenses','FIXED','Camera bodies, lenses and accessories'),
  ('1330','Lighting & Grip','FIXED','Lights, stands, modifiers and grip gear'),
  ('1340','Audio Equipment','FIXED','Microphones, recorders and monitoring'),
  ('1350','Computers & Editing Suite','FIXED','Workstations, monitors and storage'),
  ('1360','Furniture & Fixtures','FIXED','Studio furniture and fit-out'),
  ('1370','Studio Improvements','FIXED','Acoustic treatment, cyclorama, build-out'),
  ('1390','Accumulated Depreciation','FIXED','Contra asset for accumulated depreciation'),
  -- Liabilities
  ('2110','Withholding Tax Payable','TAX','Tax deducted at source, payable'),
  ('2210','Customer Advances','OTHER_LIAB','Deposits received for future bookings'),
  ('2220','Salaries Payable','OTHER_LIAB','Accrued unpaid staff salaries'),
  ('2230','Utilities Payable','OTHER_LIAB','Accrued electricity, gas and internet'),
  ('2240','Rent Payable','OTHER_LIAB','Accrued unpaid studio rent'),
  ('2300','Loans Payable','OTHER_LIAB','Bank or private loans outstanding'),
  -- Revenue
  ('4010','Studio Rental - Hourly','REVENUE','Hourly room and floor bookings'),
  ('4020','Studio Rental - Daily','REVENUE','Full-day and multi-day bookings'),
  ('4110','Photography Services','REVENUE','Shoots billed as a service'),
  ('4120','Videography Services','REVENUE','Video production services'),
  ('4130','Editing & Post-production','REVENUE','Retouching, editing and grading'),
  ('4140','Equipment Rental Income','REVENUE','Gear rented out to clients'),
  ('4210','Print & Album Sales','REVENUE','Physical product sales'),
  -- Cost of sales
  ('5010','Freelancer & Crew Costs','COS','Assistants, crew and freelancers'),
  ('5020','Props, Sets & Consumables','COS','Props, backdrops and consumables'),
  ('5030','Printing & Album Costs','COS','Lab, print and album production'),
  ('5040','Outsourced Equipment Rental','COS','Gear rented in for a job'),
  -- Operating expenses
  ('6050','Staff Bonus & Allowance','EXPENSE','Bonuses, festival and other allowances'),
  ('6210','Water & Gas','EXPENSE','Water and gas utilities'),
  ('6350','Software Subscriptions','EXPENSE','Editing, cloud and business software'),
  ('6410','Advertising & Promotion','EXPENSE','Paid ads, shoots and promotions'),
  ('6550','Equipment Repair & Service','EXPENSE','Repairs and servicing of gear'),
  ('6650','Fuel & Travel','EXPENSE','Fuel, fares and location travel'),
  ('6710','Bank Charges','EXPENSE','Bank and payment gateway fees'),
  ('6720','Depreciation Expense','EXPENSE','Periodic depreciation of fixed assets'),
  ('6730','Professional Fees','EXPENSE','Accounting, legal and consulting'),
  ('6740','Office Supplies','EXPENSE','Stationery and office consumables'),
  ('6750','Insurance','EXPENSE','Equipment, studio and liability cover'),
  ('6760','Training & Development','EXPENSE','Courses and skills development'),
  ('6770','Entertainment & Refreshment','EXPENSE','Client and crew refreshments')
) AS v(code, name, type_code, descr)
JOIN public.account_types t ON t.code = v.type_code
ON CONFLICT (code) DO NOTHING;

-- 2. Group child accounts under their header accounts
UPDATE public.accounts c SET parent_id = p.id
FROM (VALUES
  ('1010','1000'),('1020','1000'),('1030','1000'),
  ('1110','1100'),('1120','1100'),
  ('1320','1300'),('1330','1300'),('1340','1300'),('1350','1300'),('1360','1300'),('1370','1300'),('1390','1300'),
  ('2110','2100'),
  ('2210','2200'),('2220','2200'),('2230','2200'),('2240','2200'),('2300','2200'),
  ('4010','4000'),('4020','4000'),
  ('4110','4100'),('4120','4100'),('4130','4100'),('4140','4100'),
  ('4210','4200'),
  ('5010','5000'),('5020','5000'),('5030','5000'),('5040','5000'),
  ('6050','6000'),('6210','6200'),('6350','6300'),('6410','6400'),('6550','6500'),('6650','6600'),
  ('6710','6700'),('6720','6700'),('6730','6700'),('6740','6700'),('6750','6700'),('6760','6700'),('6770','6700')
) AS m(child, parent)
JOIN public.accounts p ON p.code = m.parent
WHERE c.code = m.child AND c.parent_id IS DISTINCT FROM p.id;

-- Header and control accounts should not be posted to by accident
UPDATE public.accounts SET is_system = true
WHERE code IN ('1000','1100','1200','1300','2000','2100','2200','3000','3100','3200','3300','4000','4100','4200','5000','6000','6100','6200','6300','6400','6500','6600','6700');

-- 3. Cash, bank and mobile registers mapped to the ledger
INSERT INTO public.cash_accounts (name, account_id, kind)
SELECT v.name, a.id, v.kind
FROM (VALUES ('Main Cash Drawer','1010','cash'), ('Petty Cash','1020','petty')) AS v(name, code, kind)
JOIN public.accounts a ON a.code = v.code
WHERE NOT EXISTS (SELECT 1 FROM public.cash_accounts c WHERE c.account_id = a.id);

INSERT INTO public.bank_accounts (name, account_id, bank_name, kind)
SELECT v.name, a.id, v.bank, v.kind
FROM (VALUES
  ('Current Account','1110','Primary Bank','bank'),
  ('Savings Account','1120','Primary Bank','bank'),
  ('Mobile Wallet','1030','bKash / Nagad','mobile')
) AS v(name, code, bank, kind)
JOIN public.accounts a ON a.code = v.code
WHERE NOT EXISTS (SELECT 1 FROM public.bank_accounts b WHERE b.account_id = a.id);

-- 4. Expense categories for the new expense accounts
INSERT INTO public.expense_categories (name, account_id)
SELECT a.name, a.id
FROM public.accounts a
JOIN public.account_types t ON t.id = a.account_type_id
WHERE t.code IN ('EXPENSE','COS') AND a.is_system = false
  AND NOT EXISTS (SELECT 1 FROM public.expense_categories e WHERE e.account_id = a.id);

-- 5. Fiscal periods for the current calendar year
INSERT INTO public.fiscal_periods (name, start_date, end_date, status)
SELECT to_char(m, 'FY YYYY - Month'), m::date, (m + interval '1 month - 1 day')::date, 'open'
FROM generate_series(date_trunc('year', CURRENT_DATE), date_trunc('year', CURRENT_DATE) + interval '11 months', interval '1 month') AS m
WHERE NOT EXISTS (SELECT 1 FROM public.fiscal_periods f WHERE f.start_date = m::date);

-- 6. Opening balances posted as a single balanced journal entry
DO $$
DECLARE _lines jsonb := '[]'::jsonb; _r record; _je uuid;
BEGIN
  IF EXISTS (SELECT 1 FROM public.journal_entries WHERE source_type = 'opening_balance') THEN
    RETURN;
  END IF;

  FOR _r IN
    SELECT a.id, v.dr, v.cr
    FROM (VALUES
      ('1010', 25000, 0),
      ('1020', 5000, 0),
      ('1030', 15000, 0),
      ('1110', 350000, 0),
      ('1320', 450000, 0),
      ('1330', 180000, 0),
      ('1340', 120000, 0),
      ('1350', 250000, 0),
      ('1360', 95000, 0),
      ('2300', 0, 200000),
      ('3000', 0, 1290000)
    ) AS v(code, dr, cr)
    JOIN public.accounts a ON a.code = v.code
  LOOP
    _lines := _lines || jsonb_build_object('account_id', _r.id, 'debit', _r.dr, 'credit', _r.cr,
      'description', 'Opening balance');
  END LOOP;

  _je := public.post_journal_entry(date_trunc('year', CURRENT_DATE)::date, 'Opening balances', 'opening_balance', NULL, _lines);

  INSERT INTO public.audit_logs (action, entity_type, entity_id, description)
  VALUES ('post', 'journal_entry', _je, 'Posted opening balances for the chart of accounts');
END $$;

-- 7. Default minimum cash reserve so profit distribution has a real floor
UPDATE public.settings
SET value = jsonb_set(value, '{minimum_cash_reserve}', '50000'::jsonb), updated_at = now()
WHERE key = 'finance' AND COALESCE((value->>'minimum_cash_reserve')::numeric, 0) = 0;