CREATE TABLE "es_org_subscription" (
	"id" text PRIMARY KEY NOT NULL,
	"organization_id" text NOT NULL,
	"plan" text DEFAULT 'free' NOT NULL,
	"interval" text,
	"stripe_customer_id" text,
	"stripe_subscription_id" text,
	"stripe_price_id" text,
	"status" text DEFAULT 'active' NOT NULL,
	"current_period_start" timestamp with time zone,
	"current_period_end" timestamp with time zone,
	"cancel_at_period_end" boolean DEFAULT false NOT NULL,
	"canceled_at" timestamp with time zone,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	"updated_at" timestamp with time zone DEFAULT now() NOT NULL,
	CONSTRAINT "es_org_subscription_organization_id_unique" UNIQUE("organization_id"),
	CONSTRAINT "es_org_subscription_stripe_customer_id_unique" UNIQUE("stripe_customer_id"),
	CONSTRAINT "es_org_subscription_stripe_subscription_id_unique" UNIQUE("stripe_subscription_id")
);
--> statement-breakpoint
ALTER TABLE "es_org_subscription" ADD CONSTRAINT "es_org_subscription_organization_id_organization_id_fk" FOREIGN KEY ("organization_id") REFERENCES "public"."organization"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
CREATE INDEX "idx_sub_stripe_customer" ON "es_org_subscription" USING btree ("stripe_customer_id");--> statement-breakpoint
CREATE INDEX "idx_sub_stripe_sub" ON "es_org_subscription" USING btree ("stripe_subscription_id");

-- Backfill: insert one es_org_subscription row per organization that doesn't
-- already have one. Default plan=free, stripe_customer_id=null (lazy-created
-- on first /settings/billing access — Phase 6). Stable derived id (`sub_<orgId>`)
-- makes re-runs idempotent.
INSERT INTO "es_org_subscription" ("id", "organization_id", "plan", "status")
SELECT
  'sub_' || o."id",
  o."id",
  'free',
  'active'
FROM "organization" o
WHERE NOT EXISTS (
  SELECT 1 FROM "es_org_subscription" s WHERE s."organization_id" = o."id"
);