-- 0016: account subtypes — presentation-and-grouping detail types so credit
-- cards group as credit cards, banks as banks, COGS separate from overhead.
-- Spec: docs/superpowers/specs/2026-08-02-chart-management-design.md Part 1.
-- Forward-only; pure DDL plus safe backfill UPDATEs (subtype only — no other
-- column is ever touched, no ledger data involved).
--
-- Design in one paragraph: the five types (asset/liability/equity/income/
-- expense) stay the ledger truth — every report, trigger, and posting rule
-- keys off `type` exactly as before. `subtype` is a NULLable presentation
-- refinement (QB-style detail types, simplified) whose only schema-level
-- promise is the CHECK matrix below: a subtype, when present, must belong to
-- its row's type. NULL means "untagged" and is always allowed — the UI groups
-- untagged accounts under the bare type. Subtype is freely editable (like
-- name/code, unlike type which no code path ever changes); no trigger armor
-- beyond the CHECK is warranted because subtype participates in no stored
-- math, no posting, and no balance.

ALTER TABLE accounts ADD COLUMN subtype text;

-- The full type/subtype matrix, enforced by the table itself (CLAUDE.md §12:
-- database-level enforcement first). NULL always passes.
ALTER TABLE accounts ADD CONSTRAINT accounts_subtype_valid_for_type CHECK (
  subtype IS NULL
  OR (type = 'asset' AND subtype IN
        ('bank', 'accounts-receivable', 'other-current-asset',
         'fixed-asset', 'other-asset'))
  OR (type = 'liability' AND subtype IN
        ('credit-card', 'line-of-credit', 'accounts-payable',
         'other-current-liability', 'long-term-liability'))
  OR (type = 'equity' AND subtype = 'equity')
  OR (type = 'income' AND subtype IN ('income', 'other-income'))
  OR (type = 'expense' AND subtype IN
        ('expense', 'cost-of-goods-sold', 'other-expense'))
);

-- Grants: 0002 granted app table-wide SELECT/INSERT/UPDATE/DELETE on
-- accounts, and table-wide grants automatically cover columns added later —
-- so `app` can already read and write subtype. Made explicit anyway (a
-- column-level grant is additive and harmless) so this migration documents
-- its own access story the way 0015 does:
GRANT UPDATE (subtype) ON accounts TO app;

-- ---------------------------------------------------------------------------
-- Backfill (a): template-row match. An existing account whose (code, name,
-- type) exactly matches a chart-template row gets that row's subtype. The
-- list below is the template data (server/src/templates/{core,small-business,
-- industries}.ts) hardcoded as of this migration — static, honest, and
-- reviewable; a test (tests/server/accounts-api.test.ts) asserts this list
-- matches the live template modules so they cannot drift silently. The spine
-- (identical across all eight templates) comes first, then the 'general'
-- template's two income accounts, then each industry template's additions.
-- Matching on type as well as (code, name) is belt-and-braces: a mismatched
-- type would violate the CHECK above and abort the whole migration.
-- ---------------------------------------------------------------------------

