CREATE TABLE "admin_roles" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"name" text NOT NULL,
	"permissions" text[] DEFAULT '{}'::text[] NOT NULL,
	"is_system_role" boolean DEFAULT false NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	CONSTRAINT "admin_roles_name_unique" UNIQUE("name")
);
--> statement-breakpoint
CREATE TABLE "ai_chat_messages" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"session_id" uuid NOT NULL,
	"tenant_id" uuid NOT NULL,
	"role" text NOT NULL,
	"content" text NOT NULL,
	"tokens_used" integer,
	"metadata" jsonb DEFAULT '{}'::jsonb NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	CONSTRAINT "ai_chat_messages_role_check" CHECK ("ai_chat_messages"."role" IN ('user', 'assistant', 'system'))
);
--> statement-breakpoint
CREATE TABLE "ai_chat_sessions" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"tenant_id" uuid NOT NULL,
	"user_id" uuid,
	"title" text,
	"channel" text DEFAULT 'in_app' NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	"updated_at" timestamp with time zone DEFAULT now() NOT NULL,
	CONSTRAINT "ai_chat_sessions_channel_check" CHECK ("ai_chat_sessions"."channel" IN ('in_app', 'telegram', 'whatsapp')),
	CONSTRAINT "ai_chat_sessions_user_id_in_app_check" CHECK ("ai_chat_sessions"."channel" <> 'in_app' OR "ai_chat_sessions"."user_id" IS NOT NULL)
);
--> statement-breakpoint
CREATE TABLE "tenant_audit_log" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"tenant_id" uuid NOT NULL,
	"user_id" uuid,
	"actor_name" text,
	"actor_email" text,
	"event_type" text NOT NULL,
	"entity_type" text,
	"entity_id" uuid,
	"entity_label" text,
	"metadata" jsonb,
	"ip_address" text,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "tax_rates" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"country_code" text NOT NULL,
	"tax_type" text NOT NULL,
	"rate" numeric(6, 4) NOT NULL,
	"effective_from" date NOT NULL,
	"threshold_ils" numeric,
	"notes" text,
	"created_by" uuid NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	CONSTRAINT "tax_rates_country_type_from_unique" UNIQUE("country_code","tax_type","effective_from"),
	CONSTRAINT "tax_rates_tax_type_check" CHECK ("tax_rates"."tax_type" IN (
        'corporate_income',
        'personal_bracket_1','personal_bracket_2','personal_bracket_3',
        'personal_bracket_4','personal_bracket_5','personal_bracket_6',
        'withholding_default'
      ))
);
--> statement-breakpoint
CREATE TABLE "kb_articles" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"tenant_id" uuid NOT NULL,
	"space_id" uuid NOT NULL,
	"parent_id" uuid,
	"title" text NOT NULL,
	"slug" text NOT NULL,
	"content" jsonb NOT NULL,
	"status" text DEFAULT 'DRAFT' NOT NULL,
	"position" numeric NOT NULL,
	"view_count" integer DEFAULT 0 NOT NULL,
	"published_at" timestamp with time zone,
	"created_by" uuid NOT NULL,
	"updated_by" uuid,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	"updated_at" timestamp with time zone DEFAULT now() NOT NULL,
	CONSTRAINT "kb_articles_status_check" CHECK ("kb_articles"."status" IN ('DRAFT', 'PUBLISHED'))
);
--> statement-breakpoint
CREATE TABLE "kb_attachments" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"tenant_id" uuid NOT NULL,
	"article_id" uuid NOT NULL,
	"filename" text NOT NULL,
	"r2_key" text NOT NULL,
	"file_type" text NOT NULL,
	"file_size_bytes" integer NOT NULL,
	"created_by" uuid NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "kb_spaces" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"tenant_id" uuid NOT NULL,
	"name" text NOT NULL,
	"slug" text NOT NULL,
	"type" text DEFAULT 'internal' NOT NULL,
	"customer_id" uuid,
	"icon" text,
	"is_public" boolean DEFAULT false NOT NULL,
	"description" text,
	"created_by" uuid NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	CONSTRAINT "kb_spaces_type_check" CHECK ("kb_spaces"."type" IN ('internal', 'vault'))
);
--> statement-breakpoint
CREATE TABLE "project_members" (
	"project_id" uuid NOT NULL,
	"user_id" uuid NOT NULL,
	"tenant_id" uuid NOT NULL,
	"role" text DEFAULT 'member' NOT NULL,
	"hourly_rate" numeric(10, 2),
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	CONSTRAINT "project_members_project_id_user_id_pk" PRIMARY KEY("project_id","user_id"),
	CONSTRAINT "project_members_role_check" CHECK ("project_members"."role" IN ('owner', 'member', 'viewer'))
);
--> statement-breakpoint
CREATE TABLE "projects" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"tenant_id" uuid NOT NULL,
	"customer_id" uuid,
	"name" text NOT NULL,
	"description" text,
	"status" text DEFAULT 'active' NOT NULL,
	"billing_type" text NOT NULL,
	"billing_config" jsonb,
	"currency" text DEFAULT 'ILS' NOT NULL,
	"start_date" date,
	"end_date" date,
	"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 "projects_status_check" CHECK ("projects"."status" IN ('active', 'on_hold', 'completed', 'archived')),
	CONSTRAINT "projects_billing_type_check" CHECK ("projects"."billing_type" IN ('fixed', 'hourly', 'retainer'))
);
--> statement-breakpoint
CREATE TABLE "retainer_months" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"project_id" uuid NOT NULL,
	"tenant_id" uuid NOT NULL,
	"month" text NOT NULL,
	"hours_included" numeric(6, 2),
	"hours_used" numeric(6, 2) DEFAULT '0' NOT NULL,
	"hours_rolled_over" numeric(6, 2) DEFAULT '0' NOT NULL,
	"invoice_triggered_at" timestamp with time zone,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "zync_subscriptions" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"tenant_id" uuid NOT NULL,
	"tier" text DEFAULT 'freelancer' NOT NULL,
	"status" text DEFAULT 'active' NOT NULL,
	"period" text DEFAULT NULL,
	"adapter" text DEFAULT 'null' NOT NULL,
	"adapter_subscription_id" text,
	"adapter_customer_id" text,
	"current_period_start" timestamp with time zone,
	"current_period_end" timestamp with time zone,
	"trial_ends_at" timestamp with time zone,
	"canceled_at" timestamp with time zone,
	"grace_period_started_at" timestamp with time zone,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	CONSTRAINT "zync_subscriptions_tenant_id_unique" UNIQUE("tenant_id"),
	CONSTRAINT "zync_subscriptions_tier_check" CHECK ("zync_subscriptions"."tier" IN ('freelancer', 'business', 'enterprise', 'white_label')),
	CONSTRAINT "zync_subscriptions_status_check" CHECK ("zync_subscriptions"."status" IN ('active', 'trialing', 'past_due', 'canceled')),
	CONSTRAINT "zync_subscriptions_period_check" CHECK ("zync_subscriptions"."period" IS NULL OR "zync_subscriptions"."period" IN ('monthly', 'annual'))
);
--> statement-breakpoint
ALTER TABLE "admin_users" ADD COLUMN "role_id" uuid;--> statement-breakpoint
ALTER TABLE "tenants" ADD COLUMN "logo_url" text;--> statement-breakpoint
ALTER TABLE "ai_chat_messages" ADD CONSTRAINT "ai_chat_messages_session_id_ai_chat_sessions_id_fk" FOREIGN KEY ("session_id") REFERENCES "public"."ai_chat_sessions"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "ai_chat_messages" ADD CONSTRAINT "ai_chat_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 "ai_chat_sessions" ADD CONSTRAINT "ai_chat_sessions_tenant_id_tenants_id_fk" FOREIGN KEY ("tenant_id") REFERENCES "public"."tenants"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "ai_chat_sessions" ADD CONSTRAINT "ai_chat_sessions_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "tenant_audit_log" ADD CONSTRAINT "tenant_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 "tenant_audit_log" ADD CONSTRAINT "tenant_audit_log_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 "tax_rates" ADD CONSTRAINT "tax_rates_created_by_admin_users_id_fk" FOREIGN KEY ("created_by") REFERENCES "public"."admin_users"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "kb_articles" ADD CONSTRAINT "kb_articles_tenant_id_tenants_id_fk" FOREIGN KEY ("tenant_id") REFERENCES "public"."tenants"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "kb_articles" ADD CONSTRAINT "kb_articles_space_id_kb_spaces_id_fk" FOREIGN KEY ("space_id") REFERENCES "public"."kb_spaces"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "kb_articles" ADD CONSTRAINT "kb_articles_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 "kb_articles" ADD CONSTRAINT "kb_articles_updated_by_users_id_fk" FOREIGN KEY ("updated_by") REFERENCES "public"."users"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "kb_attachments" ADD CONSTRAINT "kb_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 "kb_attachments" ADD CONSTRAINT "kb_attachments_article_id_kb_articles_id_fk" FOREIGN KEY ("article_id") REFERENCES "public"."kb_articles"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "kb_attachments" ADD CONSTRAINT "kb_attachments_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 "kb_spaces" ADD CONSTRAINT "kb_spaces_tenant_id_tenants_id_fk" FOREIGN KEY ("tenant_id") REFERENCES "public"."tenants"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "kb_spaces" ADD CONSTRAINT "kb_spaces_customer_id_customers_id_fk" FOREIGN KEY ("customer_id") REFERENCES "public"."customers"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "kb_spaces" ADD CONSTRAINT "kb_spaces_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_members" ADD CONSTRAINT "project_members_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_members" ADD CONSTRAINT "project_members_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "project_members" ADD CONSTRAINT "project_members_tenant_id_tenants_id_fk" FOREIGN KEY ("tenant_id") REFERENCES "public"."tenants"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "projects" ADD CONSTRAINT "projects_tenant_id_tenants_id_fk" FOREIGN KEY ("tenant_id") REFERENCES "public"."tenants"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "projects" ADD CONSTRAINT "projects_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 "projects" ADD CONSTRAINT "projects_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 "retainer_months" ADD CONSTRAINT "retainer_months_project_id_projects_id_fk" FOREIGN KEY ("project_id") REFERENCES "public"."projects"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "retainer_months" ADD CONSTRAINT "retainer_months_tenant_id_tenants_id_fk" FOREIGN KEY ("tenant_id") REFERENCES "public"."tenants"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "zync_subscriptions" ADD CONSTRAINT "zync_subscriptions_tenant_id_tenants_id_fk" FOREIGN KEY ("tenant_id") REFERENCES "public"."tenants"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
CREATE INDEX "ai_chat_messages_session" ON "ai_chat_messages" USING btree ("session_id","created_at");--> statement-breakpoint
CREATE INDEX "ai_chat_sessions_user" ON "ai_chat_sessions" USING btree ("tenant_id","user_id","created_at" DESC NULLS LAST);--> statement-breakpoint
CREATE INDEX "idx_tal_tenant_time" ON "tenant_audit_log" USING btree ("tenant_id","created_at" DESC NULLS LAST);--> statement-breakpoint
CREATE INDEX "idx_tal_entity" ON "tenant_audit_log" USING btree ("tenant_id","entity_type","entity_id");--> statement-breakpoint
CREATE INDEX "idx_tal_user" ON "tenant_audit_log" USING btree ("tenant_id","user_id");--> statement-breakpoint
CREATE INDEX "idx_tal_event_type" ON "tenant_audit_log" USING btree ("tenant_id","event_type");--> statement-breakpoint
CREATE INDEX "tax_rates_country_code_idx" ON "tax_rates" USING btree ("country_code");--> statement-breakpoint
CREATE INDEX "idx_kb_articles_space" ON "kb_articles" USING btree ("tenant_id","space_id");--> statement-breakpoint
CREATE INDEX "idx_kb_articles_tree" ON "kb_articles" USING btree ("space_id","parent_id","position");--> statement-breakpoint
CREATE UNIQUE INDEX "kb_articles_space_slug_uniq" ON "kb_articles" USING btree ("space_id","slug");--> statement-breakpoint
CREATE INDEX "idx_kb_attachments_article" ON "kb_attachments" USING btree ("tenant_id","article_id");--> statement-breakpoint
CREATE INDEX "idx_kb_spaces_tenant" ON "kb_spaces" USING btree ("tenant_id");--> statement-breakpoint
CREATE INDEX "idx_kb_spaces_customer" ON "kb_spaces" USING btree ("tenant_id","customer_id");--> statement-breakpoint
CREATE UNIQUE INDEX "kb_spaces_tenant_slug_uniq" ON "kb_spaces" USING btree ("tenant_id","slug");--> statement-breakpoint
CREATE INDEX "idx_project_members_tenant_user" ON "project_members" USING btree ("tenant_id","user_id");--> statement-breakpoint
CREATE INDEX "idx_projects_tenant_status" ON "projects" USING btree ("tenant_id","status","updated_at" DESC NULLS LAST);--> statement-breakpoint
CREATE INDEX "idx_projects_tenant_customer" ON "projects" USING btree ("tenant_id","customer_id");--> statement-breakpoint
CREATE INDEX "idx_projects_tenant_billing" ON "projects" USING btree ("tenant_id","billing_type");--> statement-breakpoint
CREATE INDEX "idx_projects_tenant_name" ON "projects" USING btree ("tenant_id","name");--> statement-breakpoint
CREATE UNIQUE INDEX "idx_retainer_months_project_month_uniq" ON "retainer_months" USING btree ("project_id","month");--> statement-breakpoint
CREATE INDEX "idx_retainer_months_project" ON "retainer_months" USING btree ("tenant_id","project_id","month");--> statement-breakpoint
CREATE INDEX "idx_zync_subscriptions_tenant" ON "zync_subscriptions" USING btree ("tenant_id");--> statement-breakpoint
CREATE INDEX "idx_zync_subscriptions_status" ON "zync_subscriptions" USING btree ("status");--> statement-breakpoint
CREATE INDEX IF NOT EXISTS "customers_search_gin_idx" ON "customers" USING gin (to_tsvector('simple', coalesce("name",'') || ' ' || coalesce("email",'') || ' ' || coalesce("company",'')));
--> statement-breakpoint
-- admin-dashboard: add role_id FK column to admin_users
ALTER TABLE admin_users ADD COLUMN IF NOT EXISTS role_id UUID REFERENCES admin_roles(id);
--> statement-breakpoint
-- ai-assistant: GIN-style partial index for telegram chat lookup
CREATE INDEX ai_chat_messages_telegram ON ai_chat_messages ((metadata->>'telegram_chat_id'), created_at DESC) WHERE metadata ? 'telegram_chat_id';
--> statement-breakpoint
-- kb-module: self-referencing FK for nested articles with cascade delete
ALTER TABLE kb_articles ADD CONSTRAINT kb_articles_parent_id_fkey FOREIGN KEY (parent_id) REFERENCES kb_articles(id) ON DELETE CASCADE;
