CREATE TABLE "calendar_connections" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"tenant_id" uuid NOT NULL,
	"user_id" uuid NOT NULL,
	"provider" text NOT NULL,
	"external_user_id" text NOT NULL,
	"access_token" "bytea" NOT NULL,
	"refresh_token" "bytea" NOT NULL,
	"token_expires_at" timestamp with time zone,
	"selected_calendar_id" text,
	"sync_enabled" boolean DEFAULT true NOT NULL,
	"last_synced_at" timestamp with time zone,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	CONSTRAINT "uq_calendar_connections_tenant_user_provider" UNIQUE("tenant_id","user_id","provider"),
	CONSTRAINT "calendar_connections_provider_check" CHECK ("calendar_connections"."provider" IN ('google','outlook'))
);
--> statement-breakpoint
CREATE TABLE "calendar_events" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"tenant_id" uuid NOT NULL,
	"created_by" uuid NOT NULL,
	"title" text NOT NULL,
	"description" text,
	"start_at" timestamp with time zone NOT NULL,
	"end_at" timestamp with time zone NOT NULL,
	"all_day" boolean DEFAULT false NOT NULL,
	"location" text,
	"source" text DEFAULT 'manual' NOT NULL,
	"task_id" uuid,
	"project_id" uuid,
	"customer_id" uuid,
	"external_id" text,
	"external_calendar_id" text,
	"synced_at" timestamp with time zone,
	"sync_status" text DEFAULT 'local' NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	"updated_at" timestamp with time zone DEFAULT now() NOT NULL,
	CONSTRAINT "uq_calendar_events_tenant_source_external" UNIQUE("tenant_id","source","external_id"),
	CONSTRAINT "calendar_events_source_check" CHECK ("calendar_events"."source" IN ('manual','task','project','google','outlook','calendly','acuity','mocal')),
	CONSTRAINT "calendar_events_sync_status_check" CHECK ("calendar_events"."sync_status" IN ('local','synced','pending','error'))
);
--> statement-breakpoint
CREATE TABLE "scheduling_connections" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"tenant_id" uuid NOT NULL,
	"provider" text NOT NULL,
	"api_key" "bytea" NOT NULL,
	"webhook_uri" text,
	"settings" jsonb DEFAULT '{}'::jsonb,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	CONSTRAINT "uq_scheduling_connections_tenant_provider" UNIQUE("tenant_id","provider"),
	CONSTRAINT "scheduling_connections_provider_check" CHECK ("scheduling_connections"."provider" IN ('calendly','acuity','mocal'))
);
--> statement-breakpoint
CREATE TABLE "recurring_tasks" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"tenant_id" uuid NOT NULL,
	"project_id" uuid,
	"title" text NOT NULL,
	"description" text,
	"assignee_id" uuid,
	"estimated_hours" numeric(6, 2),
	"priority" text DEFAULT 'medium' NOT NULL,
	"labels" text[] DEFAULT '{}'::text[] NOT NULL,
	"recurrence" text NOT NULL,
	"advance_days" integer DEFAULT 1 NOT NULL,
	"status_id" uuid,
	"is_active" boolean DEFAULT true NOT NULL,
	"created_by" uuid NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	"last_generated_at" timestamp with time zone,
	CONSTRAINT "recurring_tasks_priority_check" CHECK ("recurring_tasks"."priority" IN ('low','medium','high','urgent'))
);
--> statement-breakpoint
CREATE TABLE "task_templates" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"tenant_id" uuid NOT NULL,
	"name" text NOT NULL,
	"title" text NOT NULL,
	"description" text,
	"estimated_hours" numeric(6, 2),
	"priority" text DEFAULT 'medium' NOT NULL,
	"labels" text[] DEFAULT '{}'::text[] NOT NULL,
	"created_by" uuid NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	CONSTRAINT "task_templates_priority_check" CHECK ("task_templates"."priority" IN ('low','medium','high','urgent'))
);
--> statement-breakpoint
CREATE TABLE "task_dependencies" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"tenant_id" uuid NOT NULL,
	"blocking_task_id" uuid NOT NULL,
	"blocked_task_id" uuid NOT NULL,
	"created_by" uuid NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	CONSTRAINT "task_dependencies_blocking_blocked_uniq" UNIQUE("blocking_task_id","blocked_task_id"),
	CONSTRAINT "task_dependencies_no_self_dep_check" CHECK ("task_dependencies"."blocking_task_id" <> "task_dependencies"."blocked_task_id")
);
--> statement-breakpoint
CREATE TABLE "magic_link_tokens" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"tenant_id" uuid NOT NULL,
	"user_id" uuid,
	"task_id" uuid,
	"email" text,
	"token" text,
	"token_hash" text NOT NULL,
	"purpose" text DEFAULT 'timer' NOT NULL,
	"expires_at" timestamp with time zone NOT NULL,
	"used_at" timestamp with time zone,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	CONSTRAINT "magic_link_tokens_token_unique" UNIQUE("token"),
	CONSTRAINT "magic_link_tokens_token_hash_unique" UNIQUE("token_hash"),
	CONSTRAINT "magic_link_tokens_purpose_check" CHECK ("magic_link_tokens"."purpose" IN ('timer','portal','portal_invite','portal_password_reset','pending_2fa','pending_2fa_setup','magic_link'))
);
--> statement-breakpoint
CREATE TABLE "time_entries" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"tenant_id" uuid NOT NULL,
	"user_id" uuid,
	"contractor_id" uuid,
	"task_id" uuid,
	"project_id" uuid NOT NULL,
	"description" text,
	"started_at" timestamp with time zone NOT NULL,
	"stopped_at" timestamp with time zone,
	"duration_seconds" integer,
	"source" text DEFAULT 'manual' NOT NULL,
	"billable" boolean DEFAULT true NOT NULL,
	"approval_status" text DEFAULT 'auto_approved' NOT NULL,
	"locked_at" timestamp with time zone,
	"locked_reason" text,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	"updated_at" timestamp with time zone DEFAULT now() NOT NULL,
	CONSTRAINT "time_entries_source_check" CHECK ("time_entries"."source" IN ('manual','magic_link','auto','contractor_portal')),
	CONSTRAINT "time_entries_approval_status_check" CHECK ("time_entries"."approval_status" IN ('auto_approved','pending','approved','rejected','locked')),
	CONSTRAINT "time_entries_locked_reason_check" CHECK ("time_entries"."locked_reason" IS NULL OR "time_entries"."locked_reason" IN ('invoiced','period_closed','approved')),
	CONSTRAINT "time_entries_user_or_contractor_check" CHECK ("time_entries"."user_id" IS NOT NULL OR "time_entries"."contractor_id" IS NOT NULL)
);
--> statement-breakpoint
CREATE TABLE "task_audit_log" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"task_id" uuid NOT NULL,
	"tenant_id" uuid NOT NULL,
	"actor_id" uuid NOT NULL,
	"action" text NOT NULL,
	"previous_value" jsonb,
	"new_value" jsonb,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "task_message_attachments" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"message_id" uuid,
	"tenant_id" uuid NOT NULL,
	"filename" text NOT NULL,
	"url" text NOT NULL,
	"r2_key" 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 "task_messages" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"task_id" uuid NOT NULL,
	"tenant_id" uuid NOT NULL,
	"author_id" uuid,
	"message_type" text NOT NULL,
	"content" text NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	"deleted_at" timestamp with time zone,
	CONSTRAINT "task_messages_message_type_check" CHECK ("task_messages"."message_type" IN ('comment','system'))
);
--> statement-breakpoint
ALTER TABLE "expenses" ADD COLUMN "business_percent" integer DEFAULT 100 NOT NULL;--> statement-breakpoint
ALTER TABLE "tenant_settings" ADD COLUMN "idle_timer_threshold_minutes" integer DEFAULT 10 NOT NULL;--> statement-breakpoint
ALTER TABLE "tenant_settings" ADD COLUMN "time_rounding" text DEFAULT 'none' NOT NULL;--> statement-breakpoint
ALTER TABLE "tenant_settings" ADD COLUMN "contractor_require_time_approval" boolean DEFAULT false NOT NULL;--> statement-breakpoint
ALTER TABLE "zync_subscriptions" ADD COLUMN "trial_warning_sent_at" timestamp with time zone;--> statement-breakpoint
ALTER TABLE "zync_subscriptions" ADD COLUMN "trial_expiry_ack_at" timestamp with time zone;--> statement-breakpoint
ALTER TABLE "tasks" ADD COLUMN "recurring_task_id" uuid;--> statement-breakpoint
ALTER TABLE "calendar_connections" ADD CONSTRAINT "calendar_connections_tenant_id_tenants_id_fk" FOREIGN KEY ("tenant_id") REFERENCES "public"."tenants"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "calendar_connections" ADD CONSTRAINT "calendar_connections_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "calendar_events" ADD CONSTRAINT "calendar_events_tenant_id_tenants_id_fk" FOREIGN KEY ("tenant_id") REFERENCES "public"."tenants"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "calendar_events" ADD CONSTRAINT "calendar_events_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 "calendar_events" ADD CONSTRAINT "calendar_events_task_id_tasks_id_fk" FOREIGN KEY ("task_id") REFERENCES "public"."tasks"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "calendar_events" ADD CONSTRAINT "calendar_events_project_id_projects_id_fk" FOREIGN KEY ("project_id") REFERENCES "public"."projects"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "calendar_events" ADD CONSTRAINT "calendar_events_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 "scheduling_connections" ADD CONSTRAINT "scheduling_connections_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_tasks" ADD CONSTRAINT "recurring_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 "recurring_tasks" ADD CONSTRAINT "recurring_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 "recurring_tasks" ADD CONSTRAINT "recurring_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 "recurring_tasks" ADD CONSTRAINT "recurring_tasks_status_id_task_statuses_id_fk" FOREIGN KEY ("status_id") REFERENCES "public"."task_statuses"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "recurring_tasks" ADD CONSTRAINT "recurring_tasks_created_by_users_id_fk" FOREIGN KEY ("created_by") REFERENCES "public"."users"("id") ON DELETE restrict ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "task_templates" ADD CONSTRAINT "task_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 "task_templates" ADD CONSTRAINT "task_templates_created_by_users_id_fk" FOREIGN KEY ("created_by") REFERENCES "public"."users"("id") ON DELETE restrict ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "task_dependencies" ADD CONSTRAINT "task_dependencies_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_dependencies" ADD CONSTRAINT "task_dependencies_blocking_task_id_tasks_id_fk" FOREIGN KEY ("blocking_task_id") REFERENCES "public"."tasks"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "task_dependencies" ADD CONSTRAINT "task_dependencies_blocked_task_id_tasks_id_fk" FOREIGN KEY ("blocked_task_id") REFERENCES "public"."tasks"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "task_dependencies" ADD CONSTRAINT "task_dependencies_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 "magic_link_tokens" ADD CONSTRAINT "magic_link_tokens_tenant_id_tenants_id_fk" FOREIGN KEY ("tenant_id") REFERENCES "public"."tenants"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "magic_link_tokens" ADD CONSTRAINT "magic_link_tokens_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "magic_link_tokens" ADD CONSTRAINT "magic_link_tokens_task_id_tasks_id_fk" FOREIGN KEY ("task_id") REFERENCES "public"."tasks"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "time_entries" ADD CONSTRAINT "time_entries_tenant_id_tenants_id_fk" FOREIGN KEY ("tenant_id") REFERENCES "public"."tenants"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "time_entries" ADD CONSTRAINT "time_entries_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 "time_entries" ADD CONSTRAINT "time_entries_task_id_tasks_id_fk" FOREIGN KEY ("task_id") REFERENCES "public"."tasks"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "time_entries" ADD CONSTRAINT "time_entries_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_audit_log" ADD CONSTRAINT "task_audit_log_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_audit_log" ADD CONSTRAINT "task_audit_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 "task_audit_log" ADD CONSTRAINT "task_audit_log_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 "task_message_attachments" ADD CONSTRAINT "task_message_attachments_message_id_task_messages_id_fk" FOREIGN KEY ("message_id") REFERENCES "public"."task_messages"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "task_message_attachments" ADD CONSTRAINT "task_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 "task_messages" ADD CONSTRAINT "task_messages_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_messages" ADD CONSTRAINT "task_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 "task_messages" ADD CONSTRAINT "task_messages_author_id_users_id_fk" FOREIGN KEY ("author_id") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
CREATE INDEX "idx_calendar_connections_user" ON "calendar_connections" USING btree ("tenant_id","user_id");--> statement-breakpoint
CREATE INDEX "idx_calendar_events_range" ON "calendar_events" USING btree ("tenant_id","start_at","end_at");--> statement-breakpoint
CREATE INDEX "idx_calendar_events_created_by" ON "calendar_events" USING btree ("tenant_id","created_by");--> statement-breakpoint
CREATE INDEX "idx_recurring_tasks_tenant_active" ON "recurring_tasks" USING btree ("tenant_id","is_active");--> statement-breakpoint
CREATE INDEX "idx_recurring_tasks_tenant_created" ON "recurring_tasks" USING btree ("tenant_id","created_at");--> statement-breakpoint
CREATE INDEX "idx_task_templates_tenant_name" ON "task_templates" USING btree ("tenant_id","name");--> statement-breakpoint
CREATE INDEX "idx_td_blocking" ON "task_dependencies" USING btree ("blocking_task_id");--> statement-breakpoint
CREATE INDEX "idx_td_blocked" ON "task_dependencies" USING btree ("blocked_task_id");--> statement-breakpoint
CREATE INDEX "idx_td_tenant" ON "task_dependencies" USING btree ("tenant_id");--> statement-breakpoint
CREATE INDEX "idx_magic_link_tokens_hash" ON "magic_link_tokens" USING btree ("token_hash");--> statement-breakpoint
CREATE INDEX "idx_time_entries_project" ON "time_entries" USING btree ("project_id","started_at");--> statement-breakpoint
CREATE INDEX "idx_time_entries_cursor" ON "time_entries" USING btree ("tenant_id","started_at","id");--> statement-breakpoint
CREATE INDEX "idx_task_audit_log_task" ON "task_audit_log" USING btree ("task_id","created_at");--> statement-breakpoint
CREATE INDEX "idx_task_audit_log_tenant" ON "task_audit_log" USING btree ("tenant_id");--> statement-breakpoint
CREATE INDEX "idx_task_msg_attachments_message" ON "task_message_attachments" USING btree ("message_id");--> statement-breakpoint
CREATE INDEX "idx_task_msg_attachments_tenant" ON "task_message_attachments" USING btree ("tenant_id");--> statement-breakpoint
CREATE INDEX "idx_task_messages_stream" ON "task_messages" USING btree ("task_id","created_at");--> statement-breakpoint
CREATE INDEX "idx_task_messages_tenant" ON "task_messages" USING btree ("tenant_id");--> statement-breakpoint
ALTER TABLE "expenses" ADD CONSTRAINT "expenses_business_percent_check" CHECK ("expenses"."business_percent" >= 0 AND "expenses"."business_percent" <= 100);--> statement-breakpoint

-- ── Wave-4/5 raw_ddl: non-modelable FK + partial unique index ──────────────────
-- recurring-tasks-templates: spawned-task backlink FK + dedup guard.
-- Drizzle omits the FK (column declared without .references()) and cannot model
-- the partial unique index (WHERE clause). tasks.recurring_task_id column + the
-- recurring_tasks table are both created earlier in this migration.
ALTER TABLE "tasks" ADD CONSTRAINT "tasks_recurring_task_id_recurring_tasks_id_fk" FOREIGN KEY ("recurring_task_id") REFERENCES "public"."recurring_tasks"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
CREATE UNIQUE INDEX IF NOT EXISTS "idx_tasks_recurring_due" ON "tasks" ("recurring_task_id","due_date") WHERE "recurring_task_id" IS NOT NULL;