-- Balance-sheet classification for the chart of accounts.
-- Adds a `subtype` column that splits assets into current vs non-current (fixed)
-- and liabilities into current vs long-term, classifies the accounts every farm
-- already has, and seeds the new fixed-asset accounts (Land, Poultry Houses,
-- Equipment, Motor Vehicles, Accumulated Depreciation) plus a long-term
-- liability (Loans Payable) for each existing farm.
-- Safe / idempotent: re-running it changes nothing.

-- ---- add the subtype column (guarded) ---------------------------------------
SET @has := (SELECT COUNT(*) FROM information_schema.columns
             WHERE table_schema = DATABASE() AND table_name = 'chart_of_accounts' AND column_name = 'subtype');
SET @sql := IF(@has = 0, 'ALTER TABLE chart_of_accounts ADD COLUMN subtype VARCHAR(24) NULL AFTER type', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- ---- classify the accounts that already exist -------------------------------
-- every asset a farm has today is a current asset; every liability is current.
UPDATE chart_of_accounts SET subtype = 'current_asset'
  WHERE type = 'asset' AND (subtype IS NULL OR subtype = '');
UPDATE chart_of_accounts SET subtype = 'current_liability'
  WHERE type = 'liability' AND (subtype IS NULL OR subtype = '');

-- ---- seed fixed-asset + long-term liability accounts for every farm ----------
-- one row per (farm, code) that the farm doesn't already have. Codes are unique
-- per farm (uq_coa_code), and the NOT EXISTS guard makes re-runs a no-op.
INSERT INTO chart_of_accounts (farm_id, code, name, type, subtype, role, is_system, sort_order)
SELECT f.id, x.code, x.name, x.type, x.subtype, x.role, 1, x.sort_order
FROM farms f
CROSS JOIN (
              SELECT '1500' AS code, 'Land'                        AS name, 'asset'     AS type, 'fixed_asset'         AS subtype, 'fixed_land'         AS role, 15 AS sort_order
    UNION ALL SELECT '1510',         'Poultry Houses & Buildings',        'asset',                'fixed_asset',                    'fixed_buildings',           16
    UNION ALL SELECT '1520',         'Equipment & Machinery',             'asset',                'fixed_asset',                    'fixed_equipment',           17
    UNION ALL SELECT '1530',         'Motor Vehicles',                    'asset',                'fixed_asset',                    'fixed_vehicles',            18
    UNION ALL SELECT '1590',         'Accumulated Depreciation',          'asset',                'fixed_asset',                    'accum_depreciation',        19
    UNION ALL SELECT '2500',         'Loans Payable',                     'liability',            'long_term_liability',            'loans_payable',             25
) AS x
WHERE NOT EXISTS (
    SELECT 1 FROM chart_of_accounts c WHERE c.farm_id = f.id AND c.code = x.code
);