-- 03_fixes.sql: missing PKs, type fixes, CHECKs, updated_at columns.
SET ROLE personal;

-- missing primary keys (promote the existing unique indexes)
ALTER TABLE cs.item_dimensions ADD CONSTRAINT item_dimensions_pkey PRIMARY KEY USING INDEX item_dimensions_unique_idx;
ALTER TABLE cs.price_streams   ADD CONSTRAINT price_streams_pkey   PRIMARY KEY USING INDEX price_streams_unique_idx;
ALTER TABLE house.price_history ADD CONSTRAINT price_history_pkey PRIMARY KEY (listing_id, observed_at);
DROP INDEX house.price_history_listing_idx;   -- now covered by the PK

-- include_recurring was an int used as a boolean (app code: charts.ts `!== 0`)
ALTER TABLE money.projection_settings ALTER COLUMN include_recurring DROP DEFAULT;
ALTER TABLE money.projection_settings ALTER COLUMN include_recurring TYPE boolean USING include_recurring <> 0;
ALTER TABLE money.projection_settings ALTER COLUMN include_recurring SET DEFAULT true;

-- osu numbers stored as text (0 rows in prod)
ALTER TABLE games.osu_entries ALTER COLUMN pp TYPE numeric USING nullif(pp, '')::numeric;
ALTER TABLE games.osu_entries ALTER COLUMN accuracy TYPE numeric USING nullif(replace(accuracy, '%', ''), '')::numeric;
ALTER TABLE games.osu_entries ALTER COLUMN rate DROP DEFAULT;
ALTER TABLE games.osu_entries ALTER COLUMN rate TYPE numeric(4,2) USING nullif(rate, '')::numeric;
ALTER TABLE games.osu_entries ALTER COLUMN rate SET DEFAULT 1.00;

-- CHECKs matching the values the app already enforces (src/lib/server/money-tracker.ts, money page)
ALTER TABLE money.recurring        ADD CONSTRAINT recurring_cadence_check  CHECK (cadence IN ('weekly','monthly','yearly'));
ALTER TABLE money.insurances       ADD CONSTRAINT insurances_cadence_check CHECK (cadence IN ('weekly','monthly','yearly'));
ALTER TABLE money.accounts         ADD CONSTRAINT accounts_kind_check      CHECK (kind IN ('spendable','investment'));
ALTER TABLE money.utility_settings ADD CONSTRAINT utility_settings_kind_check CHECK (kind IN ('electricity','gas','water'));
ALTER TABLE money.utility_readings ADD CONSTRAINT utility_readings_kind_check CHECK (kind IN ('electricity','gas','water'));
ALTER TABLE money.projection_settings ADD CONSTRAINT projection_settings_contribution_mode_check CHECK (contribution_mode IN ('excess','fixed'));

-- user-editable tables without updated_at
ALTER TABLE house.alert_rules ADD COLUMN updated_at timestamptz NOT NULL DEFAULT now();
RESET ROLE;