CREATE TABLE "expense_corrections" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"expense_id" uuid NOT NULL,
	"user_id" uuid NOT NULL,
	"field_name" text NOT NULL,
	"original_value" text,
	"corrected_value" text NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "expenses" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"tenant_id" uuid NOT NULL,
	"project_id" uuid,
	"created_by" uuid NOT NULL,
	"r2_key" text NOT NULL,
	"file_name" text NOT NULL,
	"file_type" text NOT NULL,
	"file_size_bytes" integer NOT NULL,
	"vendor_name" text,
	"vendor_tax_id" text,
	"invoice_number" text,
	"invoice_total" numeric(12, 2),
	"vat_amount" numeric(12, 2),
	"currency" text DEFAULT 'ILS' NOT NULL,
	"allocation_number" text,
	"raw_ocr_text" text,
	"expense_date" date,
	"amount" numeric(12, 2),
	"vat_deductible" boolean DEFAULT true NOT NULL,
	"status" text DEFAULT 'PENDING' NOT NULL,
	"ocr_confidence" numeric(3, 2),
	"processing_started_at" timestamp with time zone,
	"processed_at" timestamp with time zone,
	"processing_error" text,
	"expense_category" text,
	"deduction_pct" integer,
	"deduction_confidence" numeric(3, 2),
	"deduction_reasoning_he" text,
	"deduction_reasoning_en" text,
	"evaluated_at" timestamp with time zone,
	"is_per_diem" boolean DEFAULT false NOT NULL,
	"per_diem_days" numeric(5, 2),
	"per_diem_rate_ils" numeric(10, 2),
	"source" text DEFAULT 'upload' NOT NULL,
	"source_metadata" jsonb,
	"notes" text,
	"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 "expenses_file_type_check" CHECK ("expenses"."file_type" IN ('pdf','jpg','png','heic')),
	CONSTRAINT "expenses_status_check" CHECK ("expenses"."status" IN ('PENDING','PROCESSING','COMPLETED','FAILED','NEEDS_REVIEW')),
	CONSTRAINT "expenses_category_check" CHECK ("expenses"."expense_category" IS NULL OR "expenses"."expense_category" IN ('office','marketing','professional','vehicle','equipment','finance','welfare','exceptional','travel')),
	CONSTRAINT "expenses_deduction_pct_check" CHECK ("expenses"."deduction_pct" IS NULL OR ("expenses"."deduction_pct" >= 0 AND "expenses"."deduction_pct" <= 100)),
	CONSTRAINT "expenses_source_check" CHECK ("expenses"."source" IN ('upload','email','whatsapp','telegram'))
);
--> statement-breakpoint
CREATE TABLE "status_subscribers" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"email" text NOT NULL,
	"subscribed_at" timestamp with time zone DEFAULT now() NOT NULL,
	"unsubscribe_token" text NOT NULL,
	CONSTRAINT "status_subscribers_email_unique" UNIQUE("email"),
	CONSTRAINT "status_subscribers_unsubscribe_token_unique" UNIQUE("unsubscribe_token")
);
--> statement-breakpoint
CREATE TABLE "system_incident_updates" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"incident_id" uuid NOT NULL,
	"body" text NOT NULL,
	"status" text NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	"created_by" text NOT NULL,
	CONSTRAINT "system_incident_updates_status_check" CHECK ("system_incident_updates"."status" IN ('investigating','identified','monitoring','resolved'))
);
--> statement-breakpoint
CREATE TABLE "system_incidents" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"title" text NOT NULL,
	"status" text NOT NULL,
	"impact" text NOT NULL,
	"affected_services" text[] DEFAULT '{}'::text[] NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	"resolved_at" timestamp with time zone,
	"created_by" text NOT NULL,
	CONSTRAINT "system_incidents_status_check" CHECK ("system_incidents"."status" IN ('investigating','identified','monitoring','resolved')),
	CONSTRAINT "system_incidents_impact_check" CHECK ("system_incidents"."impact" IN ('minor','major','critical'))
);
--> statement-breakpoint
CREATE TABLE "task_labels" (
	"task_id" uuid NOT NULL,
	"label" text NOT NULL,
	CONSTRAINT "task_labels_task_id_label_pk" PRIMARY KEY("task_id","label")
);
--> statement-breakpoint
CREATE TABLE "task_statuses" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"tenant_id" uuid NOT NULL,
	"project_id" uuid,
	"name" text NOT NULL,
	"color" text NOT NULL,
	"position" integer NOT NULL,
	"is_terminal" boolean DEFAULT false NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "task_sync_settings" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"tenant_id" uuid NOT NULL,
	"sync_interval_minutes" integer DEFAULT 120 NOT NULL,
	"auto_create_tickets_from" jsonb DEFAULT '{}'::jsonb NOT NULL,
	"default_project_id" uuid,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	"updated_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "tasks" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"tenant_id" uuid NOT NULL,
	"project_id" uuid,
	"status_id" uuid NOT NULL,
	"title" text NOT NULL,
	"description" jsonb,
	"priority" text DEFAULT 'medium' NOT NULL,
	"assignee_id" uuid,
	"reporter_id" uuid NOT NULL,
	"due_date" date,
	"estimated_hours" numeric(6, 2),
	"source" text DEFAULT 'manual' NOT NULL,
	"external_id" text,
	"position" numeric NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	"updated_at" timestamp with time zone DEFAULT now() NOT NULL,
	CONSTRAINT "tasks_priority_check" CHECK ("tasks"."priority" IN ('low','medium','high','urgent')),
	CONSTRAINT "tasks_source_check" CHECK ("tasks"."source" IN ('manual','email','telegram','slack','whatsapp','trello','asana','jira','monday','clickup'))
);
--> statement-breakpoint
ALTER TABLE "users" ADD COLUMN "last_login_at" timestamp with time zone;--> statement-breakpoint
ALTER TABLE "tenant_settings" ADD COLUMN "filing_cadence" text DEFAULT 'monthly' NOT NULL;--> statement-breakpoint
ALTER TABLE "tenant_settings" ADD COLUMN "tax_basis" text DEFAULT 'cash' NOT NULL;--> statement-breakpoint
ALTER TABLE "tenant_settings" ADD COLUMN "business_category" text;--> statement-breakpoint
ALTER TABLE "tenant_settings" ADD COLUMN "per_diem_rates" jsonb DEFAULT '{"domestic_full_day":100,"domestic_half_day":50,"international_full_day":250,"international_half_day":125}'::jsonb NOT NULL;--> statement-breakpoint
ALTER TABLE "tenant_settings" ADD COLUMN "expense_default_category" text DEFAULT 'exceptional' NOT NULL;--> statement-breakpoint
ALTER TABLE "tenant_settings" ADD COLUMN "expense_auto_approve_threshold_ils" integer DEFAULT 0 NOT NULL;--> statement-breakpoint
ALTER TABLE "tenant_settings" ADD COLUMN "expense_receipt_reminder_enabled" boolean DEFAULT false NOT NULL;--> statement-breakpoint
ALTER TABLE "tenant_settings" ADD COLUMN "expense_receipt_reminder_days" integer DEFAULT 7 NOT NULL;--> statement-breakpoint
ALTER TABLE "tenant_settings" ADD COLUMN "expense_approval_threshold_ils" integer DEFAULT 0 NOT NULL;--> statement-breakpoint
ALTER TABLE "tenant_settings" ADD COLUMN "expense_approver_role" text DEFAULT 'any_admin' NOT NULL;--> statement-breakpoint
ALTER TABLE "kb_articles" ADD COLUMN "content_text" text;--> statement-breakpoint
ALTER TABLE "expense_corrections" ADD CONSTRAINT "expense_corrections_expense_id_expenses_id_fk" FOREIGN KEY ("expense_id") REFERENCES "public"."expenses"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "expense_corrections" ADD CONSTRAINT "expense_corrections_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "expenses" ADD CONSTRAINT "expenses_tenant_id_tenants_id_fk" FOREIGN KEY ("tenant_id") REFERENCES "public"."tenants"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "expenses" ADD CONSTRAINT "expenses_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 "expenses" ADD CONSTRAINT "expenses_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 "system_incident_updates" ADD CONSTRAINT "system_incident_updates_incident_id_system_incidents_id_fk" FOREIGN KEY ("incident_id") REFERENCES "public"."system_incidents"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "task_labels" ADD CONSTRAINT "task_labels_task_id_tasks_id_fk" FOREIGN KEY ("task_id") REFERENCES "public"."tasks"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "task_statuses" ADD CONSTRAINT "task_statuses_tenant_id_tenants_id_fk" FOREIGN KEY ("tenant_id") REFERENCES "public"."tenants"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "task_statuses" ADD CONSTRAINT "task_statuses_project_id_projects_id_fk" FOREIGN KEY ("project_id") REFERENCES "public"."projects"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "task_sync_settings" ADD CONSTRAINT "task_sync_settings_tenant_id_tenants_id_fk" FOREIGN KEY ("tenant_id") REFERENCES "public"."tenants"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "task_sync_settings" ADD CONSTRAINT "task_sync_settings_default_project_id_projects_id_fk" FOREIGN KEY ("default_project_id") REFERENCES "public"."projects"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "tasks" ADD CONSTRAINT "tasks_tenant_id_tenants_id_fk" FOREIGN KEY ("tenant_id") REFERENCES "public"."tenants"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "tasks" ADD CONSTRAINT "tasks_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 "tasks" ADD CONSTRAINT "tasks_status_id_task_statuses_id_fk" FOREIGN KEY ("status_id") REFERENCES "public"."task_statuses"("id") ON DELETE restrict ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "tasks" ADD CONSTRAINT "tasks_assignee_id_users_id_fk" FOREIGN KEY ("assignee_id") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "tasks" ADD CONSTRAINT "tasks_reporter_id_users_id_fk" FOREIGN KEY ("reporter_id") REFERENCES "public"."users"("id") ON DELETE restrict ON UPDATE no action;--> statement-breakpoint
CREATE INDEX "idx_expense_corrections_expense" ON "expense_corrections" USING btree ("expense_id");--> statement-breakpoint
CREATE INDEX "idx_expenses_report" ON "expenses" USING btree ("tenant_id","status","expense_date");--> statement-breakpoint
CREATE INDEX "idx_expenses_cursor" ON "expenses" USING btree ("tenant_id","created_at","id");--> statement-breakpoint
CREATE INDEX "idx_expenses_project" ON "expenses" USING btree ("project_id");--> statement-breakpoint
CREATE INDEX "idx_system_incident_updates_incident" ON "system_incident_updates" USING btree ("incident_id","created_at");--> statement-breakpoint
CREATE INDEX "idx_system_incidents_resolved_at" ON "system_incidents" USING btree ("resolved_at");--> statement-breakpoint
CREATE INDEX "idx_task_labels_label" ON "task_labels" USING btree ("label");--> statement-breakpoint
CREATE INDEX "idx_task_statuses_tenant_project_position" ON "task_statuses" USING btree ("tenant_id","project_id","position");--> statement-breakpoint
CREATE UNIQUE INDEX "idx_task_sync_settings_tenant_uniq" ON "task_sync_settings" USING btree ("tenant_id");--> statement-breakpoint
CREATE INDEX "idx_tasks_tenant_project_status" ON "tasks" USING btree ("tenant_id","project_id","status_id");--> statement-breakpoint
CREATE INDEX "idx_tasks_tenant_status_position" ON "tasks" USING btree ("tenant_id","status_id","position");--> statement-breakpoint
CREATE INDEX "idx_tasks_tenant_assignee" ON "tasks" USING btree ("tenant_id","assignee_id");--> statement-breakpoint
CREATE INDEX "idx_users_last_login" ON "users" USING btree ("last_login_at");--> statement-breakpoint
ALTER TABLE "tenant_settings" ADD CONSTRAINT "tenant_settings_filing_cadence_check" CHECK ("tenant_settings"."filing_cadence" IN ('monthly','bimonthly'));--> statement-breakpoint
ALTER TABLE "tenant_settings" ADD CONSTRAINT "tenant_settings_tax_basis_check" CHECK ("tenant_settings"."tax_basis" IN ('cash','accrual'));