UPDATE accounts a
   SET subtype = v.subtype
  FROM (VALUES
    -- shared spine (core.ts) — identical in all eight templates
    ('1000', 'Cash on Hand',            'asset',     'bank'),
    ('1010', 'Checking Account',        'asset',     'bank'),
    ('1020', 'Savings Account',         'asset',     'bank'),
    ('1100', 'Accounts Receivable',     'asset',     'accounts-receivable'),
    ('1200', 'Prepaid Expenses',        'asset',     'other-current-asset'),
    ('1500', 'Furniture and Equipment', 'asset',     'fixed-asset'),
    ('1510', 'Vehicles',                'asset',     'fixed-asset'),
    ('1590', 'Accumulated Depreciation','asset',     'fixed-asset'),
    ('2000', 'Accounts Payable',        'liability', 'accounts-payable'),
    ('2100', 'Credit Card',             'liability', 'credit-card'),
    ('2200', 'Sales Tax Payable',       'liability', 'other-current-liability'),
    ('2300', 'Payroll Liabilities',     'liability', 'other-current-liability'),
    ('2400', 'Loan Payable',            'liability', 'long-term-liability'),
    ('3000', 'Owner''s Equity',         'equity',    'equity'),
    ('3010', 'Owner''s Distributions',  'equity',    'equity'),
    ('4020', 'Other Income',            'income',    'other-income'),
    ('6000', 'Advertising',             'expense',   'expense'),
    ('6010', 'Bank Fees',               'expense',   'expense'),
    ('6020', 'Insurance',               'expense',   'expense'),
    ('6030', 'Meals and Entertainment', 'expense',   'expense'),
    ('6040', 'Office Supplies',         'expense',   'expense'),
    ('6050', 'Payroll Wages',           'expense',   'expense'),
    ('6060', 'Payroll Taxes',           'expense',   'expense'),
    ('6070', 'Professional Fees',       'expense',   'expense'),
    ('6080', 'Rent',                    'expense',   'expense'),
    ('6090', 'Repairs and Maintenance', 'expense',   'expense'),
    ('6100', 'Software Subscriptions',  'expense',   'expense'),
    ('6110', 'Phone and Internet',      'expense',   'expense'),
    ('6120', 'Travel',                  'expense',   'expense'),
    ('6130', 'Utilities',               'expense',   'expense'),
    ('6140', 'Vehicle Expenses',        'expense',   'expense'),
    ('6150', 'Contract Labor',          'expense',   'expense'),
    ('6160', 'Dues and Subscriptions',  'expense',   'expense'),
    ('6170', 'Postage and Shipping',    'expense',   'expense'),
    ('6180', 'Taxes and Licenses',      'expense',   'expense'),
    ('6190', 'Continuing Education',    'expense',   'expense'),
    ('6200', 'Depreciation Expense',    'expense',   'expense'),
    ('6210', 'Miscellaneous Expense',   'expense',   'expense'),
    -- general (small-business.ts)
    ('4000', 'Sales Income',            'income',    'income'),
    ('4010', 'Service Income',          'income',    'income'),
    -- services
    ('2500', 'Client Deposits Held',    'liability', 'other-current-liability'),
    ('4000', 'Project Income',          'income',    'income'),
    ('4010', 'Retainer Income',         'income',    'income'),
    ('4030', 'Reimbursed Client Expenses', 'income', 'income'),
    ('5000', 'Subcontracted Work',      'expense',   'cost-of-goods-sold'),
    ('5010', 'Project Supplies and Materials', 'expense', 'cost-of-goods-sold'),
    ('6300', 'Website and Hosting',     'expense',   'expense'),
    ('6310', 'Freelance Platform Fees', 'expense',   'expense'),
    -- retail
    ('1300', 'Inventory',               'asset',     'other-current-asset'),
    ('2500', 'Gift Cards Outstanding',  'liability', 'other-current-liability'),
    ('4000', 'Product Sales',           'income',    'income'),
    ('4030', 'Sales Returns and Refunds', 'income',  'income'),
    ('4040', 'Discounts Given',         'income',    'income'),
    ('4050', 'Shipping Income',         'income',    'income'),
    ('5000', 'Cost of Goods Sold',      'expense',   'cost-of-goods-sold'),
    ('5010', 'Shipping on Purchases',   'expense',   'cost-of-goods-sold'),
    ('6300', 'Card Processing Fees',    'expense',   'expense'),
    ('6310', 'Shipping to Customers',   'expense',   'expense'),
    ('6320', 'Store Supplies and Packaging', 'expense', 'expense'),
    ('6330', 'Online Marketplace Fees', 'expense',   'expense'),
    -- restaurant
    ('1300', 'Food and Beverage Inventory', 'asset', 'other-current-asset'),
    ('2500', 'Tips Payable',            'liability', 'other-current-liability'),
    ('2510', 'Gift Cards Outstanding',  'liability', 'other-current-liability'),
    ('4000', 'Food Sales',              'income',    'income'),
    ('4010', 'Beverage Sales',          'income',    'income'),
    ('4030', 'Catering Income',         'income',    'income'),
    ('5000', 'Food Purchases',          'expense',   'cost-of-goods-sold'),
    ('5010', 'Beverage Purchases',      'expense',   'cost-of-goods-sold'),
    ('6300', 'Kitchen Supplies',        'expense',   'expense'),
    ('6310', 'Delivery Platform Fees',  'expense',   'expense'),
    ('6320', 'Card Processing Fees',    'expense',   'expense'),
    ('6330', 'Linens and Uniforms',     'expense',   'expense'),
    -- construction
    ('1300', 'Retainage Receivable',    'asset',     'other-current-asset'),
    ('2500', 'Customer Deposits Held',  'liability', 'other-current-liability'),
    ('4000', 'Contract Income',         'income',    'income'),
    ('4010', 'Change Order Income',     'income',    'income'),
    ('5000', 'Job Materials',           'expense',   'cost-of-goods-sold'),
    ('5010', 'Subcontractors',          'expense',   'cost-of-goods-sold'),
    ('5020', 'Equipment Rental',        'expense',   'cost-of-goods-sold'),
    ('5030', 'Permits and Inspection Fees', 'expense', 'cost-of-goods-sold'),
    ('6300', 'Small Tools and Equipment', 'expense', 'expense'),
    ('6310', 'Fuel',                    'expense',   'expense'),
    -- field-services
    ('4000', 'Service Income',          'income',    'income'),
    ('4010', 'Recurring Service Plans', 'income',    'income'),
    ('5000', 'Job Materials and Parts', 'expense',   'cost-of-goods-sold'),
    ('5010', 'Subcontracted Help',      'expense',   'cost-of-goods-sold'),
    ('6300', 'Fuel',                    'expense',   'expense'),
    ('6310', 'Vehicle Repairs and Tires', 'expense', 'expense'),
    ('6320', 'Route Supplies',          'expense',   'expense'),
    ('6330', 'Small Equipment and Tools', 'expense', 'expense'),
    ('6340', 'Uniforms',                'expense',   'expense'),
    ('6350', 'Equipment Rental',        'expense',   'expense'),
    -- property
    ('1300', 'Buildings and Improvements', 'asset',  'fixed-asset'),
    ('1310', 'Land',                    'asset',     'fixed-asset'),
    ('2500', 'Security Deposits Held',  'liability', 'other-current-liability'),
    ('4000', 'Rental Income',           'income',    'income'),
    ('4010', 'Late Fee Income',         'income',    'income'),
    ('4030', 'Application Fee Income',  'income',    'income'),
    ('6300', 'Property Management Fees', 'expense',  'expense'),
    ('6310', 'Property Taxes',          'expense',   'expense'),
    ('6320', 'Mortgage Interest',       'expense',   'expense'),
    ('6330', 'Landscaping and Snow Removal', 'expense', 'expense'),
    ('6340', 'Cleaning and Turnover',   'expense',   'expense'),
    ('6350', 'HOA Dues',                'expense',   'expense'),
    -- practice
    ('1300', 'Client Trust Account',    'asset',     'bank'),
    ('2500', 'Client Retainers Held',   'liability', 'other-current-liability'),
    ('2510', 'Client Trust Obligations','liability', 'other-current-liability'),
    ('4000', 'Client and Patient Fees', 'income',    'income'),
    ('4010', 'Insurance Reimbursements','income',    'income'),
    ('6300', 'Professional Liability Insurance', 'expense', 'expense'),
    ('6310', 'Professional Licenses',   'expense',   'expense'),
    ('6320', 'Reference Materials and Research', 'expense', 'expense'),
    ('6330', 'Billing and Collection Fees', 'expense', 'expense')
  ) AS v (code, name, type, subtype)
 WHERE a.code = v.code
   AND a.name = v.name
   AND a.type = v.type
   AND a.subtype IS NULL;

