CREATE TABLE "tenant_security_settings" (
	"tenant_id" uuid PRIMARY KEY NOT NULL,
	"idle_timeout_minutes" integer,
	"max_sessions_per_user" integer DEFAULT 10 NOT NULL,
	"require_2fa_for_roles" text[] DEFAULT '{}' NOT NULL,
	"block_suspicious_logins" boolean DEFAULT false NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	"updated_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "user_sessions" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"tenant_id" uuid NOT NULL,
	"user_id" uuid NOT NULL,
	"token_hash" text NOT NULL,
	"device_name" text,
	"ip_address" text,
	"country_code" text,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	"last_active_at" timestamp with time zone DEFAULT now() NOT NULL,
	"expires_at" timestamp with time zone NOT NULL,
	"revoked_at" timestamp with time zone,
	"revoked_reason" text,
	CONSTRAINT "user_sessions_token_hash_unique" UNIQUE("token_hash"),
	CONSTRAINT "user_sessions_revoked_reason_check" CHECK (revoked_reason IN ('user', 'admin', 'idle_timeout', 'suspicious')),
	CONSTRAINT "user_sessions_valid_expiry_check" CHECK (expires_at > created_at)
);
--> statement-breakpoint
ALTER TABLE "user_preferences" ADD COLUMN "notify_new_login" boolean DEFAULT true NOT NULL;--> statement-breakpoint
ALTER TABLE "tenant_security_settings" ADD CONSTRAINT "tenant_security_settings_tenant_id_tenants_id_fk" FOREIGN KEY ("tenant_id") REFERENCES "public"."tenants"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "user_sessions" ADD CONSTRAINT "user_sessions_tenant_id_tenants_id_fk" FOREIGN KEY ("tenant_id") REFERENCES "public"."tenants"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "user_sessions" ADD CONSTRAINT "user_sessions_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
CREATE INDEX "idx_sessions_user" ON "user_sessions" USING btree ("user_id","revoked_at");--> statement-breakpoint
CREATE INDEX "idx_sessions_tenant" ON "user_sessions" USING btree ("tenant_id","last_active_at");--> statement-breakpoint
CREATE INDEX "idx_sessions_expires_at" ON "user_sessions" USING btree ("expires_at");--> statement-breakpoint
CREATE INDEX "idx_sessions_user_id" ON "user_sessions" USING btree ("user_id","created_at");--> statement-breakpoint
CREATE INDEX "idx_sessions_tenant_id" ON "user_sessions" USING btree ("tenant_id");--> statement-breakpoint
CREATE INDEX "idx_payout_bills_rpt" ON "payout_bills" USING btree ("tenant_id","status","paid_at");--> statement-breakpoint
CREATE INDEX "idx_invoices_report" ON "invoices" USING btree ("tenant_id","status","tax_issue_date");--> statement-breakpoint
CREATE INDEX "idx_invoice_payments_rpt" ON "invoice_payments" USING btree ("tenant_id","paid_at");

-- session-security raw DDL: INET cast, unique token index, partial indexes
-- Convert ip_address column to INET for proper network-type storage
ALTER TABLE "user_sessions" ALTER COLUMN "ip_address" TYPE inet USING ip_address::inet;

-- Unique index on token_hash for single-request auth hot-path (idx_sessions_token)
CREATE UNIQUE INDEX IF NOT EXISTS "idx_sessions_token" ON "user_sessions" ("token_hash");

-- Partial index: only live sessions (revoked_at IS NULL) for user session list hot path
CREATE INDEX IF NOT EXISTS "idx_sessions_user_live" ON "user_sessions" ("user_id", "revoked_at") WHERE revoked_at IS NULL;

-- Index: expires_at for cleanup sweep (volatile functions disallowed in index predicates)
CREATE INDEX IF NOT EXISTS "idx_sessions_expires_sweep" ON "user_sessions" ("expires_at");