CREATE TABLE "ai_quotas" (
	"key" text NOT NULL,
	"feature" text NOT NULL,
	"date" text NOT NULL,
	"count" integer DEFAULT 0 NOT NULL
);
--> statement-breakpoint
CREATE TABLE "research_messages" (
	"id" text PRIMARY KEY NOT NULL,
	"session_id" text NOT NULL,
	"role" text NOT NULL,
	"content" text NOT NULL,
	"tool_calls" jsonb DEFAULT '[]'::jsonb NOT NULL,
	"usage" jsonb DEFAULT '{}'::jsonb NOT NULL,
	"usd_est" real DEFAULT 0 NOT NULL,
	"model" text,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "research_sessions" (
	"id" text PRIMARY KEY NOT NULL,
	"user_id" text,
	"anon_id" text,
	"title" text,
	"model" text,
	"message_count" integer DEFAULT 0 NOT NULL,
	"usd_est" real DEFAULT 0 NOT NULL,
	"archived" 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 "scanner_sessions" (
	"id" text PRIMARY KEY NOT NULL,
	"user_id" text,
	"anon_id" text,
	"ip_hash" text,
	"mode" text NOT NULL,
	"input_url" text,
	"input_text" text,
	"image_count" integer DEFAULT 0 NOT NULL,
	"thumbnails" jsonb DEFAULT '[]'::jsonb NOT NULL,
	"guess" jsonb DEFAULT '{}'::jsonb NOT NULL,
	"guess_confidence" real,
	"candidates" jsonb DEFAULT '[]'::jsonb NOT NULL,
	"chosen_asset_id" text,
	"listing" jsonb DEFAULT '{}'::jsonb NOT NULL,
	"model" text,
	"usd_est" real DEFAULT 0 NOT NULL,
	"duration_ms" integer,
	"error" text,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	"chosen_at" timestamp with time zone
);
--> statement-breakpoint
CREATE TABLE "auth_codes" (
	"id" text PRIMARY KEY NOT NULL,
	"user_id" text,
	"email" text NOT NULL,
	"purpose" text NOT NULL,
	"code_hash" text NOT NULL,
	"expires_at" timestamp with time zone NOT NULL,
	"attempts" integer DEFAULT 0 NOT NULL,
	"consumed_at" timestamp with time zone,
	"payload" jsonb DEFAULT '{}'::jsonb NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "login_events" (
	"id" text PRIMARY KEY NOT NULL,
	"user_id" text,
	"email" text,
	"ip" text,
	"user_agent" text,
	"outcome" text NOT NULL,
	"method" text,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "notifications" (
	"id" text PRIMARY KEY NOT NULL,
	"user_id" text NOT NULL,
	"kind" text NOT NULL,
	"title" text NOT NULL,
	"body" text,
	"href" text,
	"payload" jsonb DEFAULT '{}'::jsonb NOT NULL,
	"read_at" timestamp with time zone,
	"emailed_at" timestamp with time zone,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "price_targets" (
	"id" text PRIMARY KEY NOT NULL,
	"user_id" text NOT NULL,
	"asset_id" text NOT NULL,
	"variant_id" text,
	"direction" text DEFAULT 'above' NOT NULL,
	"target_usd" numeric(18, 4) NOT NULL,
	"baseline_usd" numeric(18, 4),
	"note" text,
	"hit_at" timestamp with time zone,
	"notified_at" timestamp with time zone,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "rate_limits" (
	"key" text PRIMARY KEY NOT NULL,
	"count" integer DEFAULT 0 NOT NULL,
	"reset_at" timestamp with time zone NOT NULL
);
--> statement-breakpoint
CREATE TABLE "recovery_codes" (
	"id" text PRIMARY KEY NOT NULL,
	"user_id" text NOT NULL,
	"code_hash" text NOT NULL,
	"used_at" timestamp with time zone,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "saved_searches" (
	"id" text PRIMARY KEY NOT NULL,
	"user_id" text NOT NULL,
	"name" text NOT NULL,
	"url" text NOT NULL,
	"params" jsonb DEFAULT '{}'::jsonb NOT NULL,
	"notify" boolean DEFAULT false NOT NULL,
	"last_run_at" timestamp with time zone,
	"last_count" integer,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "trusted_devices" (
	"id" text PRIMARY KEY NOT NULL,
	"user_id" text NOT NULL,
	"token_hash" text NOT NULL,
	"label" text,
	"user_agent" text,
	"ip" text,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	"last_used_at" timestamp with time zone,
	"expires_at" timestamp with time zone NOT NULL,
	"revoked_at" timestamp with time zone
);
--> statement-breakpoint
CREATE TABLE "uploads" (
	"id" text PRIMARY KEY NOT NULL,
	"user_id" text NOT NULL,
	"kind" text NOT NULL,
	"path" text NOT NULL,
	"mime" text NOT NULL,
	"bytes" integer NOT NULL,
	"width" integer,
	"height" integer,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "user_badges" (
	"user_id" text NOT NULL,
	"badge" text NOT NULL,
	"evidence" jsonb DEFAULT '{}'::jsonb NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
DROP INDEX "normalized_records_raw_uq";--> statement-breakpoint
ALTER TABLE "normalized_records" ADD COLUMN "seq" integer DEFAULT 0 NOT NULL;--> statement-breakpoint
ALTER TABLE "alerts" ADD COLUMN "name" text;--> statement-breakpoint
ALTER TABLE "alerts" ADD COLUMN "cooldown_minutes" integer DEFAULT 1440 NOT NULL;--> statement-breakpoint
ALTER TABLE "alerts" ADD COLUMN "trigger_count" integer DEFAULT 0 NOT NULL;--> statement-breakpoint
ALTER TABLE "collection_items" ADD COLUMN "tags" jsonb DEFAULT '[]'::jsonb NOT NULL;--> statement-breakpoint
ALTER TABLE "collection_items" ADD COLUMN "condition" text;--> statement-breakpoint
ALTER TABLE "collection_items" ADD COLUMN "manual_value_usd" numeric(18, 4);--> statement-breakpoint
ALTER TABLE "collection_items" ADD COLUMN "sold_at" date;--> statement-breakpoint
ALTER TABLE "collection_items" ADD COLUMN "sold_price_usd" numeric(18, 4);--> statement-breakpoint
ALTER TABLE "collections" ADD COLUMN "kind" text DEFAULT 'collection' NOT NULL;--> statement-breakpoint
ALTER TABLE "collections" ADD COLUMN "budget_usd" numeric(18, 4);--> statement-breakpoint
ALTER TABLE "collections" ADD COLUMN "color" text;--> statement-breakpoint
ALTER TABLE "sessions" ADD COLUMN "ip" text;--> statement-breakpoint
ALTER TABLE "sessions" ADD COLUMN "last_seen_at" timestamp with time zone;--> statement-breakpoint
ALTER TABLE "sessions" ADD COLUMN "revoked_at" timestamp with time zone;--> statement-breakpoint
ALTER TABLE "users" ADD COLUMN "handle" text;--> statement-breakpoint
ALTER TABLE "users" ADD COLUMN "avatar_url" text;--> statement-breakpoint
ALTER TABLE "users" ADD COLUMN "bio" text;--> statement-breakpoint
ALTER TABLE "users" ADD COLUMN "mfa_enabled" boolean DEFAULT false NOT NULL;--> statement-breakpoint
ALTER TABLE "users" ADD COLUMN "totp_secret_enc" text;--> statement-breakpoint
ALTER TABLE "users" ADD COLUMN "always_ask_code" boolean DEFAULT true NOT NULL;--> statement-breakpoint
ALTER TABLE "users" ADD COLUMN "password_changed_at" timestamp with time zone;--> statement-breakpoint
ALTER TABLE "users" ADD COLUMN "pending_email" text;--> statement-breakpoint
ALTER TABLE "users" ADD COLUMN "deleted_at" timestamp with time zone;--> statement-breakpoint
ALTER TABLE "users" ADD COLUMN "purge_after" timestamp with time zone;--> statement-breakpoint
ALTER TABLE "watchlist_items" ADD COLUMN "label" text;--> statement-breakpoint
ALTER TABLE "watchlist_items" ADD COLUMN "note" text;--> statement-breakpoint
ALTER TABLE "watchlist_items" ADD COLUMN "target_price_usd" numeric(18, 4);--> statement-breakpoint
ALTER TABLE "watchlist_items" ADD COLUMN "baseline_usd" numeric(18, 4);--> statement-breakpoint
CREATE INDEX "ai_quotas_uq" ON "ai_quotas" USING btree ("key","feature","date");--> statement-breakpoint
CREATE INDEX "research_messages_session_idx" ON "research_messages" USING btree ("session_id","created_at");--> statement-breakpoint
CREATE INDEX "research_sessions_anon_idx" ON "research_sessions" USING btree ("anon_id","updated_at");--> statement-breakpoint
CREATE INDEX "research_sessions_user_idx" ON "research_sessions" USING btree ("user_id","updated_at");--> statement-breakpoint
CREATE INDEX "scanner_sessions_created_idx" ON "scanner_sessions" USING btree ("created_at");--> statement-breakpoint
CREATE INDEX "scanner_sessions_ip_idx" ON "scanner_sessions" USING btree ("ip_hash","created_at");--> statement-breakpoint
CREATE INDEX "scanner_sessions_user_idx" ON "scanner_sessions" USING btree ("user_id");--> statement-breakpoint
CREATE INDEX "auth_codes_lookup_idx" ON "auth_codes" USING btree ("email","purpose","created_at");--> statement-breakpoint
CREATE INDEX "login_events_user_idx" ON "login_events" USING btree ("user_id","created_at");--> statement-breakpoint
CREATE INDEX "notifications_user_idx" ON "notifications" USING btree ("user_id","created_at");--> statement-breakpoint
CREATE INDEX "notifications_unread_idx" ON "notifications" USING btree ("user_id","read_at");--> statement-breakpoint
CREATE INDEX "price_targets_user_idx" ON "price_targets" USING btree ("user_id");--> statement-breakpoint
CREATE INDEX "price_targets_asset_idx" ON "price_targets" USING btree ("asset_id","hit_at");--> statement-breakpoint
CREATE INDEX "recovery_codes_user_idx" ON "recovery_codes" USING btree ("user_id");--> statement-breakpoint
CREATE INDEX "saved_searches_user_idx" ON "saved_searches" USING btree ("user_id");--> statement-breakpoint
CREATE INDEX "trusted_devices_user_idx" ON "trusted_devices" USING btree ("user_id");--> statement-breakpoint
CREATE UNIQUE INDEX "trusted_devices_token_uq" ON "trusted_devices" USING btree ("token_hash");--> statement-breakpoint
CREATE INDEX "uploads_user_idx" ON "uploads" USING btree ("user_id");--> statement-breakpoint
CREATE UNIQUE INDEX "user_badges_uq" ON "user_badges" USING btree ("user_id","badge");--> statement-breakpoint
CREATE UNIQUE INDEX "normalized_records_raw_uq" ON "normalized_records" USING btree ("raw_record_id","kind","seq");--> statement-breakpoint
ALTER TABLE "users" ADD CONSTRAINT "users_handle_unique" UNIQUE("handle");