-- ---------------------------------------------------------------------------
-- Backfill (b): company_settings-designated accounts. These pointers are the
-- system's own knowledge of what an account IS, regardless of its name or
-- code — an AR account designated in settings is the A/R account. Only fills
-- where subtype is still NULL (never overwrites backfill (a), which agrees
-- anyway wherever both apply). company_settings has exactly three account
-- pointers as of this migration: ar_account_id (0005), ap_account_id (0009),
-- sales_tax_account_id (0015). There is no undeposited-funds or other system
-- account pointer in this schema.
-- ---------------------------------------------------------------------------

UPDATE accounts a SET subtype = 'accounts-receivable'
  FROM company_settings s
 WHERE s.company_id = a.company_id AND s.ar_account_id = a.id
   AND a.type = 'asset' AND a.subtype IS NULL;

UPDATE accounts a SET subtype = 'accounts-payable'
  FROM company_settings s
 WHERE s.company_id = a.company_id AND s.ap_account_id = a.id
   AND a.type = 'liability' AND a.subtype IS NULL;

UPDATE accounts a SET subtype = 'other-current-liability'
  FROM company_settings s
 WHERE s.company_id = a.company_id AND s.sales_tax_account_id = a.id
   AND a.type = 'liability' AND a.subtype IS NULL;

