-- Add Stripe customer ID to users
ALTER TABLE "user" ADD COLUMN IF NOT EXISTS "stripe_customer_id" text;

-- Monthly billing records
CREATE TABLE IF NOT EXISTS "billing_records" (
  "id"                       text PRIMARY KEY,
  "user_id"                  text NOT NULL REFERENCES "user"("id") ON DELETE CASCADE,
  "period_start"             timestamp NOT NULL,
  "period_end"               timestamp NOT NULL,
  "total_tokens"             bigint NOT NULL DEFAULT 0,
  "amount_cents"             integer NOT NULL DEFAULT 0,
  "currency"                 text NOT NULL DEFAULT 'eur',
  "status"                   text NOT NULL DEFAULT 'pending',
  "stripe_payment_intent_id" text,
  "stripe_invoice_id"        text,
  "error_message"            text,
  "created_at"               timestamp DEFAULT now()
);

CREATE INDEX IF NOT EXISTS "billing_records_user_id_idx" ON "billing_records" ("user_id");
CREATE INDEX IF NOT EXISTS "billing_records_period_start_idx" ON "billing_records" ("period_start");
