-- Normalise statements.statement_type to a fixed vocabulary.
--
-- Before this migration the column held whatever free text Gemini put in
-- `account_type` ('Credit Card', 'credit card', 'credit_card', 'Business Card',
-- 'ACCESS ADVANTAGE', 'multi-currency account', ...). The UI coped only by
-- doing `.includes("card")`, which breaks as soon as bank and loan statements
-- arrive.
--
-- The raw extracted text is preserved in `account_type` — this only touches
-- `statement_type`. A BEFORE trigger normalises on write, so the N8N ingestion
-- workflow keeps working unchanged while it still sends free text.
--
-- Idempotent: safe to re-run.

CREATE OR REPLACE FUNCTION normalize_statement_type(raw TEXT)
RETURNS TEXT AS $$
DECLARE
  v TEXT := lower(trim(coalesce(raw, '')));
BEGIN
  IF v = '' THEN
    RETURN 'other';
  END IF;

  -- Already canonical
  IF v IN ('credit_card', 'transaction', 'savings', 'loan', 'offset', 'investment', 'other') THEN
    RETURN v;
  END IF;

  -- Offset before loan: "Mortgage Offset" is an offset account, not a loan.
  IF v LIKE '%offset%' THEN
    RETURN 'offset';
  END IF;

  -- Loans before transaction: "home loan account" must not match '%account%'.
  IF v LIKE '%loan%' OR v LIKE '%mortgage%' THEN
    RETURN 'loan';
  END IF;

  -- Cards: covers 'Credit Card', 'credit card', 'Business Card', 'Charge Card'
  IF v LIKE '%card%' THEN
    RETURN 'credit_card';
  END IF;

  IF v LIKE '%saving%' OR v LIKE '%term deposit%' THEN
    RETURN 'savings';
  END IF;

  IF v LIKE '%invest%' OR v LIKE '%share%' OR v LIKE '%broker%' THEN
    RETURN 'investment';
  END IF;

  -- Everyday transaction accounts. Bank-specific product names go here; the
  -- generic keywords catch the rest.
  IF v LIKE '%access advantage%'      -- ANZ
     OR v LIKE '%complete access%'    -- Westpac
     OR v LIKE '%smart access%'       -- CommBank
     OR v LIKE '%netbank%'            -- CommBank
     OR v LIKE '%classic banking%'    -- NAB
     OR v LIKE '%multi-currency%'     -- Wise
     OR v LIKE '%multi currency%'
     OR v LIKE '%transaction%'
     OR v LIKE '%everyday%'
     OR v LIKE '%cheque%'
     OR v LIKE '%current account%'
     OR v LIKE '%debit%'
  THEN
    RETURN 'transaction';
  END IF;

  RETURN 'other';
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- Normalise on write so the N8N workflow can keep sending raw account_type.
CREATE OR REPLACE FUNCTION statements_normalize_type_trigger()
RETURNS TRIGGER AS $$
BEGIN
  NEW.statement_type := normalize_statement_type(NEW.statement_type);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trg_statements_normalize_type ON statements;
CREATE TRIGGER trg_statements_normalize_type
  BEFORE INSERT OR UPDATE OF statement_type ON statements
  FOR EACH ROW EXECUTE FUNCTION statements_normalize_type_trigger();

-- Backfill existing rows.
UPDATE statements
SET statement_type = normalize_statement_type(statement_type)
WHERE statement_type IS DISTINCT FROM normalize_statement_type(statement_type);

-- Guard the vocabulary. Safe because the trigger runs first on every write.
ALTER TABLE statements DROP CONSTRAINT IF EXISTS statements_statement_type_check;
ALTER TABLE statements
  ADD CONSTRAINT statements_statement_type_check
  CHECK (statement_type IN ('credit_card', 'transaction', 'savings', 'loan', 'offset', 'investment', 'other'));

ALTER TABLE statements ALTER COLUMN statement_type SET DEFAULT 'other';