--> statement-breakpoint
-- ════════════════════════════════════════════════════════════════════════════
-- Wave-4 raw_ddl: non-modelable indexes, expressions, extensions
-- (Idempotent — safe to re-run)
-- ════════════════════════════════════════════════════════════════════════════

-- ── expenses-module: expression/partial indexes ───────────────────────────
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX IF NOT EXISTS idx_expenses_pending ON expenses (tenant_id, created_at DESC) WHERE status = 'PENDING';
CREATE INDEX IF NOT EXISTS idx_expenses_raw_ocr_text ON expenses USING gin (raw_ocr_text gin_trgm_ops) WHERE raw_ocr_text IS NOT NULL;

--> statement-breakpoint
-- ── system-status-page: pgcrypto + server-side default for unsubscribe_token
CREATE EXTENSION IF NOT EXISTS pgcrypto;
ALTER TABLE status_subscribers
  ALTER COLUMN unsubscribe_token
  SET DEFAULT encode(gen_random_bytes(16), 'hex');

--> statement-breakpoint
-- ── tasks-board-engine: unique partial index for external_id dedup ─────────
CREATE UNIQUE INDEX IF NOT EXISTS idx_tasks_tenant_source_external_id_dedup ON tasks (tenant_id, source, external_id) WHERE external_id IS NOT NULL;

