CREATE TABLE "audit_log" (
	"id" uuid NOT NULL DEFAULT gen_random_uuid(),
	"tenant_id" uuid NOT NULL,
	"actor_id" uuid,
	"actor_type" text NOT NULL DEFAULT 'user'
	  CHECK ("actor_type" IN ('user', 'system', 'api_key', 'portal_customer')),
	"api_key_id" uuid,
	"entity_type" text NOT NULL,
	"entity_id" uuid NOT NULL,
	"action" text NOT NULL,
	"changes" jsonb,
	"request_id" text,
	"ip" text,
	"created_at" timestamp with time zone NOT NULL DEFAULT now(),
	PRIMARY KEY ("id", "created_at")
) PARTITION BY RANGE ("created_at");
--> statement-breakpoint
CREATE TABLE "field_permission_rules" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"tenant_id" uuid NOT NULL,
	"entity_type" text NOT NULL,
	"field_name" text NOT NULL,
	"role" text NOT NULL,
	"permission" text DEFAULT 'visible' NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "tenant_api_keys" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"tenant_id" uuid NOT NULL,
	"name" text NOT NULL,
	"key_prefix" text NOT NULL,
	"key_hash" text NOT NULL,
	"scopes" jsonb DEFAULT '[]'::jsonb NOT NULL,
	"expires_at" timestamp with time zone,
	"last_used_at" timestamp with time zone,
	"created_by" uuid NOT NULL,
	"revoked_at" timestamp with time zone,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	CONSTRAINT "tenant_api_keys_key_hash_unique" UNIQUE("key_hash")
);
--> statement-breakpoint
CREATE TABLE "webhook_deliveries" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"tenant_id" uuid NOT NULL,
	"endpoint_id" uuid NOT NULL,
	"event_type" text NOT NULL,
	"status" text DEFAULT 'pending' NOT NULL,
	"request_body" text,
	"response_status" integer,
	"response_body" text,
	"latency_ms" integer,
	"attempt" integer DEFAULT 1 NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	CONSTRAINT "webhook_deliveries_status_check" CHECK ("webhook_deliveries"."status" IN ('pending','delivered','failed','test'))
);
--> statement-breakpoint
CREATE TABLE "webhook_delivery_log" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"tenant_id" uuid,
	"endpoint_id" uuid,
	"source" text NOT NULL,
	"source_ip" text,
	"event_type" text,
	"status" text NOT NULL,
	"signature_valid" boolean DEFAULT false NOT NULL,
	"raw_body" text,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	CONSTRAINT "webhook_delivery_log_status_check" CHECK ("webhook_delivery_log"."status" IN ('accepted','rejected_invalid_signature','rejected_rate_limited'))
);
--> statement-breakpoint
CREATE TABLE "webhook_endpoints" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"tenant_id" uuid NOT NULL,
	"url" text NOT NULL,
	"events" jsonb DEFAULT '[]'::jsonb NOT NULL,
	"secret_encrypted" text NOT NULL,
	"is_active" boolean DEFAULT true NOT NULL,
	"description" text,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	"updated_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "tenant_export_jobs" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"tenant_id" uuid NOT NULL,
	"status" text DEFAULT 'PENDING' NOT NULL,
	"r2_key" text,
	"created_by" uuid,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	"completed_at" timestamp with time zone,
	CONSTRAINT "tenant_export_jobs_status_check" CHECK ("tenant_export_jobs"."status" IN ('PENDING', 'PROCESSING', 'DONE', 'FAILED'))
);
--> statement-breakpoint
CREATE TABLE "tenant_email_templates" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"tenant_id" uuid NOT NULL,
	"template_key" text NOT NULL,
	"subject" text NOT NULL,
	"body_html" text NOT NULL,
	"body_text" text NOT NULL,
	"is_active" 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
);
--> statement-breakpoint
CREATE TABLE "vendors" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"tenant_id" uuid NOT NULL,
	"name" text NOT NULL,
	"tax_id" text,
	"withholding_rate" numeric(5, 4),
	"withholding_cert_number" text,
	"withholding_cert_expiry" date,
	"withholding_cert_r2_key" text,
	"default_category" text,
	"default_vat_deductible" boolean DEFAULT true NOT NULL,
	"payment_terms_days" integer DEFAULT 30 NOT NULL,
	"email" text,
	"phone" text,
	"address" text,
	"notes" text,
	"is_archived" boolean DEFAULT false NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	"updated_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
