CREATE TABLE "invoice_lines" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"invoice_id" uuid NOT NULL,
	"tenant_id" uuid NOT NULL,
	"description" text NOT NULL,
	"quantity" numeric(10, 3) DEFAULT '1' NOT NULL,
	"unit_price" numeric(12, 2) NOT NULL,
	"discount_pct" numeric(5, 2) DEFAULT '0' NOT NULL,
	"line_total" numeric(12, 2) NOT NULL,
	"taxable" boolean DEFAULT true NOT NULL,
	"position" integer NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "invoice_sequences" (
	"tenant_id" uuid NOT NULL,
	"type" text NOT NULL,
	"last_number" integer DEFAULT 0 NOT NULL,
	"prefix" text DEFAULT '' NOT NULL,
	CONSTRAINT "invoice_sequences_tenant_id_type_pk" PRIMARY KEY("tenant_id","type"),
	CONSTRAINT "invoice_sequences_type_check" CHECK ("invoice_sequences"."type" IN ('invoice','proforma'))
);
--> statement-breakpoint
CREATE TABLE "invoices" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"tenant_id" uuid NOT NULL,
	"customer_id" uuid NOT NULL,
	"project_id" uuid,
	"invoice_number" text,
	"proforma_number" text,
	"status" text DEFAULT 'DRAFT' NOT NULL,
	"currency" text DEFAULT 'ILS' NOT NULL,
	"issue_date" date,
	"tax_issue_date" date,
	"due_date" date,
	"vat_rate" numeric(5, 4),
	"subtotal" numeric(12, 2) DEFAULT '0' NOT NULL,
	"vat_amount" numeric(12, 2) DEFAULT '0' NOT NULL,
	"total" numeric(12, 2) DEFAULT '0' NOT NULL,
	"amount_paid" numeric(12, 2) DEFAULT '0' NOT NULL,
	"notes" text,
	"source" text DEFAULT 'manual' NOT NULL,
	"sent_at" timestamp with time zone,
	"approved_at" timestamp with time zone,
	"tax_issued_at" timestamp with time zone,
	"paid_at" timestamp with time zone,
	"external_id" text,
	"external_provider" text,
	"void_reason" text,
	"voided_at" timestamp with time zone,
	"voided_by" uuid,
	"parent_invoice_id" uuid,
	"html_snapshot_url" 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 "invoices_status_check" CHECK ("invoices"."status" IN ('DRAFT','SENT','APPROVED','REJECTED','TAX_ISSUED','PAID','PARTIALLY_PAID','VOID','WRITTEN_OFF','BAD_DEBT')),
	CONSTRAINT "invoices_source_check" CHECK ("invoices"."source" IN ('manual','retainer','hourly_auto','fixed_deposit','credit_note','auto_charge'))
);
--> statement-breakpoint
CREATE TABLE "project_template_tasks" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"template_id" uuid NOT NULL,
	"title" text NOT NULL,
	"description" text,
	"phase" text,
	"position" integer DEFAULT 0 NOT NULL,
	"relative_start_days" integer,
	"relative_due_days" integer,
	"is_milestone" boolean DEFAULT false NOT NULL,
	"checklist_items" jsonb DEFAULT '[]'::jsonb NOT NULL,
	"estimated_hours" numeric(6, 2),
	"assigned_role" text,
	"tags" text[],
	CONSTRAINT "project_template_tasks_assigned_role_check" CHECK ("project_template_tasks"."assigned_role" IS NULL OR "project_template_tasks"."assigned_role" IN ('MEMBER','ADMIN','CONTRACTOR'))
);
--> statement-breakpoint
CREATE TABLE "project_templates" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"tenant_id" uuid,
	"name" text NOT NULL,
	"description" text,
	"category" text,
	"estimated_days" integer,
	"billing_type" text,
	"thumbnail_emoji" text DEFAULT '📋' NOT NULL,
	"is_public" boolean DEFAULT false NOT NULL,
	"usage_count" integer DEFAULT 0 NOT NULL,
	"created_by" uuid,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	"updated_at" timestamp with time zone DEFAULT now() NOT NULL,
	CONSTRAINT "project_templates_category_check" CHECK ("project_templates"."category" IS NULL OR "project_templates"."category" IN ('design','development','marketing','consulting','other')),
	CONSTRAINT "project_templates_billing_type_check" CHECK ("project_templates"."billing_type" IS NULL OR "project_templates"."billing_type" IN ('fixed','hourly','retainer'))
);
--> statement-breakpoint
CREATE TABLE "ticket_categories" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"tenant_id" uuid NOT NULL,
	"name" text NOT NULL,
	"color" text,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "ticket_message_attachments" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"message_id" uuid NOT NULL,
	"tenant_id" uuid NOT NULL,
	"filename" text NOT NULL,
	"r2_key" text NOT NULL,
	"url" text NOT NULL,
	"size_bytes" integer NOT NULL,
	"mime_type" text NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "ticket_messages" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"ticket_id" uuid NOT NULL,
	"tenant_id" uuid NOT NULL,
	"author_type" text NOT NULL,
	"author_id" uuid,
	"author_name" text,
	"content" text NOT NULL,
	"source" text DEFAULT 'web' NOT NULL,
	"deleted_at" timestamp with time zone,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	CONSTRAINT "ticket_messages_author_type_check" CHECK ("ticket_messages"."author_type" IN ('staff','customer','system')),
	CONSTRAINT "ticket_messages_source_check" CHECK ("ticket_messages"."source" IN ('web','email','telegram','whatsapp','portal'))
);
--> statement-breakpoint
CREATE TABLE "tickets" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"tenant_id" uuid NOT NULL,
	"customer_id" uuid,
	"contact_id" uuid,
	"title" text NOT NULL,
	"description" text DEFAULT '' NOT NULL,
	"status" text DEFAULT 'open' NOT NULL,
	"priority" text DEFAULT 'medium' NOT NULL,
	"category_id" uuid,
	"assignee_id" uuid,
	"source" text DEFAULT 'web' NOT NULL,
	"external_id" text,
	"external_thread_id" text,
	"resolved_at" timestamp with time zone,
	"closed_at" timestamp with time zone,
	"deleted_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 "tickets_status_check" CHECK ("tickets"."status" IN ('open','in_progress','pending_customer','resolved','closed')),
	CONSTRAINT "tickets_priority_check" CHECK ("tickets"."priority" IN ('low','medium','high','urgent')),
	CONSTRAINT "tickets_source_check" CHECK ("tickets"."source" IN ('web','email','telegram','whatsapp','portal'))
);
--> statement-breakpoint
ALTER TABLE "tenant_settings" ADD COLUMN "default_payment_terms_days" integer DEFAULT 30 NOT NULL;--> statement-breakpoint
ALTER TABLE "tasks" ADD COLUMN "checklist_items" jsonb DEFAULT '[]'::jsonb;--> statement-breakpoint
ALTER TABLE "tasks" ADD COLUMN "phase" text;--> statement-breakpoint
ALTER TABLE "invoice_lines" ADD CONSTRAINT "invoice_lines_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_lines" ADD CONSTRAINT "invoice_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 "invoice_sequences" ADD CONSTRAINT "invoice_sequences_tenant_id_tenants_id_fk" FOREIGN KEY ("tenant_id") REFERENCES "public"."tenants"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "invoices" ADD CONSTRAINT "invoices_tenant_id_tenants_id_fk" FOREIGN KEY ("tenant_id") REFERENCES "public"."tenants"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "invoices" ADD CONSTRAINT "invoices_customer_id_customers_id_fk" FOREIGN KEY ("customer_id") REFERENCES "public"."customers"("id") ON DELETE restrict ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "invoices" ADD CONSTRAINT "invoices_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 "invoices" ADD CONSTRAINT "invoices_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 "invoices" ADD CONSTRAINT "invoices_parent_invoice_id_invoices_id_fk" FOREIGN KEY ("parent_invoice_id") REFERENCES "public"."invoices"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "invoices" ADD CONSTRAINT "invoices_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_template_tasks" ADD CONSTRAINT "project_template_tasks_template_id_project_templates_id_fk" FOREIGN KEY ("template_id") REFERENCES "public"."project_templates"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "project_templates" ADD CONSTRAINT "project_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 "project_templates" ADD CONSTRAINT "project_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 "ticket_categories" ADD CONSTRAINT "ticket_categories_tenant_id_tenants_id_fk" FOREIGN KEY ("tenant_id") REFERENCES "public"."tenants"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "ticket_message_attachments" ADD CONSTRAINT "ticket_message_attachments_message_id_ticket_messages_id_fk" FOREIGN KEY ("message_id") REFERENCES "public"."ticket_messages"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "ticket_message_attachments" ADD CONSTRAINT "ticket_message_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 "ticket_messages" ADD CONSTRAINT "ticket_messages_ticket_id_tickets_id_fk" FOREIGN KEY ("ticket_id") REFERENCES "public"."tickets"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "ticket_messages" ADD CONSTRAINT "ticket_messages_tenant_id_tenants_id_fk" FOREIGN KEY ("tenant_id") REFERENCES "public"."tenants"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "ticket_messages" ADD CONSTRAINT "ticket_messages_author_id_users_id_fk" FOREIGN KEY ("author_id") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "tickets" ADD CONSTRAINT "tickets_tenant_id_tenants_id_fk" FOREIGN KEY ("tenant_id") REFERENCES "public"."tenants"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "tickets" ADD CONSTRAINT "tickets_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 "tickets" ADD CONSTRAINT "tickets_contact_id_customer_contacts_id_fk" FOREIGN KEY ("contact_id") REFERENCES "public"."customer_contacts"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "tickets" ADD CONSTRAINT "tickets_category_id_ticket_categories_id_fk" FOREIGN KEY ("category_id") REFERENCES "public"."ticket_categories"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "tickets" ADD CONSTRAINT "tickets_assignee_id_users_id_fk" FOREIGN KEY ("assignee_id") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
CREATE INDEX "idx_invoice_lines_invoice" ON "invoice_lines" USING btree ("invoice_id","position");--> statement-breakpoint
CREATE INDEX "idx_invoice_lines_tenant" ON "invoice_lines" USING btree ("tenant_id");--> statement-breakpoint
CREATE INDEX "idx_invoices_tenant_status" ON "invoices" USING btree ("tenant_id","status","created_at" DESC NULLS LAST);--> statement-breakpoint
CREATE INDEX "idx_invoices_tenant_customer" ON "invoices" USING btree ("tenant_id","customer_id");--> statement-breakpoint
CREATE INDEX "idx_invoices_tenant_project" ON "invoices" USING btree ("tenant_id","project_id");--> statement-breakpoint
CREATE UNIQUE INDEX "idx_invoices_invoice_number_uniq" ON "invoices" USING btree ("tenant_id","invoice_number");--> statement-breakpoint
CREATE UNIQUE INDEX "idx_invoices_proforma_number_uniq" ON "invoices" USING btree ("tenant_id","proforma_number");--> statement-breakpoint
CREATE INDEX "idx_template_tasks_template" ON "project_template_tasks" USING btree ("template_id","position");--> statement-breakpoint
CREATE INDEX "idx_project_templates_tenant" ON "project_templates" USING btree ("tenant_id");--> statement-breakpoint
CREATE UNIQUE INDEX "idx_ticket_categories_tenant_name_uniq" ON "ticket_categories" USING btree ("tenant_id","name");--> statement-breakpoint
CREATE INDEX "idx_ticket_message_attachments_message" ON "ticket_message_attachments" USING btree ("message_id");--> statement-breakpoint
CREATE INDEX "idx_ticket_messages_ticket_created" ON "ticket_messages" USING btree ("ticket_id","created_at");--> statement-breakpoint
CREATE INDEX "idx_ticket_messages_tenant" ON "ticket_messages" USING btree ("tenant_id");--> statement-breakpoint
CREATE INDEX "idx_tickets_tenant_status" ON "tickets" USING btree ("tenant_id","status","created_at");--> statement-breakpoint
CREATE INDEX "idx_tickets_tenant_assignee" ON "tickets" USING btree ("tenant_id","assignee_id");--> statement-breakpoint
CREATE INDEX "idx_tickets_tenant_customer" ON "tickets" USING btree ("tenant_id","customer_id");--> statement-breakpoint
CREATE INDEX "idx_tickets_tenant_source" ON "tickets" USING btree ("tenant_id","source");--> statement-breakpoint
-- wave-6 raw_ddl: partial/expression indexes not modelable by drizzle-kit
-- crm-support-center: partial unique index on external thread per source
CREATE UNIQUE INDEX IF NOT EXISTS "idx_tickets_external_thread_uniq" ON "tickets" ("tenant_id","source","external_thread_id") WHERE external_thread_id IS NOT NULL AND deleted_at IS NULL;--> statement-breakpoint
-- crm-support-center: partial index for active tickets ordered by created_at
CREATE INDEX IF NOT EXISTS "idx_tickets_created_desc" ON "tickets" ("tenant_id","created_at" DESC) WHERE deleted_at IS NULL;