-- Everything else stays NULL (untagged) — one click to tag in the new Chart
-- of Accounts screen, or the tools/backfill-subtypes-from-qb.ts script for
-- QB-migrated charts whose Account Listing xlsx carries QB's Type column.

-- ---------------------------------------------------------------------------
-- enable_sales_tax: 0015's text verbatim, except the account it CREATES (when
-- no 'Sales Tax Payable' exists to adopt) is now born tagged
-- other-current-liability — the one system function that creates an account.
-- Adopted accounts are handled by backfill (b) above / stay as tagged.
-- ---------------------------------------------------------------------------

CREATE OR REPLACE FUNCTION enable_sales_tax(p_company_id uuid, p_user_id uuid) RETURNS bigint
LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
DECLARE
  v_account_id bigint;
  v_count int;
  v_code text;
  v_n int;
BEGIN
  IF current_company_id() IS DISTINCT FROM p_company_id THEN
    RAISE EXCEPTION 'session company does not match';
  END IF;

  INSERT INTO company_settings (company_id) VALUES (p_company_id)
    ON CONFLICT (company_id) DO NOTHING;
  SELECT sales_tax_account_id INTO v_account_id FROM company_settings
   WHERE company_id = p_company_id FOR UPDATE;

  IF v_account_id IS NULL THEN
    SELECT count(*), min(id) INTO v_count, v_account_id FROM accounts
     WHERE company_id = p_company_id AND type = 'liability' AND active
       AND name = 'Sales Tax Payable';
    IF v_count > 1 THEN
      RAISE EXCEPTION
        'More than one active account is named ''Sales Tax Payable'' — set the one to use in Settings';
    ELSIF v_count = 0 THEN
      v_n := 2200;
      v_code := v_n::text;
      WHILE EXISTS (
        SELECT 1 FROM accounts WHERE company_id = p_company_id AND code = v_code
      ) LOOP
        v_n := v_n + 1;
        IF v_n > 2299 THEN
          RAISE EXCEPTION
            'no free account code between 2200 and 2299 — create a ''Sales Tax Payable'' liability account yourself';
        END IF;
        v_code := v_n::text;
      END LOOP;
      INSERT INTO accounts (company_id, code, name, type, subtype)
      VALUES (p_company_id, v_code, 'Sales Tax Payable', 'liability',
              'other-current-liability')
      RETURNING id INTO v_account_id;
    END IF;
    UPDATE company_settings SET sales_tax_account_id = v_account_id
     WHERE company_id = p_company_id;
  END IF;

  UPDATE company_settings SET sales_tax_enabled = true
   WHERE company_id = p_company_id;

  INSERT INTO audit_log (company_id, user_id, action, entity, entity_id, detail)
  VALUES (p_company_id, p_user_id, 'enable_sales_tax', 'company_settings',
          p_company_id::text,
          jsonb_build_object('sales_tax_account_id', v_account_id));

  RETURN v_account_id;
END $$;
