CREATE TABLE "guest_sessions" (
	"id" varchar(64) PRIMARY KEY NOT NULL,
	"ip_hash" varchar(64),
	"scan_count" numeric DEFAULT '0' NOT NULL,
	"last_scan_at" timestamp,
	"expires_at" timestamp,
	"created_at" timestamp DEFAULT now() NOT NULL,
	"updated_at" timestamp DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "licenses" (
	"id" varchar(64) PRIMARY KEY NOT NULL,
	"user_id" varchar(64),
	"license_key" varchar(128) NOT NULL,
	"plan" varchar(32) NOT NULL,
	"status" varchar(32) DEFAULT 'active' NOT NULL,
	"stripe_customer_id" varchar(128),
	"stripe_subscription_id" varchar(128),
	"stripe_checkout_session_id" varchar(128),
	"activated_at" timestamp DEFAULT now() NOT NULL,
	"expires_at" timestamp,
	"created_at" timestamp DEFAULT now() NOT NULL,
	"updated_at" timestamp DEFAULT now() NOT NULL,
	CONSTRAINT "licenses_license_key_unique" UNIQUE("license_key")
);
--> statement-breakpoint
CREATE TABLE "line_items" (
	"id" varchar(64) PRIMARY KEY NOT NULL,
	"receipt_id" varchar(64) NOT NULL,
	"description" text NOT NULL,
	"quantity" numeric(10, 3) DEFAULT '1' NOT NULL,
	"price" numeric(12, 2) NOT NULL,
	"unit_price" numeric(12, 2),
	"tax_rate" numeric(5, 2),
	"created_at" timestamp DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "receipts" (
	"id" varchar(64) PRIMARY KEY NOT NULL,
	"user_id" varchar(64) NOT NULL,
	"image_hash" varchar(64) NOT NULL,
	"storage_url" text,
	"merchant_name" varchar(255),
	"receipt_date" varchar(32),
	"receipt_number" varchar(128),
	"document_type" varchar(64) DEFAULT 'KASSENBON',
	"category" varchar(64) DEFAULT 'Sonstiges',
	"currency" varchar(8) DEFAULT 'EUR' NOT NULL,
	"total_amount" numeric(12, 2) NOT NULL,
	"net_amount" numeric(12, 2),
	"tax_7_amount" numeric(12, 2),
	"tax_19_amount" numeric(12, 2),
	"tip_amount" numeric(12, 2),
	"tax_breakdown_json" jsonb,
	"line_items_json" jsonb,
	"validation_json" jsonb,
	"raw_ocr_text" text,
	"payment_method" varchar(64),
	"is_math_valid" boolean DEFAULT true NOT NULL,
	"needs_review" boolean DEFAULT false NOT NULL,
	"created_at" timestamp DEFAULT now() NOT NULL,
	"updated_at" timestamp DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "users" (
	"id" varchar(64) PRIMARY KEY NOT NULL,
	"email" varchar(255),
	"is_guest" boolean DEFAULT true NOT NULL,
	"plan" varchar(32) DEFAULT 'free' NOT NULL,
	"stripe_customer_id" varchar(128),
	"stripe_subscription_id" varchar(128),
	"scan_count" numeric DEFAULT '0' NOT NULL,
	"expires_at" timestamp,
	"created_at" timestamp DEFAULT now() NOT NULL,
	"updated_at" timestamp DEFAULT now() NOT NULL
);
--> statement-breakpoint
ALTER TABLE "licenses" ADD CONSTRAINT "licenses_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "line_items" ADD CONSTRAINT "line_items_receipt_id_receipts_id_fk" FOREIGN KEY ("receipt_id") REFERENCES "public"."receipts"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "receipts" ADD CONSTRAINT "receipts_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_guest_sessions_ip" ON "guest_sessions" USING btree ("ip_hash");--> statement-breakpoint
CREATE INDEX "idx_licenses_key" ON "licenses" USING btree ("license_key");--> statement-breakpoint
CREATE INDEX "idx_licenses_user_id" ON "licenses" USING btree ("user_id");--> statement-breakpoint
CREATE INDEX "idx_licenses_sub_id" ON "licenses" USING btree ("stripe_subscription_id");--> statement-breakpoint
CREATE INDEX "idx_line_items_receipt_id" ON "line_items" USING btree ("receipt_id");--> statement-breakpoint
CREATE INDEX "idx_receipts_user_date" ON "receipts" USING btree ("user_id","receipt_date");--> statement-breakpoint
CREATE INDEX "idx_receipts_hash" ON "receipts" USING btree ("image_hash");--> statement-breakpoint
CREATE INDEX "idx_receipts_duplicate_check" ON "receipts" USING btree ("user_id","merchant_name","total_amount","receipt_date");--> statement-breakpoint
CREATE INDEX "idx_users_email" ON "users" USING btree ("email");--> statement-breakpoint
CREATE INDEX "idx_users_guest_created" ON "users" USING btree ("is_guest","created_at");