CREATE TABLE "money"."bank_sessions" (
	"id" serial PRIMARY KEY,
	"user_id" text NOT NULL,
	"provider" text DEFAULT 'enablebanking' NOT NULL,
	"session_id" text NOT NULL CONSTRAINT "bank_sessions_session_id_key" UNIQUE,
	"aspsp_name" text NOT NULL,
	"aspsp_country" text NOT NULL,
	"psu_type" text,
	"status" text DEFAULT 'authorized' NOT NULL,
	"valid_until" timestamp with time zone,
	"accounts" jsonb DEFAULT '[]' NOT NULL,
	"last_synced_at" timestamp with time zone,
	"last_error" text,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	"updated_at" timestamp with time zone DEFAULT now() NOT NULL,
	CONSTRAINT "bank_sessions_status_check" CHECK (status IN ('authorized','expired','revoked','error'))
);
--> statement-breakpoint
ALTER TABLE "money"."accounts" ADD COLUMN "scope" text DEFAULT 'personal' NOT NULL;--> statement-breakpoint
ALTER TABLE "money"."accounts" ADD COLUMN "owner" text;--> statement-breakpoint
ALTER TABLE "money"."accounts" ADD COLUMN "iban" text;--> statement-breakpoint
ALTER TABLE "money"."accounts" ADD COLUMN "external_uid" text;--> statement-breakpoint
ALTER TABLE "money"."accounts" ADD COLUMN "provider" text DEFAULT 'manual' NOT NULL;--> statement-breakpoint
ALTER TABLE "money"."transactions" ADD COLUMN "external_id" text;--> statement-breakpoint
ALTER TABLE "money"."transactions" ADD COLUMN "source" text DEFAULT 'manual' NOT NULL;--> statement-breakpoint
ALTER TABLE "money"."transactions" ADD COLUMN "booking_status" text;--> statement-breakpoint
ALTER TABLE "money"."transactions" ADD COLUMN "counterparty" text;--> statement-breakpoint
ALTER TABLE "money"."transactions" ADD COLUMN "category" text;--> statement-breakpoint
ALTER TABLE "money"."transactions" ADD COLUMN "raw" jsonb;--> statement-breakpoint
ALTER TABLE "money"."transactions" ADD COLUMN "value_date" date;--> statement-breakpoint
ALTER TABLE "money"."accounts" ADD CONSTRAINT "accounts_external_uid_key" UNIQUE("external_uid");--> statement-breakpoint
ALTER TABLE "money"."transactions" ADD CONSTRAINT "transactions_account_external_id_key" UNIQUE("account_id","external_id");--> statement-breakpoint
CREATE INDEX "bank_sessions_user_id_index" ON "money"."bank_sessions" ("user_id");--> statement-breakpoint
ALTER TABLE "money"."bank_sessions" ADD CONSTRAINT "bank_sessions_user_id_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "auth"."user"("id") ON DELETE CASCADE;--> statement-breakpoint
ALTER TABLE "money"."accounts" ADD CONSTRAINT "accounts_scope_check" CHECK (scope IN ('personal','joint'));--> statement-breakpoint
ALTER TABLE "money"."accounts" ADD CONSTRAINT "accounts_provider_check" CHECK (provider IN ('manual','enablebanking'));--> statement-breakpoint
ALTER TABLE "money"."transactions" ADD CONSTRAINT "transactions_source_check" CHECK (source IN ('manual','csv','enablebanking'));--> statement-breakpoint
-- Hand-written below this line (not generated by drizzle-kit).
-- Backfill: the shared account with Juhee is the only joint one; everything else stays personal.
UPDATE "money"."accounts" SET "scope" = 'joint', "owner" = 'Stan & Juhee' WHERE lower("name") = 'joint';
--> statement-breakpoint
UPDATE "money"."accounts" SET "owner" = 'Stan' WHERE "scope" = 'personal' AND "owner" IS NULL;
--> statement-breakpoint
-- updated_at trigger, same as every other core table (07_triggers_meta_grants.sql).
DO $$
BEGIN
  IF to_regprocedure('meta.touch_updated_at()') IS NOT NULL THEN
    CREATE TRIGGER touch_updated_at BEFORE UPDATE ON money.bank_sessions
      FOR EACH ROW EXECUTE FUNCTION meta.touch_updated_at();
  END IF;
END $$;
--> statement-breakpoint
-- Grants only run on prod (migrations run as the schema owner `personal`); the dev DB
-- (owned by personal_dev) carries no prod grants, matching db-refresh-dev.sh. Default
-- privileges on `money` already cover new tables; this is explicit for clarity.
DO $$
BEGIN
  IF current_user = 'personal' AND EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'personal_app') THEN
    GRANT SELECT, INSERT, UPDATE, DELETE ON money.bank_sessions TO personal_app;
    GRANT USAGE, SELECT ON SEQUENCE money.bank_sessions_id_seq TO personal_app;
  END IF;
  IF current_user = 'personal' AND EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'personal_ro') THEN
    GRANT SELECT ON money.bank_sessions TO personal_ro;
  END IF;
END $$;