CREATE TABLE "ai_credit_purchases" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"tenant_id" uuid NOT NULL,
	"amount_usd" numeric(10, 2) NOT NULL,
	"tokens_granted" bigint NOT NULL,
	"tokens_remaining" bigint NOT NULL,
	"payment_reference" text,
	"expires_at" timestamp with time zone,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "ai_global_config" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"main_model_id" text NOT NULL,
	"backup_model_ids" jsonb DEFAULT '[]'::jsonb NOT NULL,
	"use_case_prompts" jsonb DEFAULT '{}'::jsonb NOT NULL,
	"updated_at" timestamp with time zone DEFAULT now() NOT NULL,
	"updated_by" uuid
);
--> statement-breakpoint
CREATE TABLE "ai_model_pricing" (
	"model_id" text PRIMARY KEY NOT NULL,
	"provider" text NOT NULL,
	"label" text NOT NULL,
	"input_cost_per_1m" numeric(12, 6) NOT NULL,
	"output_cost_per_1m" numeric(12, 6) NOT NULL,
	"is_vision_capable" boolean DEFAULT false NOT NULL,
	"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,
	CONSTRAINT "ai_model_pricing_provider_check" CHECK ("ai_model_pricing"."provider" IN ('anthropic', 'openai', 'google'))
);
--> statement-breakpoint
CREATE TABLE "ai_tenant_settings" (
	"tenant_id" uuid PRIMARY KEY NOT NULL,
	"personality_prompt" text,
	"use_case_overrides" jsonb DEFAULT '{}'::jsonb NOT NULL,
	"extra_usage_enabled" boolean DEFAULT false NOT NULL,
	"extra_spend_limit_usd" numeric(10, 2),
	"auto_reload_enabled" boolean DEFAULT false NOT NULL,
	"auto_reload_amount_usd" numeric(10, 2),
	"updated_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "ai_tier_quotas" (
	"tier" text NOT NULL,
	"model_id" text NOT NULL,
	"monthly_tokens" bigint NOT NULL,
	"extra_allowed" boolean DEFAULT false NOT NULL,
	"extra_max_usd" numeric(10, 2),
	"effective_from" date NOT NULL,
	"effective_to" date,
	CONSTRAINT "ai_tier_quotas_tier_effective_from_pk" PRIMARY KEY("tier","effective_from"),
	CONSTRAINT "ai_tier_quotas_tier_check" CHECK ("ai_tier_quotas"."tier" IN ('freelancer', 'business', 'enterprise', 'white_label'))
);
--> statement-breakpoint
CREATE TABLE "ai_usage_log" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"tenant_id" uuid NOT NULL,
	"user_id" uuid,
	"use_case" text NOT NULL,
	"model_id" text NOT NULL,
	"provider" text NOT NULL,
	"input_tokens" integer NOT NULL,
	"output_tokens" integer NOT NULL,
	"total_tokens" integer NOT NULL,
	"cost_usd" numeric(12, 6) NOT NULL,
	"billed_from" text NOT NULL,
	"duration_ms" integer NOT NULL,
	"entity_type" text,
	"entity_id" uuid,
	"error" text,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	CONSTRAINT "ai_usage_log_provider_check" CHECK ("ai_usage_log"."provider" IN ('anthropic', 'openai', 'google')),
	CONSTRAINT "ai_usage_log_billed_from_check" CHECK ("ai_usage_log"."billed_from" IN ('quota', 'extra', 'purchased'))
);
--> statement-breakpoint
CREATE TABLE "adapter_credentials" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"tenant_id" uuid NOT NULL,
	"adapter_id" text NOT NULL,
	"ciphertext" text NOT NULL,
	"iv" text NOT NULL,
	"auth_tag" text NOT NULL,
	"metadata" jsonb DEFAULT '{}'::jsonb NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	"updated_at" timestamp with time zone DEFAULT now() NOT NULL,
	CONSTRAINT "adapter_credentials_tenant_adapter_unique" UNIQUE("tenant_id","adapter_id"),
	CONSTRAINT "adapter_credentials_adapter_id_check" CHECK ("adapter_credentials"."adapter_id" IN ('gmail','outlook','telegram','slack','whatsapp','smtp'))
);
--> statement-breakpoint
CREATE TABLE "notifications" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"tenant_id" uuid NOT NULL,
	"user_id" uuid NOT NULL,
	"type" text NOT NULL,
	"title_key" text NOT NULL,
	"body_key" text,
	"params" jsonb DEFAULT '{}'::jsonb NOT NULL,
	"entity_type" text,
	"entity_id" uuid,
	"read_at" timestamp with time zone,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "push_subscriptions" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"user_id" uuid NOT NULL,
	"tenant_id" uuid NOT NULL,
	"endpoint" text NOT NULL,
	"p256dh" text NOT NULL,
	"auth" text NOT NULL,
	"user_agent" text,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	"last_used_at" timestamp with time zone,
	CONSTRAINT "push_subscriptions_endpoint_unique" UNIQUE("endpoint")
);
--> statement-breakpoint
CREATE TABLE "customer_communications" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"tenant_id" uuid NOT NULL,
	"customer_id" uuid NOT NULL,
	"direction" text NOT NULL,
	"channel" text NOT NULL,
	"subject" text,
	"body" text,
	"from_address" text,
	"to_address" text,
	"related_id" uuid,
	"related_type" text,
	"sent_at" timestamp with time zone DEFAULT now() NOT NULL,
	"created_by" uuid,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	CONSTRAINT "customer_communications_direction_check" CHECK ("customer_communications"."direction" IN ('outbound', 'inbound', 'internal')),
	CONSTRAINT "customer_communications_channel_check" CHECK ("customer_communications"."channel" IN ('email', 'telegram', 'ticket', 'note', 'system'))
);
--> statement-breakpoint
CREATE TABLE "customer_contacts" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"customer_id" uuid NOT NULL,
	"tenant_id" uuid NOT NULL,
	"name" text NOT NULL,
	"email" text NOT NULL,
	"phone" text,
	"role" text,
	"is_primary" boolean DEFAULT false NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "customer_portal_users" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"customer_id" uuid NOT NULL,
	"tenant_id" uuid NOT NULL,
	"contact_id" uuid NOT NULL,
	"user_id" uuid NOT NULL,
	"portal_role" text DEFAULT 'customer_viewer' NOT NULL,
	"status" text DEFAULT 'active' NOT NULL,
	"invited_at" timestamp with time zone,
	"accepted_at" timestamp with time zone,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	CONSTRAINT "customer_portal_users_status_check" CHECK ("customer_portal_users"."status" IN ('active', 'frozen'))
);
--> statement-breakpoint
CREATE TABLE "customers" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"tenant_id" uuid NOT NULL,
	"name" text NOT NULL,
	"company" text,
	"email" text,
	"phone" text,
	"address" jsonb,
	"notes" text,
	"status" text DEFAULT 'active' NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	"updated_at" timestamp with time zone DEFAULT now() NOT NULL,
	CONSTRAINT "customers_status_check" CHECK ("customers"."status" IN ('active', 'archived'))
);
--> statement-breakpoint
CREATE TABLE "user_2fa_backup_codes" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"user_id" uuid NOT NULL,
	"code_hash" text NOT NULL,
	"used_at" timestamp with time zone,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "user_trusted_devices" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"user_id" uuid NOT NULL,
	"tenant_id" uuid NOT NULL,
	"token_hash" text NOT NULL,
	"user_agent" text,
	"ip_address" text,
	"expires_at" timestamp with time zone NOT NULL,
	"revoked_at" timestamp with time zone,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	CONSTRAINT "user_trusted_devices_token_hash_unique" UNIQUE("token_hash")
);
--> statement-breakpoint
CREATE TABLE "tenant_modules" (
	"tenant_id" uuid NOT NULL,
	"module_id" text NOT NULL,
	"enabled" boolean DEFAULT true NOT NULL,
	"enabled_at" timestamp with time zone,
	"disabled_at" timestamp with time zone,
	"disabled_by" uuid,
	"override_by_system_admin" 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,
	CONSTRAINT "tenant_modules_tenant_id_module_id_pk" PRIMARY KEY("tenant_id","module_id"),
	CONSTRAINT "module_id_valid" CHECK ("tenant_modules"."module_id" IN (
        'system','crm','customers','time_management','projects','tasks',
        'invoices','expenses','billing','calendar','marketing','reports',
        'kb','contractor_payouts','ai_assistant'
      ))
);
--> statement-breakpoint
CREATE TABLE "vat_rates" (
	"country_code" text NOT NULL,
	"effective_from" date NOT NULL,
	"rate" numeric(5, 4) NOT NULL,
	CONSTRAINT "vat_rates_country_code_effective_from_pk" PRIMARY KEY("country_code","effective_from")
);
--> statement-breakpoint
ALTER TABLE "tenant_memberships" DROP CONSTRAINT "tenant_memberships_status_check";--> statement-breakpoint
ALTER TABLE "users" ADD COLUMN "two_factor_enabled" boolean DEFAULT false NOT NULL;--> statement-breakpoint
ALTER TABLE "users" ADD COLUMN "two_factor_phone" text;--> statement-breakpoint
ALTER TABLE "users" ADD COLUMN "two_factor_phone_suffix" text;--> statement-breakpoint
ALTER TABLE "tenants" ADD COLUMN "enforce_2fa" boolean DEFAULT false NOT NULL;--> statement-breakpoint
ALTER TABLE "tenants" ADD COLUMN "disable_2fa_remember_device" boolean DEFAULT false NOT NULL;--> statement-breakpoint
ALTER TABLE "tenants" ADD COLUMN "country_code" text DEFAULT 'IL' NOT NULL;--> statement-breakpoint
ALTER TABLE "tenants" ADD COLUMN "default_currency" text DEFAULT 'ILS' NOT NULL;--> statement-breakpoint
ALTER TABLE "tenants" ADD COLUMN "default_timezone" text DEFAULT 'Asia/Jerusalem' NOT NULL;--> statement-breakpoint
ALTER TABLE "user_preferences" ADD COLUMN "ui_theme" text DEFAULT 'dark';--> statement-breakpoint
ALTER TABLE "ai_credit_purchases" ADD CONSTRAINT "ai_credit_purchases_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_global_config" ADD CONSTRAINT "ai_global_config_main_model_id_ai_model_pricing_model_id_fk" FOREIGN KEY ("main_model_id") REFERENCES "public"."ai_model_pricing"("model_id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "ai_global_config" ADD CONSTRAINT "ai_global_config_updated_by_admin_users_id_fk" FOREIGN KEY ("updated_by") REFERENCES "public"."admin_users"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "ai_tenant_settings" ADD CONSTRAINT "ai_tenant_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 "ai_usage_log" ADD CONSTRAINT "ai_usage_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 "ai_usage_log" ADD CONSTRAINT "ai_usage_log_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 "adapter_credentials" ADD CONSTRAINT "adapter_credentials_tenant_id_tenants_id_fk" FOREIGN KEY ("tenant_id") REFERENCES "public"."tenants"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "notifications" ADD CONSTRAINT "notifications_tenant_id_tenants_id_fk" FOREIGN KEY ("tenant_id") REFERENCES "public"."tenants"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "notifications" ADD CONSTRAINT "notifications_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "push_subscriptions" ADD CONSTRAINT "push_subscriptions_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "push_subscriptions" ADD CONSTRAINT "push_subscriptions_tenant_id_tenants_id_fk" FOREIGN KEY ("tenant_id") REFERENCES "public"."tenants"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "customer_communications" ADD CONSTRAINT "customer_communications_tenant_id_tenants_id_fk" FOREIGN KEY ("tenant_id") REFERENCES "public"."tenants"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "customer_communications" ADD CONSTRAINT "customer_communications_customer_id_customers_id_fk" FOREIGN KEY ("customer_id") REFERENCES "public"."customers"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "customer_communications" ADD CONSTRAINT "customer_communications_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 "customer_contacts" ADD CONSTRAINT "customer_contacts_customer_id_customers_id_fk" FOREIGN KEY ("customer_id") REFERENCES "public"."customers"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "customer_contacts" ADD CONSTRAINT "customer_contacts_tenant_id_tenants_id_fk" FOREIGN KEY ("tenant_id") REFERENCES "public"."tenants"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "customer_portal_users" ADD CONSTRAINT "customer_portal_users_customer_id_customers_id_fk" FOREIGN KEY ("customer_id") REFERENCES "public"."customers"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "customer_portal_users" ADD CONSTRAINT "customer_portal_users_tenant_id_tenants_id_fk" FOREIGN KEY ("tenant_id") REFERENCES "public"."tenants"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "customer_portal_users" ADD CONSTRAINT "customer_portal_users_contact_id_customer_contacts_id_fk" FOREIGN KEY ("contact_id") REFERENCES "public"."customer_contacts"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "customer_portal_users" ADD CONSTRAINT "customer_portal_users_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "customers" ADD CONSTRAINT "customers_tenant_id_tenants_id_fk" FOREIGN KEY ("tenant_id") REFERENCES "public"."tenants"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "user_2fa_backup_codes" ADD CONSTRAINT "user_2fa_backup_codes_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "user_trusted_devices" ADD CONSTRAINT "user_trusted_devices_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "user_trusted_devices" ADD CONSTRAINT "user_trusted_devices_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_modules" ADD CONSTRAINT "tenant_modules_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_modules" ADD CONSTRAINT "tenant_modules_disabled_by_users_id_fk" FOREIGN KEY ("disabled_by") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
CREATE UNIQUE INDEX "ai_global_config_singleton" ON "ai_global_config" USING btree ((true));--> statement-breakpoint
CREATE INDEX "ai_usage_log_tenant_period" ON "ai_usage_log" USING btree ("tenant_id","created_at");--> statement-breakpoint
CREATE INDEX "ai_usage_log_use_case" ON "ai_usage_log" USING btree ("tenant_id","use_case","created_at");--> statement-breakpoint
CREATE INDEX "idx_adapter_credentials_tenant" ON "adapter_credentials" USING btree ("tenant_id");--> statement-breakpoint
CREATE INDEX "notifications_inbox" ON "notifications" USING btree ("tenant_id","user_id","read_at","created_at" DESC NULLS LAST);--> statement-breakpoint
CREATE INDEX "idx_push_subs_user" ON "push_subscriptions" USING btree ("user_id","tenant_id");--> statement-breakpoint
CREATE INDEX "idx_customer_comms_customer" ON "customer_communications" USING btree ("tenant_id","customer_id","sent_at" DESC NULLS LAST);--> statement-breakpoint
CREATE INDEX "idx_customer_contacts_customer" ON "customer_contacts" USING btree ("tenant_id","customer_id");--> statement-breakpoint
CREATE INDEX "idx_customer_portal_users_customer" ON "customer_portal_users" USING btree ("tenant_id","customer_id");--> statement-breakpoint
CREATE INDEX "idx_customers_tenant_status" ON "customers" USING btree ("tenant_id","status","created_at");--> statement-breakpoint
CREATE INDEX "idx_customers_tenant_name" ON "customers" USING btree ("tenant_id","name");--> statement-breakpoint
CREATE INDEX "idx_2fa_backup_codes_user" ON "user_2fa_backup_codes" USING btree ("user_id") WHERE "user_2fa_backup_codes"."used_at" is null;--> statement-breakpoint
CREATE INDEX "idx_trusted_devices_user" ON "user_trusted_devices" USING btree ("user_id","tenant_id");--> statement-breakpoint
CREATE INDEX "idx_tenant_modules_tenant_id" ON "tenant_modules" USING btree ("tenant_id");--> statement-breakpoint
ALTER TABLE "tenants" ADD CONSTRAINT "tenants_country_code_check" CHECK ("tenants"."country_code" IN ('IL'));--> statement-breakpoint
ALTER TABLE "tenant_memberships" ADD CONSTRAINT "tenant_memberships_status_check" CHECK ("tenant_memberships"."status" IN ('active', 'frozen', 'pending_approval'));--> statement-breakpoint
ALTER TABLE "user_preferences" ADD CONSTRAINT "user_preferences_ui_theme_check" CHECK (ui_theme IN ('dark','light','system'));