CREATE TABLE "attachments" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"tenant_id" uuid NOT NULL,
	"uploader_id" uuid NOT NULL,
	"entity_type" text NOT NULL,
	"entity_id" text NOT NULL,
	"filename" text NOT NULL,
	"mime_type" text NOT NULL,
	"size_bytes" integer NOT NULL,
	"r2_key" text NOT NULL,
	"signed_url" text,
	"deleted_at" timestamp with time zone,
	"deleted_by" uuid,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	"updated_at" timestamp with time zone DEFAULT now() NOT NULL,
	CONSTRAINT "attachments_entity_type_check" CHECK ("attachments"."entity_type" IN ('task_message','expense','ticket_message','kb_article','vendor'))
);
--> statement-breakpoint
CREATE TABLE "contractor_assignments" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"tenant_id" uuid NOT NULL,
	"contractor_id" uuid NOT NULL,
	"project_id" uuid NOT NULL,
	"role" text,
	"rate_override" numeric(10, 2),
	"created_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "contractors" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"tenant_id" uuid NOT NULL,
	"name" text NOT NULL,
	"email" text,
	"phone" text,
	"tax_id" text,
	"billing_type" text DEFAULT 'hourly' NOT NULL,
	"hourly_rate" numeric(10, 2),
	"currency" text DEFAULT 'ILS' NOT NULL,
	"active" boolean DEFAULT true NOT NULL,
	"notes" text,
	"withholding_tax_rate" numeric(5, 4),
	"withholding_certificate_number" text,
	"withholding_certificate_expiry" date,
	"withholding_certificate_r2_key" text,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	"updated_at" timestamp with time zone DEFAULT now() NOT NULL,
	CONSTRAINT "contractors_billing_type_check" CHECK ("contractors"."billing_type" IN ('hourly','fixed','retainer')),
	CONSTRAINT "contractors_withholding_rate_check" CHECK ("contractors"."withholding_tax_rate" IS NULL OR ("contractors"."withholding_tax_rate" >= 0 AND "contractors"."withholding_tax_rate" <= 1))
);
--> statement-breakpoint
CREATE TABLE "payout_bill_lines" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"bill_id" uuid NOT NULL,
	"tenant_id" uuid NOT NULL,
	"time_entry_id" uuid,
	"description" text NOT NULL,
	"hours" numeric(8, 2),
	"rate" numeric(10, 2),
	"line_total" numeric(12, 2) NOT NULL,
	"project_id" uuid,
	"position" text DEFAULT '0' NOT NULL
);
--> statement-breakpoint
CREATE TABLE "payout_bills" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"tenant_id" uuid NOT NULL,
	"contractor_id" uuid NOT NULL,
	"period_start" date NOT NULL,
	"period_end" date NOT NULL,
	"status" text DEFAULT 'DRAFT' NOT NULL,
	"total_hours" numeric(8, 2),
	"amount" numeric(12, 2) NOT NULL,
	"currency" text DEFAULT 'ILS' NOT NULL,
	"withholding_rate" numeric(5, 4) DEFAULT '0' NOT NULL,
	"withholding_amount" numeric(12, 2) DEFAULT '0' NOT NULL,
	"net_amount" numeric(12, 2),
	"notes" text,
	"paid_at" timestamp with time zone,
	"payment_method" text,
	"payment_reference" text,
	"voided_at" timestamp with time zone,
	"voided_by" uuid,
	"void_reason" text,
	"created_by" uuid NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	"updated_at" timestamp with time zone DEFAULT now() NOT NULL,
	CONSTRAINT "payout_bills_status_check" CHECK ("payout_bills"."status" IN ('DRAFT','SENT','APPROVED','PAID','VOID')),
	CONSTRAINT "payout_bills_payment_method_check" CHECK ("payout_bills"."payment_method" IS NULL OR "payout_bills"."payment_method" IN ('bank_transfer','check','other')),
	CONSTRAINT "payout_bills_void_consistency_check" CHECK (("payout_bills"."status" = 'VOID') = ("payout_bills"."voided_at" IS NOT NULL)),
	CONSTRAINT "payout_bills_paid_consistency_check" CHECK (("payout_bills"."status" = 'PAID') = ("payout_bills"."paid_at" IS NOT NULL))
);
--> statement-breakpoint
CREATE TABLE "withholding_tax_certificates" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"tenant_id" uuid NOT NULL,
	"contractor_id" uuid NOT NULL,
	"certificate_number" text NOT NULL,
	"tax_year" text NOT NULL,
	"withholding_rate" numeric(5, 4) NOT NULL,
	"expiry_date" date NOT NULL,
	"r2_key" text,
	"uploaded_by" uuid NOT NULL,
	"uploaded_at" timestamp with time zone DEFAULT now() NOT NULL,
	CONSTRAINT "withholding_tax_certs_rate_check" CHECK ("withholding_tax_certificates"."withholding_rate" >= 0 AND "withholding_tax_certificates"."withholding_rate" <= 1)
);
--> statement-breakpoint
CREATE TABLE "customer_merge_suggestions" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"tenant_id" uuid NOT NULL,
	"primary_customer_id" uuid NOT NULL,
	"duplicate_customer_id" uuid NOT NULL,
	"reason" text NOT NULL,
	"match_metadata" jsonb,
	"status" text DEFAULT 'pending' NOT NULL,
	"resolved_by" uuid,
	"resolved_at" timestamp with time zone,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	CONSTRAINT "customer_merge_suggestions_status_check" CHECK ("customer_merge_suggestions"."status" IN ('pending', 'merged', 'dismissed')),
	CONSTRAINT "customer_merge_suggestions_reason_check" CHECK ("customer_merge_suggestions"."reason" IN ('email_match', 'name_similarity'))
);
--> statement-breakpoint
CREATE TABLE "import_job_results" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"import_job_id" uuid NOT NULL,
	"tenant_id" uuid NOT NULL,
	"row_number" integer NOT NULL,
	"status" text NOT NULL,
	"message" text,
	"original_data" text,
	"entity_id" uuid,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	CONSTRAINT "import_job_results_status_check" CHECK ("import_job_results"."status" IN ('success','skipped','error'))
);
--> statement-breakpoint
CREATE TABLE "import_jobs" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"tenant_id" uuid NOT NULL,
	"created_by" uuid NOT NULL,
	"type" text NOT NULL,
	"status" text DEFAULT 'pending' NOT NULL,
	"r2_key" text NOT NULL,
	"original_filename" text NOT NULL,
	"mime_type" text NOT NULL,
	"file_size_bytes" integer NOT NULL,
	"column_mapping" jsonb,
	"total_rows" integer,
	"rows_processed" integer DEFAULT 0 NOT NULL,
	"success_count" integer DEFAULT 0 NOT NULL,
	"skipped_count" integer DEFAULT 0 NOT NULL,
	"error_count" integer DEFAULT 0 NOT NULL,
	"started_at" timestamp with time zone,
	"completed_at" timestamp with time zone,
	"error_message" text,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	"updated_at" timestamp with time zone DEFAULT now() NOT NULL,
	CONSTRAINT "import_jobs_type_check" CHECK ("import_jobs"."type" IN ('customers','invoices','products','time_entries','bulk_action')),
	CONSTRAINT "import_jobs_status_check" CHECK ("import_jobs"."status" IN ('pending','processing','completed','failed'))
);
--> statement-breakpoint
CREATE TABLE "recurring_invoice_templates" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"tenant_id" uuid NOT NULL,
	"customer_id" uuid NOT NULL,
	"title" text NOT NULL,
	"description" text,
	"line_items" jsonb DEFAULT '[]'::jsonb NOT NULL,
	"currency" text DEFAULT 'ILS' NOT NULL,
	"vat_rate" numeric(5, 4) DEFAULT '0.18' NOT NULL,
	"frequency" text NOT NULL,
	"frequency_day" integer,
	"payment_terms_days" integer DEFAULT 30 NOT NULL,
	"start_date" date NOT NULL,
	"end_date" date,
	"auto_send" boolean DEFAULT false NOT NULL,
	"auto_charge" boolean DEFAULT false NOT NULL,
	"next_generation_date" date NOT NULL,
	"last_generated_at" timestamp with time zone,
	"status" text DEFAULT 'active' NOT NULL,
	"generated_count" integer DEFAULT 0 NOT NULL,
	"created_by" uuid NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	"updated_at" timestamp with time zone DEFAULT now() NOT NULL,
	CONSTRAINT "rit_frequency_check" CHECK ("recurring_invoice_templates"."frequency" IN ('weekly','monthly','quarterly','yearly')),
	CONSTRAINT "rit_status_check" CHECK ("recurring_invoice_templates"."status" IN ('active','paused','completed','cancelled'))
);
--> statement-breakpoint
CREATE TABLE "project_milestones" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"tenant_id" uuid NOT NULL,
	"project_id" uuid NOT NULL,
	"name" text NOT NULL,
	"description" text,
	"amount" numeric(10, 2),
	"invoice_id" uuid,
	"status" text DEFAULT 'pending' NOT NULL,
	"due_date" date,
	"completed_at" timestamp with time zone,
	"created_by" uuid NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	"updated_at" timestamp with time zone DEFAULT now() NOT NULL,
	CONSTRAINT "project_milestones_status_check" CHECK ("project_milestones"."status" IN ('pending', 'in_progress', 'completed', 'cancelled'))
);
--> statement-breakpoint
CREATE TABLE "lead_activities" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"tenant_id" uuid NOT NULL,
	"lead_id" uuid NOT NULL,
	"user_id" uuid,
	"type" text NOT NULL,
	"content" text,
	"metadata" jsonb,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	CONSTRAINT "lead_activities_type_check" CHECK ("lead_activities"."type" IN ('note','email_sent','call_logged','stage_changed','form_submitted','webhook_received','converted','contract_linked'))
);
--> statement-breakpoint
CREATE TABLE "lead_form_submissions" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"tenant_id" uuid NOT NULL,
	"form_id" uuid NOT NULL,
	"lead_id" uuid NOT NULL,
	"payload" jsonb NOT NULL,
	"ip" text,
	"user_agent" text,
	"referrer" text,
	"utm_source" text,
	"utm_medium" text,
	"utm_campaign" text,
	"utm_content" text,
	"utm_term" text,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "lead_forms" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"tenant_id" uuid NOT NULL,
	"name" text NOT NULL,
	"slug" text NOT NULL,
	"fields" jsonb NOT NULL,
	"redirect_url" text,
	"notify_email" text,
	"is_active" boolean DEFAULT true NOT NULL,
	"style" jsonb,
	"created_by" uuid 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 "lead_webhooks" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"tenant_id" uuid NOT NULL,
	"name" text NOT NULL,
	"source" text NOT NULL,
	"secret" text NOT NULL,
	"field_mapping" jsonb,
	"is_active" boolean DEFAULT true NOT NULL,
	"last_received_at" timestamp with time zone,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	CONSTRAINT "lead_webhooks_source_check" CHECK ("lead_webhooks"."source" IN ('facebook','google','linkedin','instagram','zapier','make','generic'))
);
--> statement-breakpoint
CREATE TABLE "leads" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"tenant_id" uuid NOT NULL,
	"name" text NOT NULL,
	"email" text,
	"phone" text,
	"company" text,
	"notes" text,
	"stage" text DEFAULT 'NEW' NOT NULL,
	"stage_position" numeric NOT NULL,
	"source" text DEFAULT 'manual' NOT NULL,
	"source_metadata" jsonb,
	"assigned_to" uuid,
	"customer_id" uuid,
	"contract_id" uuid,
	"lost_reason" text,
	"estimated_value" numeric,
	"utm_source" text,
	"utm_medium" text,
	"utm_campaign" text,
	"utm_content" text,
	"utm_term" text,
	"archived_at" timestamp with time zone,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	"updated_at" timestamp with time zone DEFAULT now() NOT NULL,
	CONSTRAINT "leads_stage_check" CHECK ("leads"."stage" IN ('NEW','CONTACTED','QUALIFIED','PROPOSAL','WON','LOST')),
	CONSTRAINT "leads_source_check" CHECK ("leads"."source" IN ('manual','form','webhook','facebook','google','linkedin','instagram','zapier','make','referral','cold_outreach'))
);
--> statement-breakpoint
CREATE TABLE "pipeline_stages" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"tenant_id" uuid NOT NULL,
	"name" text NOT NULL,
	"slug" text NOT NULL,
	"color" text,
	"position" numeric NOT NULL,
	"is_system" boolean DEFAULT false NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "sla_policies" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"tenant_id" uuid NOT NULL,
	"priority" text NOT NULL,
	"first_response_hours" integer NOT NULL,
	"resolution_hours" integer NOT NULL,
	"escalation_email" text,
	"notify_email" boolean DEFAULT true NOT NULL,
	"notify_in_app" boolean DEFAULT true NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	"updated_at" timestamp with time zone DEFAULT now() NOT NULL,
	CONSTRAINT "sla_policies_tenant_priority_uniq" UNIQUE("tenant_id","priority"),
	CONSTRAINT "sla_policies_priority_check" CHECK ("sla_policies"."priority" IN ('low','medium','high','urgent'))
);
--> statement-breakpoint
CREATE TABLE "integration_sync_logs" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"tenant_id" uuid NOT NULL,
	"provider" text NOT NULL,
	"entity_type" text NOT NULL,
	"entity_id" uuid NOT NULL,
	"external_id" text,
	"direction" text NOT NULL,
	"status" text NOT NULL,
	"http_status" integer,
	"error_payload" jsonb,
	"duration_ms" integer,
	"triggered_by" uuid,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	CONSTRAINT "integration_sync_logs_provider_check" CHECK ("integration_sync_logs"."provider" IN ('morning','icount','rivhit','invoice4u','easycount')),
	CONSTRAINT "integration_sync_logs_entity_type_check" CHECK ("integration_sync_logs"."entity_type" IN ('invoice','customer')),
	CONSTRAINT "integration_sync_logs_direction_check" CHECK ("integration_sync_logs"."direction" IN ('push','pull')),
	CONSTRAINT "integration_sync_logs_status_check" CHECK ("integration_sync_logs"."status" IN ('success','failure','skipped'))
);
--> statement-breakpoint
ALTER TABLE "adapter_credentials" DROP CONSTRAINT "adapter_credentials_adapter_id_check";--> statement-breakpoint
ALTER TABLE "invoices" ALTER COLUMN "customer_id" DROP NOT NULL;--> statement-breakpoint
ALTER TABLE "expenses" ADD COLUMN "customer_id" uuid;--> statement-breakpoint
ALTER TABLE "expenses" ADD COLUMN "billable" boolean DEFAULT false NOT NULL;--> statement-breakpoint
ALTER TABLE "expenses" ADD COLUMN "invoice_id" uuid;--> statement-breakpoint
ALTER TABLE "expenses" ADD COLUMN "billed_at" timestamp with time zone;--> statement-breakpoint
ALTER TABLE "tenant_settings" ADD COLUMN "sla_enabled" boolean DEFAULT false NOT NULL;--> statement-breakpoint
ALTER TABLE "tenant_settings" ADD COLUMN "project_default_billing_type" text DEFAULT 'fixed' NOT NULL;--> statement-breakpoint
ALTER TABLE "tenant_settings" ADD COLUMN "project_default_hourly_rate" numeric(10, 2) DEFAULT '0' NOT NULL;--> statement-breakpoint
ALTER TABLE "tenant_settings" ADD COLUMN "project_default_currency" text DEFAULT 'ILS' NOT NULL;--> statement-breakpoint
ALTER TABLE "tenant_settings" ADD COLUMN "project_default_time_tracking" boolean DEFAULT true NOT NULL;--> statement-breakpoint
ALTER TABLE "tenant_settings" ADD COLUMN "project_default_time_rounding_minutes" integer DEFAULT 0 NOT NULL;--> statement-breakpoint
ALTER TABLE "tenant_settings" ADD COLUMN "project_default_budget_alert_pct" integer DEFAULT 80 NOT NULL;--> statement-breakpoint
ALTER TABLE "tenant_settings" ADD COLUMN "project_budget_alert_email" boolean DEFAULT true NOT NULL;--> statement-breakpoint
ALTER TABLE "tenant_settings" ADD COLUMN "project_budget_alert_in_app" boolean DEFAULT true NOT NULL;--> statement-breakpoint
ALTER TABLE "tenants" ADD COLUMN "checklist_dismissed_at" timestamp with time zone;--> statement-breakpoint
ALTER TABLE "tenants" ADD COLUMN "onboarding_completed" boolean DEFAULT false NOT NULL;--> statement-breakpoint
ALTER TABLE "projects" ADD COLUMN "completed_at" timestamp with time zone;--> statement-breakpoint
ALTER TABLE "projects" ADD COLUMN "archived_at" timestamp with time zone;--> statement-breakpoint
ALTER TABLE "time_entries" ADD COLUMN "invoice_id" uuid;--> statement-breakpoint
ALTER TABLE "time_entries" ADD COLUMN "billed_at" timestamp with time zone;--> statement-breakpoint
ALTER TABLE "invoice_lines" ADD COLUMN "expense_id" uuid;--> statement-breakpoint
ALTER TABLE "invoices" ADD COLUMN "approved_by" uuid;--> statement-breakpoint
ALTER TABLE "invoices" ADD COLUMN "approval_note" text;--> statement-breakpoint
ALTER TABLE "invoices" ADD COLUMN "rejection_reason" text;--> statement-breakpoint
ALTER TABLE "invoices" ADD COLUMN "rejection_notify_customer" boolean DEFAULT false NOT NULL;--> statement-breakpoint
ALTER TABLE "invoices" ADD COLUMN "is_template" boolean DEFAULT false NOT NULL;--> statement-breakpoint
ALTER TABLE "invoices" ADD COLUMN "recurring_template_id" uuid;--> statement-breakpoint
ALTER TABLE "tickets" ADD COLUMN "due_at" timestamp with time zone;--> statement-breakpoint
ALTER TABLE "tickets" ADD COLUMN "first_response_at" timestamp with time zone;--> statement-breakpoint
ALTER TABLE "tickets" ADD COLUMN "sla_breached" boolean DEFAULT false;--> statement-breakpoint
ALTER TABLE "attachments" ADD CONSTRAINT "attachments_tenant_id_tenants_id_fk" FOREIGN KEY ("tenant_id") REFERENCES "public"."tenants"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "attachments" ADD CONSTRAINT "attachments_uploader_id_users_id_fk" FOREIGN KEY ("uploader_id") REFERENCES "public"."users"("id") ON DELETE restrict ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "attachments" ADD CONSTRAINT "attachments_deleted_by_users_id_fk" FOREIGN KEY ("deleted_by") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "contractor_assignments" ADD CONSTRAINT "contractor_assignments_tenant_id_tenants_id_fk" FOREIGN KEY ("tenant_id") REFERENCES "public"."tenants"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "contractor_assignments" ADD CONSTRAINT "contractor_assignments_contractor_id_contractors_id_fk" FOREIGN KEY ("contractor_id") REFERENCES "public"."contractors"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "contractor_assignments" ADD CONSTRAINT "contractor_assignments_project_id_projects_id_fk" FOREIGN KEY ("project_id") REFERENCES "public"."projects"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "contractors" ADD CONSTRAINT "contractors_tenant_id_tenants_id_fk" FOREIGN KEY ("tenant_id") REFERENCES "public"."tenants"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "payout_bill_lines" ADD CONSTRAINT "payout_bill_lines_bill_id_payout_bills_id_fk" FOREIGN KEY ("bill_id") REFERENCES "public"."payout_bills"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "payout_bill_lines" ADD CONSTRAINT "payout_bill_lines_tenant_id_tenants_id_fk" FOREIGN KEY ("tenant_id") REFERENCES "public"."tenants"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "payout_bill_lines" ADD CONSTRAINT "payout_bill_lines_project_id_projects_id_fk" FOREIGN KEY ("project_id") REFERENCES "public"."projects"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "payout_bills" ADD CONSTRAINT "payout_bills_tenant_id_tenants_id_fk" FOREIGN KEY ("tenant_id") REFERENCES "public"."tenants"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "payout_bills" ADD CONSTRAINT "payout_bills_contractor_id_contractors_id_fk" FOREIGN KEY ("contractor_id") REFERENCES "public"."contractors"("id") ON DELETE restrict ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "payout_bills" ADD CONSTRAINT "payout_bills_voided_by_users_id_fk" FOREIGN KEY ("voided_by") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "payout_bills" ADD CONSTRAINT "payout_bills_created_by_users_id_fk" FOREIGN KEY ("created_by") REFERENCES "public"."users"("id") ON DELETE restrict ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "withholding_tax_certificates" ADD CONSTRAINT "withholding_tax_certificates_tenant_id_tenants_id_fk" FOREIGN KEY ("tenant_id") REFERENCES "public"."tenants"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "withholding_tax_certificates" ADD CONSTRAINT "withholding_tax_certificates_contractor_id_contractors_id_fk" FOREIGN KEY ("contractor_id") REFERENCES "public"."contractors"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "withholding_tax_certificates" ADD CONSTRAINT "withholding_tax_certificates_uploaded_by_users_id_fk" FOREIGN KEY ("uploaded_by") REFERENCES "public"."users"("id") ON DELETE restrict ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "customer_merge_suggestions" ADD CONSTRAINT "customer_merge_suggestions_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_merge_suggestions" ADD CONSTRAINT "customer_merge_suggestions_primary_customer_id_customers_id_fk" FOREIGN KEY ("primary_customer_id") REFERENCES "public"."customers"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "customer_merge_suggestions" ADD CONSTRAINT "customer_merge_suggestions_duplicate_customer_id_customers_id_fk" FOREIGN KEY ("duplicate_customer_id") REFERENCES "public"."customers"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "customer_merge_suggestions" ADD CONSTRAINT "customer_merge_suggestions_resolved_by_users_id_fk" FOREIGN KEY ("resolved_by") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "import_job_results" ADD CONSTRAINT "import_job_results_import_job_id_import_jobs_id_fk" FOREIGN KEY ("import_job_id") REFERENCES "public"."import_jobs"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "import_jobs" ADD CONSTRAINT "import_jobs_tenant_id_tenants_id_fk" FOREIGN KEY ("tenant_id") REFERENCES "public"."tenants"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "import_jobs" ADD CONSTRAINT "import_jobs_created_by_users_id_fk" FOREIGN KEY ("created_by") REFERENCES "public"."users"("id") ON DELETE restrict ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "recurring_invoice_templates" ADD CONSTRAINT "recurring_invoice_templates_tenant_id_tenants_id_fk" FOREIGN KEY ("tenant_id") REFERENCES "public"."tenants"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "recurring_invoice_templates" ADD CONSTRAINT "recurring_invoice_templates_customer_id_customers_id_fk" FOREIGN KEY ("customer_id") REFERENCES "public"."customers"("id") ON DELETE restrict ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "recurring_invoice_templates" ADD CONSTRAINT "recurring_invoice_templates_created_by_users_id_fk" FOREIGN KEY ("created_by") REFERENCES "public"."users"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "project_milestones" ADD CONSTRAINT "project_milestones_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_milestones" ADD CONSTRAINT "project_milestones_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_milestones" ADD CONSTRAINT "project_milestones_created_by_users_id_fk" FOREIGN KEY ("created_by") REFERENCES "public"."users"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "lead_activities" ADD CONSTRAINT "lead_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 "lead_activities" ADD CONSTRAINT "lead_activities_lead_id_leads_id_fk" FOREIGN KEY ("lead_id") REFERENCES "public"."leads"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "lead_activities" ADD CONSTRAINT "lead_activities_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 "lead_form_submissions" ADD CONSTRAINT "lead_form_submissions_tenant_id_tenants_id_fk" FOREIGN KEY ("tenant_id") REFERENCES "public"."tenants"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "lead_form_submissions" ADD CONSTRAINT "lead_form_submissions_form_id_lead_forms_id_fk" FOREIGN KEY ("form_id") REFERENCES "public"."lead_forms"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "lead_form_submissions" ADD CONSTRAINT "lead_form_submissions_lead_id_leads_id_fk" FOREIGN KEY ("lead_id") REFERENCES "public"."leads"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "lead_forms" ADD CONSTRAINT "lead_forms_tenant_id_tenants_id_fk" FOREIGN KEY ("tenant_id") REFERENCES "public"."tenants"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "lead_forms" ADD CONSTRAINT "lead_forms_created_by_users_id_fk" FOREIGN KEY ("created_by") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "lead_webhooks" ADD CONSTRAINT "lead_webhooks_tenant_id_tenants_id_fk" FOREIGN KEY ("tenant_id") REFERENCES "public"."tenants"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "leads" ADD CONSTRAINT "leads_tenant_id_tenants_id_fk" FOREIGN KEY ("tenant_id") REFERENCES "public"."tenants"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "leads" ADD CONSTRAINT "leads_assigned_to_users_id_fk" FOREIGN KEY ("assigned_to") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "leads" ADD CONSTRAINT "leads_customer_id_customers_id_fk" FOREIGN KEY ("customer_id") REFERENCES "public"."customers"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "pipeline_stages" ADD CONSTRAINT "pipeline_stages_tenant_id_tenants_id_fk" FOREIGN KEY ("tenant_id") REFERENCES "public"."tenants"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "sla_policies" ADD CONSTRAINT "sla_policies_tenant_id_tenants_id_fk" FOREIGN KEY ("tenant_id") REFERENCES "public"."tenants"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "integration_sync_logs" ADD CONSTRAINT "integration_sync_logs_tenant_id_tenants_id_fk" FOREIGN KEY ("tenant_id") REFERENCES "public"."tenants"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "integration_sync_logs" ADD CONSTRAINT "integration_sync_logs_triggered_by_users_id_fk" FOREIGN KEY ("triggered_by") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
CREATE INDEX "idx_attachments_tenant_created" ON "attachments" USING btree ("tenant_id","created_at");--> statement-breakpoint
CREATE INDEX "contractor_assignments_contractor_project_idx" ON "contractor_assignments" USING btree ("contractor_id","project_id");--> statement-breakpoint
CREATE INDEX "contractor_assignments_tenant_contractor_idx" ON "contractor_assignments" USING btree ("tenant_id","contractor_id");--> statement-breakpoint
CREATE INDEX "contractors_tenant_idx" ON "contractors" USING btree ("tenant_id");--> statement-breakpoint
CREATE INDEX "contractors_tenant_active_idx" ON "contractors" USING btree ("tenant_id","active");--> statement-breakpoint
CREATE INDEX "payout_bill_lines_bill_idx" ON "payout_bill_lines" USING btree ("bill_id");--> statement-breakpoint
CREATE INDEX "payout_bill_lines_time_entry_idx" ON "payout_bill_lines" USING btree ("time_entry_id");--> statement-breakpoint
CREATE INDEX "payout_bills_tenant_contractor_idx" ON "payout_bills" USING btree ("tenant_id","contractor_id");--> statement-breakpoint
CREATE INDEX "payout_bills_tenant_status_idx" ON "payout_bills" USING btree ("tenant_id","status");--> statement-breakpoint
CREATE INDEX "payout_bills_period_idx" ON "payout_bills" USING btree ("tenant_id","period_start","period_end");--> statement-breakpoint
CREATE INDEX "withholding_tax_certs_contractor_idx" ON "withholding_tax_certificates" USING btree ("contractor_id");--> statement-breakpoint
CREATE INDEX "withholding_tax_certs_tenant_year_idx" ON "withholding_tax_certificates" USING btree ("tenant_id","tax_year");--> statement-breakpoint
CREATE UNIQUE INDEX "idx_cms_tenant_pair" ON "customer_merge_suggestions" USING btree ("tenant_id","primary_customer_id","duplicate_customer_id");--> statement-breakpoint
CREATE INDEX "idx_import_job_results_job" ON "import_job_results" USING btree ("import_job_id","row_number");--> statement-breakpoint
CREATE INDEX "idx_import_jobs_tenant_status" ON "import_jobs" USING btree ("tenant_id","status");--> statement-breakpoint
CREATE INDEX "idx_project_milestones_project" ON "project_milestones" USING btree ("project_id");--> statement-breakpoint
CREATE INDEX "idx_project_milestones_tenant_project" ON "project_milestones" USING btree ("tenant_id","project_id");--> statement-breakpoint
CREATE INDEX "idx_lead_activities_tenant_lead" ON "lead_activities" USING btree ("tenant_id","lead_id");--> statement-breakpoint
CREATE INDEX "idx_lead_form_submissions_form" ON "lead_form_submissions" USING btree ("form_id","created_at");--> statement-breakpoint
CREATE INDEX "idx_lead_form_submissions_lead" ON "lead_form_submissions" USING btree ("lead_id");--> statement-breakpoint
CREATE UNIQUE INDEX "idx_lead_forms_tenant_slug_uniq" ON "lead_forms" USING btree ("tenant_id","slug");--> statement-breakpoint
CREATE INDEX "idx_lead_webhooks_tenant" ON "lead_webhooks" USING btree ("tenant_id");--> statement-breakpoint
CREATE INDEX "idx_leads_tenant_created" ON "leads" USING btree ("tenant_id","created_at");--> statement-breakpoint
CREATE UNIQUE INDEX "idx_pipeline_stages_tenant_slug_uniq" ON "pipeline_stages" USING btree ("tenant_id","slug");--> statement-breakpoint
CREATE INDEX "idx_pipeline_stages_tenant_position" ON "pipeline_stages" USING btree ("tenant_id","position");--> statement-breakpoint
CREATE INDEX "idx_isl_tenant" ON "integration_sync_logs" USING btree ("tenant_id","provider","created_at" DESC NULLS LAST);--> statement-breakpoint
ALTER TABLE "expenses" ADD CONSTRAINT "expenses_customer_id_customers_id_fk" FOREIGN KEY ("customer_id") REFERENCES "public"."customers"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "expenses" ADD CONSTRAINT "expenses_invoice_id_invoices_id_fk" FOREIGN KEY ("invoice_id") REFERENCES "public"."invoices"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "time_entries" ADD CONSTRAINT "time_entries_invoice_id_invoices_id_fk" FOREIGN KEY ("invoice_id") REFERENCES "public"."invoices"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "invoice_lines" ADD CONSTRAINT "invoice_lines_expense_id_expenses_id_fk" FOREIGN KEY ("expense_id") REFERENCES "public"."expenses"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "invoices" ADD CONSTRAINT "invoices_approved_by_users_id_fk" FOREIGN KEY ("approved_by") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "invoices" ADD CONSTRAINT "invoices_recurring_template_id_recurring_invoice_templates_id_fk" FOREIGN KEY ("recurring_template_id") REFERENCES "public"."recurring_invoice_templates"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "adapter_credentials" ADD CONSTRAINT "adapter_credentials_adapter_id_check" CHECK ("adapter_credentials"."adapter_id" IN ('gmail','outlook','telegram','slack','whatsapp','smtp','trello','asana','jira','monday','clickup') OR "adapter_credentials"."adapter_id" LIKE 'invoice:%');--> statement-breakpoint
ALTER TABLE "tenant_settings" ADD CONSTRAINT "tenant_settings_project_default_billing_type_check" CHECK ("tenant_settings"."project_default_billing_type" IN ('fixed','hourly','retainer'));--> statement-breakpoint
ALTER TABLE "tenant_settings" ADD CONSTRAINT "tenant_settings_project_default_time_rounding_check" CHECK ("tenant_settings"."project_default_time_rounding_minutes" IN (0,5,15,30));--> statement-breakpoint
ALTER TABLE "tenant_settings" ADD CONSTRAINT "tenant_settings_project_default_budget_alert_pct_check" CHECK ("tenant_settings"."project_default_budget_alert_pct" BETWEEN 1 AND 100);--> statement-breakpoint
ALTER TABLE "invoices" ADD CONSTRAINT "chk_invoice_requires_customer" CHECK ("invoices"."is_template" = true OR "invoices"."customer_id" IS NOT NULL);
-- ── Wave-7 hand-crafted indexes (partial/GIN/expression — drizzle snapshot-truncation) ──

