CREATE TABLE "unmatched_payments" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"tenant_id" uuid NOT NULL,
	"amount" numeric(12, 2) NOT NULL,
	"currency" text DEFAULT 'ILS' NOT NULL,
	"paid_at" date NOT NULL,
	"payment_method" text DEFAULT 'bank_transfer' NOT NULL,
	"reference" text,
	"notes" text,
	"payer_name" text,
	"matched_to_invoice_id" uuid,
	"matched_at" timestamp with time zone,
	"matched_by" uuid,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	CONSTRAINT "unmatched_payments_amount_check" CHECK ("unmatched_payments"."amount" > 0),
	CONSTRAINT "unmatched_payments_currency_check" CHECK ("unmatched_payments"."currency" IN ('ILS','USD','EUR')),
	CONSTRAINT "unmatched_payments_payment_method_check" CHECK ("unmatched_payments"."payment_method" IN ('bank_transfer','credit_card','check','cash','other'))
);
--> statement-breakpoint
ALTER TABLE "unmatched_payments" ADD CONSTRAINT "unmatched_payments_tenant_id_tenants_id_fk" FOREIGN KEY ("tenant_id") REFERENCES "public"."tenants"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "unmatched_payments" ADD CONSTRAINT "unmatched_payments_matched_to_invoice_id_invoices_id_fk" FOREIGN KEY ("matched_to_invoice_id") REFERENCES "public"."invoices"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "unmatched_payments" ADD CONSTRAINT "unmatched_payments_matched_by_users_id_fk" FOREIGN KEY ("matched_by") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
CREATE INDEX "idx_unmatched_payments_tenant" ON "unmatched_payments" USING btree ("tenant_id","paid_at");
-- partial index: unmatched rows only (drizzle index() builder cannot emit WHERE clauses)
CREATE INDEX "idx_unmatched_payments_unmatched" ON "unmatched_payments" ("tenant_id")
  WHERE matched_to_invoice_id IS NULL;
