-- Canonical category vocabulary, enforced at the database.
--
-- Categories arrive from three places: the Gemini extraction (which writes
-- straight to Postgres from N8N, bypassing the app entirely), CSV import, and
-- manual edits. Only the DB sits under all three, so that is where the rule has
-- to live -- the same reasoning as normalize_statement_type() in 0013.
--
-- What had leaked in without it:
--   payment    19 rows  $42,569.42   transaction_type written into the category
--   refund     15 rows   $8,558.99   ditto
--   Shopping   13 rows   $2,917.44   title-case duplicates of real categories,
--   Dining     10 rows     $127.30   which every GROUP BY counted separately
--   (NULL)      3 rows   $1,135.05

CREATE OR REPLACE FUNCTION normalize_category(raw TEXT)
RETURNS TEXT AS $$
DECLARE
  v TEXT;
BEGIN
  IF raw IS NULL OR btrim(raw) = '' THEN RETURN 'other'; END IF;

  -- Title-case and spaced variants collapse onto the canonical spelling:
  -- 'Home Goods' -> 'home_goods', 'Shopping' -> 'shopping'.
  v := replace(replace(lower(btrim(raw)), ' ', '_'), '-', '_');

  IF v IN ('groceries','dining','transport','fuel','shopping','utilities',
           'entertainment','travel','health','insurance','subscriptions',
           'cash_advance','government','education','rent','home_goods',
           'home_maintenance','transfers','income','investment','loan_interest',
           'personal_care','pets','gifts','charity','fees','other')
  THEN RETURN v; END IF;

  -- transaction_type leaking into the category field. A payment is money moving
  -- to a card or account, which is exactly what 'transfers' means here.
  IF v IN ('payment','payments','transfer') THEN RETURN 'transfers'; END IF;

  -- Bare 'interest' is credit-card interest; loan interest is categorised
  -- loan_interest by the extraction prompt and matches the list above.
  IF v IN ('fee','bank_fees','interest') THEN RETURN 'fees'; END IF;

  -- Singular/plural and common synonyms.
  IF v IN ('grocery','supermarket')        THEN RETURN 'groceries'; END IF;
  IF v IN ('subscription')                 THEN RETURN 'subscriptions'; END IF;
  IF v IN ('utility','bills')              THEN RETURN 'utilities'; END IF;
  IF v IN ('gift')                         THEN RETURN 'gifts'; END IF;
  IF v IN ('pet')                          THEN RETURN 'pets'; END IF;
  IF v IN ('restaurant','restaurants','food','takeaway') THEN RETURN 'dining'; END IF;
  IF v IN ('petrol','gas','gasoline')      THEN RETURN 'fuel'; END IF;
  IF v IN ('medical','pharmacy')           THEN RETURN 'health'; END IF;
  IF v IN ('donation','donations')         THEN RETURN 'charity'; END IF;
  IF v IN ('salary','wages')               THEN RETURN 'income'; END IF;
  IF v IN ('investments','savings')        THEN RETURN 'investment'; END IF;
  IF v IN ('housing','mortgage')           THEN RETURN 'rent'; END IF;

  -- A 'refund' is not a category -- it says nothing about what was bought. The
  -- backfill below recovers the real category from the merchant where it can;
  -- anything still unknown lands in 'other' rather than inventing a category.
  RETURN 'other';
END;
$$ LANGUAGE plpgsql IMMUTABLE;


-- Recover categories for rows typed as refunds before collapsing them to
-- 'other': use the most common category that merchant has on ordinary spend.
UPDATE transactions t
SET category = m.mode_category
FROM (
  SELECT merchant_normalized,
         MODE() WITHIN GROUP (ORDER BY category) AS mode_category
  FROM transactions
  WHERE merchant_normalized IS NOT NULL
    AND category IS NOT NULL
    AND category NOT IN ('payment','refund','fee','interest')
    AND transaction_type IN ('debit','fee','interest')
  GROUP BY merchant_normalized
) m
WHERE t.merchant_normalized = m.merchant_normalized
  AND t.category = 'refund'
  AND m.mode_category IS NOT NULL;


-- Backfill everything else through the function.
UPDATE transactions
SET category = normalize_category(category)
WHERE category IS NULL OR category <> normalize_category(category);

UPDATE transaction_overrides
SET category_override = normalize_category(category_override)
WHERE category_override IS NOT NULL
  AND category_override <> normalize_category(category_override);


CREATE OR REPLACE FUNCTION transactions_normalize_category()
RETURNS TRIGGER AS $$
BEGIN
  NEW.category := normalize_category(NEW.category);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trg_transactions_normalize_category ON transactions;
CREATE TRIGGER trg_transactions_normalize_category
  BEFORE INSERT OR UPDATE OF category ON transactions
  FOR EACH ROW EXECUTE FUNCTION transactions_normalize_category();

-- Overrides keep NULL meaning "no override"; only a set value is normalised.
CREATE OR REPLACE FUNCTION overrides_normalize_category()
RETURNS TRIGGER AS $$
BEGIN
  IF NEW.category_override IS NOT NULL THEN
    NEW.category_override := normalize_category(NEW.category_override);
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trg_overrides_normalize_category ON transaction_overrides;
CREATE TRIGGER trg_overrides_normalize_category
  BEFORE INSERT OR UPDATE OF category_override ON transaction_overrides
  FOR EACH ROW EXECUTE FUNCTION overrides_normalize_category();


ALTER TABLE transactions DROP CONSTRAINT IF EXISTS transactions_category_check;
ALTER TABLE transactions ADD CONSTRAINT transactions_category_check
  CHECK (category IS NULL OR category IN (
    'groceries','dining','transport','fuel','shopping','utilities','entertainment',
    'travel','health','insurance','subscriptions','cash_advance','government',
    'education','rent','home_goods','home_maintenance','transfers','income',
    'investment','loan_interest','personal_care','pets','gifts','charity','fees','other'));

ALTER TABLE transaction_overrides DROP CONSTRAINT IF EXISTS overrides_category_check;
ALTER TABLE transaction_overrides ADD CONSTRAINT overrides_category_check
  CHECK (category_override IS NULL OR category_override IN (
    'groceries','dining','transport','fuel','shopping','utilities','entertainment',
    'travel','health','insurance','subscriptions','cash_advance','government',
    'education','rent','home_goods','home_maintenance','transfers','income',
    'investment','loan_interest','personal_care','pets','gifts','charity','fees','other'));