ALTER TABLE "tasks" DROP CONSTRAINT "tasks_source_check";--> statement-breakpoint
ALTER TABLE "expenses" ADD COLUMN "approval_status" text DEFAULT 'not_required' NOT NULL;--> statement-breakpoint
ALTER TABLE "expenses" ADD COLUMN "approved_by" uuid;--> statement-breakpoint
ALTER TABLE "expenses" ADD COLUMN "approved_at" timestamp with time zone;--> statement-breakpoint
ALTER TABLE "expenses" ADD COLUMN "approval_note" text;--> statement-breakpoint
ALTER TABLE "expenses" ADD COLUMN "vendor_id" uuid;--> statement-breakpoint
ALTER TABLE "tenant_settings" ADD COLUMN "contractor_portal_enabled" boolean DEFAULT true NOT NULL;--> statement-breakpoint
ALTER TABLE "tenant_settings" ADD COLUMN "contractor_portal_show_billing" boolean DEFAULT false NOT NULL;--> statement-breakpoint
ALTER TABLE "tenant_settings" ADD COLUMN "contractor_approval_deadline_days" integer DEFAULT 7 NOT NULL;--> statement-breakpoint
ALTER TABLE "tenant_settings" ADD COLUMN "contractor_payout_period" text DEFAULT 'monthly' NOT NULL;--> statement-breakpoint
ALTER TABLE "tenant_settings" ADD COLUMN "contractor_withholding_note" text;--> statement-breakpoint
ALTER TABLE "field_permission_rules" ADD CONSTRAINT "field_permission_rules_tenant_id_tenants_id_fk" FOREIGN KEY ("tenant_id") REFERENCES "public"."tenants"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "tenant_api_keys" ADD CONSTRAINT "tenant_api_keys_tenant_id_tenants_id_fk" FOREIGN KEY ("tenant_id") REFERENCES "public"."tenants"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "tenant_api_keys" ADD CONSTRAINT "tenant_api_keys_created_by_users_id_fk" FOREIGN KEY ("created_by") REFERENCES "public"."users"("id") ON DELETE restrict ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "webhook_deliveries" ADD CONSTRAINT "webhook_deliveries_tenant_id_tenants_id_fk" FOREIGN KEY ("tenant_id") REFERENCES "public"."tenants"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "webhook_deliveries" ADD CONSTRAINT "webhook_deliveries_endpoint_id_webhook_endpoints_id_fk" FOREIGN KEY ("endpoint_id") REFERENCES "public"."webhook_endpoints"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "webhook_delivery_log" ADD CONSTRAINT "webhook_delivery_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 "webhook_delivery_log" ADD CONSTRAINT "webhook_delivery_log_endpoint_id_webhook_endpoints_id_fk" FOREIGN KEY ("endpoint_id") REFERENCES "public"."webhook_endpoints"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "webhook_endpoints" ADD CONSTRAINT "webhook_endpoints_tenant_id_tenants_id_fk" FOREIGN KEY ("tenant_id") REFERENCES "public"."tenants"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "tenant_email_templates" ADD CONSTRAINT "tenant_email_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 "vendors" ADD CONSTRAINT "vendors_tenant_id_tenants_id_fk" FOREIGN KEY ("tenant_id") REFERENCES "public"."tenants"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
CREATE UNIQUE INDEX "idx_fpr_unique" ON "field_permission_rules" USING btree ("tenant_id","entity_type","field_name","role");--> statement-breakpoint
CREATE INDEX "idx_fpr_tenant" ON "field_permission_rules" USING btree ("tenant_id","entity_type");--> statement-breakpoint
CREATE INDEX "idx_tenant_api_keys_tenant" ON "tenant_api_keys" USING btree ("tenant_id","created_at" DESC NULLS LAST);--> statement-breakpoint
CREATE INDEX "idx_tenant_api_keys_hash" ON "tenant_api_keys" USING btree ("key_hash");--> statement-breakpoint
CREATE INDEX "idx_webhook_deliveries_tenant" ON "webhook_deliveries" USING btree ("tenant_id","created_at" DESC NULLS LAST);--> statement-breakpoint
CREATE INDEX "idx_wdl_tenant_created" ON "webhook_delivery_log" USING btree ("tenant_id","created_at" DESC NULLS LAST);--> statement-breakpoint
CREATE INDEX "idx_webhook_endpoints_tenant" ON "webhook_endpoints" USING btree ("tenant_id","created_at" DESC NULLS LAST);--> statement-breakpoint
CREATE UNIQUE INDEX "idx_tenant_email_templates_key" ON "tenant_email_templates" USING btree ("tenant_id","template_key");--> statement-breakpoint
CREATE INDEX "idx_vendors_tenant" ON "vendors" USING btree ("tenant_id","is_archived","name");--> statement-breakpoint
ALTER TABLE "expenses" ADD CONSTRAINT "expenses_approved_by_users_id_fk" FOREIGN KEY ("approved_by") REFERENCES "public"."users"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "expenses" ADD CONSTRAINT "expenses_vendor_id_vendors_id_fk" FOREIGN KEY ("vendor_id") REFERENCES "public"."vendors"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "tasks" ADD CONSTRAINT "tasks_source_check" CHECK ("tasks"."source" IN ('manual','email','telegram','slack','whatsapp','trello','asana','jira','monday','clickup','api'));--> statement-breakpoint
ALTER TABLE "expenses" ADD CONSTRAINT "expenses_approval_status_check" CHECK ("expenses"."approval_status" IN ('not_required', 'pending', 'approved', 'rejected'));--> statement-breakpoint
ALTER TABLE "tenant_settings" DROP CONSTRAINT IF EXISTS "tenant_settings_contractor_payout_period_check";--> statement-breakpoint
ALTER TABLE "tenant_settings" ADD CONSTRAINT "tenant_settings_contractor_payout_period_check" CHECK ("tenant_settings"."contractor_payout_period" IN ('weekly', 'biweekly', 'monthly'));
-- ── audit_log partitioning setup (managed via raw SQL only) ──────────────────

