Base de Datos Servicios

-- =================================================
-- Tipos de datos enumerados
-- =================================================
CREATE TYPE property_type AS ENUM ('casa', 'departamento', 'garaje', 'trastero', 'local', 'oficina');
CREATE TYPE service_type AS ENUM ('fontanería', 'electricidad', 'albañilería', 'pintura', 'carpintería', 'limpieza', 'jardinería', 'otros');
CREATE TYPE role_type AS ENUM ('inquilino', 'propietario', 'empresa', 'técnico', 'gestor');
CREATE TYPE doc_type AS ENUM ('foto', 'pdf', 'factura', 'contrato', 'certificado');
CREATE TYPE request_status AS ENUM ('abierto', 'asignado', 'en_proceso', 'completado', 'cancelado');

-- =================================================
-- Tablas principales
-- =================================================
CREATE TABLE persons (
    person_id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE,
    phone VARCHAR(20),
    role role_type NOT NULL,
    nif VARCHAR(20) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE properties (
    property_id SERIAL PRIMARY KEY,
    type property_type NOT NULL,
    address TEXT NOT NULL,
    city VARCHAR(50),
    postal_code VARCHAR(10),
    description TEXT,
    area_m2 NUMERIC(8,2),
    purchase_date DATE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- =================================================
-- Tablas de gestión
-- =================================================
CREATE TABLE managers (
    manager_id SERIAL PRIMARY KEY,
    person_id INT NOT NULL UNIQUE REFERENCES persons(person_id),
    department VARCHAR(50),
    work_phone VARCHAR(20),
    active BOOLEAN DEFAULT true,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE manager_services (
    manager_id INT REFERENCES managers(manager_id) ON DELETE CASCADE,
    service_type service_type NOT NULL,
    PRIMARY KEY (manager_id, service_type)
);

CREATE TABLE service_companies (
    company_id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    service_types service_type[] NOT NULL,
    contact_person_id INT REFERENCES persons(person_id),
    hourly_rate NUMERIC(8,2),
    certification_number VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE technicians (
    technician_id SERIAL PRIMARY KEY,
    person_id INT NOT NULL REFERENCES persons(person_id),
    company_id INT REFERENCES service_companies(company_id),
    specialty service_type NOT NULL,
    certification_number VARCHAR(50),
    active BOOLEAN DEFAULT true,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- =================================================
-- Tablas de relaciones y operaciones
-- =================================================
CREATE TABLE insurances (
    insurance_id SERIAL PRIMARY KEY,
    property_id INT NOT NULL REFERENCES properties(property_id) ON DELETE CASCADE,
    company_name VARCHAR(100) NOT NULL,
    policy_number VARCHAR(50) UNIQUE NOT NULL,
    coverage_details TEXT,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    annual_premium NUMERIC(10,2),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CHECK (start_date < end_date)
);

CREATE TABLE leases (
    lease_id SERIAL PRIMARY KEY,
    property_id INT NOT NULL REFERENCES properties(property_id) ON DELETE CASCADE,
    tenant_id INT NOT NULL REFERENCES persons(person_id),
    owner_id INT NOT NULL REFERENCES persons(person_id),
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    monthly_rent NUMERIC(8,2) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CHECK (start_date < end_date)
);

CREATE TABLE maintenance_requests (
    request_id SERIAL PRIMARY KEY,
    property_id INT NOT NULL REFERENCES properties(property_id) ON DELETE CASCADE,
    reported_by INT NOT NULL REFERENCES persons(person_id),
    service_type service_type NOT NULL,
    description TEXT NOT NULL,
    urgency_level INT NOT NULL CHECK (urgency_level BETWEEN 1 AND 5),
    status request_status DEFAULT 'abierto',
    assigned_manager INT REFERENCES managers(manager_id),
    assignment_date TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    closed_at TIMESTAMP
);

CREATE TABLE assignment_history (
    assignment_id SERIAL PRIMARY KEY,
    request_id INT NOT NULL REFERENCES maintenance_requests(request_id) ON DELETE CASCADE,
    manager_id INT NOT NULL REFERENCES managers(manager_id),
    assigned_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    unassigned_at TIMESTAMP,
    assignment_notes TEXT
);

CREATE TABLE work_orders (
    order_id SERIAL PRIMARY KEY,
    request_id INT NOT NULL REFERENCES maintenance_requests(request_id) ON DELETE CASCADE,
    company_id INT REFERENCES service_companies(company_id),
    technician_id INT REFERENCES technicians(technician_id),
    assigned_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    estimated_cost NUMERIC(10,2),
    actual_cost NUMERIC(10,2),
    start_time TIMESTAMP,
    end_time TIMESTAMP,
    notes TEXT,
    CHECK (estimated_cost >= 0 AND actual_cost >= 0)
);

CREATE TABLE documents (
    document_id SERIAL PRIMARY KEY,
    request_id INT REFERENCES maintenance_requests(request_id) ON DELETE CASCADE,
    order_id INT REFERENCES work_orders(order_id) ON DELETE CASCADE,
    insurance_id INT REFERENCES insurances(insurance_id) ON DELETE CASCADE,
    lease_id INT REFERENCES leases(lease_id) ON DELETE CASCADE,
    document_type doc_type NOT NULL,
    file_path TEXT NOT NULL,
    description TEXT,
    uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE service_history (
    history_id SERIAL PRIMARY KEY,
    property_id INT NOT NULL REFERENCES properties(property_id) ON DELETE CASCADE,
    request_id INT REFERENCES maintenance_requests(request_id) ON DELETE SET NULL,
    event_description TEXT NOT NULL,
    event_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    responsible_person_id INT REFERENCES persons(person_id)
);

-- =================================================
-- Índices para optimización
-- =================================================
CREATE INDEX idx_properties_type ON properties(type);
CREATE INDEX idx_requests_status ON maintenance_requests(status);
CREATE INDEX idx_requests_urgency ON maintenance_requests(urgency_level);
CREATE INDEX idx_work_orders_company ON work_orders(company_id);
CREATE INDEX idx_technicians_specialty ON technicians(specialty);
CREATE INDEX idx_documents_type ON documents(document_type);
CREATE INDEX idx_service_history_date ON service_history(event_date);
CREATE INDEX idx_manager_services_type ON manager_services(service_type);

Estructura Completa Explicada

1. Entidades Principales:

• persons: Todas las personas del sistema (inquilinos, propietarios, técnicos, gestores)
• properties: Todas las propiedades inmobiliarias
• managers: Gestores especializados (relación 1:1 con persons)
• service_companies: Empresas de servicios con sus especialidades
• technicians: Técnicos especializados (relación con persons y empresas)

2. Gestión de Gestores:

• manager_services: Asociación de gestores con tipos de servicio
• assignment_history: Historial completo de asignación de gestores
• Campo assigned_manager en maintenance_requests para el gestor actual

3. Ciclo de Averías:

1. maintenance_requests: Reporte inicial de avería
2. work_orders: Asignación a empresa/técnico
3. documents: Evidencia fotográfica/documental
4. service_history: Registro histórico de eventos

4. Relaciones Clave:

• leases: Vincula propiedades con inquilinos y propietarios
• insurances: Seguros asociados a propiedades
• documents: Soporta múltiples relaciones (averías, seguros, contratos)

5. Optimizaciones:

• Índices en campos de búsqueda frecuente
• Enumerados para valores predefinidos
• Restricciones de integridad (CHECK, FK)
• ON DELETE CASCADE para mantener consistencia

Ejemplo de Flujo Completo

1. Reporte de Avería:

-- Insertar reporte
INSERT INTO maintenance_requests (
    property_id, 
    reported_by, 
    service_type, 
    description, 
    urgency_level)
    VALUES (
	    101, 
	    202, 
	    'fontanería', 
	    'Fuga de agua en baño principal', 
	    4) 
	RETURNING request_id;

2. Asignar Gestor:

-- Asignar gestor especializado
UPDATE maintenance_requests 
SET assigned_manager = (
    SELECT manager_id 
    FROM manager_services 
    WHERE service_type = 'fontanería'
    LIMIT 1),
assignment_date = NOW()
WHERE request_id = 305;

-- Registrar en historial
INSERT INTO assignment_history (request_id, manager_id)
VALUES (305, 15);

3. Asignar Técnico:

INSERT INTO work_orders (
    request_id,
    company_id,
    technician_id,
    estimated_cost) 
    VALUES (
	    305,
	    42,
	    78,
	    150.00);

4. Registrar Documentos:

INSERT INTO documents (
    request_id,
    document_type,
    file_path,
    description)
    VALUES 
		(305, 'foto', 's3://bucket/fuga_antes.jpg', 'Estado inicial de la fuga'),
		(305, 'foto', 's3://bucket/fuga_despues.jpg', 'Reparación finalizada'),
		(305, 'factura', 's3://bucket/factura_rep.pdf', 'Factura de reparación');

5. Cerrar Incidencia:

UPDATE maintenance_requests
SET status = 'completado', closed_at = NOW()
WHERE request_id = 305;

UPDATE work_orders
SET actual_cost = 145.50, end_time = NOW()
WHERE request_id = 305;

INSERT INTO service_history (
    property_id,
    request_id,
    event_description,
    responsible_person_id)
    VALUES (
	    101,
	    305,
	    'Reparación de fuga completada con éxito',
	    78);

Ventajas de este Diseño

1. Trazabilidad Completa:

   • Historial de asignación de gestores
   • Registro temporal en todas las etapas
   • Documentación vinculada a cada paso

2. Gestión Especializada:

   • Gestores asignados por tipo de servicio
   • Técnicos con especialidades definidas
   • Empresas con múltiples servicios

3. Integridad de Datos:

   • Relaciones con ON DELETE CASCADE
   • Restricciones de dominio (enumerados)
   • Validación de rangos y fechas

4. Rendimiento Optimizado:

   • Índices en campos críticos
   • Tipos de datos apropiados
   • Normalización balanceada

5. Flexibilidad Documental:

   • Soporte múltiples tipos de archivos
   • Vinculación con diferentes entidades
   • Metadatos descriptivos

Este diseño permite gestionar todo el ciclo de vida de los servicios inmobiliarios: desde el reporte inicial de averías hasta la solución final, incluyendo la gestión de gestores, documentación asociada y seguimiento histórico.

Datos de Ejemplo Completos para la Base de Datos

Aquí tienes un conjunto de datos de prueba que cubre todas las tablas y relaciones:

-- 1. Insertar personas
INSERT INTO persons (name, email, phone, role, nif) VALUES
('Laura Martínez', 'laura@example.com', '600111222', 'propietario', 'Y1234567A'),
('Carlos Rodríguez', 'carlos@example.com', '600222333', 'inquilino', 'Z7654321B'),
('Tecnirep S.L.', 'info@tecnirep.com', '910001122', 'empresa', 'A12345678'),
('Juan García', 'juan@tecnirep.com', '655000111', 'técnico', 'X11222333'),
('Sofía López', 'sofia@example.com', '677888999', 'gestor', 'B44555666'),
('Marta Jiménez', 'marta@example.com', '699000111', 'inquilino', 'C99888777'),
('Electroserv S.A.', 'contacto@electroserv.com', '915550000', 'empresa', 'B87654321'),
('Pedro Sánchez', 'pedro@electroserv.com', '622333444', 'técnico', 'D55444666'),
('Ana Ruiz', 'ana@example.com', '688777666', 'propietario', 'E11222333'),
('David Torres', 'david@example.com', '666999000', 'gestor', 'F44555666');

-- 2. Insertar propiedades
INSERT INTO properties (type, address, city, postal_code, area_m2) VALUES
('casa', 'Calle Principal 123', 'Madrid', '28001', 120.5),
('departamento', 'Avenida Libertad 45', 'Barcelona', '08001', 75.0),
('garaje', 'Calle Secundaria 8', 'Madrid', '28002', 15.0),
('trastero', 'Plaza Central 3', 'Valencia', '46001', 5.5);

-- 3. Insertar gestores
INSERT INTO managers (person_id, department, work_phone) VALUES
((SELECT person_id FROM persons WHERE name = 'Sofía López'), 'Mantenimiento', '910222333'),
((SELECT person_id FROM persons WHERE name = 'David Torres'), 'Emergencias', '910444555');

-- 4. Especialidades de gestores
INSERT INTO manager_services (manager_id, service_type) VALUES
(1, 'fontanería'),
(1, 'albañilería'),
(1, 'pintura'),
(2, 'electricidad'),
(2, 'fontanería');

-- 5. Empresas de servicios
INSERT INTO service_companies (name, service_types, contact_person_id, hourly_rate) VALUES
('Tecnirep S.L.', '{fontanería,albañilería,pintura}', 
 (SELECT person_id FROM persons WHERE name = 'Tecnirep S.L.'), 45.00),
('Electroserv S.A.', '{electricidad,carpintería}', 
 (SELECT person_id FROM persons WHERE name = 'Electroserv S.A.'), 50.00);

-- 6. Técnicos
INSERT INTO technicians (person_id, company_id, specialty) VALUES
((SELECT person_id FROM persons WHERE name = 'Juan García'), 1, 'fontanería'),
((SELECT person_id FROM persons WHERE name = 'Pedro Sánchez'), 2, 'electricidad'),
((SELECT person_id FROM persons WHERE name = 'Carlos Rodríguez'), 1, 'pintura');

-- 7. Seguros
INSERT INTO insurances (property_id, company_name, policy_number, start_date, end_date, annual_premium) VALUES
(1, 'Seguros Total', 'POL-2023-001', '2023-01-01', '2024-01-01', 350.00),
(2, 'Aseguradora Global', 'POL-2023-002', '2023-02-15', '2024-02-15', 220.00);

-- 8. Contratos de alquiler
INSERT INTO leases (property_id, tenant_id, owner_id, start_date, end_date, monthly_rent) VALUES
(1, (SELECT person_id FROM persons WHERE name = 'Carlos Rodríguez'), 
 (SELECT person_id FROM persons WHERE name = 'Laura Martínez'),
 '2023-01-01', '2024-12-31', 850.00),
 
(2, (SELECT person_id FROM persons WHERE name = 'Marta Jiménez'), 
 (SELECT person_id FROM persons WHERE name = 'Ana Ruiz'),
 '2023-03-01', '2024-03-01', 650.00);

-- 9. Reportes de averías
INSERT INTO maintenance_requests (property_id, reported_by, service_type, description, urgency_level) VALUES
(1, (SELECT person_id FROM persons WHERE name = 'Carlos Rodríguez'), 'fontanería', 
 'Fuga de agua en cocina', 4) RETURNING request_id;
 
INSERT INTO maintenance_requests (property_id, reported_by, service_type, description, urgency_level) VALUES
(2, (SELECT person_id FROM persons WHERE name = 'Marta Jiménez'), 'electricidad', 
 'Luz del baño no funciona', 3) RETURNING request_id;

-- 10. Asignar gestores a reportes
UPDATE maintenance_requests 
SET assigned_manager = 1, assignment_date = NOW()
WHERE request_id = 1;

UPDATE maintenance_requests 
SET assigned_manager = 2, assignment_date = NOW()
WHERE request_id = 2;

-- 11. Historial de asignaciones
INSERT INTO assignment_history (request_id, manager_id, assignment_notes) VALUES
(1, 1, 'Asignado por turno matutino'),
(2, 2, 'Asignado por especialidad eléctrica');

-- 12. Órdenes de trabajo
INSERT INTO work_orders (request_id, company_id, technician_id, estimated_cost) VALUES
(1, 1, 1, 120.00),
(2, 2, 2, 85.00);

-- 13. Documentos
INSERT INTO documents (request_id, document_type, file_path, description) VALUES
(1, 'foto', 's3://bucket/fuga_cocina.jpg', 'Foto inicial de la fuga'),
(1, 'factura', 's3://bucket/factura_fontaneria.pdf', 'Factura de reparación'),
(2, 'foto', 's3://bucket/luz_bano.jpg', 'Lámpara defectuosa');

-- 14. Historial de servicio
INSERT INTO service_history (property_id, request_id, event_description, responsible_person_id) VALUES
(1, 1, 'Reporte de fuga creado', (SELECT person_id FROM persons WHERE name = 'Carlos Rodríguez')),
(1, 1, 'Reparación completada', (SELECT person_id FROM persons WHERE name = 'Juan García'));

Consultas de Verificación

Para comprobar los datos insertados:

1. Ver todas las averías con su estado:

SELECT 
    mr.request_id,
    p.address AS propiedad,
    per_reporter.name AS reportado_por,
    mr.service_type,
    mr.status,
    per_manager.name AS gestor_asignado
FROM maintenance_requests mr
JOIN properties p ON mr.property_id = p.property_id
JOIN persons per_reporter ON mr.reported_by = per_reporter.person_id
LEFT JOIN managers m ON mr.assigned_manager = m.manager_id
LEFT JOIN persons per_manager ON m.person_id = per_manager.person_id;

2. Ver documentos asociados a una avería:

SELECT 
    d.document_id,
    d.document_type,
    d.file_path,
    d.description,
    d.uploaded_at
FROM documents d
WHERE d.request_id = 1;

3. Costo real vs estimado de reparaciones:

SELECT 
    wo.order_id,
    p.address AS propiedad,
    sc.name AS empresa,
    t.person_id AS tecnico_id,
    per_tech.name AS tecnico,
    wo.estimated_cost,
    wo.actual_cost
FROM work_orders wo
JOIN maintenance_requests mr ON wo.request_id = mr.request_id
JOIN properties p ON mr.property_id = p.property_id
LEFT JOIN service_companies sc ON wo.company_id = sc.company_id
LEFT JOIN technicians t ON wo.technician_id = t.technician_id
LEFT JOIN persons per_tech ON t.person_id = per_tech.person_id;

4. Gestores y sus especialidades:

SELECT 
    p.name AS gestor,
    m.department,
    ms.service_type
FROM managers m
JOIN persons p ON m.person_id = p.person_id
JOIN manager_services ms ON m.manager_id = ms.manager_id;

Datos Generados

Este conjunto de datos incluye:

• 4 propiedades: 1 casa, 1 departamento, 1 garaje, 1 trastero
• 10 personas: 2 propietarios, 2 inquilinos, 2 gestores, 2 empresas, 3 técnicos
• 2 empresas de servicios con diferentes especialidades
• 2 reportes de averías (fontanería y electricidad)
• 2 órdenes de trabajo asignadas
• 3 documentos (fotos y facturas)
• 2 seguros para propiedades
• 2 contratos de alquiler
• Historial completo de asignaciones y eventos

Ejecutar estas sentencias en orden para tener una base de datos de prueba completamente funcional que refleje todos los aspectos del sistema.