-- =========================================== -- MECANICAS DIESEL - Schema service_management -- =========================================== -- Ordenes de servicio, diagnosticos, cotizaciones SET search_path TO service_management, public; -- ------------------------------------------- -- SERVICE_ORDERS - Ordenes de servicio -- ------------------------------------------- CREATE TABLE service_management.service_orders ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), tenant_id UUID NOT NULL, -- Identificador del taller (multi-tenant) -- Identificación order_number VARCHAR(20) NOT NULL, -- Relaciones (referencias a tablas de otros schemas) customer_id UUID NOT NULL, -- core.partners vehicle_id UUID NOT NULL, -- vehicle_management.vehicles quote_id UUID, -- service_management.quotes -- Asignación assigned_to UUID, -- auth.users bay_id UUID, -- Bahía de trabajo -- Estado con CHECK constraint status VARCHAR(30) DEFAULT 'received' CHECK (status IN ('received', 'diagnosed', 'quoted', 'approved', 'in_progress', 'waiting_parts', 'completed', 'delivered', 'cancelled')), priority VARCHAR(20) DEFAULT 'normal' CHECK (priority IN ('low', 'normal', 'high', 'urgent')), -- Fechas received_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(), promised_at TIMESTAMP WITH TIME ZONE, started_at TIMESTAMP WITH TIME ZONE, completed_at TIMESTAMP WITH TIME ZONE, delivered_at TIMESTAMP WITH TIME ZONE, -- Kilometraje odometer_in INTEGER CHECK (odometer_in >= 0), odometer_out INTEGER CHECK (odometer_out >= 0), -- Síntomas reportados customer_symptoms TEXT, -- Totales labor_total DECIMAL(12,2) DEFAULT 0 CHECK (labor_total >= 0), parts_total DECIMAL(12,2) DEFAULT 0 CHECK (parts_total >= 0), discount_amount DECIMAL(12,2) DEFAULT 0 CHECK (discount_amount >= 0), discount_percent DECIMAL(5,2) DEFAULT 0 CHECK (discount_percent >= 0 AND discount_percent <= 100), tax DECIMAL(12,2) DEFAULT 0 CHECK (tax >= 0), grand_total DECIMAL(12,2) DEFAULT 0 CHECK (grand_total >= 0), -- Notas internal_notes TEXT, customer_notes TEXT, -- Audit created_by UUID, created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(), updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(), CONSTRAINT uq_order_number UNIQUE (tenant_id, order_number), CONSTRAINT chk_odometer CHECK (odometer_out IS NULL OR odometer_out >= odometer_in) ); -- Índices CREATE INDEX idx_orders_tenant ON service_management.service_orders(tenant_id); CREATE INDEX idx_orders_status ON service_management.service_orders(tenant_id, status); CREATE INDEX idx_orders_vehicle ON service_management.service_orders(vehicle_id); CREATE INDEX idx_orders_customer ON service_management.service_orders(customer_id); CREATE INDEX idx_orders_assigned ON service_management.service_orders(assigned_to); CREATE INDEX idx_orders_received ON service_management.service_orders(received_at DESC); -- Trigger updated_at CREATE TRIGGER trg_service_orders_updated_at BEFORE UPDATE ON service_management.service_orders FOR EACH ROW EXECUTE FUNCTION trigger_set_updated_at(); -- RLS SELECT create_tenant_rls_policies('service_management', 'service_orders'); -- ------------------------------------------- -- ORDER_ITEMS - Líneas de trabajo/refacciones -- ------------------------------------------- CREATE TABLE service_management.order_items ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), order_id UUID NOT NULL REFERENCES service_management.service_orders(id) ON DELETE CASCADE, -- Tipo item_type VARCHAR(20) NOT NULL CHECK (item_type IN ('service', 'part')), -- Referencias opcionales service_id UUID, -- core.services part_id UUID, -- parts_management.parts -- Descripción description VARCHAR(500) NOT NULL, -- Cantidades y precios quantity DECIMAL(10,3) DEFAULT 1 CHECK (quantity > 0), unit_price DECIMAL(12,2) NOT NULL CHECK (unit_price >= 0), discount_pct DECIMAL(5,2) DEFAULT 0 CHECK (discount_pct >= 0 AND discount_pct <= 100), subtotal DECIMAL(12,2) NOT NULL CHECK (subtotal >= 0), -- Estado status VARCHAR(20) DEFAULT 'pending' CHECK (status IN ('pending', 'in_progress', 'completed')), -- Para mano de obra estimated_hours DECIMAL(5,2) CHECK (estimated_hours >= 0), actual_hours DECIMAL(5,2) CHECK (actual_hours >= 0), -- Mecánico performed_by UUID, completed_at TIMESTAMP WITH TIME ZONE, notes TEXT, sort_order INTEGER DEFAULT 0, created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW() ); CREATE INDEX idx_order_items_order ON service_management.order_items(order_id); CREATE INDEX idx_order_items_type ON service_management.order_items(order_id, item_type); -- ------------------------------------------- -- ORDER_STATUS_HISTORY - Historial de estados -- ------------------------------------------- CREATE TABLE service_management.order_status_history ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), order_id UUID NOT NULL REFERENCES service_management.service_orders(id) ON DELETE CASCADE, from_status VARCHAR(30), to_status VARCHAR(30) NOT NULL, changed_by UUID, notes TEXT, created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW() ); CREATE INDEX idx_status_history_order ON service_management.order_status_history(order_id); -- ------------------------------------------- -- WORK_BAYS - Bahías de trabajo -- ------------------------------------------- CREATE TABLE service_management.work_bays ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), tenant_id UUID NOT NULL, name VARCHAR(50) NOT NULL, description VARCHAR(200), bay_type VARCHAR(50) CHECK (bay_type IN ('general', 'diagnostic', 'heavy_duty', 'quick_service')), -- Estado (current_order_id es NULLABLE para evitar referencia circular) status VARCHAR(20) DEFAULT 'available' CHECK (status IN ('available', 'occupied', 'maintenance')), current_order_id UUID, -- NULLABLE - se actualiza después -- Capacidad max_weight DECIMAL(10,2) CHECK (max_weight > 0), has_lift BOOLEAN DEFAULT FALSE, has_pit BOOLEAN DEFAULT FALSE, is_active BOOLEAN DEFAULT TRUE, created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(), updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(), CONSTRAINT uq_bay_name UNIQUE (tenant_id, name) ); CREATE INDEX idx_bays_tenant ON service_management.work_bays(tenant_id); CREATE INDEX idx_bays_status ON service_management.work_bays(tenant_id, status); CREATE TRIGGER trg_work_bays_updated_at BEFORE UPDATE ON service_management.work_bays FOR EACH ROW EXECUTE FUNCTION trigger_set_updated_at(); SELECT create_tenant_rls_policies('service_management', 'work_bays'); -- FK de service_orders.bay_id (se agrega después de crear work_bays) ALTER TABLE service_management.service_orders ADD CONSTRAINT fk_orders_bay FOREIGN KEY (bay_id) REFERENCES service_management.work_bays(id); -- ------------------------------------------- -- DIAGNOSTICS - Diagnósticos realizados -- ------------------------------------------- CREATE TABLE service_management.diagnostics ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), tenant_id UUID NOT NULL, order_id UUID REFERENCES service_management.service_orders(id), vehicle_id UUID NOT NULL, diagnostic_type VARCHAR(50) NOT NULL CHECK (diagnostic_type IN ('scanner', 'injector_test', 'pump_test', 'compression', 'turbo_test', 'other')), equipment VARCHAR(200), performed_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(), performed_by UUID, result VARCHAR(20) CHECK (result IN ('pass', 'fail', 'needs_attention')), summary TEXT, raw_data JSONB, created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(), updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW() ); CREATE INDEX idx_diagnostics_tenant ON service_management.diagnostics(tenant_id); CREATE INDEX idx_diagnostics_vehicle ON service_management.diagnostics(vehicle_id); CREATE INDEX idx_diagnostics_order ON service_management.diagnostics(order_id); CREATE INDEX idx_diagnostics_date ON service_management.diagnostics(performed_at DESC); CREATE TRIGGER trg_diagnostics_updated_at BEFORE UPDATE ON service_management.diagnostics FOR EACH ROW EXECUTE FUNCTION trigger_set_updated_at(); SELECT create_tenant_rls_policies('service_management', 'diagnostics'); -- ------------------------------------------- -- DIAGNOSTIC_ITEMS - Hallazgos del diagnóstico -- ------------------------------------------- CREATE TABLE service_management.diagnostic_items ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), diagnostic_id UUID NOT NULL REFERENCES service_management.diagnostics(id) ON DELETE CASCADE, item_type VARCHAR(50) NOT NULL CHECK (item_type IN ('dtc_code', 'test_result', 'measurement', 'observation')), -- Para códigos DTC code VARCHAR(20), description VARCHAR(500), severity VARCHAR(20) CHECK (severity IN ('critical', 'warning', 'info')), -- Para mediciones parameter VARCHAR(100), value DECIMAL(12,4), unit VARCHAR(20), min_ref DECIMAL(12,4), max_ref DECIMAL(12,4), status VARCHAR(20) CHECK (status IN ('ok', 'warning', 'fail', 'no_reference')), -- Componente component VARCHAR(100), cylinder INTEGER CHECK (cylinder >= 1 AND cylinder <= 12), notes TEXT, sort_order INTEGER DEFAULT 0, created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW() ); CREATE INDEX idx_diag_items_diagnostic ON service_management.diagnostic_items(diagnostic_id); -- ------------------------------------------- -- DIAGNOSTIC_PHOTOS - Fotos de evidencia -- ------------------------------------------- CREATE TABLE service_management.diagnostic_photos ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), diagnostic_id UUID NOT NULL REFERENCES service_management.diagnostics(id) ON DELETE CASCADE, url VARCHAR(500) NOT NULL, thumbnail_url VARCHAR(500), description VARCHAR(300), category VARCHAR(50) CHECK (category IN ('before', 'damage', 'process', 'after', 'other')), file_size INTEGER, mime_type VARCHAR(50), sort_order INTEGER DEFAULT 0, uploaded_by UUID, created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW() ); CREATE INDEX idx_diag_photos_diagnostic ON service_management.diagnostic_photos(diagnostic_id); -- ------------------------------------------- -- DIAGNOSTIC_RECOMMENDATIONS - Recomendaciones -- ------------------------------------------- CREATE TABLE service_management.diagnostic_recommendations ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), diagnostic_id UUID NOT NULL REFERENCES service_management.diagnostics(id) ON DELETE CASCADE, diagnostic_item_id UUID REFERENCES service_management.diagnostic_items(id), description TEXT NOT NULL, priority VARCHAR(20) DEFAULT 'medium' CHECK (priority IN ('critical', 'high', 'medium', 'low')), urgency VARCHAR(20) DEFAULT 'soon' CHECK (urgency IN ('immediate', 'soon', 'scheduled', 'preventive')), suggested_service_id UUID, estimated_cost DECIMAL(12,2) CHECK (estimated_cost >= 0), status VARCHAR(20) DEFAULT 'pending' CHECK (status IN ('pending', 'approved', 'declined', 'completed')), notes TEXT, created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW() ); CREATE INDEX idx_recommendations_diagnostic ON service_management.diagnostic_recommendations(diagnostic_id); -- ------------------------------------------- -- QUOTES - Cotizaciones -- ------------------------------------------- CREATE TABLE service_management.quotes ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), tenant_id UUID NOT NULL, quote_number VARCHAR(20) NOT NULL, customer_id UUID NOT NULL, vehicle_id UUID NOT NULL, diagnostic_id UUID REFERENCES service_management.diagnostics(id), status VARCHAR(20) DEFAULT 'draft' CHECK (status IN ('draft', 'sent', 'viewed', 'approved', 'rejected', 'expired', 'converted')), -- Fechas created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(), sent_at TIMESTAMP WITH TIME ZONE, viewed_at TIMESTAMP WITH TIME ZONE, responded_at TIMESTAMP WITH TIME ZONE, expires_at TIMESTAMP WITH TIME ZONE, -- Totales labor_total DECIMAL(12,2) DEFAULT 0 CHECK (labor_total >= 0), parts_total DECIMAL(12,2) DEFAULT 0 CHECK (parts_total >= 0), discount_amount DECIMAL(12,2) DEFAULT 0 CHECK (discount_amount >= 0), discount_percent DECIMAL(5,2) DEFAULT 0 CHECK (discount_percent >= 0 AND discount_percent <= 100), discount_reason VARCHAR(200), tax DECIMAL(12,2) DEFAULT 0 CHECK (tax >= 0), grand_total DECIMAL(12,2) DEFAULT 0 CHECK (grand_total >= 0), validity_days INTEGER DEFAULT 15 CHECK (validity_days > 0), terms TEXT, notes TEXT, -- Conversión a orden converted_order_id UUID REFERENCES service_management.service_orders(id), -- Aprobación digital approved_by_name VARCHAR(200), approval_signature TEXT, approval_ip INET, created_by UUID, updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(), CONSTRAINT uq_quote_number UNIQUE (tenant_id, quote_number) ); CREATE INDEX idx_quotes_tenant ON service_management.quotes(tenant_id); CREATE INDEX idx_quotes_status ON service_management.quotes(tenant_id, status); CREATE INDEX idx_quotes_customer ON service_management.quotes(customer_id); CREATE INDEX idx_quotes_created ON service_management.quotes(created_at DESC); CREATE TRIGGER trg_quotes_updated_at BEFORE UPDATE ON service_management.quotes FOR EACH ROW EXECUTE FUNCTION trigger_set_updated_at(); SELECT create_tenant_rls_policies('service_management', 'quotes'); -- ------------------------------------------- -- QUOTE_ITEMS - Líneas de cotización -- ------------------------------------------- CREATE TABLE service_management.quote_items ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), quote_id UUID NOT NULL REFERENCES service_management.quotes(id) ON DELETE CASCADE, item_type VARCHAR(20) NOT NULL CHECK (item_type IN ('service', 'part')), service_id UUID, part_id UUID, description VARCHAR(500) NOT NULL, quantity DECIMAL(10,3) DEFAULT 1 CHECK (quantity > 0), unit_price DECIMAL(12,2) NOT NULL CHECK (unit_price >= 0), discount_pct DECIMAL(5,2) DEFAULT 0 CHECK (discount_pct >= 0 AND discount_pct <= 100), subtotal DECIMAL(12,2) NOT NULL CHECK (subtotal >= 0), is_approved BOOLEAN DEFAULT TRUE, sort_order INTEGER DEFAULT 0, created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW() ); CREATE INDEX idx_quote_items_quote ON service_management.quote_items(quote_id); -- ------------------------------------------- -- QUOTE_TRACKING - Tracking de cotizaciones -- ------------------------------------------- CREATE TABLE service_management.quote_tracking ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), quote_id UUID NOT NULL REFERENCES service_management.quotes(id) ON DELETE CASCADE, event_type VARCHAR(30) NOT NULL CHECK (event_type IN ('sent_email', 'sent_whatsapp', 'opened', 'link_clicked', 'approved', 'rejected')), channel VARCHAR(20) CHECK (channel IN ('email', 'whatsapp', 'link')), ip_address INET, user_agent TEXT, device_type VARCHAR(20), created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW() ); CREATE INDEX idx_quote_tracking_quote ON service_management.quote_tracking(quote_id); -- ------------------------------------------- -- QUOTE_FOLLOWUPS - Seguimiento de cotizaciones -- ------------------------------------------- CREATE TABLE service_management.quote_followups ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), quote_id UUID NOT NULL REFERENCES service_management.quotes(id) ON DELETE CASCADE, action VARCHAR(100) NOT NULL, notes TEXT, next_action VARCHAR(100), next_action_at TIMESTAMP WITH TIME ZONE, created_by UUID, created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW() ); CREATE INDEX idx_quote_followups_quote ON service_management.quote_followups(quote_id); -- ------------------------------------------- -- TEST_TYPES - Tipos de prueba configurables -- ------------------------------------------- CREATE TABLE service_management.test_types ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), tenant_id UUID, -- NULL = global name VARCHAR(100) NOT NULL, description TEXT, component_type VARCHAR(50) CHECK (component_type IN ('injector', 'pump', 'turbo', 'engine', 'other')), is_system BOOLEAN DEFAULT FALSE, is_active BOOLEAN DEFAULT TRUE, created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(), updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW() ); CREATE INDEX idx_test_types_tenant ON service_management.test_types(tenant_id); CREATE TRIGGER trg_test_types_updated_at BEFORE UPDATE ON service_management.test_types FOR EACH ROW EXECUTE FUNCTION trigger_set_updated_at(); -- ------------------------------------------- -- TEST_PARAMETERS - Parámetros de prueba -- ------------------------------------------- CREATE TABLE service_management.test_parameters ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), test_type_id UUID NOT NULL REFERENCES service_management.test_types(id) ON DELETE CASCADE, name VARCHAR(100) NOT NULL, unit VARCHAR(20), data_type VARCHAR(20) DEFAULT 'numeric' CHECK (data_type IN ('numeric', 'boolean', 'text')), sort_order INTEGER DEFAULT 0, created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW() ); CREATE INDEX idx_test_params_type ON service_management.test_parameters(test_type_id); -- ------------------------------------------- -- PARAMETER_REFERENCES - Valores de referencia por motor -- ------------------------------------------- CREATE TABLE service_management.parameter_references ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), parameter_id UUID NOT NULL REFERENCES service_management.test_parameters(id) ON DELETE CASCADE, engine_model_id UUID, -- vehicle_management.engine_catalog min_value DECIMAL(12,4), max_value DECIMAL(12,4), nominal_value DECIMAL(12,4), tolerance_pct DECIMAL(5,2) CHECK (tolerance_pct >= 0), source VARCHAR(200), created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(), CONSTRAINT uq_param_engine UNIQUE (parameter_id, engine_model_id) ); CREATE INDEX idx_param_refs_param ON service_management.parameter_references(parameter_id); CREATE INDEX idx_param_refs_engine ON service_management.parameter_references(engine_model_id); -- ------------------------------------------- -- SERVICE_CATEGORIES - Categorías de servicios -- ------------------------------------------- CREATE TABLE service_management.service_categories ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), tenant_id UUID NOT NULL, name VARCHAR(100) NOT NULL, description VARCHAR(300), color VARCHAR(7), icon VARCHAR(50), parent_id UUID REFERENCES service_management.service_categories(id), sort_order INTEGER DEFAULT 0, created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(), updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW() ); CREATE INDEX idx_svc_categories_tenant ON service_management.service_categories(tenant_id); CREATE INDEX idx_svc_categories_parent ON service_management.service_categories(parent_id); CREATE TRIGGER trg_service_categories_updated_at BEFORE UPDATE ON service_management.service_categories FOR EACH ROW EXECUTE FUNCTION trigger_set_updated_at(); SELECT create_tenant_rls_policies('service_management', 'service_categories'); -- ------------------------------------------- -- SERVICES - Catálogo de servicios -- ------------------------------------------- CREATE TABLE service_management.services ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), tenant_id UUID NOT NULL, code VARCHAR(20) NOT NULL, name VARCHAR(200) NOT NULL, description TEXT, category_id UUID REFERENCES service_management.service_categories(id), price DECIMAL(12,2) NOT NULL CHECK (price >= 0), cost DECIMAL(12,2) CHECK (cost >= 0), estimated_hours DECIMAL(5,2) CHECK (estimated_hours >= 0), is_active BOOLEAN DEFAULT TRUE, created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(), updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(), CONSTRAINT uq_service_code UNIQUE (tenant_id, code) ); CREATE INDEX idx_services_tenant ON service_management.services(tenant_id); CREATE INDEX idx_services_category ON service_management.services(category_id); CREATE INDEX idx_services_code ON service_management.services(code); CREATE TRIGGER trg_services_updated_at BEFORE UPDATE ON service_management.services FOR EACH ROW EXECUTE FUNCTION trigger_set_updated_at(); SELECT create_tenant_rls_policies('service_management', 'services');