567 lines
20 KiB
SQL
567 lines
20 KiB
SQL
-- ===========================================
|
|
-- 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');
|