CREATE TABLE "export_jobs" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"tenant_id" uuid NOT NULL,
	"created_by" uuid NOT NULL,
	"status" text DEFAULT 'pending' NOT NULL,
	"download_url" text,
	"error_message" text,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	"completed_at" timestamp with time zone,
	CONSTRAINT "export_jobs_status_check" CHECK ("export_jobs"."status" IN ('pending','processing','completed','failed'))
);
--> statement-breakpoint
CREATE TABLE "einvoice_submissions" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"tenant_id" uuid NOT NULL,
	"invoice_id" uuid NOT NULL,
	"ack_number" text,
	"status" text DEFAULT 'submitted' NOT NULL,
	"error_message" text,
	"submitted_by" uuid,
	"submitted_at" timestamp with time zone DEFAULT now() NOT NULL,
	CONSTRAINT "einvoice_submissions_tenant_invoice_unique" UNIQUE("tenant_id","invoice_id"),
	CONSTRAINT "einvoice_submissions_status_check" CHECK ("einvoice_submissions"."status" IN ('submitted','failed'))
);
--> statement-breakpoint
CREATE TABLE "exchange_rates" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"tenant_id" uuid NOT NULL,
	"from_currency" text NOT NULL,
	"to_currency" text NOT NULL,
	"rate" numeric(18, 8) NOT NULL,
	"source" text DEFAULT 'manual',
	"effective_date" date NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "time_approval_requests" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"tenant_id" uuid NOT NULL,
	"submitter_id" uuid NOT NULL,
	"manager_id" uuid,
	"period_start" text NOT NULL,
	"period_end" text NOT NULL,
	"entry_ids" jsonb NOT NULL,
	"status" text DEFAULT 'pending' NOT NULL,
	"submitted_at" timestamp with time zone DEFAULT now() NOT NULL,
	"reviewed_at" timestamp with time zone,
	"reviewed_by" uuid,
	"comments" text,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	CONSTRAINT "time_approval_requests_status_check" CHECK ("time_approval_requests"."status" IN ('pending','approved','rejected'))
);
--> statement-breakpoint
CREATE TABLE "email_sequence_enrollments" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"sequence_id" uuid NOT NULL,
	"tenant_id" uuid NOT NULL,
	"contact_email" text NOT NULL,
	"lead_id" uuid,
	"current_step" integer DEFAULT 0 NOT NULL,
	"status" text DEFAULT 'active' NOT NULL,
	"enrolled_at" timestamp with time zone DEFAULT now() NOT NULL,
	"next_send_at" timestamp with time zone,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	CONSTRAINT "email_sequence_enrollments_status_check" CHECK ("email_sequence_enrollments"."status" IN ('active','completed','unsubscribed'))
);
--> statement-breakpoint
CREATE TABLE "email_sequence_steps" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"sequence_id" uuid NOT NULL,
	"tenant_id" uuid NOT NULL,
	"step_number" integer NOT NULL,
	"delay_days" integer DEFAULT 0 NOT NULL,
	"subject" text NOT NULL,
	"body_html" text NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "email_sequences" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"tenant_id" uuid NOT NULL,
	"name" text NOT NULL,
	"trigger_type" text DEFAULT 'manual' NOT NULL,
	"is_active" boolean DEFAULT true NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	CONSTRAINT "email_sequences_trigger_check" CHECK ("email_sequences"."trigger_type" IN ('manual','lead_created','proposal_sent','invoice_overdue'))
);
--> statement-breakpoint
CREATE TABLE "white_label_configs" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"tenant_id" uuid NOT NULL,
	"custom_domain" text,
	"ssl_status" text DEFAULT 'pending' NOT NULL,
	"brand_name" text,
	"logo_url" text,
	"primary_color" text,
	"favicon_url" text,
	"custom_css" text,
	"api_domain" text,
	"portal_domain" text,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	"updated_at" timestamp with time zone DEFAULT now() NOT NULL,
	CONSTRAINT "white_label_configs_tenant_id_unique" UNIQUE("tenant_id"),
	CONSTRAINT "white_label_configs_custom_domain_unique" UNIQUE("custom_domain"),
	CONSTRAINT "white_label_configs_ssl_status_check" CHECK ("white_label_configs"."ssl_status" IN ('pending', 'active', 'failed'))
);
--> statement-breakpoint
CREATE TABLE "recurring_expense_templates" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"tenant_id" uuid NOT NULL,
	"created_by" uuid NOT NULL,
	"description" text NOT NULL,
	"amount" numeric(12, 2) NOT NULL,
	"currency" text DEFAULT 'ILS' NOT NULL,
	"category_id" text,
	"vendor_name" text,
	"interval" text NOT NULL,
	"day_of_month" integer,
	"next_due_at" timestamp with time zone NOT NULL,
	"is_active" boolean DEFAULT true NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	CONSTRAINT "recurring_expense_templates_interval_check" CHECK ("recurring_expense_templates"."interval" IN ('daily','weekly','monthly','yearly')),
	CONSTRAINT "recurring_expense_templates_day_of_month_check" CHECK ("recurring_expense_templates"."day_of_month" IS NULL OR ("recurring_expense_templates"."day_of_month" >= 1 AND "recurring_expense_templates"."day_of_month" <= 31)),
	CONSTRAINT "recurring_expense_templates_amount_check" CHECK ("recurring_expense_templates"."amount" > 0)
);
--> statement-breakpoint
CREATE TABLE "mileage_entries" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"tenant_id" uuid NOT NULL,
	"user_id" uuid NOT NULL,
	"date" timestamp with time zone NOT NULL,
	"from_location" text NOT NULL,
	"to_location" text NOT NULL,
	"distance_km" numeric(8, 2) NOT NULL,
	"purpose" text NOT NULL,
	"vehicle_id" text,
	"is_business_trip" boolean DEFAULT true NOT NULL,
	"customer_id" uuid,
	"project_id" uuid,
	"reimbursement_amount" numeric(12, 2),
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	CONSTRAINT "mileage_entries_distance_check" CHECK ("mileage_entries"."distance_km" > 0)
);
--> statement-breakpoint
CREATE TABLE "payment_gateway_configs" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"tenant_id" uuid NOT NULL,
	"gateway" text NOT NULL,
	"encrypted_config" text NOT NULL,
	"is_active" boolean DEFAULT false NOT NULL,
	"test_mode" 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 "payment_gateway_configs_tenant_unique" UNIQUE("tenant_id"),
	CONSTRAINT "payment_gateway_configs_gateway_check" CHECK ("payment_gateway_configs"."gateway" IN ('cardcom','payplus','stripe'))
);
--> statement-breakpoint
ALTER TABLE "customer_portal_users" ADD COLUMN "modules_enabled" jsonb DEFAULT '[]'::jsonb NOT NULL;--> statement-breakpoint
ALTER TABLE "zync_subscriptions" ADD COLUMN "cancel_reason" text;--> statement-breakpoint
ALTER TABLE "zync_subscriptions" ADD COLUMN "cancel_feedback" text;--> statement-breakpoint
ALTER TABLE "zync_subscriptions" ADD COLUMN "cancel_intent_at" timestamp with time zone;--> statement-breakpoint
ALTER TABLE "zync_subscriptions" ADD COLUMN "cancel_at_period_end" boolean DEFAULT false NOT NULL;--> statement-breakpoint
ALTER TABLE "time_entries" ADD COLUMN "submitted_at" timestamp with time zone;--> statement-breakpoint
ALTER TABLE "time_entries" ADD COLUMN "approved_at" timestamp with time zone;--> statement-breakpoint
ALTER TABLE "time_entries" ADD COLUMN "approved_by" uuid;--> statement-breakpoint
ALTER TABLE "time_entries" ADD COLUMN "rejected_at" timestamp with time zone;--> statement-breakpoint
ALTER TABLE "time_entries" ADD COLUMN "rejection_reason" text;--> statement-breakpoint
ALTER TABLE "proposals" ADD COLUMN "contract_id" uuid;--> statement-breakpoint
ALTER TABLE "export_jobs" ADD CONSTRAINT "export_jobs_tenant_id_tenants_id_fk" FOREIGN KEY ("tenant_id") REFERENCES "public"."tenants"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "export_jobs" ADD CONSTRAINT "export_jobs_created_by_users_id_fk" FOREIGN KEY ("created_by") REFERENCES "public"."users"("id") ON DELETE restrict ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "einvoice_submissions" ADD CONSTRAINT "einvoice_submissions_tenant_id_tenants_id_fk" FOREIGN KEY ("tenant_id") REFERENCES "public"."tenants"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "einvoice_submissions" ADD CONSTRAINT "einvoice_submissions_invoice_id_invoices_id_fk" FOREIGN KEY ("invoice_id") REFERENCES "public"."invoices"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "einvoice_submissions" ADD CONSTRAINT "einvoice_submissions_submitted_by_users_id_fk" FOREIGN KEY ("submitted_by") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "time_approval_requests" ADD CONSTRAINT "time_approval_requests_tenant_id_tenants_id_fk" FOREIGN KEY ("tenant_id") REFERENCES "public"."tenants"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "email_sequence_enrollments" ADD CONSTRAINT "email_sequence_enrollments_sequence_id_email_sequences_id_fk" FOREIGN KEY ("sequence_id") REFERENCES "public"."email_sequences"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "email_sequence_steps" ADD CONSTRAINT "email_sequence_steps_sequence_id_email_sequences_id_fk" FOREIGN KEY ("sequence_id") REFERENCES "public"."email_sequences"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "white_label_configs" ADD CONSTRAINT "white_label_configs_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_expense_templates" ADD CONSTRAINT "recurring_expense_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 "recurring_expense_templates" ADD CONSTRAINT "recurring_expense_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 "mileage_entries" ADD CONSTRAINT "mileage_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 "mileage_entries" ADD CONSTRAINT "mileage_entries_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE restrict ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "mileage_entries" ADD CONSTRAINT "mileage_entries_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 "mileage_entries" ADD CONSTRAINT "mileage_entries_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 "payment_gateway_configs" ADD CONSTRAINT "payment_gateway_configs_tenant_id_tenants_id_fk" FOREIGN KEY ("tenant_id") REFERENCES "public"."tenants"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
CREATE INDEX "idx_export_jobs_tenant_created" ON "export_jobs" USING btree ("tenant_id","created_at");--> statement-breakpoint
CREATE INDEX "idx_einvoice_submissions_tenant" ON "einvoice_submissions" USING btree ("tenant_id","submitted_at" DESC NULLS LAST);--> statement-breakpoint
CREATE UNIQUE INDEX "exchange_rates_pair_date_uniq" ON "exchange_rates" USING btree ("tenant_id","from_currency","to_currency","effective_date");--> statement-breakpoint
CREATE INDEX "idx_time_approval_requests_tenant" ON "time_approval_requests" USING btree ("tenant_id","created_at");--> statement-breakpoint
CREATE INDEX "idx_time_approval_requests_submitter" ON "time_approval_requests" USING btree ("tenant_id","submitter_id");--> statement-breakpoint
CREATE INDEX "idx_email_sequence_enrollments_sequence" ON "email_sequence_enrollments" USING btree ("sequence_id");--> statement-breakpoint
CREATE INDEX "idx_email_sequence_enrollments_tenant" ON "email_sequence_enrollments" USING btree ("tenant_id");--> statement-breakpoint
CREATE INDEX "idx_email_sequence_enrollments_next_send" ON "email_sequence_enrollments" USING btree ("tenant_id","next_send_at");--> statement-breakpoint
CREATE INDEX "idx_email_sequence_steps_sequence" ON "email_sequence_steps" USING btree ("sequence_id");--> statement-breakpoint
CREATE INDEX "idx_email_sequence_steps_tenant" ON "email_sequence_steps" USING btree ("tenant_id");--> statement-breakpoint
CREATE INDEX "idx_email_sequences_tenant" ON "email_sequences" USING btree ("tenant_id");--> statement-breakpoint
CREATE INDEX "idx_white_label_tenant" ON "white_label_configs" USING btree ("tenant_id");--> statement-breakpoint
CREATE UNIQUE INDEX "idx_white_label_domain" ON "white_label_configs" USING btree ("custom_domain");--> statement-breakpoint
CREATE INDEX "idx_payment_gateway_configs_tenant" ON "payment_gateway_configs" USING btree ("tenant_id");--> statement-breakpoint
-- raw DDL: indexes not emitted by drizzle-kit (no index() call in schema)
CREATE INDEX IF NOT EXISTS "idx_mileage_entries_tenant_user" ON "mileage_entries" ("tenant_id", "user_id", "date" DESC);--> statement-breakpoint
CREATE INDEX IF NOT EXISTS "idx_mileage_entries_date" ON "mileage_entries" ("tenant_id", "date");--> statement-breakpoint
CREATE INDEX IF NOT EXISTS "idx_ret_tenant_active" ON "recurring_expense_templates" ("tenant_id", "next_due_at") WHERE "is_active" = true;
ALTER TABLE "exchange_rates" ALTER COLUMN "tenant_id" DROP DEFAULT;