-- W9 W1 — module companion tables only (ledger_entries, ledger_entry_vesting, wallet_vesting, affiliate_entries).
-- Donor tables (wallet_balances, credit_ledger, affiliate_payouts, affiliate_enrollments) stay host-owned.

CREATE TYPE "public"."affiliate_entry_type" AS ENUM(
  'affiliate_commission',
  'referral_reward',
  'refund_clawback',
  'adjustment',
  'redemption'
);
--> statement-breakpoint
CREATE TABLE "ledger_entries" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"delta" bigint NOT NULL,
	"reason" text NOT NULL,
	"ref" jsonb,
	"idempotency_key" text NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "ledger_entry_vesting" (
	"entry_id" uuid PRIMARY KEY NOT NULL,
	"mature_at" timestamp with time zone,
	"swept_at" timestamp with time zone,
	"withdrawable_at" timestamp with time zone
);
--> statement-breakpoint
CREATE TABLE "wallet_vesting" (
	"owner_id" text PRIMARY KEY NOT NULL,
	"pending_minor" bigint DEFAULT 0 NOT NULL,
	"matured_minor" bigint DEFAULT 0 NOT NULL,
	"withdrawable_minor" bigint DEFAULT 0 NOT NULL,
	"lifetime_earned_minor" bigint DEFAULT 0 NOT NULL,
	"updated_at" timestamp with time zone DEFAULT now() NOT NULL,
	CONSTRAINT "wallet_vesting_pending_minor_nonneg" CHECK ("wallet_vesting"."pending_minor" >= 0),
	CONSTRAINT "wallet_vesting_withdrawable_minor_nonneg" CHECK ("wallet_vesting"."withdrawable_minor" >= 0)
);
--> statement-breakpoint
CREATE TABLE "affiliate_entries" (
	"entry_id" uuid PRIMARY KEY NOT NULL,
	"owner_id" text NOT NULL,
	"entry_type" "affiliate_entry_type" NOT NULL,
	"source_type" text NOT NULL,
	"source_id" text NOT NULL,
	"referral_id" uuid,
	"resolved_pct" smallint,
	"memo" text,
	"consumed_at" timestamp with time zone
);
--> statement-breakpoint
ALTER TABLE "affiliate_entries" ADD CONSTRAINT "affiliate_entries_referral_id_referrals_id_fk" FOREIGN KEY ("referral_id") REFERENCES "public"."referrals"("id") ON DELETE no action ON UPDATE no action;
--> statement-breakpoint
CREATE UNIQUE INDEX "affiliate_entries_idem_uq" ON "affiliate_entries" USING btree ("entry_type","source_type","source_id");
--> statement-breakpoint
CREATE INDEX "affiliate_entries_source_idx" ON "affiliate_entries" USING btree ("source_type","source_id");
--> statement-breakpoint
CREATE INDEX "affiliate_entries_unconsumed_idx" ON "affiliate_entries" USING btree ("source_type","source_id") WHERE "affiliate_entries"."consumed_at" IS NULL;
--> statement-breakpoint
CREATE UNIQUE INDEX "ledger_entries_idempotency_key_uq" ON "ledger_entries" USING btree ("idempotency_key");
--> statement-breakpoint
CREATE INDEX "ledger_entry_vesting_mature_idx" ON "ledger_entry_vesting" USING btree ("mature_at") WHERE "ledger_entry_vesting"."swept_at" IS NULL;