-- pg_trgm extension (leaf9: customer name fuzzy search)
CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- leaf2: recurring-invoice-generator
CREATE INDEX idx_rit_next_gen ON recurring_invoice_templates (next_generation_date) WHERE status = 'active';
CREATE INDEX idx_rit_tenant ON recurring_invoice_templates (tenant_id);
CREATE INDEX idx_rit_customer ON recurring_invoice_templates (customer_id);

-- leaf3: time-to-invoice / expense-to-invoice
CREATE INDEX idx_time_entries_unbilled ON time_entries (tenant_id, project_id) WHERE invoice_id IS NULL AND billable = true;
CREATE INDEX idx_expenses_unbilled_billable ON expenses (tenant_id, customer_id) WHERE invoice_id IS NULL AND billable = true;

-- leaf4: project milestones
CREATE INDEX idx_project_milestones_incomplete ON project_milestones (project_id, due_date) WHERE status NOT IN ('completed','cancelled');
CREATE INDEX idx_projects_active ON projects (tenant_id, created_at DESC) WHERE archived_at IS NULL;
CREATE INDEX idx_projects_archived ON projects (tenant_id, archived_at DESC) WHERE archived_at IS NOT NULL;

-- leaf5: contractor payouts / withholding
CREATE INDEX idx_contractors_tenant_active ON contractors (tenant_id) WHERE active = true;
CREATE INDEX idx_payout_bills_unpaid ON payout_bills (tenant_id, created_at DESC) WHERE status = 'PENDING';
CREATE INDEX idx_time_entries_locked_contractor ON time_entries (contractor_id, started_at) WHERE locked_at IS NOT NULL AND contractor_id IS NOT NULL;
CREATE INDEX idx_contractors_cert_expiry ON contractors (tenant_id, withholding_certificate_expiry) WHERE active = true AND withholding_certificate_expiry IS NOT NULL;

