-- CreateEnum CREATE TYPE "VehicleStatus" AS ENUM ('AVAILABLE', 'RESERVED', 'SOLD', 'CONSIGNMENT', 'COMING_SOON', 'INACTIVE'); -- CreateEnum CREATE TYPE "VehicleOrigin" AS ENUM ('OWN', 'CONSIGNMENT'); -- CreateEnum CREATE TYPE "Currency" AS ENUM ('USD', 'PEN'); -- CreateEnum CREATE TYPE "Transmission" AS ENUM ('MANUAL', 'AUTOMATIC', 'CVT', 'OTHER'); -- CreateEnum CREATE TYPE "FuelType" AS ENUM ('GASOLINE', 'DIESEL', 'HYBRID', 'PLUG_IN_HYBRID', 'ELECTRIC', 'GLP', 'GNV'); -- CreateEnum CREATE TYPE "BodyType" AS ENUM ('SUV', 'SEDAN', 'HATCHBACK', 'PICKUP', 'SPORTS', 'VAN'); -- CreateEnum CREATE TYPE "FeatureCategory" AS ENUM ('EQUIPMENT', 'SAFETY', 'COMFORT', 'TECHNOLOGY', 'OTHER'); -- CreateEnum CREATE TYPE "DocumentType" AS ENUM ('DNI', 'CE', 'RUC', 'PASSPORT', 'OTHER'); -- CreateEnum CREATE TYPE "LeadType" AS ENUM ('PURCHASE', 'SELL', 'CONSIGNMENT', 'CONTACT'); -- CreateEnum CREATE TYPE "LeadStatus" AS ENUM ('NEW', 'PENDING_CONTACT', 'CONTACTED', 'APPOINTMENT', 'NEGOTIATION', 'RESERVED', 'SOLD', 'NOT_INTERESTED'); -- CreateEnum CREATE TYPE "LeadSource" AS ENUM ('WEB', 'WHATSAPP', 'FACEBOOK', 'INSTAGRAM', 'TIKTOK', 'GOOGLE', 'REFERIDO', 'OTRO'); -- CreateEnum CREATE TYPE "LeadActivityType" AS ENUM ('CREATED', 'STATUS_CHANGE', 'ASSIGNED', 'CALL', 'WHATSAPP', 'NOTE', 'APPOINTMENT', 'RESERVATION', 'SALE'); -- CreateEnum CREATE TYPE "AppointmentStatus" AS ENUM ('PENDIENTE', 'CONFIRMADA', 'REALIZADA', 'CANCELADA', 'NO_ASISTIO'); -- CreateEnum CREATE TYPE "ConsignmentStatus" AS ENUM ('SOLICITUD', 'CONTACTADO', 'EVALUACION', 'APROBADO', 'PUBLICADO', 'VENDIDO', 'RECHAZADO'); -- CreateEnum CREATE TYPE "ConsignmentPhotoKind" AS ENUM ('FRONT', 'REAR', 'SIDE', 'INTERIOR', 'DASHBOARD', 'OTHER'); -- CreateEnum CREATE TYPE "ChescoCommissionType" AS ENUM ('PERCENTAGE', 'FIXED'); -- CreateEnum CREATE TYPE "AdvisorCommissionType" AS ENUM ('NONE', 'PERCENTAGE', 'FIXED'); -- CreateEnum CREATE TYPE "CommissionStatus" AS ENUM ('PENDING', 'PAID', 'CANCELLED'); -- CreateEnum CREATE TYPE "OwnerPayoutStatus" AS ENUM ('PENDING', 'PAID', 'CANCELLED'); -- CreateEnum CREATE TYPE "ReservationStatus" AS ENUM ('ACTIVE', 'CONVERTED', 'CANCELLED', 'EXPIRED'); -- CreateEnum CREATE TYPE "SaleStatus" AS ENUM ('ACTIVE', 'ANNULLED'); -- CreateEnum CREATE TYPE "FileVisibility" AS ENUM ('PUBLIC', 'PRIVATE'); -- CreateEnum CREATE TYPE "NotificationType" AS ENUM ('LEAD_CREATED', 'LEAD_REASSIGNED', 'APPOINTMENT_CREATED', 'CONSIGNMENT_CREATED', 'CONSIGNMENT_AGREEMENT_EXPIRING', 'SALE_REGISTERED', 'SALE_ANNULLED'); -- CreateTable CREATE TABLE "roles" ( "id" TEXT NOT NULL, "key" TEXT NOT NULL, "name" TEXT NOT NULL, "description" TEXT, "is_system" BOOLEAN NOT NULL DEFAULT false, "created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP, "updated_at" TIMESTAMPTZ(3) NOT NULL, CONSTRAINT "roles_pkey" PRIMARY KEY ("id") ); -- CreateTable CREATE TABLE "permissions" ( "id" TEXT NOT NULL, "key" TEXT NOT NULL, "module" TEXT NOT NULL, "description" TEXT NOT NULL, "created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT "permissions_pkey" PRIMARY KEY ("id") ); -- CreateTable CREATE TABLE "role_permissions" ( "role_id" TEXT NOT NULL, "permission_id" TEXT NOT NULL, CONSTRAINT "role_permissions_pkey" PRIMARY KEY ("role_id","permission_id") ); -- CreateTable CREATE TABLE "users" ( "id" TEXT NOT NULL, "name" TEXT NOT NULL, "email" TEXT NOT NULL, "phone" TEXT, "password_hash" TEXT NOT NULL, "role_id" TEXT NOT NULL, "is_active" BOOLEAN NOT NULL DEFAULT true, "must_change_password" BOOLEAN NOT NULL DEFAULT false, "failed_login_count" INTEGER NOT NULL DEFAULT 0, "locked_until" TIMESTAMPTZ(3), "last_login_at" TIMESTAMPTZ(3), "last_login_ip" TEXT, "password_changed_at" TIMESTAMPTZ(3), "avatar_url" TEXT, "created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP, "updated_at" TIMESTAMPTZ(3) NOT NULL, "deleted_at" TIMESTAMPTZ(3), CONSTRAINT "users_pkey" PRIMARY KEY ("id") ); -- CreateTable CREATE TABLE "sessions" ( "id" TEXT NOT NULL, "token_hash" TEXT NOT NULL, "user_id" TEXT NOT NULL, "expires_at" TIMESTAMPTZ(3) NOT NULL, "idle_expires_at" TIMESTAMPTZ(3) NOT NULL, "ip" TEXT, "user_agent" TEXT, "last_seen_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP, "revoked_at" TIMESTAMPTZ(3), "revoke_reason" TEXT, "created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT "sessions_pkey" PRIMARY KEY ("id") ); -- CreateTable CREATE TABLE "login_attempts" ( "id" TEXT NOT NULL, "email" TEXT NOT NULL, "ip" TEXT NOT NULL, "success" BOOLEAN NOT NULL, "reason" TEXT, "created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT "login_attempts_pkey" PRIMARY KEY ("id") ); -- CreateTable CREATE TABLE "password_resets" ( "id" TEXT NOT NULL, "token_hash" TEXT NOT NULL, "user_id" TEXT NOT NULL, "expires_at" TIMESTAMPTZ(3) NOT NULL, "used_at" TIMESTAMPTZ(3), "created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT "password_resets_pkey" PRIMARY KEY ("id") ); -- CreateTable CREATE TABLE "brands" ( "id" TEXT NOT NULL, "name" TEXT NOT NULL, "slug" TEXT NOT NULL, "logo_url" TEXT, "is_active" BOOLEAN NOT NULL DEFAULT true, "created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP, "updated_at" TIMESTAMPTZ(3) NOT NULL, "deleted_at" TIMESTAMPTZ(3), CONSTRAINT "brands_pkey" PRIMARY KEY ("id") ); -- CreateTable CREATE TABLE "models" ( "id" TEXT NOT NULL, "brand_id" TEXT NOT NULL, "name" TEXT NOT NULL, "slug" TEXT NOT NULL, "created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP, "updated_at" TIMESTAMPTZ(3) NOT NULL, "deleted_at" TIMESTAMPTZ(3), CONSTRAINT "models_pkey" PRIMARY KEY ("id") ); -- CreateTable CREATE TABLE "vehicles" ( "id" TEXT NOT NULL, "code" TEXT NOT NULL, "slug" TEXT NOT NULL, "brand_id" TEXT NOT NULL, "model_id" TEXT NOT NULL, "version" TEXT, "year" INTEGER NOT NULL, "price" DECIMAL(12,2) NOT NULL, "currency" "Currency" NOT NULL DEFAULT 'USD', "mileage_km" INTEGER NOT NULL, "transmission" "Transmission" NOT NULL, "fuel_type" "FuelType" NOT NULL, "engine" TEXT, "color" TEXT, "body_type" "BodyType" NOT NULL, "description" TEXT, "plate" TEXT, "show_plate" BOOLEAN NOT NULL DEFAULT false, "status" "VehicleStatus" NOT NULL DEFAULT 'INACTIVE', "is_featured" BOOLEAN NOT NULL DEFAULT false, "is_demo" BOOLEAN NOT NULL DEFAULT false, "origin" "VehicleOrigin" NOT NULL DEFAULT 'OWN', "published_at" TIMESTAMPTZ(3), "meta_title" TEXT, "meta_description" TEXT, "views_count" INTEGER NOT NULL DEFAULT 0, "created_by_id" TEXT, "created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP, "updated_at" TIMESTAMPTZ(3) NOT NULL, "deleted_at" TIMESTAMPTZ(3), CONSTRAINT "vehicles_pkey" PRIMARY KEY ("id") ); -- CreateTable CREATE TABLE "vehicle_images" ( "id" TEXT NOT NULL, "vehicle_id" TEXT NOT NULL, "provider_public_id" TEXT NOT NULL, "url" TEXT NOT NULL, "width" INTEGER NOT NULL, "height" INTEGER NOT NULL, "position" INTEGER NOT NULL DEFAULT 0, "is_cover" BOOLEAN NOT NULL DEFAULT false, "alt" TEXT, "created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT "vehicle_images_pkey" PRIMARY KEY ("id") ); -- CreateTable CREATE TABLE "features" ( "id" TEXT NOT NULL, "name" TEXT NOT NULL, "category" "FeatureCategory" NOT NULL, "created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT "features_pkey" PRIMARY KEY ("id") ); -- CreateTable CREATE TABLE "vehicle_features" ( "vehicle_id" TEXT NOT NULL, "feature_id" TEXT NOT NULL, CONSTRAINT "vehicle_features_pkey" PRIMARY KEY ("vehicle_id","feature_id") ); -- CreateTable CREATE TABLE "vehicle_status_history" ( "id" TEXT NOT NULL, "vehicle_id" TEXT NOT NULL, "from_status" "VehicleStatus", "to_status" "VehicleStatus" NOT NULL, "user_id" TEXT, "reason" TEXT, "created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT "vehicle_status_history_pkey" PRIMARY KEY ("id") ); -- CreateTable CREATE TABLE "vehicle_slug_redirects" ( "id" TEXT NOT NULL, "old_slug" TEXT NOT NULL, "vehicle_id" TEXT NOT NULL, "created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT "vehicle_slug_redirects_pkey" PRIMARY KEY ("id") ); -- CreateTable CREATE TABLE "customers" ( "id" TEXT NOT NULL, "first_name" TEXT NOT NULL, "last_name" TEXT, "document_type" "DocumentType", "document_number" TEXT, "phone" TEXT, "email" TEXT, "notes" TEXT, "created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP, "updated_at" TIMESTAMPTZ(3) NOT NULL, "deleted_at" TIMESTAMPTZ(3), CONSTRAINT "customers_pkey" PRIMARY KEY ("id") ); -- CreateTable CREATE TABLE "leads" ( "id" TEXT NOT NULL, "number" SERIAL NOT NULL, "customer_id" TEXT NOT NULL, "vehicle_id" TEXT, "type" "LeadType" NOT NULL, "source" "LeadSource" NOT NULL DEFAULT 'WEB', "status" "LeadStatus" NOT NULL DEFAULT 'NEW', "advisor_id" TEXT, "utm_source" TEXT, "utm_medium" TEXT, "utm_campaign" TEXT, "utm_content" TEXT, "landing_url" TEXT, "message" TEXT, "last_contact_at" TIMESTAMPTZ(3), "next_action_at" TIMESTAMPTZ(3), "lost_reason" TEXT, "created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP, "updated_at" TIMESTAMPTZ(3) NOT NULL, "deleted_at" TIMESTAMPTZ(3), CONSTRAINT "leads_pkey" PRIMARY KEY ("id") ); -- CreateTable CREATE TABLE "lead_notes" ( "id" TEXT NOT NULL, "lead_id" TEXT NOT NULL, "user_id" TEXT NOT NULL, "body" TEXT NOT NULL, "created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP, "updated_at" TIMESTAMPTZ(3) NOT NULL, "deleted_at" TIMESTAMPTZ(3), CONSTRAINT "lead_notes_pkey" PRIMARY KEY ("id") ); -- CreateTable CREATE TABLE "lead_activities" ( "id" TEXT NOT NULL, "lead_id" TEXT NOT NULL, "user_id" TEXT, "type" "LeadActivityType" NOT NULL, "payload" JSONB, "created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT "lead_activities_pkey" PRIMARY KEY ("id") ); -- CreateTable CREATE TABLE "appointments" ( "id" TEXT NOT NULL, "lead_id" TEXT, "customer_id" TEXT NOT NULL, "vehicle_id" TEXT, "advisor_id" TEXT, "scheduled_at" TIMESTAMPTZ(3) NOT NULL, "duration_min" INTEGER NOT NULL DEFAULT 30, "status" "AppointmentStatus" NOT NULL DEFAULT 'PENDIENTE', "comments" TEXT, "created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP, "updated_at" TIMESTAMPTZ(3) NOT NULL, "deleted_at" TIMESTAMPTZ(3), CONSTRAINT "appointments_pkey" PRIMARY KEY ("id") ); -- CreateTable CREATE TABLE "consignments" ( "id" TEXT NOT NULL, "number" SERIAL NOT NULL, "owner_id" TEXT NOT NULL, "lead_id" TEXT, "advisor_id" TEXT, "brand_id" TEXT, "model_id" TEXT, "brand_text" TEXT, "model_text" TEXT, "version" TEXT, "year" INTEGER, "mileage_km" INTEGER, "plate" TEXT, "color" TEXT, "currency" "Currency" NOT NULL DEFAULT 'USD', "requested_price" DECIMAL(12,2), "recommended_price" DECIMAL(12,2), "minimum_price" DECIMAL(12,2), "chesco_commission_type" "ChescoCommissionType", "chesco_commission_value" DECIMAL(12,2), "agreement_start_at" TIMESTAMPTZ(3), "agreement_end_at" TIMESTAMPTZ(3), "agreement_signed_at" TIMESTAMPTZ(3), "contract_file_id" TEXT, "status" "ConsignmentStatus" NOT NULL DEFAULT 'SOLICITUD', "vehicle_id" TEXT, "rejection_reason" TEXT, "comments" TEXT, "created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP, "updated_at" TIMESTAMPTZ(3) NOT NULL, "deleted_at" TIMESTAMPTZ(3), CONSTRAINT "consignments_pkey" PRIMARY KEY ("id") ); -- CreateTable CREATE TABLE "consignment_images" ( "id" TEXT NOT NULL, "consignment_id" TEXT NOT NULL, "provider_public_id" TEXT NOT NULL, "url" TEXT NOT NULL, "kind" "ConsignmentPhotoKind" NOT NULL, "created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT "consignment_images_pkey" PRIMARY KEY ("id") ); -- CreateTable CREATE TABLE "reservations" ( "id" TEXT NOT NULL, "vehicle_id" TEXT NOT NULL, "customer_id" TEXT NOT NULL, "advisor_id" TEXT NOT NULL, "lead_id" TEXT, "amount" DECIMAL(12,2) NOT NULL, "currency" "Currency" NOT NULL, "reserved_at" TIMESTAMPTZ(3) NOT NULL, "expires_at" TIMESTAMPTZ(3), "status" "ReservationStatus" NOT NULL DEFAULT 'ACTIVE', "notes" TEXT, "cancelled_at" TIMESTAMPTZ(3), "cancelled_by_id" TEXT, "cancel_reason" TEXT, "created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP, "updated_at" TIMESTAMPTZ(3) NOT NULL, "deleted_at" TIMESTAMPTZ(3), CONSTRAINT "reservations_pkey" PRIMARY KEY ("id") ); -- CreateTable CREATE TABLE "sales" ( "id" TEXT NOT NULL, "number" SERIAL NOT NULL, "vehicle_id" TEXT NOT NULL, "customer_id" TEXT NOT NULL, "advisor_id" TEXT NOT NULL, "lead_id" TEXT, "reservation_id" TEXT, "consignment_id" TEXT, "final_price" DECIMAL(12,2) NOT NULL, "currency" "Currency" NOT NULL, "sold_at" TIMESTAMPTZ(3) NOT NULL, "notes" TEXT, "status" "SaleStatus" NOT NULL DEFAULT 'ACTIVE', "vehicle_status_before_sale" "VehicleStatus" NOT NULL, "advisor_commission_type" "AdvisorCommissionType" NOT NULL DEFAULT 'NONE', "advisor_commission_value" DECIMAL(12,2) NOT NULL DEFAULT 0, "advisor_commission_amount" DECIMAL(12,2) NOT NULL DEFAULT 0, "advisor_commission_status" "CommissionStatus", "advisor_commission_paid_at" TIMESTAMPTZ(3), "chesco_commission_type" "ChescoCommissionType", "chesco_commission_value" DECIMAL(12,2), "chesco_commission_amount" DECIMAL(12,2), "consignment_minimum_price" DECIMAL(12,2), "owner_payout_amount" DECIMAL(12,2), "owner_payout_status" "OwnerPayoutStatus", "owner_paid_at" TIMESTAMPTZ(3), "below_minimum_price_accepted" BOOLEAN NOT NULL DEFAULT false, "created_by_id" TEXT NOT NULL, "annulled_at" TIMESTAMPTZ(3), "annulled_by_id" TEXT, "annulment_reason" TEXT, "created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP, "updated_at" TIMESTAMPTZ(3) NOT NULL, CONSTRAINT "sales_pkey" PRIMARY KEY ("id") ); -- CreateTable CREATE TABLE "stored_files" ( "id" TEXT NOT NULL, "provider" TEXT NOT NULL, "provider_public_id" TEXT NOT NULL, "url" TEXT, "mime_type" TEXT NOT NULL, "size_bytes" INTEGER NOT NULL, "original_name" TEXT NOT NULL, "visibility" "FileVisibility" NOT NULL DEFAULT 'PRIVATE', "uploaded_by_id" TEXT, "created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT "stored_files_pkey" PRIMARY KEY ("id") ); -- CreateTable CREATE TABLE "favorites" ( "id" TEXT NOT NULL, "owner_key" TEXT NOT NULL, "vehicle_id" TEXT NOT NULL, "created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT "favorites_pkey" PRIMARY KEY ("id") ); -- CreateTable CREATE TABLE "notifications" ( "id" TEXT NOT NULL, "user_id" TEXT NOT NULL, "type" "NotificationType" NOT NULL, "title" TEXT NOT NULL, "body" TEXT, "link" TEXT, "read_at" TIMESTAMPTZ(3), "created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT "notifications_pkey" PRIMARY KEY ("id") ); -- CreateTable CREATE TABLE "audit_logs" ( "id" TEXT NOT NULL, "user_id" TEXT, "action" TEXT NOT NULL, "entity" TEXT NOT NULL, "entity_id" TEXT, "summary" TEXT NOT NULL, "diff" JSONB, "ip" TEXT, "user_agent" TEXT, "created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT "audit_logs_pkey" PRIMARY KEY ("id") ); -- CreateTable CREATE TABLE "settings" ( "key" TEXT NOT NULL, "value" JSONB NOT NULL, "is_public" BOOLEAN NOT NULL DEFAULT false, "updated_by_id" TEXT, "updated_at" TIMESTAMPTZ(3) NOT NULL, CONSTRAINT "settings_pkey" PRIMARY KEY ("key") ); -- CreateIndex CREATE UNIQUE INDEX "roles_key_key" ON "roles"("key"); -- CreateIndex CREATE UNIQUE INDEX "permissions_key_key" ON "permissions"("key"); -- CreateIndex CREATE INDEX "role_permissions_permission_id_idx" ON "role_permissions"("permission_id"); -- CreateIndex CREATE UNIQUE INDEX "users_email_key" ON "users"("email"); -- CreateIndex CREATE INDEX "users_role_id_idx" ON "users"("role_id"); -- CreateIndex CREATE INDEX "users_is_active_deleted_at_idx" ON "users"("is_active", "deleted_at"); -- CreateIndex CREATE UNIQUE INDEX "sessions_token_hash_key" ON "sessions"("token_hash"); -- CreateIndex CREATE INDEX "sessions_user_id_idx" ON "sessions"("user_id"); -- CreateIndex CREATE INDEX "sessions_expires_at_idx" ON "sessions"("expires_at"); -- CreateIndex CREATE INDEX "login_attempts_email_created_at_idx" ON "login_attempts"("email", "created_at"); -- CreateIndex CREATE INDEX "login_attempts_ip_created_at_idx" ON "login_attempts"("ip", "created_at"); -- CreateIndex CREATE UNIQUE INDEX "password_resets_token_hash_key" ON "password_resets"("token_hash"); -- CreateIndex CREATE INDEX "password_resets_user_id_idx" ON "password_resets"("user_id"); -- CreateIndex CREATE UNIQUE INDEX "brands_slug_key" ON "brands"("slug"); -- CreateIndex CREATE UNIQUE INDEX "models_brand_id_slug_key" ON "models"("brand_id", "slug"); -- CreateIndex CREATE UNIQUE INDEX "vehicles_code_key" ON "vehicles"("code"); -- CreateIndex CREATE UNIQUE INDEX "vehicles_slug_key" ON "vehicles"("slug"); -- CreateIndex CREATE INDEX "vehicles_status_idx" ON "vehicles"("status"); -- CreateIndex CREATE INDEX "vehicles_brand_id_idx" ON "vehicles"("brand_id"); -- CreateIndex CREATE INDEX "vehicles_model_id_idx" ON "vehicles"("model_id"); -- CreateIndex CREATE INDEX "vehicles_price_idx" ON "vehicles"("price"); -- CreateIndex CREATE INDEX "vehicles_year_idx" ON "vehicles"("year"); -- CreateIndex CREATE INDEX "vehicles_mileage_km_idx" ON "vehicles"("mileage_km"); -- CreateIndex CREATE INDEX "vehicles_body_type_idx" ON "vehicles"("body_type"); -- CreateIndex CREATE INDEX "vehicles_is_featured_idx" ON "vehicles"("is_featured"); -- CreateIndex CREATE INDEX "vehicles_plate_idx" ON "vehicles"("plate"); -- CreateIndex CREATE INDEX "vehicles_status_deleted_at_published_at_idx" ON "vehicles"("status", "deleted_at", "published_at" DESC); -- CreateIndex CREATE INDEX "vehicle_images_vehicle_id_position_idx" ON "vehicle_images"("vehicle_id", "position"); -- CreateIndex CREATE UNIQUE INDEX "vehicle_images_one_cover_per_vehicle" ON "vehicle_images"("vehicle_id") WHERE (is_cover = true); -- CreateIndex CREATE UNIQUE INDEX "features_name_category_key" ON "features"("name", "category"); -- CreateIndex CREATE INDEX "vehicle_features_feature_id_idx" ON "vehicle_features"("feature_id"); -- CreateIndex CREATE INDEX "vehicle_status_history_vehicle_id_created_at_idx" ON "vehicle_status_history"("vehicle_id", "created_at"); -- CreateIndex CREATE UNIQUE INDEX "vehicle_slug_redirects_old_slug_key" ON "vehicle_slug_redirects"("old_slug"); -- CreateIndex CREATE INDEX "vehicle_slug_redirects_vehicle_id_idx" ON "vehicle_slug_redirects"("vehicle_id"); -- CreateIndex CREATE INDEX "customers_phone_idx" ON "customers"("phone"); -- CreateIndex CREATE INDEX "customers_email_idx" ON "customers"("email"); -- CreateIndex CREATE INDEX "customers_document_number_idx" ON "customers"("document_number"); -- CreateIndex CREATE INDEX "customers_created_at_idx" ON "customers"("created_at"); -- CreateIndex CREATE UNIQUE INDEX "leads_number_key" ON "leads"("number"); -- CreateIndex CREATE INDEX "leads_status_idx" ON "leads"("status"); -- CreateIndex CREATE INDEX "leads_advisor_id_idx" ON "leads"("advisor_id"); -- CreateIndex CREATE INDEX "leads_source_idx" ON "leads"("source"); -- CreateIndex CREATE INDEX "leads_created_at_idx" ON "leads"("created_at"); -- CreateIndex CREATE INDEX "leads_customer_id_idx" ON "leads"("customer_id"); -- CreateIndex CREATE INDEX "leads_vehicle_id_idx" ON "leads"("vehicle_id"); -- CreateIndex CREATE INDEX "leads_advisor_id_status_created_at_idx" ON "leads"("advisor_id", "status", "created_at"); -- CreateIndex CREATE INDEX "leads_next_action_at_idx" ON "leads"("next_action_at"); -- CreateIndex CREATE INDEX "lead_notes_lead_id_created_at_idx" ON "lead_notes"("lead_id", "created_at"); -- CreateIndex CREATE INDEX "lead_activities_lead_id_created_at_idx" ON "lead_activities"("lead_id", "created_at"); -- CreateIndex CREATE INDEX "appointments_scheduled_at_idx" ON "appointments"("scheduled_at"); -- CreateIndex CREATE INDEX "appointments_advisor_id_scheduled_at_idx" ON "appointments"("advisor_id", "scheduled_at"); -- CreateIndex CREATE INDEX "appointments_status_idx" ON "appointments"("status"); -- CreateIndex CREATE INDEX "appointments_customer_id_idx" ON "appointments"("customer_id"); -- CreateIndex CREATE UNIQUE INDEX "consignments_number_key" ON "consignments"("number"); -- CreateIndex CREATE UNIQUE INDEX "consignments_lead_id_key" ON "consignments"("lead_id"); -- CreateIndex CREATE UNIQUE INDEX "consignments_contract_file_id_key" ON "consignments"("contract_file_id"); -- CreateIndex CREATE UNIQUE INDEX "consignments_vehicle_id_key" ON "consignments"("vehicle_id"); -- CreateIndex CREATE INDEX "consignments_status_idx" ON "consignments"("status"); -- CreateIndex CREATE INDEX "consignments_advisor_id_idx" ON "consignments"("advisor_id"); -- CreateIndex CREATE INDEX "consignments_owner_id_idx" ON "consignments"("owner_id"); -- CreateIndex CREATE INDEX "consignments_agreement_end_at_idx" ON "consignments"("agreement_end_at"); -- CreateIndex CREATE INDEX "consignment_images_consignment_id_idx" ON "consignment_images"("consignment_id"); -- CreateIndex CREATE INDEX "reservations_vehicle_id_idx" ON "reservations"("vehicle_id"); -- CreateIndex CREATE INDEX "reservations_advisor_id_idx" ON "reservations"("advisor_id"); -- CreateIndex CREATE INDEX "reservations_status_idx" ON "reservations"("status"); -- CreateIndex CREATE UNIQUE INDEX "reservations_one_active_per_vehicle" ON "reservations"("vehicle_id") WHERE (status = 'ACTIVE' AND deleted_at IS NULL); -- CreateIndex CREATE UNIQUE INDEX "sales_number_key" ON "sales"("number"); -- CreateIndex CREATE UNIQUE INDEX "sales_reservation_id_key" ON "sales"("reservation_id"); -- CreateIndex CREATE INDEX "sales_vehicle_id_idx" ON "sales"("vehicle_id"); -- CreateIndex CREATE INDEX "sales_advisor_id_sold_at_idx" ON "sales"("advisor_id", "sold_at"); -- CreateIndex CREATE INDEX "sales_sold_at_idx" ON "sales"("sold_at"); -- CreateIndex CREATE INDEX "sales_status_idx" ON "sales"("status"); -- CreateIndex CREATE INDEX "sales_advisor_commission_status_idx" ON "sales"("advisor_commission_status"); -- CreateIndex CREATE INDEX "sales_owner_payout_status_idx" ON "sales"("owner_payout_status"); -- CreateIndex CREATE INDEX "sales_consignment_id_idx" ON "sales"("consignment_id"); -- CreateIndex CREATE UNIQUE INDEX "sales_one_active_per_vehicle" ON "sales"("vehicle_id") WHERE (status = 'ACTIVE'); -- CreateIndex CREATE INDEX "favorites_vehicle_id_idx" ON "favorites"("vehicle_id"); -- CreateIndex CREATE UNIQUE INDEX "favorites_owner_key_vehicle_id_key" ON "favorites"("owner_key", "vehicle_id"); -- CreateIndex CREATE INDEX "notifications_user_id_read_at_idx" ON "notifications"("user_id", "read_at"); -- CreateIndex CREATE INDEX "notifications_user_id_created_at_idx" ON "notifications"("user_id", "created_at"); -- CreateIndex CREATE INDEX "audit_logs_entity_entity_id_idx" ON "audit_logs"("entity", "entity_id"); -- CreateIndex CREATE INDEX "audit_logs_user_id_created_at_idx" ON "audit_logs"("user_id", "created_at"); -- CreateIndex CREATE INDEX "audit_logs_action_idx" ON "audit_logs"("action"); -- CreateIndex CREATE INDEX "audit_logs_created_at_idx" ON "audit_logs"("created_at"); -- AddForeignKey ALTER TABLE "role_permissions" ADD CONSTRAINT "role_permissions_role_id_fkey" FOREIGN KEY ("role_id") REFERENCES "roles"("id") ON DELETE CASCADE ON UPDATE CASCADE; -- AddForeignKey ALTER TABLE "role_permissions" ADD CONSTRAINT "role_permissions_permission_id_fkey" FOREIGN KEY ("permission_id") REFERENCES "permissions"("id") ON DELETE CASCADE ON UPDATE CASCADE; -- AddForeignKey ALTER TABLE "users" ADD CONSTRAINT "users_role_id_fkey" FOREIGN KEY ("role_id") REFERENCES "roles"("id") ON DELETE RESTRICT ON UPDATE CASCADE; -- AddForeignKey ALTER TABLE "sessions" ADD CONSTRAINT "sessions_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "users"("id") ON DELETE CASCADE ON UPDATE CASCADE; -- AddForeignKey ALTER TABLE "password_resets" ADD CONSTRAINT "password_resets_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "users"("id") ON DELETE CASCADE ON UPDATE CASCADE; -- AddForeignKey ALTER TABLE "models" ADD CONSTRAINT "models_brand_id_fkey" FOREIGN KEY ("brand_id") REFERENCES "brands"("id") ON DELETE RESTRICT ON UPDATE CASCADE; -- AddForeignKey ALTER TABLE "vehicles" ADD CONSTRAINT "vehicles_brand_id_fkey" FOREIGN KEY ("brand_id") REFERENCES "brands"("id") ON DELETE RESTRICT ON UPDATE CASCADE; -- AddForeignKey ALTER TABLE "vehicles" ADD CONSTRAINT "vehicles_model_id_fkey" FOREIGN KEY ("model_id") REFERENCES "models"("id") ON DELETE RESTRICT ON UPDATE CASCADE; -- AddForeignKey ALTER TABLE "vehicles" ADD CONSTRAINT "vehicles_created_by_id_fkey" FOREIGN KEY ("created_by_id") REFERENCES "users"("id") ON DELETE SET NULL ON UPDATE CASCADE; -- AddForeignKey ALTER TABLE "vehicle_images" ADD CONSTRAINT "vehicle_images_vehicle_id_fkey" FOREIGN KEY ("vehicle_id") REFERENCES "vehicles"("id") ON DELETE CASCADE ON UPDATE CASCADE; -- AddForeignKey ALTER TABLE "vehicle_features" ADD CONSTRAINT "vehicle_features_vehicle_id_fkey" FOREIGN KEY ("vehicle_id") REFERENCES "vehicles"("id") ON DELETE CASCADE ON UPDATE CASCADE; -- AddForeignKey ALTER TABLE "vehicle_features" ADD CONSTRAINT "vehicle_features_feature_id_fkey" FOREIGN KEY ("feature_id") REFERENCES "features"("id") ON DELETE CASCADE ON UPDATE CASCADE; -- AddForeignKey ALTER TABLE "vehicle_status_history" ADD CONSTRAINT "vehicle_status_history_vehicle_id_fkey" FOREIGN KEY ("vehicle_id") REFERENCES "vehicles"("id") ON DELETE CASCADE ON UPDATE CASCADE; -- AddForeignKey ALTER TABLE "vehicle_status_history" ADD CONSTRAINT "vehicle_status_history_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "users"("id") ON DELETE SET NULL ON UPDATE CASCADE; -- AddForeignKey ALTER TABLE "vehicle_slug_redirects" ADD CONSTRAINT "vehicle_slug_redirects_vehicle_id_fkey" FOREIGN KEY ("vehicle_id") REFERENCES "vehicles"("id") ON DELETE CASCADE ON UPDATE CASCADE; -- AddForeignKey ALTER TABLE "leads" ADD CONSTRAINT "leads_customer_id_fkey" FOREIGN KEY ("customer_id") REFERENCES "customers"("id") ON DELETE RESTRICT ON UPDATE CASCADE; -- AddForeignKey ALTER TABLE "leads" ADD CONSTRAINT "leads_vehicle_id_fkey" FOREIGN KEY ("vehicle_id") REFERENCES "vehicles"("id") ON DELETE SET NULL ON UPDATE CASCADE; -- AddForeignKey ALTER TABLE "leads" ADD CONSTRAINT "leads_advisor_id_fkey" FOREIGN KEY ("advisor_id") REFERENCES "users"("id") ON DELETE SET NULL ON UPDATE CASCADE; -- AddForeignKey ALTER TABLE "lead_notes" ADD CONSTRAINT "lead_notes_lead_id_fkey" FOREIGN KEY ("lead_id") REFERENCES "leads"("id") ON DELETE RESTRICT ON UPDATE CASCADE; -- AddForeignKey ALTER TABLE "lead_notes" ADD CONSTRAINT "lead_notes_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "users"("id") ON DELETE RESTRICT ON UPDATE CASCADE; -- AddForeignKey ALTER TABLE "lead_activities" ADD CONSTRAINT "lead_activities_lead_id_fkey" FOREIGN KEY ("lead_id") REFERENCES "leads"("id") ON DELETE RESTRICT ON UPDATE CASCADE; -- AddForeignKey ALTER TABLE "lead_activities" ADD CONSTRAINT "lead_activities_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "users"("id") ON DELETE SET NULL ON UPDATE CASCADE; -- AddForeignKey ALTER TABLE "appointments" ADD CONSTRAINT "appointments_lead_id_fkey" FOREIGN KEY ("lead_id") REFERENCES "leads"("id") ON DELETE SET NULL ON UPDATE CASCADE; -- AddForeignKey ALTER TABLE "appointments" ADD CONSTRAINT "appointments_customer_id_fkey" FOREIGN KEY ("customer_id") REFERENCES "customers"("id") ON DELETE RESTRICT ON UPDATE CASCADE; -- AddForeignKey ALTER TABLE "appointments" ADD CONSTRAINT "appointments_vehicle_id_fkey" FOREIGN KEY ("vehicle_id") REFERENCES "vehicles"("id") ON DELETE SET NULL ON UPDATE CASCADE; -- AddForeignKey ALTER TABLE "appointments" ADD CONSTRAINT "appointments_advisor_id_fkey" FOREIGN KEY ("advisor_id") REFERENCES "users"("id") ON DELETE SET NULL ON UPDATE CASCADE; -- AddForeignKey ALTER TABLE "consignments" ADD CONSTRAINT "consignments_owner_id_fkey" FOREIGN KEY ("owner_id") REFERENCES "customers"("id") ON DELETE RESTRICT ON UPDATE CASCADE; -- AddForeignKey ALTER TABLE "consignments" ADD CONSTRAINT "consignments_lead_id_fkey" FOREIGN KEY ("lead_id") REFERENCES "leads"("id") ON DELETE SET NULL ON UPDATE CASCADE; -- AddForeignKey ALTER TABLE "consignments" ADD CONSTRAINT "consignments_advisor_id_fkey" FOREIGN KEY ("advisor_id") REFERENCES "users"("id") ON DELETE SET NULL ON UPDATE CASCADE; -- AddForeignKey ALTER TABLE "consignments" ADD CONSTRAINT "consignments_brand_id_fkey" FOREIGN KEY ("brand_id") REFERENCES "brands"("id") ON DELETE SET NULL ON UPDATE CASCADE; -- AddForeignKey ALTER TABLE "consignments" ADD CONSTRAINT "consignments_model_id_fkey" FOREIGN KEY ("model_id") REFERENCES "models"("id") ON DELETE SET NULL ON UPDATE CASCADE; -- AddForeignKey ALTER TABLE "consignments" ADD CONSTRAINT "consignments_contract_file_id_fkey" FOREIGN KEY ("contract_file_id") REFERENCES "stored_files"("id") ON DELETE SET NULL ON UPDATE CASCADE; -- AddForeignKey ALTER TABLE "consignments" ADD CONSTRAINT "consignments_vehicle_id_fkey" FOREIGN KEY ("vehicle_id") REFERENCES "vehicles"("id") ON DELETE SET NULL ON UPDATE CASCADE; -- AddForeignKey ALTER TABLE "consignment_images" ADD CONSTRAINT "consignment_images_consignment_id_fkey" FOREIGN KEY ("consignment_id") REFERENCES "consignments"("id") ON DELETE CASCADE ON UPDATE CASCADE; -- AddForeignKey ALTER TABLE "reservations" ADD CONSTRAINT "reservations_vehicle_id_fkey" FOREIGN KEY ("vehicle_id") REFERENCES "vehicles"("id") ON DELETE RESTRICT ON UPDATE CASCADE; -- AddForeignKey ALTER TABLE "reservations" ADD CONSTRAINT "reservations_customer_id_fkey" FOREIGN KEY ("customer_id") REFERENCES "customers"("id") ON DELETE RESTRICT ON UPDATE CASCADE; -- AddForeignKey ALTER TABLE "reservations" ADD CONSTRAINT "reservations_advisor_id_fkey" FOREIGN KEY ("advisor_id") REFERENCES "users"("id") ON DELETE RESTRICT ON UPDATE CASCADE; -- AddForeignKey ALTER TABLE "reservations" ADD CONSTRAINT "reservations_lead_id_fkey" FOREIGN KEY ("lead_id") REFERENCES "leads"("id") ON DELETE SET NULL ON UPDATE CASCADE; -- AddForeignKey ALTER TABLE "reservations" ADD CONSTRAINT "reservations_cancelled_by_id_fkey" FOREIGN KEY ("cancelled_by_id") REFERENCES "users"("id") ON DELETE SET NULL ON UPDATE CASCADE; -- AddForeignKey ALTER TABLE "sales" ADD CONSTRAINT "sales_vehicle_id_fkey" FOREIGN KEY ("vehicle_id") REFERENCES "vehicles"("id") ON DELETE RESTRICT ON UPDATE CASCADE; -- AddForeignKey ALTER TABLE "sales" ADD CONSTRAINT "sales_customer_id_fkey" FOREIGN KEY ("customer_id") REFERENCES "customers"("id") ON DELETE RESTRICT ON UPDATE CASCADE; -- AddForeignKey ALTER TABLE "sales" ADD CONSTRAINT "sales_advisor_id_fkey" FOREIGN KEY ("advisor_id") REFERENCES "users"("id") ON DELETE RESTRICT ON UPDATE CASCADE; -- AddForeignKey ALTER TABLE "sales" ADD CONSTRAINT "sales_lead_id_fkey" FOREIGN KEY ("lead_id") REFERENCES "leads"("id") ON DELETE SET NULL ON UPDATE CASCADE; -- AddForeignKey ALTER TABLE "sales" ADD CONSTRAINT "sales_reservation_id_fkey" FOREIGN KEY ("reservation_id") REFERENCES "reservations"("id") ON DELETE SET NULL ON UPDATE CASCADE; -- AddForeignKey ALTER TABLE "sales" ADD CONSTRAINT "sales_consignment_id_fkey" FOREIGN KEY ("consignment_id") REFERENCES "consignments"("id") ON DELETE SET NULL ON UPDATE CASCADE; -- AddForeignKey ALTER TABLE "sales" ADD CONSTRAINT "sales_created_by_id_fkey" FOREIGN KEY ("created_by_id") REFERENCES "users"("id") ON DELETE RESTRICT ON UPDATE CASCADE; -- AddForeignKey ALTER TABLE "sales" ADD CONSTRAINT "sales_annulled_by_id_fkey" FOREIGN KEY ("annulled_by_id") REFERENCES "users"("id") ON DELETE SET NULL ON UPDATE CASCADE; -- AddForeignKey ALTER TABLE "stored_files" ADD CONSTRAINT "stored_files_uploaded_by_id_fkey" FOREIGN KEY ("uploaded_by_id") REFERENCES "users"("id") ON DELETE SET NULL ON UPDATE CASCADE; -- AddForeignKey ALTER TABLE "notifications" ADD CONSTRAINT "notifications_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "users"("id") ON DELETE CASCADE ON UPDATE CASCADE; -- AddForeignKey ALTER TABLE "audit_logs" ADD CONSTRAINT "audit_logs_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "users"("id") ON DELETE SET NULL ON UPDATE CASCADE; -- AddForeignKey ALTER TABLE "settings" ADD CONSTRAINT "settings_updated_by_id_fkey" FOREIGN KEY ("updated_by_id") REFERENCES "users"("id") ON DELETE SET NULL ON UPDATE CASCADE; -- ───────────────────────────────────────────────────────────── -- Restricciones de integridad (Prisma no las modela; se mantienen en SQL). -- Nota: cada rama compara NULL de forma explícita, porque en SQL un CHECK que evalúa NULL se acepta. -- ───────────────────────────────────────────────────────────── ALTER TABLE "vehicles" ADD CONSTRAINT "vehicles_price_non_negative" CHECK ("price" >= 0), ADD CONSTRAINT "vehicles_mileage_non_negative" CHECK ("mileage_km" >= 0), ADD CONSTRAINT "vehicles_year_range" CHECK ("year" BETWEEN 1950 AND 2100); ALTER TABLE "reservations" ADD CONSTRAINT "reservations_amount_non_negative" CHECK ("amount" >= 0); ALTER TABLE "consignments" ADD CONSTRAINT "consignments_prices_non_negative" CHECK ( coalesce("requested_price", 0) >= 0 AND coalesce("recommended_price", 0) >= 0 AND coalesce("minimum_price", 0) >= 0 ), ADD CONSTRAINT "consignments_commission_value" CHECK ( "chesco_commission_type" IS NULL OR ("chesco_commission_value" IS NOT NULL AND "chesco_commission_value" >= 0 AND ("chesco_commission_type" <> 'PERCENTAGE' OR "chesco_commission_value" <= 100)) ), ADD CONSTRAINT "consignments_agreement_dates" CHECK ( "agreement_start_at" IS NULL OR "agreement_end_at" IS NULL OR "agreement_end_at" > "agreement_start_at" ); ALTER TABLE "sales" ADD CONSTRAINT "sales_final_price_non_negative" CHECK ("final_price" >= 0), -- Comisión del asesor: NONE ⇒ valor 0, monto 0 y sin estado; con tipo ⇒ estado obligatorio. ADD CONSTRAINT "sales_advisor_commission_consistency" CHECK ( ("advisor_commission_type" = 'NONE' AND "advisor_commission_value" = 0 AND "advisor_commission_amount" = 0 AND "advisor_commission_status" IS NULL) OR ("advisor_commission_type" <> 'NONE' AND "advisor_commission_status" IS NOT NULL AND "advisor_commission_value" >= 0 AND "advisor_commission_amount" >= 0 AND ("advisor_commission_type" <> 'PERCENTAGE' OR "advisor_commission_value" <= 100)) ), -- Fecha de pago solo cuando el estado es PAID (comisión del asesor y liquidación al propietario). ADD CONSTRAINT "sales_advisor_commission_paid_at" CHECK ( ("advisor_commission_status" IS NULL AND "advisor_commission_paid_at" IS NULL) OR ("advisor_commission_status" = 'PAID' AND "advisor_commission_paid_at" IS NOT NULL) OR ("advisor_commission_status" IN ('PENDING', 'CANCELLED') AND "advisor_commission_paid_at" IS NULL) ), ADD CONSTRAINT "sales_owner_paid_at" CHECK ( ("owner_payout_status" IS NULL AND "owner_paid_at" IS NULL) OR ("owner_payout_status" = 'PAID' AND "owner_paid_at" IS NOT NULL) OR ("owner_payout_status" IN ('PENDING', 'CANCELLED') AND "owner_paid_at" IS NULL) ), -- Liquidación al propietario: solo existe en ventas de vehículos consignados. ADD CONSTRAINT "sales_consignment_snapshot" CHECK ( ("consignment_id" IS NULL AND "chesco_commission_type" IS NULL AND "chesco_commission_amount" IS NULL AND "owner_payout_amount" IS NULL AND "owner_payout_status" IS NULL) OR ("consignment_id" IS NOT NULL AND "chesco_commission_type" IS NOT NULL AND "chesco_commission_value" IS NOT NULL AND "chesco_commission_amount" IS NOT NULL AND "chesco_commission_amount" >= 0 AND "owner_payout_amount" IS NOT NULL AND "owner_payout_amount" >= 0 AND "owner_payout_status" IS NOT NULL) ), -- Anulación: estado, fecha, usuario y motivo van juntos. ADD CONSTRAINT "sales_annulment_consistency" CHECK ( ("status" = 'ACTIVE' AND "annulled_at" IS NULL AND "annulled_by_id" IS NULL AND "annulment_reason" IS NULL) OR ("status" = 'ANNULLED' AND "annulled_at" IS NOT NULL AND "annulled_by_id" IS NOT NULL AND "annulment_reason" IS NOT NULL AND length(trim("annulment_reason")) > 0) );