--> statement-breakpoint
-- tasks-board-engine: widen adapter_credentials adapter_id CHECK constraint
ALTER TABLE adapter_credentials DROP CONSTRAINT IF EXISTS adapter_credentials_adapter_id_check;
ALTER TABLE adapter_credentials ADD CONSTRAINT adapter_credentials_adapter_id_check CHECK (adapter_id IN ('gmail','outlook','telegram','slack','whatsapp','smtp','trello','asana','jira','monday','clickup'));

--> statement-breakpoint
--> statement-breakpoint
-- ════════════════════════════════════════════════════════════════════════════
-- search-completeness: search_vector generated columns + GIN indexes
-- Scoped to tables existing as of wave-4 (tasks, projects, customers, expenses,
-- kb_articles, users). Future waves add their own search_vector when their tables land.
-- Column refs verified against live schema; jsonb cols cast ::text. Hebrew has no
-- Postgres stemmer so 'hebrew' config = COPY of 'simple' (literal must resolve).
-- GIN indexes raw-SQL-only (drizzle snapshot serializer truncates expressions).
-- ════════════════════════════════════════════════════════════════════════════
DO $$ BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_ts_config WHERE cfgname = 'hebrew') THEN
    CREATE TEXT SEARCH CONFIGURATION hebrew (COPY = simple);
  END IF;