-- leaf6: marketing leads pipeline
CREATE INDEX idx_leads_tenant_stage ON leads (tenant_id, stage, stage_position);
CREATE INDEX idx_leads_assigned ON leads (tenant_id, assigned_to) WHERE assigned_to IS NOT NULL;
CREATE INDEX idx_leads_customer ON leads (customer_id) WHERE customer_id IS NOT NULL;
CREATE INDEX idx_lead_forms_slug ON lead_forms (slug, tenant_id);
CREATE INDEX idx_lead_activities_lead ON lead_activities (lead_id, created_at DESC);

-- leaf8: data-import
CREATE INDEX import_jobs_tenant_created ON import_jobs (tenant_id, created_at DESC);
CREATE INDEX import_job_results_job ON import_job_results (import_job_id, status);
CREATE INDEX import_job_results_errors ON import_job_results (import_job_id) WHERE status = 'error';

-- leaf8: unified attachments
CREATE INDEX idx_attachments_entity ON attachments (entity_type, entity_id) WHERE deleted_at IS NULL;
CREATE INDEX idx_attachments_uploader ON attachments (tenant_id, uploader_id) WHERE deleted_at IS NULL;

-- leaf9: customer dedup / trgm search / dashboard
CREATE INDEX idx_cms_tenant_pending ON customer_merge_suggestions (tenant_id, created_at DESC) WHERE status = 'pending';
CREATE INDEX idx_customers_email_lower ON customers (tenant_id, lower(email)) WHERE email IS NOT NULL;
CREATE INDEX idx_customers_name_trgm ON customers USING gin (name gin_trgm_ops);
CREATE INDEX idx_isl_tenant_entity ON integration_sync_logs (tenant_id, entity_type, entity_id, created_at DESC);
--> statement-breakpoint
CREATE INDEX IF NOT EXISTS idx_invoices_recurring_template_id ON invoices(recurring_template_id) WHERE recurring_template_id IS NOT NULL;
