CREATE TABLE IF NOT EXISTS money_accounts (
  id serial PRIMARY KEY,
  user_id text NOT NULL REFERENCES "user"(id) ON DELETE CASCADE,
  name text NOT NULL,
  opening_balance_cents integer NOT NULL DEFAULT 0,
  kind text NOT NULL DEFAULT 'spendable',
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE (user_id, name)
);
CREATE INDEX IF NOT EXISTS money_accounts_user_id_idx ON money_accounts (user_id);
ALTER TABLE money_accounts ADD COLUMN IF NOT EXISTS kind text NOT NULL DEFAULT 'spendable';

CREATE TABLE IF NOT EXISTS money_transactions (
  id serial PRIMARY KEY,
  user_id text NOT NULL REFERENCES "user"(id) ON DELETE CASCADE,
  account_id integer NOT NULL REFERENCES money_accounts(id),
  date date NOT NULL,
  description text NOT NULL,
  amount_cents integer NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS money_transactions_user_date_idx ON money_transactions (user_id, date, id);
CREATE INDEX IF NOT EXISTS money_transactions_account_id_idx ON money_transactions (account_id);

CREATE TABLE IF NOT EXISTS money_recurring (
  id serial PRIMARY KEY,
  user_id text NOT NULL REFERENCES "user"(id) ON DELETE CASCADE,
  name text NOT NULL,
  amount_cents integer NOT NULL,
  cadence text NOT NULL,
  starts_on date,
  ends_on date,
  account_id integer REFERENCES money_accounts(id) ON DELETE SET NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS money_recurring_user_id_idx ON money_recurring (user_id);
ALTER TABLE money_recurring ADD COLUMN IF NOT EXISTS starts_on date;
ALTER TABLE money_recurring ADD COLUMN IF NOT EXISTS ends_on date;

CREATE TABLE IF NOT EXISTS money_forecasts (
  id serial PRIMARY KEY,
  user_id text NOT NULL REFERENCES "user"(id) ON DELETE CASCADE,
  date date NOT NULL,
  description text NOT NULL,
  amount_cents integer NOT NULL,
  account_id integer REFERENCES money_accounts(id) ON DELETE SET NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS money_forecasts_user_date_idx ON money_forecasts (user_id, date);

CREATE TABLE IF NOT EXISTS money_utility_settings (
  id serial PRIMARY KEY,
  user_id text NOT NULL REFERENCES "user"(id) ON DELETE CASCADE,
  kind text NOT NULL,
  rate_per_unit numeric(12, 4) NOT NULL DEFAULT 0,
  monthly_advance_cents integer NOT NULL DEFAULT 0,
  billing_start date,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE (user_id, kind)
);
ALTER TABLE money_utility_settings ADD COLUMN IF NOT EXISTS billing_start date;

CREATE TABLE IF NOT EXISTS money_utility_readings (
  id serial PRIMARY KEY,
  user_id text NOT NULL REFERENCES "user"(id) ON DELETE CASCADE,
  kind text NOT NULL,
  date date NOT NULL,
  reading numeric(12, 3) NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS money_utility_readings_user_kind_date_idx ON money_utility_readings (user_id, kind, date);

CREATE TABLE IF NOT EXISTS money_wishlist (
  id serial PRIMARY KEY,
  user_id text NOT NULL REFERENCES "user"(id) ON DELETE CASCADE,
  name text NOT NULL,
  estimated_cents integer,
  priority integer NOT NULL DEFAULT 3,
  notes text,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS money_wishlist_user_id_idx ON money_wishlist (user_id);
ALTER TABLE money_wishlist ADD COLUMN IF NOT EXISTS priority integer NOT NULL DEFAULT 3;

CREATE TABLE IF NOT EXISTS money_projection_settings (
  id serial PRIMARY KEY,
  user_id text NOT NULL UNIQUE REFERENCES "user"(id) ON DELETE CASCADE,
  investment_account_id integer REFERENCES money_accounts(id) ON DELETE SET NULL,
  reserve_cents integer NOT NULL DEFAULT 100000,
  annual_return_percent numeric(5, 2) NOT NULL DEFAULT 5.00,
  contribution_mode text NOT NULL DEFAULT 'excess',
  include_recurring integer NOT NULL DEFAULT 1,
  fixed_monthly_cents integer NOT NULL DEFAULT 0,
  end_date date,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);
ALTER TABLE money_projection_settings ADD COLUMN IF NOT EXISTS include_recurring integer NOT NULL DEFAULT 1;

CREATE TABLE IF NOT EXISTS money_insurances (
  id serial PRIMARY KEY,
  user_id text NOT NULL REFERENCES "user"(id) ON DELETE CASCADE,
  name text NOT NULL,
  provider text,
  coverage text,
  premium_cents integer,
  cadence text NOT NULL DEFAULT 'monthly',
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS money_insurances_user_id_idx ON money_insurances (user_id);

ALTER TABLE money_accounts ADD COLUMN IF NOT EXISTS color text;