CREATE TABLE "dunning_log" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"tenant_id" uuid NOT NULL,
	"invoice_id" uuid NOT NULL,
	"offset_days" integer NOT NULL,
	"action" text NOT NULL,
	"sent_at" timestamp with time zone DEFAULT now() NOT NULL,
	"result" text NOT NULL,
	"error_msg" text,
	CONSTRAINT "dunning_log_result_check" CHECK ("dunning_log"."result" IN ('sent','skipped','error'))
);
--> statement-breakpoint
CREATE TABLE "dunning_schedules" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"tenant_id" uuid NOT NULL,
	"offset_days" integer NOT NULL,
	"action" text NOT NULL,
	"email_template_id" uuid,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	CONSTRAINT "dunning_schedules_tenant_offset_uniq" UNIQUE("tenant_id","offset_days"),
	CONSTRAINT "dunning_schedules_action_check" CHECK ("dunning_schedules"."action" IN ('email_reminder','suspend_access','flag_for_review'))
);
--> statement-breakpoint
CREATE TABLE "invoice_payments" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"tenant_id" uuid NOT NULL,
	"invoice_id" uuid NOT NULL,
	"amount" numeric(12, 2) NOT NULL,
	"currency" text DEFAULT 'ILS' NOT NULL,
	"paid_at" timestamp with time zone NOT NULL,
	"source" text DEFAULT 'manual' NOT NULL,
	"reference" text,
	"recorded_by" uuid,
	"note" text,
	"receipt_id" uuid,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	CONSTRAINT "invoice_payments_amount_check" CHECK ("invoice_payments"."amount" > 0),
	CONSTRAINT "invoice_payments_source_check" CHECK ("invoice_payments"."source" IN ('manual','gateway','bank_transfer','auto_billing'))
);
--> statement-breakpoint
ALTER TABLE "tenant_settings" ADD COLUMN "dunning_suspend_access" boolean DEFAULT false NOT NULL;--> statement-breakpoint
ALTER TABLE "tenant_settings" ADD COLUMN "invoice_pdf_layout" text DEFAULT 'classic' NOT NULL;--> statement-breakpoint
ALTER TABLE "tenant_settings" ADD COLUMN "invoice_pdf_show_project" boolean DEFAULT false NOT NULL;--> statement-breakpoint
ALTER TABLE "tenant_settings" ADD COLUMN "invoice_pdf_show_sku" boolean DEFAULT false NOT NULL;--> statement-breakpoint
ALTER TABLE "tenant_settings" ADD COLUMN "invoice_pdf_date_format" text DEFAULT 'dd.MM.yyyy' NOT NULL;--> statement-breakpoint
ALTER TABLE "tenant_settings" ADD COLUMN "invoice_pdf_accent_hex" text;--> statement-breakpoint
ALTER TABLE "invoices" ADD COLUMN "overpayment_amount" numeric(12, 2) DEFAULT '0' NOT NULL;--> statement-breakpoint
ALTER TABLE "invoices" ADD COLUMN "proposal_id" uuid;--> statement-breakpoint
ALTER TABLE "dunning_log" ADD CONSTRAINT "dunning_log_tenant_id_tenants_id_fk" FOREIGN KEY ("tenant_id") REFERENCES "public"."tenants"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "dunning_log" ADD CONSTRAINT "dunning_log_invoice_id_invoices_id_fk" FOREIGN KEY ("invoice_id") REFERENCES "public"."invoices"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "dunning_schedules" ADD CONSTRAINT "dunning_schedules_tenant_id_tenants_id_fk" FOREIGN KEY ("tenant_id") REFERENCES "public"."tenants"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "invoice_payments" ADD CONSTRAINT "invoice_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 "invoice_payments" ADD CONSTRAINT "invoice_payments_invoice_id_invoices_id_fk" FOREIGN KEY ("invoice_id") REFERENCES "public"."invoices"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "invoice_payments" ADD CONSTRAINT "invoice_payments_recorded_by_users_id_fk" FOREIGN KEY ("recorded_by") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
CREATE INDEX "idx_dunning_log_invoice" ON "dunning_log" USING btree ("invoice_id");--> statement-breakpoint
CREATE INDEX "idx_dunning_log_tenant" ON "dunning_log" USING btree ("tenant_id");--> statement-breakpoint
CREATE INDEX "idx_invoice_payments_invoice" ON "invoice_payments" USING btree ("invoice_id");--> statement-breakpoint
CREATE INDEX "idx_invoice_payments_tenant" ON "invoice_payments" USING btree ("tenant_id","paid_at" DESC NULLS LAST);--> statement-breakpoint
ALTER TABLE "tenant_settings" ADD CONSTRAINT "tenant_settings_invoice_pdf_layout_check" CHECK ("tenant_settings"."invoice_pdf_layout" IN ('classic','modern','minimal'));--> statement-breakpoint
ALTER TABLE "tenant_settings" ADD CONSTRAINT "tenant_settings_invoice_pdf_date_format_check" CHECK ("tenant_settings"."invoice_pdf_date_format" IN ('dd.MM.yyyy','yyyy-MM-dd','MMMM d, yyyy'));