92 lines
3.8 KiB
SQL
92 lines
3.8 KiB
SQL
-- ===========================================
|
|
-- MECANICAS DIESEL - Customers Table
|
|
-- ===========================================
|
|
-- Tabla de clientes para el taller
|
|
-- Nota: Esta tabla se crea en service_management para mantener
|
|
-- consistencia con las referencias de service_orders
|
|
|
|
SET search_path TO service_management, public;
|
|
|
|
-- -------------------------------------------
|
|
-- CUSTOMERS - Clientes del taller
|
|
-- -------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS service_management.customers (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
tenant_id UUID NOT NULL, -- Identificador del taller (multi-tenant)
|
|
|
|
-- Tipo de cliente
|
|
customer_type VARCHAR(20) DEFAULT 'individual'
|
|
CHECK (customer_type IN ('individual', 'company', 'fleet')),
|
|
|
|
-- Identificación
|
|
name VARCHAR(200) NOT NULL,
|
|
legal_name VARCHAR(300),
|
|
rfc VARCHAR(13),
|
|
|
|
-- Contacto
|
|
email VARCHAR(200),
|
|
phone VARCHAR(20),
|
|
phone_secondary VARCHAR(20),
|
|
|
|
-- Dirección
|
|
address TEXT,
|
|
city VARCHAR(100),
|
|
state VARCHAR(100),
|
|
postal_code VARCHAR(10),
|
|
|
|
-- Términos comerciales (para empresas/flotas)
|
|
credit_days INTEGER DEFAULT 0 CHECK (credit_days >= 0),
|
|
credit_limit DECIMAL(12,2) DEFAULT 0 CHECK (credit_limit >= 0),
|
|
discount_labor_pct DECIMAL(5,2) DEFAULT 0 CHECK (discount_labor_pct >= 0 AND discount_labor_pct <= 100),
|
|
discount_parts_pct DECIMAL(5,2) DEFAULT 0 CHECK (discount_parts_pct >= 0 AND discount_parts_pct <= 100),
|
|
|
|
-- Estadísticas
|
|
total_orders INTEGER DEFAULT 0 CHECK (total_orders >= 0),
|
|
total_spent DECIMAL(14,2) DEFAULT 0 CHECK (total_spent >= 0),
|
|
last_visit_at TIMESTAMP WITH TIME ZONE,
|
|
|
|
-- Notas y preferencias
|
|
notes TEXT,
|
|
preferred_contact VARCHAR(20) DEFAULT 'phone'
|
|
CHECK (preferred_contact IN ('phone', 'email', 'whatsapp')),
|
|
|
|
-- Estado
|
|
is_active BOOLEAN DEFAULT true,
|
|
|
|
-- Audit
|
|
created_by UUID,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
|
|
-- Constraints
|
|
CONSTRAINT uq_customer_email UNIQUE (tenant_id, email),
|
|
CONSTRAINT uq_customer_rfc UNIQUE (tenant_id, rfc)
|
|
);
|
|
|
|
-- Índices
|
|
CREATE INDEX idx_customers_tenant ON service_management.customers(tenant_id);
|
|
CREATE INDEX idx_customers_email ON service_management.customers(tenant_id, email);
|
|
CREATE INDEX idx_customers_phone ON service_management.customers(phone);
|
|
CREATE INDEX idx_customers_rfc ON service_management.customers(rfc);
|
|
CREATE INDEX idx_customers_type ON service_management.customers(tenant_id, customer_type);
|
|
CREATE INDEX idx_customers_name ON service_management.customers(tenant_id, name);
|
|
CREATE INDEX idx_customers_active ON service_management.customers(tenant_id, is_active);
|
|
|
|
-- Trigger updated_at
|
|
CREATE TRIGGER trg_customers_updated_at
|
|
BEFORE UPDATE ON service_management.customers
|
|
FOR EACH ROW EXECUTE FUNCTION trigger_set_updated_at();
|
|
|
|
-- RLS
|
|
SELECT create_tenant_rls_policies('service_management', 'customers');
|
|
|
|
-- Comentarios
|
|
COMMENT ON TABLE service_management.customers IS 'Clientes del taller mecánico';
|
|
COMMENT ON COLUMN service_management.customers.customer_type IS 'Tipo: individual (persona física), company (empresa), fleet (flota)';
|
|
COMMENT ON COLUMN service_management.customers.credit_days IS 'Días de crédito otorgados al cliente';
|
|
COMMENT ON COLUMN service_management.customers.credit_limit IS 'Límite de crédito en pesos';
|
|
COMMENT ON COLUMN service_management.customers.discount_labor_pct IS 'Descuento en mano de obra (%)';
|
|
COMMENT ON COLUMN service_management.customers.discount_parts_pct IS 'Descuento en refacciones (%)';
|
|
COMMENT ON COLUMN service_management.customers.total_orders IS 'Total de órdenes de servicio acumuladas';
|
|
COMMENT ON COLUMN service_management.customers.total_spent IS 'Total gastado por el cliente';
|