END $$;

-- kb_articles content_text helper (search_vector references it; modeled in schema too)
ALTER TABLE kb_articles ADD COLUMN IF NOT EXISTS content_text text;

ALTER TABLE tasks ADD COLUMN IF NOT EXISTS search_vector tsvector
  GENERATED ALWAYS AS (
    to_tsvector('hebrew', coalesce(title, '')) ||
    to_tsvector('simple', coalesce(description::text, ''))
  ) STORED;
CREATE INDEX IF NOT EXISTS idx_tasks_search_vector ON tasks USING GIN (search_vector);

ALTER TABLE projects ADD COLUMN IF NOT EXISTS search_vector tsvector
  GENERATED ALWAYS AS (
    to_tsvector('hebrew', coalesce(name, '')) ||
    to_tsvector('simple', coalesce(description, ''))
  ) STORED;
CREATE INDEX IF NOT EXISTS idx_projects_search_vector ON projects USING GIN (search_vector);

ALTER TABLE customers ADD COLUMN IF NOT EXISTS search_vector tsvector
  GENERATED ALWAYS AS (
    to_tsvector('hebrew', coalesce(name, '')) ||
    to_tsvector('simple', coalesce(email, '') || ' ' || coalesce(phone, '') || ' ' || coalesce(company, ''))
  ) STORED;
CREATE INDEX IF NOT EXISTS idx_customers_search_vector ON customers USING GIN (search_vector);

ALTER TABLE expenses ADD COLUMN IF NOT EXISTS search_vector tsvector
  GENERATED ALWAYS AS (
    to_tsvector('hebrew', coalesce(vendor_name, '') || ' ' || coalesce(notes, '')) ||
    to_tsvector('simple', coalesce(expense_category, '') || ' ' || coalesce(invoice_number, ''))
  ) STORED;
CREATE INDEX IF NOT EXISTS idx_expenses_search_vector ON expenses USING GIN (search_vector);

ALTER TABLE kb_articles ADD COLUMN IF NOT EXISTS search_vector tsvector
  GENERATED ALWAYS AS (
    to_tsvector('hebrew', coalesce(title, '') || ' ' || coalesce(content_text, ''))
  ) STORED;
CREATE INDEX IF NOT EXISTS idx_kb_articles_search_vector ON kb_articles USING GIN (search_vector);

ALTER TABLE users ADD COLUMN IF NOT EXISTS search_vector tsvector
  GENERATED ALWAYS AS (
    to_tsvector('hebrew', coalesce(name, '')) ||
    to_tsvector('simple', coalesce(email, ''))
  ) STORED;
CREATE INDEX IF NOT EXISTS idx_users_search_vector ON users USING GIN (search_vector);

