CREATE TABLE "customer_activities" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"tenant_id" uuid NOT NULL,
	"customer_id" uuid NOT NULL,
	"actor_id" uuid,
	"actor_type" text DEFAULT 'user' NOT NULL,
	"event_type" text NOT NULL,
	"metadata" jsonb DEFAULT '{}'::jsonb NOT NULL,
	"note" text,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	"deleted_at" timestamp with time zone,
	CONSTRAINT "customer_activities_actor_type_check" CHECK ("customer_activities"."actor_type" IN ('user','system','customer')),
	CONSTRAINT "customer_activities_event_type_check" CHECK ("customer_activities"."event_type" IN ('created','updated','archived','note_added','portal_user_invited','portal_user_activated','lead_converted'))
);
--> statement-breakpoint
CREATE TABLE "invoice_activities" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"tenant_id" uuid NOT NULL,
	"invoice_id" uuid NOT NULL,
	"actor_id" uuid,
	"actor_type" text DEFAULT 'user' NOT NULL,
	"event_type" text NOT NULL,
	"metadata" jsonb DEFAULT '{}'::jsonb NOT NULL,
	"note" text,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	"deleted_at" timestamp with time zone,
	CONSTRAINT "invoice_activities_actor_type_check" CHECK ("invoice_activities"."actor_type" IN ('user','system','customer')),
	CONSTRAINT "invoice_activities_event_type_check" CHECK ("invoice_activities"."event_type" IN ('created','sent','approved','rejected','tax_issued','paid','voided','credit_noted','note_added','email_sent','viewed_by_customer','payment_initiated'))
);
--> statement-breakpoint
CREATE TABLE "project_activities" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"tenant_id" uuid NOT NULL,
	"project_id" uuid NOT NULL,
	"actor_id" uuid,
	"actor_type" text DEFAULT 'user' NOT NULL,
	"event_type" text NOT NULL,
	"metadata" jsonb DEFAULT '{}'::jsonb NOT NULL,
	"note" text,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	"deleted_at" timestamp with time zone,
	CONSTRAINT "project_activities_actor_type_check" CHECK ("project_activities"."actor_type" IN ('user','system','customer')),
	CONSTRAINT "project_activities_event_type_check" CHECK ("project_activities"."event_type" IN ('created','status_changed','member_added','member_removed','note_added','invoice_linked','time_logged'))
);
--> statement-breakpoint
CREATE TABLE "vendor_activities" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"tenant_id" uuid NOT NULL,
	"vendor_id" uuid NOT NULL,
	"actor_id" uuid,
	"actor_type" text DEFAULT 'user' NOT NULL,
	"event_type" text NOT NULL,
	"metadata" jsonb DEFAULT '{}'::jsonb NOT NULL,
	"note" text,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	"deleted_at" timestamp with time zone,
	CONSTRAINT "vendor_activities_actor_type_check" CHECK ("vendor_activities"."actor_type" IN ('user','system','customer')),
	CONSTRAINT "vendor_activities_event_type_check" CHECK ("vendor_activities"."event_type" IN ('created','updated','withholding_cert_uploaded','withholding_cert_expiring','archived','note_added'))
);
--> statement-breakpoint
CREATE TABLE "bad_debt_vat_reclaims" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"tenant_id" uuid NOT NULL,
	"invoice_id" uuid NOT NULL,
	"vat_amount" numeric(12, 2) NOT NULL,
	"status" text DEFAULT 'pending' NOT NULL,
	"registered_letter_sent_at" date,
	"ita_submission_date" date,
	"ita_reference" text,
	"resolved_at" timestamp with time zone,
	"reversed_at" timestamp with time zone,
	"notes" text,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	CONSTRAINT "bad_debt_vat_reclaims_status_check" CHECK ("bad_debt_vat_reclaims"."status" IN ('pending','submitted','approved','rejected','reversed','cancelled'))
);
--> statement-breakpoint
ALTER TABLE "contract_audit_log" DROP CONSTRAINT "contract_audit_log_event_check";--> statement-breakpoint
ALTER TABLE "contract_audit_log" DROP CONSTRAINT "contract_audit_log_actor_type_check";--> statement-breakpoint
ALTER TABLE "contract_signatories" ADD COLUMN IF NOT EXISTS "order" integer DEFAULT 1 NOT NULL;--> statement-breakpoint
ALTER TABLE "contract_signatories" ADD COLUMN IF NOT EXISTS "reminder_sent_at" timestamp with time zone;--> statement-breakpoint
ALTER TABLE "contract_signatories" ADD COLUMN IF NOT EXISTS "reminder_count" integer DEFAULT 0 NOT NULL;--> statement-breakpoint
ALTER TABLE "tenant_settings" ADD COLUMN "bad_debt_threshold_days" integer DEFAULT 90 NOT NULL;--> statement-breakpoint
ALTER TABLE "invoices" ADD COLUMN "bad_debt_at" timestamp with time zone;--> statement-breakpoint
ALTER TABLE "invoices" ADD COLUMN "bad_debt_reason" text;--> statement-breakpoint
ALTER TABLE "invoices" ADD COLUMN "bad_debt_note" text;--> statement-breakpoint
ALTER TABLE "tenant_api_keys" ADD COLUMN "monthly_quota" integer;--> statement-breakpoint
ALTER TABLE "customer_activities" ADD CONSTRAINT "customer_activities_tenant_id_tenants_id_fk" FOREIGN KEY ("tenant_id") REFERENCES "public"."tenants"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "customer_activities" ADD CONSTRAINT "customer_activities_customer_id_customers_id_fk" FOREIGN KEY ("customer_id") REFERENCES "public"."customers"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "customer_activities" ADD CONSTRAINT "customer_activities_actor_id_users_id_fk" FOREIGN KEY ("actor_id") REFERENCES "public"."users"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "invoice_activities" ADD CONSTRAINT "invoice_activities_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_activities" ADD CONSTRAINT "invoice_activities_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_activities" ADD CONSTRAINT "invoice_activities_actor_id_users_id_fk" FOREIGN KEY ("actor_id") REFERENCES "public"."users"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "project_activities" ADD CONSTRAINT "project_activities_tenant_id_tenants_id_fk" FOREIGN KEY ("tenant_id") REFERENCES "public"."tenants"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "project_activities" ADD CONSTRAINT "project_activities_project_id_projects_id_fk" FOREIGN KEY ("project_id") REFERENCES "public"."projects"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "project_activities" ADD CONSTRAINT "project_activities_actor_id_users_id_fk" FOREIGN KEY ("actor_id") REFERENCES "public"."users"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "vendor_activities" ADD CONSTRAINT "vendor_activities_tenant_id_tenants_id_fk" FOREIGN KEY ("tenant_id") REFERENCES "public"."tenants"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "vendor_activities" ADD CONSTRAINT "vendor_activities_vendor_id_vendors_id_fk" FOREIGN KEY ("vendor_id") REFERENCES "public"."vendors"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "vendor_activities" ADD CONSTRAINT "vendor_activities_actor_id_users_id_fk" FOREIGN KEY ("actor_id") REFERENCES "public"."users"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "bad_debt_vat_reclaims" ADD CONSTRAINT "bad_debt_vat_reclaims_tenant_id_tenants_id_fk" FOREIGN KEY ("tenant_id") REFERENCES "public"."tenants"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "bad_debt_vat_reclaims" ADD CONSTRAINT "bad_debt_vat_reclaims_invoice_id_invoices_id_fk" FOREIGN KEY ("invoice_id") REFERENCES "public"."invoices"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "contract_audit_log" ADD CONSTRAINT "contract_audit_log_event_check" CHECK ("contract_audit_log"."event" IN ('sent','viewed','signed','declined','voided','downloaded','reminder_sent','link_resent','signatory_replaced'));--> statement-breakpoint
ALTER TABLE "contract_audit_log" ADD CONSTRAINT "contract_audit_log_actor_type_check" CHECK ("contract_audit_log"."actor_type" IN ('staff','signatory','system'));--> statement-breakpoint
ALTER TABLE "invoices" ADD CONSTRAINT "invoices_bad_debt_reason_check" CHECK ("invoices"."bad_debt_reason" IS NULL OR "invoices"."bad_debt_reason" IN ('bankruptcy','collection_failed','other'));--> statement-breakpoint
ALTER TABLE "tenant_api_keys" ADD CONSTRAINT "tenant_api_keys_monthly_quota_nonneg" CHECK ("tenant_api_keys"."monthly_quota" IS NULL OR "tenant_api_keys"."monthly_quota" >= 0);