-- Initial (current-month) partition; cron creates subsequent months.
CREATE TABLE "audit_log_2026_06" PARTITION OF "audit_log"
  FOR VALUES FROM ('2026-06-01') TO ('2026-07-01');

CREATE INDEX "audit_log_tenant_entity_idx"
  ON "audit_log" ("tenant_id", "entity_type", "entity_id", "created_at" DESC);

CREATE OR REPLACE FUNCTION audit_log_immutable() RETURNS trigger AS $$
BEGIN
  RAISE EXCEPTION 'audit_log is immutable: % not permitted', TG_OP;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER "audit_log_no_update"
  BEFORE UPDATE ON "audit_log" FOR EACH ROW EXECUTE FUNCTION audit_log_immutable();
CREATE TRIGGER "audit_log_no_delete"
  BEFORE DELETE ON "audit_log" FOR EACH ROW EXECUTE FUNCTION audit_log_immutable();

-- ── Additional raw-SQL indexes (GIN, composite, partial) ─────────────────────

-- GIN index for contains-filter on webhook events (cannot be expressed in Drizzle index builder)
CREATE INDEX IF NOT EXISTS "webhook_endpoints_events_gin"
  ON "webhook_endpoints" USING GIN ("events");

-- Composite index for paginated delivery log per endpoint
CREATE INDEX IF NOT EXISTS "webhook_deliveries_endpoint_created_idx"
  ON "webhook_deliveries" ("endpoint_id", "created_at" DESC);

CREATE INDEX IF NOT EXISTS "tenant_export_jobs_tenant_idx"
  ON "tenant_export_jobs" ("tenant_id", "created_at" DESC);

-- Partial index for approval queue (WHERE clause not expressible in Drizzle)
CREATE INDEX IF NOT EXISTS "idx_expenses_approval_queue"
  ON "expenses" ("tenant_id", "expense_date" DESC)
  WHERE "approval_status" = 'pending';

-- Partial unique index (WHERE clause not expressible in Drizzle)
CREATE UNIQUE INDEX IF NOT EXISTS "idx_vendors_tenant_tax_id"
  ON "vendors" ("tenant_id", "tax_id")
  WHERE "tax_id" IS NOT NULL;

-- Partial index for vendor lookup on expenses
CREATE INDEX IF NOT EXISTS "idx_expenses_vendor"
  ON "expenses" ("tenant_id", "vendor_id")
  WHERE "vendor_id" IS NOT NULL;

-- ── recurring_expense_templates.vendor_id (guarded — table may not exist yet) ─

DO $$
BEGIN
  IF EXISTS (
    SELECT 1 FROM information_schema.tables
    WHERE table_name = 'recurring_expense_templates'
  ) THEN
    ALTER TABLE recurring_expense_templates
      ADD COLUMN IF NOT EXISTS vendor_id UUID REFERENCES vendors(id) ON DELETE SET NULL;
  END IF;
END $$;