erp-construccion/docs/02-definicion-modulos/MAI-007-seguridad-industrial/especificaciones/ET-SEG-001-database.md

ET-SEG-001: Especificación Técnica - Base de Datos

Módulo: MAI-007 - Seguridad Industrial Versión: 1.0.0 Fecha: 2025-12-06 Responsable: Database Agent

---

Resumen Ejecutivo

Este documento especifica el diseño completo de la base de datos para el módulo de Seguridad Industrial (MAI-007) del proyecto de construcción. El schema safety_management gestiona EPP (Equipo de Protección Personal), inspecciones de seguridad, incidentes laborales, investigaciones y capacitaciones.

---

Contexto del Stack

motor: PostgreSQL 15+
extensiones:
  - uuid-ossp          # Generación de UUIDs
  - pg_trgm            # Búsqueda fuzzy por trigram
  - btree_gist         # Índices GiST para exclusión
  - pgcrypto           # Funciones criptográficas
  - postgis            # Geolocalización GPS (opcional)
orm: Prisma 5.x
migraciones: Prisma migrations
schema: safety_management

---

Schema Principal: safety_management

Justificación

• Separación de responsabilidades: Aísla toda la funcionalidad de seguridad industrial
• Multi-tenant: Todas las tablas incluyen tenant_id para aislamiento de constructoras
• Auditoría completa: Registro de todas las operaciones críticas de seguridad
• Normativas: Cumple con NOM-031-STPS-2011 y NOM-017-STPS-2008

---

ENUMs

1. epp_type (Tipos de EPP)

CREATE TYPE safety_management.epp_type AS ENUM (
    'helmet',              -- Casco
    'safety_glasses',      -- Lentes de seguridad
    'goggles',            -- Gafas protectoras
    'face_shield',        -- Careta
    'ear_plugs',          -- Tapones auditivos
    'ear_muffs',          -- Orejeras
    'respirator',         -- Respirador
    'dust_mask',          -- Cubrebocas
    'work_gloves',        -- Guantes de trabajo
    'cut_resistant_gloves', -- Guantes anticorte
    'electrical_gloves',  -- Guantes dieléctricos
    'safety_boots',       -- Botas de seguridad
    'rubber_boots',       -- Botas de hule
    'safety_vest',        -- Chaleco de seguridad
    'reflective_vest',    -- Chaleco reflejante
    'harness',            -- Arnés de seguridad
    'lifeline',           -- Línea de vida
    'rain_suit',          -- Impermeable
    'coveralls',          -- Overol
    'welding_jacket',     -- Chaqueta de soldadura
    'other'               -- Otro
);

COMMENT ON TYPE safety_management.epp_type IS 'Tipos de Equipo de Protección Personal según NOM-017-STPS-2008';

2. epp_status (Estados de EPP)

CREATE TYPE safety_management.epp_status AS ENUM (
    'available',   -- Disponible en almacén
    'assigned',    -- Asignado a trabajador
    'in_use',      -- En uso activo
    'expired',     -- Vida útil vencida
    'damaged',     -- Dañado o defectuoso
    'lost',        -- Extraviado
    'retired'      -- Dado de baja
);

COMMENT ON TYPE safety_management.epp_status IS 'Estados del ciclo de vida del EPP';

3. risk_level (Nivel de Riesgo)

CREATE TYPE safety_management.risk_level AS ENUM (
    'low',         -- Riesgo bajo
    'medium',      -- Riesgo medio
    'high',        -- Riesgo alto
    'critical'     -- Riesgo crítico - requiere acción inmediata
);

COMMENT ON TYPE safety_management.risk_level IS 'Niveles de riesgo para hallazgos e incidentes';

4. incident_severity (Severidad de Incidente)

CREATE TYPE safety_management.incident_severity AS ENUM (
    'near_miss',      -- Casi accidente (sin lesiones)
    'first_aid',      -- Requiere primeros auxilios
    'minor',          -- Lesión menor sin días perdidos
    'lost_time',      -- Lesión con días perdidos
    'serious',        -- Lesión grave con hospitalización
    'fatal',          -- Accidente fatal
    'property_damage' -- Daño a propiedad sin lesiones
);

COMMENT ON TYPE safety_management.incident_severity IS 'Clasificación de gravedad de incidentes laborales';

5. incident_status (Estado de Incidente)

CREATE TYPE safety_management.incident_status AS ENUM (
    'reported',            -- Recién reportado
    'under_investigation', -- En investigación
    'investigation_completed', -- Investigación completa
    'actions_pending',     -- Acciones correctivas pendientes
    'closed',              -- Cerrado con medidas implementadas
    'archived'             -- Archivado
);

COMMENT ON TYPE safety_management.incident_status IS 'Estados del ciclo de vida de un incidente';

6. action_status (Estado de Acción Correctiva)

CREATE TYPE safety_management.action_status AS ENUM (
    'pending',     -- Pendiente de iniciar
    'in_progress', -- En progreso
    'completed',   -- Completada
    'verified',    -- Verificada y aprobada
    'cancelled',   -- Cancelada
    'overdue'      -- Vencida
);

COMMENT ON TYPE safety_management.action_status IS 'Estados de acciones correctivas y preventivas';

7. inspection_status (Estado de Inspección)

CREATE TYPE safety_management.inspection_status AS ENUM (
    'scheduled',   -- Programada
    'in_progress', -- En ejecución
    'completed',   -- Completada
    'cancelled',   -- Cancelada
    'overdue'      -- Vencida sin realizar
);

COMMENT ON TYPE safety_management.inspection_status IS 'Estados de inspecciones de seguridad';

8. training_status (Estado de Capacitación)

CREATE TYPE safety_management.training_status AS ENUM (
    'scheduled',   -- Programada
    'in_progress', -- En curso
    'completed',   -- Completada
    'cancelled',   -- Cancelada
    'postponed'    -- Pospuesta
);

COMMENT ON TYPE safety_management.training_status IS 'Estados de sesiones de capacitación';

---

Tablas

1. epp_catalog (Catálogo de EPP)

Descripción: Catálogo maestro de tipos de EPP con especificaciones técnicas y normativas aplicables.

-- ============================================================================
-- Schema: safety_management
-- Tabla: epp_catalog
-- Descripción: Catálogo de tipos de EPP con especificaciones técnicas
-- Módulo: MAI-007
-- ============================================================================

CREATE TABLE IF NOT EXISTS safety_management.epp_catalog (
    -- Primary Key
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),

    -- Multi-tenant
    tenant_id UUID NOT NULL REFERENCES core_tenants.tenants(id) ON DELETE CASCADE,

    -- Datos del EPP
    code VARCHAR(50) NOT NULL,
    name VARCHAR(200) NOT NULL,
    epp_type safety_management.epp_type NOT NULL,
    description TEXT,

    -- Especificaciones técnicas
    manufacturer VARCHAR(200),
    model VARCHAR(100),
    technical_specs JSONB DEFAULT '{}',

    -- Normativas
    norm_references TEXT[],  -- ['NOM-017-STPS-2008', 'ANSI Z87.1']
    certification_required BOOLEAN NOT NULL DEFAULT false,
    certification_details JSONB DEFAULT '{}',

    -- Vida útil
    useful_life_days INTEGER NOT NULL DEFAULT 365,  -- Días de vida útil
    replacement_alert_days INTEGER NOT NULL DEFAULT 30,  -- Alertar N días antes

    -- Inventario
    minimum_stock INTEGER NOT NULL DEFAULT 0,
    unit_cost DECIMAL(12, 2),

    -- Metadata
    image_url TEXT,
    care_instructions TEXT,
    usage_guidelines TEXT,
    metadata JSONB DEFAULT '{}',

    -- Auditoría
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    created_by UUID REFERENCES core_users.users(id),
    updated_by UUID REFERENCES core_users.users(id),

    -- Soft delete
    is_active BOOLEAN NOT NULL DEFAULT true,
    deleted_at TIMESTAMPTZ,
    deleted_by UUID REFERENCES core_users.users(id),

    -- Constraints
    CONSTRAINT uq_epp_catalog_code_tenant UNIQUE (tenant_id, code),
    CONSTRAINT chk_epp_catalog_useful_life CHECK (useful_life_days > 0),
    CONSTRAINT chk_epp_catalog_alert_days CHECK (replacement_alert_days >= 0 AND replacement_alert_days < useful_life_days)
);

-- Comentarios
COMMENT ON TABLE safety_management.epp_catalog IS 'Catálogo maestro de tipos de EPP con especificaciones técnicas';
COMMENT ON COLUMN safety_management.epp_catalog.code IS 'Código único del EPP por tenant';
COMMENT ON COLUMN safety_management.epp_catalog.epp_type IS 'Tipo de EPP según enumeración';
COMMENT ON COLUMN safety_management.epp_catalog.norm_references IS 'Normativas aplicables (NOM-STPS, ANSI, etc.)';
COMMENT ON COLUMN safety_management.epp_catalog.useful_life_days IS 'Vida útil en días desde asignación';
COMMENT ON COLUMN safety_management.epp_catalog.replacement_alert_days IS 'Días antes de vencimiento para alertar';

-- ============================================================================
-- ÍNDICES
-- ============================================================================

-- Índice obligatorio para RLS
CREATE INDEX idx_epp_catalog_tenant_id ON safety_management.epp_catalog(tenant_id);

-- Índices de búsqueda
CREATE INDEX idx_epp_catalog_code ON safety_management.epp_catalog(code);
CREATE INDEX idx_epp_catalog_type ON safety_management.epp_catalog(epp_type);
CREATE INDEX idx_epp_catalog_active ON safety_management.epp_catalog(is_active) WHERE is_active = true;

-- Índice para búsqueda de texto
CREATE INDEX idx_epp_catalog_name_trgm ON safety_management.epp_catalog
    USING gin (name gin_trgm_ops);

-- ============================================================================
-- RLS POLICIES
-- ============================================================================

ALTER TABLE safety_management.epp_catalog ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation_select ON safety_management.epp_catalog
    FOR SELECT
    USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

CREATE POLICY tenant_isolation_insert ON safety_management.epp_catalog
    FOR INSERT
    WITH CHECK (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

CREATE POLICY tenant_isolation_update ON safety_management.epp_catalog
    FOR UPDATE
    USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

CREATE POLICY tenant_isolation_delete ON safety_management.epp_catalog
    FOR DELETE
    USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

-- ============================================================================
-- TRIGGERS
-- ============================================================================

-- Trigger para updated_at
CREATE TRIGGER trg_epp_catalog_update_timestamp
    BEFORE UPDATE ON safety_management.epp_catalog
    FOR EACH ROW
    EXECUTE FUNCTION core_shared.set_updated_at();

---

2. epp_assignments (Asignaciones de EPP)

Descripción: Registro de asignación de EPP a trabajadores con control de vida útil y trazabilidad.

-- ============================================================================
-- Schema: safety_management
-- Tabla: epp_assignments
-- Descripción: Asignaciones de EPP a trabajadores con control de vigencia
-- Módulo: MAI-007
-- ============================================================================

CREATE TABLE IF NOT EXISTS safety_management.epp_assignments (
    -- Primary Key
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),

    -- Multi-tenant
    tenant_id UUID NOT NULL REFERENCES core_tenants.tenants(id) ON DELETE CASCADE,

    -- Relaciones
    epp_catalog_id UUID NOT NULL REFERENCES safety_management.epp_catalog(id),
    worker_id UUID NOT NULL,  -- FK a tabla de trabajadores de MAI-001
    project_id UUID,          -- FK a tabla de proyectos de MAI-002 (opcional)

    -- Datos de la asignación
    assignment_number VARCHAR(50) NOT NULL,  -- Número de acta de entrega
    assignment_date DATE NOT NULL DEFAULT CURRENT_DATE,
    quantity INTEGER NOT NULL DEFAULT 1,

    -- Control de vida útil
    expiry_date DATE NOT NULL,
    replaced_on DATE,
    replacement_reason TEXT,

    -- Estado
    status safety_management.epp_status NOT NULL DEFAULT 'assigned',

    -- Entrega y recepción
    delivery_notes TEXT,
    delivery_signature_url TEXT,  -- Firma digital del trabajador
    delivery_photo_url TEXT,      -- Foto de evidencia
    delivered_by UUID REFERENCES core_users.users(id),

    -- Devolución
    return_date DATE,
    return_condition TEXT,
    return_notes TEXT,
    returned_to UUID REFERENCES core_users.users(id),

    -- Costos
    unit_cost DECIMAL(12, 2),
    total_cost DECIMAL(12, 2) GENERATED ALWAYS AS (quantity * unit_cost) STORED,

    -- Metadata
    metadata JSONB DEFAULT '{}',

    -- Auditoría
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    created_by UUID REFERENCES core_users.users(id),
    updated_by UUID REFERENCES core_users.users(id),

    -- Soft delete
    is_active BOOLEAN NOT NULL DEFAULT true,
    deleted_at TIMESTAMPTZ,
    deleted_by UUID REFERENCES core_users.users(id),

    -- Constraints
    CONSTRAINT uq_epp_assignment_number_tenant UNIQUE (tenant_id, assignment_number),
    CONSTRAINT chk_epp_assignment_quantity CHECK (quantity > 0),
    CONSTRAINT chk_epp_assignment_expiry CHECK (expiry_date >= assignment_date),
    CONSTRAINT chk_epp_assignment_return CHECK (return_date IS NULL OR return_date >= assignment_date)
);

-- Comentarios
COMMENT ON TABLE safety_management.epp_assignments IS 'Asignaciones de EPP a trabajadores con control de vigencia';
COMMENT ON COLUMN safety_management.epp_assignments.assignment_number IS 'Número de acta de entrega único por tenant';
COMMENT ON COLUMN safety_management.epp_assignments.expiry_date IS 'Fecha de vencimiento calculada según vida útil';
COMMENT ON COLUMN safety_management.epp_assignments.delivery_signature_url IS 'URL de firma digital del trabajador en S3';

-- ============================================================================
-- ÍNDICES
-- ============================================================================

-- Índice obligatorio para RLS
CREATE INDEX idx_epp_assignments_tenant_id ON safety_management.epp_assignments(tenant_id);

-- Índices de relaciones
CREATE INDEX idx_epp_assignments_epp_catalog ON safety_management.epp_assignments(epp_catalog_id);
CREATE INDEX idx_epp_assignments_worker ON safety_management.epp_assignments(worker_id);
CREATE INDEX idx_epp_assignments_project ON safety_management.epp_assignments(project_id);

-- Índices de búsqueda y filtros
CREATE INDEX idx_epp_assignments_status ON safety_management.epp_assignments(status);
CREATE INDEX idx_epp_assignments_expiry ON safety_management.epp_assignments(expiry_date)
    WHERE status IN ('assigned', 'in_use');
CREATE INDEX idx_epp_assignments_assignment_date ON safety_management.epp_assignments(assignment_date DESC);

-- Índice compuesto para alertas de vencimiento
CREATE INDEX idx_epp_assignments_alerts ON safety_management.epp_assignments(tenant_id, expiry_date, status)
    WHERE status IN ('assigned', 'in_use') AND is_active = true;

-- ============================================================================
-- RLS POLICIES
-- ============================================================================

ALTER TABLE safety_management.epp_assignments ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation_select ON safety_management.epp_assignments
    FOR SELECT
    USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

CREATE POLICY tenant_isolation_insert ON safety_management.epp_assignments
    FOR INSERT
    WITH CHECK (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

CREATE POLICY tenant_isolation_update ON safety_management.epp_assignments
    FOR UPDATE
    USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

CREATE POLICY tenant_isolation_delete ON safety_management.epp_assignments
    FOR DELETE
    USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

-- ============================================================================
-- TRIGGERS
-- ============================================================================

-- Trigger para updated_at
CREATE TRIGGER trg_epp_assignments_update_timestamp
    BEFORE UPDATE ON safety_management.epp_assignments
    FOR EACH ROW
    EXECUTE FUNCTION core_shared.set_updated_at();

-- Trigger para calcular expiry_date automáticamente
CREATE TRIGGER trg_epp_assignments_calculate_expiry
    BEFORE INSERT OR UPDATE ON safety_management.epp_assignments
    FOR EACH ROW
    EXECUTE FUNCTION safety_management.calculate_epp_expiry_date();

---

3. safety_inspections (Inspecciones de Seguridad)

Descripción: Registro de inspecciones de seguridad realizadas en obra con checklist y hallazgos.

-- ============================================================================
-- Schema: safety_management
-- Tabla: safety_inspections
-- Descripción: Inspecciones de seguridad con geolocalización y evidencias
-- Módulo: MAI-007
-- ============================================================================

CREATE TABLE IF NOT EXISTS safety_management.safety_inspections (
    -- Primary Key
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),

    -- Multi-tenant
    tenant_id UUID NOT NULL REFERENCES core_tenants.tenants(id) ON DELETE CASCADE,

    -- Relaciones
    project_id UUID NOT NULL,  -- FK a tabla de proyectos de MAI-002
    template_id UUID,          -- FK a tabla de plantillas (safety.inspection_templates)

    -- Datos de la inspección
    inspection_number VARCHAR(50) NOT NULL,
    inspection_type VARCHAR(100) NOT NULL,  -- 'Diaria', 'Semanal', 'Mensual', 'Especial'
    inspection_date DATE NOT NULL DEFAULT CURRENT_DATE,
    inspection_time TIME NOT NULL DEFAULT CURRENT_TIME,

    -- Ubicación
    location_description TEXT,
    gps_latitude DECIMAL(10, 8),
    gps_longitude DECIMAL(11, 8),
    gps_accuracy DECIMAL(8, 2),  -- Precisión en metros

    -- Inspector
    inspector_id UUID NOT NULL REFERENCES core_users.users(id),
    inspector_name VARCHAR(200),
    inspector_position VARCHAR(100),

    -- Resultados
    status safety_management.inspection_status NOT NULL DEFAULT 'scheduled',
    total_items_checked INTEGER NOT NULL DEFAULT 0,
    conforming_items INTEGER NOT NULL DEFAULT 0,
    non_conforming_items INTEGER NOT NULL DEFAULT 0,
    not_applicable_items INTEGER NOT NULL DEFAULT 0,
    overall_score DECIMAL(5, 2),  -- Porcentaje de cumplimiento

    -- Clasificación de hallazgos
    critical_findings INTEGER NOT NULL DEFAULT 0,
    high_risk_findings INTEGER NOT NULL DEFAULT 0,
    medium_risk_findings INTEGER NOT NULL DEFAULT 0,
    low_risk_findings INTEGER NOT NULL DEFAULT 0,

    -- Datos del checklist
    checklist_data JSONB NOT NULL DEFAULT '[]',  -- Array de items con resultados

    -- Observaciones
    general_observations TEXT,
    recommendations TEXT,
    immediate_actions_taken TEXT,

    -- Evidencias
    photo_urls TEXT[],  -- Array de URLs de fotos en S3
    document_urls TEXT[],

    -- Firma y aprobación
    inspector_signature_url TEXT,
    reviewed_by UUID REFERENCES core_users.users(id),
    reviewed_at TIMESTAMPTZ,
    review_notes TEXT,

    -- Seguimiento
    requires_followup BOOLEAN NOT NULL DEFAULT false,
    followup_date DATE,
    followup_completed BOOLEAN NOT NULL DEFAULT false,

    -- Metadata
    weather_conditions VARCHAR(100),
    workers_present INTEGER,
    metadata JSONB DEFAULT '{}',

    -- Auditoría
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    created_by UUID REFERENCES core_users.users(id),
    updated_by UUID REFERENCES core_users.users(id),

    -- Soft delete
    is_active BOOLEAN NOT NULL DEFAULT true,
    deleted_at TIMESTAMPTZ,
    deleted_by UUID REFERENCES core_users.users(id),

    -- Constraints
    CONSTRAINT uq_safety_inspection_number_tenant UNIQUE (tenant_id, inspection_number),
    CONSTRAINT chk_safety_inspection_items CHECK (
        total_items_checked = (conforming_items + non_conforming_items + not_applicable_items)
    ),
    CONSTRAINT chk_safety_inspection_findings CHECK (
        non_conforming_items = (critical_findings + high_risk_findings + medium_risk_findings + low_risk_findings)
    ),
    CONSTRAINT chk_safety_inspection_score CHECK (overall_score >= 0 AND overall_score <= 100)
);

-- Comentarios
COMMENT ON TABLE safety_management.safety_inspections IS 'Inspecciones de seguridad con geolocalización y evidencias fotográficas';
COMMENT ON COLUMN safety_management.safety_inspections.checklist_data IS 'Array JSONB con items del checklist y sus resultados';
COMMENT ON COLUMN safety_management.safety_inspections.gps_latitude IS 'Latitud GPS capturada en app móvil';
COMMENT ON COLUMN safety_management.safety_inspections.overall_score IS 'Porcentaje de cumplimiento (conforming / total * 100)';

-- ============================================================================
-- ÍNDICES
-- ============================================================================

-- Índice obligatorio para RLS
CREATE INDEX idx_safety_inspections_tenant_id ON safety_management.safety_inspections(tenant_id);

-- Índices de relaciones
CREATE INDEX idx_safety_inspections_project ON safety_management.safety_inspections(project_id);
CREATE INDEX idx_safety_inspections_inspector ON safety_management.safety_inspections(inspector_id);
CREATE INDEX idx_safety_inspections_template ON safety_management.safety_inspections(template_id);

-- Índices de búsqueda
CREATE INDEX idx_safety_inspections_status ON safety_management.safety_inspections(status);
CREATE INDEX idx_safety_inspections_date ON safety_management.safety_inspections(inspection_date DESC);
CREATE INDEX idx_safety_inspections_type ON safety_management.safety_inspections(inspection_type);

-- Índice para inspecciones con hallazgos críticos
CREATE INDEX idx_safety_inspections_critical ON safety_management.safety_inspections(tenant_id, project_id, critical_findings)
    WHERE critical_findings > 0 AND is_active = true;

-- Índice para seguimiento
CREATE INDEX idx_safety_inspections_followup ON safety_management.safety_inspections(followup_date)
    WHERE requires_followup = true AND followup_completed = false;

-- Índice GIN para búsqueda en checklist_data
CREATE INDEX idx_safety_inspections_checklist ON safety_management.safety_inspections
    USING gin (checklist_data jsonb_path_ops);

-- ============================================================================
-- RLS POLICIES
-- ============================================================================

ALTER TABLE safety_management.safety_inspections ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation_select ON safety_management.safety_inspections
    FOR SELECT
    USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

CREATE POLICY tenant_isolation_insert ON safety_management.safety_inspections
    FOR INSERT
    WITH CHECK (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

CREATE POLICY tenant_isolation_update ON safety_management.safety_inspections
    FOR UPDATE
    USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

CREATE POLICY tenant_isolation_delete ON safety_management.safety_inspections
    FOR DELETE
    USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

-- ============================================================================
-- TRIGGERS
-- ============================================================================

-- Trigger para updated_at
CREATE TRIGGER trg_safety_inspections_update_timestamp
    BEFORE UPDATE ON safety_management.safety_inspections
    FOR EACH ROW
    EXECUTE FUNCTION core_shared.set_updated_at();

-- Trigger para calcular overall_score automáticamente
CREATE TRIGGER trg_safety_inspections_calculate_score
    BEFORE INSERT OR UPDATE ON safety_management.safety_inspections
    FOR EACH ROW
    EXECUTE FUNCTION safety_management.calculate_inspection_score();

---

4. incidents (Reportes de Incidentes)

Descripción: Registro de incidentes y accidentes laborales con geolocalización y evidencias.

-- ============================================================================
-- Schema: safety_management
-- Tabla: incidents
-- Descripción: Registro de incidentes y accidentes laborales
-- Módulo: MAI-007
-- ============================================================================

CREATE TABLE IF NOT EXISTS safety_management.incidents (
    -- Primary Key
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),

    -- Multi-tenant
    tenant_id UUID NOT NULL REFERENCES core_tenants.tenants(id) ON DELETE CASCADE,

    -- Relaciones
    project_id UUID NOT NULL,  -- FK a tabla de proyectos de MAI-002

    -- Identificación del incidente
    incident_number VARCHAR(50) NOT NULL,
    incident_date DATE NOT NULL,
    incident_time TIME NOT NULL,
    reported_at TIMESTAMPTZ NOT NULL DEFAULT now(),

    -- Clasificación
    severity safety_management.incident_severity NOT NULL,
    status safety_management.incident_status NOT NULL DEFAULT 'reported',
    incident_type VARCHAR(100) NOT NULL,  -- 'Caída', 'Golpe', 'Corte', etc.

    -- Ubicación
    location_description TEXT NOT NULL,
    area VARCHAR(200),
    gps_latitude DECIMAL(10, 8),
    gps_longitude DECIMAL(11, 8),
    gps_accuracy DECIMAL(8, 2),

    -- Descripción del incidente
    description TEXT NOT NULL,
    immediate_cause TEXT,
    activity_at_time TEXT,
    weather_conditions VARCHAR(100),

    -- Personas involucradas
    affected_worker_id UUID,  -- FK a tabla de trabajadores
    affected_worker_name VARCHAR(200),
    affected_worker_position VARCHAR(100),
    affected_worker_age INTEGER,
    affected_worker_experience_months INTEGER,

    -- Lesiones
    injury_type VARCHAR(200),
    injury_description TEXT,
    body_part_affected VARCHAR(200),
    medical_attention_required BOOLEAN NOT NULL DEFAULT false,
    hospitalization_required BOOLEAN NOT NULL DEFAULT false,
    emergency_services_called BOOLEAN NOT NULL DEFAULT false,

    -- Impacto laboral
    work_days_lost INTEGER DEFAULT 0,
    restricted_work_days INTEGER DEFAULT 0,
    estimated_return_date DATE,

    -- EPP
    epp_being_used TEXT[],
    epp_condition TEXT,
    epp_adequate_for_task BOOLEAN,

    -- Reportado por
    reported_by_id UUID NOT NULL REFERENCES core_users.users(id),
    reported_by_name VARCHAR(200),
    reporter_position VARCHAR(100),
    witness_names TEXT[],

    -- Evidencias
    photo_urls TEXT[],
    document_urls TEXT[],
    video_urls TEXT[],

    -- Notificaciones
    authorities_notified BOOLEAN NOT NULL DEFAULT false,
    authorities_notified_at TIMESTAMPTZ,
    authority_names TEXT[],
    family_notified BOOLEAN NOT NULL DEFAULT false,

    -- Costos (registrados posteriormente)
    medical_costs DECIMAL(12, 2) DEFAULT 0,
    legal_costs DECIMAL(12, 2) DEFAULT 0,
    equipment_damage_costs DECIMAL(12, 2) DEFAULT 0,
    productivity_loss_costs DECIMAL(12, 2) DEFAULT 0,
    total_costs DECIMAL(12, 2) GENERATED ALWAYS AS (
        COALESCE(medical_costs, 0) +
        COALESCE(legal_costs, 0) +
        COALESCE(equipment_damage_costs, 0) +
        COALESCE(productivity_loss_costs, 0)
    ) STORED,

    -- Metadata
    metadata JSONB DEFAULT '{}',

    -- Auditoría
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    created_by UUID REFERENCES core_users.users(id),
    updated_by UUID REFERENCES core_users.users(id),

    -- Soft delete
    is_active BOOLEAN NOT NULL DEFAULT true,
    deleted_at TIMESTAMPTZ,
    deleted_by UUID REFERENCES core_users.users(id),

    -- Constraints
    CONSTRAINT uq_incident_number_tenant UNIQUE (tenant_id, incident_number),
    CONSTRAINT chk_incident_work_days CHECK (work_days_lost >= 0),
    CONSTRAINT chk_incident_costs CHECK (
        medical_costs >= 0 AND
        legal_costs >= 0 AND
        equipment_damage_costs >= 0 AND
        productivity_loss_costs >= 0
    )
);

-- Comentarios
COMMENT ON TABLE safety_management.incidents IS 'Registro de incidentes y accidentes laborales con geolocalización';
COMMENT ON COLUMN safety_management.incidents.severity IS 'Gravedad del incidente según clasificación';
COMMENT ON COLUMN safety_management.incidents.work_days_lost IS 'Días laborales perdidos por el trabajador';
COMMENT ON COLUMN safety_management.incidents.total_costs IS 'Costo total calculado automáticamente';

-- ============================================================================
-- ÍNDICES
-- ============================================================================

-- Índice obligatorio para RLS
CREATE INDEX idx_incidents_tenant_id ON safety_management.incidents(tenant_id);

-- Índices de relaciones
CREATE INDEX idx_incidents_project ON safety_management.incidents(project_id);
CREATE INDEX idx_incidents_worker ON safety_management.incidents(affected_worker_id);
CREATE INDEX idx_incidents_reporter ON safety_management.incidents(reported_by_id);

-- Índices de búsqueda y análisis
CREATE INDEX idx_incidents_severity ON safety_management.incidents(severity);
CREATE INDEX idx_incidents_status ON safety_management.incidents(status);
CREATE INDEX idx_incidents_date ON safety_management.incidents(incident_date DESC);
CREATE INDEX idx_incidents_type ON safety_management.incidents(incident_type);

-- Índice para KPIs de seguridad
CREATE INDEX idx_incidents_kpi ON safety_management.incidents(tenant_id, incident_date, severity)
    WHERE is_active = true;

-- Índice para incidentes que requieren seguimiento
CREATE INDEX idx_incidents_pending ON safety_management.incidents(status)
    WHERE status IN ('reported', 'under_investigation', 'actions_pending');

-- Índice para incidentes graves
CREATE INDEX idx_incidents_serious ON safety_management.incidents(tenant_id, project_id, severity)
    WHERE severity IN ('serious', 'fatal') AND is_active = true;

-- ============================================================================
-- RLS POLICIES
-- ============================================================================

ALTER TABLE safety_management.incidents ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation_select ON safety_management.incidents
    FOR SELECT
    USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

CREATE POLICY tenant_isolation_insert ON safety_management.incidents
    FOR INSERT
    WITH CHECK (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

CREATE POLICY tenant_isolation_update ON safety_management.incidents
    FOR UPDATE
    USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

CREATE POLICY tenant_isolation_delete ON safety_management.incidents
    FOR DELETE
    USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

-- ============================================================================
-- TRIGGERS
-- ============================================================================

-- Trigger para updated_at
CREATE TRIGGER trg_incidents_update_timestamp
    BEFORE UPDATE ON safety_management.incidents
    FOR EACH ROW
    EXECUTE FUNCTION core_shared.set_updated_at();

-- Trigger para notificaciones automáticas de incidentes graves
CREATE TRIGGER trg_incidents_notify_serious
    AFTER INSERT OR UPDATE ON safety_management.incidents
    FOR EACH ROW
    WHEN (NEW.severity IN ('serious', 'fatal'))
    EXECUTE FUNCTION safety_management.notify_serious_incident();

---

5. incident_investigations (Investigaciones de Incidentes)

Descripción: Investigación de causas raíz de incidentes con análisis de Ishikawa y acciones correctivas.

-- ============================================================================
-- Schema: safety_management
-- Tabla: incident_investigations
-- Descripción: Investigación de causas raíz de incidentes
-- Módulo: MAI-007
-- ============================================================================

CREATE TABLE IF NOT EXISTS safety_management.incident_investigations (
    -- Primary Key
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),

    -- Multi-tenant
    tenant_id UUID NOT NULL REFERENCES core_tenants.tenants(id) ON DELETE CASCADE,

    -- Relación
    incident_id UUID NOT NULL REFERENCES safety_management.incidents(id) ON DELETE CASCADE,

    -- Investigación
    investigation_number VARCHAR(50) NOT NULL,
    investigation_start_date DATE NOT NULL,
    investigation_end_date DATE,

    -- Equipo investigador
    lead_investigator_id UUID NOT NULL REFERENCES core_users.users(id),
    team_members UUID[],  -- Array de IDs de usuarios
    team_members_names TEXT[],

    -- Metodología
    investigation_method VARCHAR(100) DEFAULT 'Ishikawa',  -- 'Ishikawa', '5 Whys', 'Root Cause Tree'

    -- Análisis de causas (Ishikawa - Espina de Pescado)
    people_factors TEXT[],       -- Factores humanos
    method_factors TEXT[],       -- Factores de método/procedimiento
    machine_factors TEXT[],      -- Factores de equipo/maquinaria
    material_factors TEXT[],     -- Factores de materiales
    environment_factors TEXT[],  -- Factores ambientales
    management_factors TEXT[],   -- Factores de gestión

    -- Causa raíz identificada
    root_cause TEXT,
    contributing_factors TEXT[],

    -- Hallazgos
    findings TEXT NOT NULL,
    evidence_collected TEXT[],
    witness_statements JSONB DEFAULT '[]',  -- Array de testimonios

    -- Conclusiones
    conclusions TEXT,
    preventability_assessment TEXT,
    similar_incidents_reviewed INTEGER DEFAULT 0,

    -- Recomendaciones
    immediate_actions_recommended TEXT[],
    corrective_actions_recommended TEXT[],
    preventive_actions_recommended TEXT[],

    -- Aprobación
    approved_by UUID REFERENCES core_users.users(id),
    approved_at TIMESTAMPTZ,
    approval_notes TEXT,

    -- Documentos
    report_url TEXT,  -- URL del informe oficial en S3
    document_urls TEXT[],
    photo_urls TEXT[],

    -- Metadata
    metadata JSONB DEFAULT '{}',

    -- Auditoría
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    created_by UUID REFERENCES core_users.users(id),
    updated_by UUID REFERENCES core_users.users(id),

    -- Soft delete
    is_active BOOLEAN NOT NULL DEFAULT true,
    deleted_at TIMESTAMPTZ,
    deleted_by UUID REFERENCES core_users.users(id),

    -- Constraints
    CONSTRAINT uq_investigation_number_tenant UNIQUE (tenant_id, investigation_number),
    CONSTRAINT uq_investigation_incident UNIQUE (incident_id),  -- Una investigación por incidente
    CONSTRAINT chk_investigation_dates CHECK (
        investigation_end_date IS NULL OR investigation_end_date >= investigation_start_date
    )
);

-- Comentarios
COMMENT ON TABLE safety_management.incident_investigations IS 'Investigación de causas raíz de incidentes con metodología Ishikawa';
COMMENT ON COLUMN safety_management.incident_investigations.people_factors IS 'Factores humanos en diagrama de Ishikawa';
COMMENT ON COLUMN safety_management.incident_investigations.root_cause IS 'Causa raíz identificada del incidente';

-- ============================================================================
-- ÍNDICES
-- ============================================================================

-- Índice obligatorio para RLS
CREATE INDEX idx_incident_investigations_tenant_id ON safety_management.incident_investigations(tenant_id);

-- Índices de relaciones
CREATE INDEX idx_incident_investigations_incident ON safety_management.incident_investigations(incident_id);
CREATE INDEX idx_incident_investigations_lead ON safety_management.incident_investigations(lead_investigator_id);

-- Índices de búsqueda
CREATE INDEX idx_incident_investigations_dates ON safety_management.incident_investigations(investigation_start_date DESC);
CREATE INDEX idx_incident_investigations_pending ON safety_management.incident_investigations(investigation_end_date)
    WHERE investigation_end_date IS NULL AND is_active = true;

-- Índice GIN para búsqueda en testimonios
CREATE INDEX idx_incident_investigations_witnesses ON safety_management.incident_investigations
    USING gin (witness_statements jsonb_path_ops);

-- ============================================================================
-- RLS POLICIES
-- ============================================================================

ALTER TABLE safety_management.incident_investigations ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation_select ON safety_management.incident_investigations
    FOR SELECT
    USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

CREATE POLICY tenant_isolation_insert ON safety_management.incident_investigations
    FOR INSERT
    WITH CHECK (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

CREATE POLICY tenant_isolation_update ON safety_management.incident_investigations
    FOR UPDATE
    USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

CREATE POLICY tenant_isolation_delete ON safety_management.incident_investigations
    FOR DELETE
    USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

-- ============================================================================
-- TRIGGERS
-- ============================================================================

-- Trigger para updated_at
CREATE TRIGGER trg_incident_investigations_update_timestamp
    BEFORE UPDATE ON safety_management.incident_investigations
    FOR EACH ROW
    EXECUTE FUNCTION core_shared.set_updated_at();

-- Trigger para actualizar estado del incidente al completar investigación
CREATE TRIGGER trg_incident_investigations_complete
    AFTER UPDATE ON safety_management.incident_investigations
    FOR EACH ROW
    WHEN (NEW.investigation_end_date IS NOT NULL AND OLD.investigation_end_date IS NULL)
    EXECUTE FUNCTION safety_management.update_incident_status_investigated();

---

6. trainings (Capacitaciones de Seguridad)

Descripción: Catálogo de capacitaciones de seguridad y programación de sesiones.

-- ============================================================================
-- Schema: safety_management
-- Tabla: trainings
-- Descripción: Sesiones de capacitación de seguridad programadas
-- Módulo: MAI-007
-- ============================================================================

CREATE TABLE IF NOT EXISTS safety_management.trainings (
    -- Primary Key
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),

    -- Multi-tenant
    tenant_id UUID NOT NULL REFERENCES core_tenants.tenants(id) ON DELETE CASCADE,

    -- Relación
    project_id UUID,  -- FK a tabla de proyectos (opcional, puede ser corporativa)

    -- Datos de la capacitación
    training_code VARCHAR(50) NOT NULL,
    training_name VARCHAR(200) NOT NULL,
    training_type VARCHAR(100) NOT NULL,  -- 'Inducción', 'Trabajo en Altura', 'EPP', etc.
    description TEXT,

    -- Normativa
    norm_references TEXT[],  -- Normativas que respaldan la capacitación
    is_mandatory BOOLEAN NOT NULL DEFAULT false,
    required_for_positions TEXT[],  -- Puestos que requieren esta capacitación

    -- Programación
    scheduled_date DATE NOT NULL,
    scheduled_start_time TIME NOT NULL,
    scheduled_end_time TIME NOT NULL,
    duration_hours DECIMAL(4, 2),

    -- Ubicación
    location VARCHAR(200),
    location_type VARCHAR(50) DEFAULT 'presencial',  -- 'presencial', 'online', 'híbrida'
    meeting_url TEXT,  -- Para capacitaciones online

    -- Instructor
    instructor_id UUID REFERENCES core_users.users(id),
    instructor_name VARCHAR(200),
    instructor_credentials TEXT,
    external_instructor BOOLEAN NOT NULL DEFAULT false,
    instructor_company VARCHAR(200),

    -- Capacidad
    max_participants INTEGER,
    min_participants INTEGER,

    -- Estado
    status safety_management.training_status NOT NULL DEFAULT 'scheduled',

    -- Contenido
    topics TEXT[],
    learning_objectives TEXT[],
    materials_provided TEXT[],
    materials_urls TEXT[],  -- URLs de materiales en S3

    -- Evaluación
    has_evaluation BOOLEAN NOT NULL DEFAULT false,
    evaluation_type VARCHAR(50),  -- 'test', 'práctica', 'ambos'
    passing_score DECIMAL(5, 2),  -- Calificación mínima aprobatoria

    -- Certificación
    issues_certificate BOOLEAN NOT NULL DEFAULT true,
    certificate_template_url TEXT,
    certificate_validity_months INTEGER,  -- Meses de validez del certificado

    -- Asistencia
    registered_participants INTEGER DEFAULT 0,
    actual_attendees INTEGER DEFAULT 0,

    -- Resultados
    average_score DECIMAL(5, 2),
    passing_participants INTEGER DEFAULT 0,
    failing_participants INTEGER DEFAULT 0,

    -- Costos
    instructor_fee DECIMAL(12, 2) DEFAULT 0,
    materials_cost DECIMAL(12, 2) DEFAULT 0,
    venue_cost DECIMAL(12, 2) DEFAULT 0,
    total_cost DECIMAL(12, 2) GENERATED ALWAYS AS (
        COALESCE(instructor_fee, 0) +
        COALESCE(materials_cost, 0) +
        COALESCE(venue_cost, 0)
    ) STORED,

    -- Feedback
    feedback_summary TEXT,
    average_satisfaction DECIMAL(3, 2),  -- Escala 1-5

    -- Metadata
    metadata JSONB DEFAULT '{}',

    -- Auditoría
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    created_by UUID REFERENCES core_users.users(id),
    updated_by UUID REFERENCES core_users.users(id),

    -- Soft delete
    is_active BOOLEAN NOT NULL DEFAULT true,
    deleted_at TIMESTAMPTZ,
    deleted_by UUID REFERENCES core_users.users(id),

    -- Constraints
    CONSTRAINT uq_training_code_tenant UNIQUE (tenant_id, training_code),
    CONSTRAINT chk_training_times CHECK (scheduled_end_time > scheduled_start_time),
    CONSTRAINT chk_training_capacity CHECK (
        max_participants IS NULL OR
        min_participants IS NULL OR
        max_participants >= min_participants
    ),
    CONSTRAINT chk_training_score CHECK (
        passing_score IS NULL OR
        (passing_score >= 0 AND passing_score <= 100)
    ),
    CONSTRAINT chk_training_participants CHECK (
        registered_participants >= 0 AND
        actual_attendees >= 0 AND
        actual_attendees <= registered_participants
    )
);

-- Comentarios
COMMENT ON TABLE safety_management.trainings IS 'Sesiones de capacitación de seguridad programadas';
COMMENT ON COLUMN safety_management.trainings.is_mandatory IS 'Indica si es capacitación obligatoria según normativa';
COMMENT ON COLUMN safety_management.trainings.certificate_validity_months IS 'Meses de validez del certificado antes de requerir renovación';

-- ============================================================================
-- ÍNDICES
-- ============================================================================

-- Índice obligatorio para RLS
CREATE INDEX idx_trainings_tenant_id ON safety_management.trainings(tenant_id);

-- Índices de relaciones
CREATE INDEX idx_trainings_project ON safety_management.trainings(project_id);
CREATE INDEX idx_trainings_instructor ON safety_management.trainings(instructor_id);

-- Índices de búsqueda
CREATE INDEX idx_trainings_code ON safety_management.trainings(training_code);
CREATE INDEX idx_trainings_type ON safety_management.trainings(training_type);
CREATE INDEX idx_trainings_status ON safety_management.trainings(status);
CREATE INDEX idx_trainings_date ON safety_management.trainings(scheduled_date DESC);

-- Índice para capacitaciones próximas
CREATE INDEX idx_trainings_upcoming ON safety_management.trainings(scheduled_date)
    WHERE status = 'scheduled' AND is_active = true;

-- Índice para capacitaciones obligatorias
CREATE INDEX idx_trainings_mandatory ON safety_management.trainings(tenant_id, is_mandatory)
    WHERE is_mandatory = true AND is_active = true;

-- Índice para búsqueda de texto en nombre
CREATE INDEX idx_trainings_name_trgm ON safety_management.trainings
    USING gin (training_name gin_trgm_ops);

-- ============================================================================
-- RLS POLICIES
-- ============================================================================

ALTER TABLE safety_management.trainings ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation_select ON safety_management.trainings
    FOR SELECT
    USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

CREATE POLICY tenant_isolation_insert ON safety_management.trainings
    FOR INSERT
    WITH CHECK (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

CREATE POLICY tenant_isolation_update ON safety_management.trainings
    FOR UPDATE
    USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

CREATE POLICY tenant_isolation_delete ON safety_management.trainings
    FOR DELETE
    USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

-- ============================================================================
-- TRIGGERS
-- ============================================================================

-- Trigger para updated_at
CREATE TRIGGER trg_trainings_update_timestamp
    BEFORE UPDATE ON safety_management.trainings
    FOR EACH ROW
    EXECUTE FUNCTION core_shared.set_updated_at();

-- Trigger para notificar capacitaciones próximas
CREATE TRIGGER trg_trainings_notify_upcoming
    AFTER INSERT OR UPDATE ON safety_management.trainings
    FOR EACH ROW
    WHEN (NEW.scheduled_date BETWEEN CURRENT_DATE AND CURRENT_DATE + INTERVAL '7 days')
    EXECUTE FUNCTION safety_management.notify_upcoming_training();

---

7. training_attendance (Registro de Asistencia)

Descripción: Registro de asistencia y evaluaciones de participantes en capacitaciones.

-- ============================================================================
-- Schema: safety_management
-- Tabla: training_attendance
-- Descripción: Registro de asistencia y evaluaciones de capacitaciones
-- Módulo: MAI-007
-- ============================================================================

CREATE TABLE IF NOT EXISTS safety_management.training_attendance (
    -- Primary Key
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),

    -- Multi-tenant
    tenant_id UUID NOT NULL REFERENCES core_tenants.tenants(id) ON DELETE CASCADE,

    -- Relaciones
    training_id UUID NOT NULL REFERENCES safety_management.trainings(id) ON DELETE CASCADE,
    worker_id UUID NOT NULL,  -- FK a tabla de trabajadores

    -- Datos del participante
    worker_name VARCHAR(200) NOT NULL,
    worker_position VARCHAR(100),
    worker_company VARCHAR(200),  -- Empresa (constructora, subcontratista)

    -- Registro
    registration_date TIMESTAMPTZ NOT NULL DEFAULT now(),
    registered_by UUID REFERENCES core_users.users(id),

    -- Asistencia
    attended BOOLEAN NOT NULL DEFAULT false,
    check_in_time TIMESTAMPTZ,
    check_out_time TIMESTAMPTZ,
    attendance_method VARCHAR(50),  -- 'manual', 'qr', 'biometric', 'digital_signature'

    -- Firma
    signature_url TEXT,  -- URL de firma digital en S3

    -- Evaluación
    evaluation_taken BOOLEAN NOT NULL DEFAULT false,
    evaluation_date TIMESTAMPTZ,
    score DECIMAL(5, 2),
    passed BOOLEAN,
    evaluation_answers JSONB DEFAULT '{}',

    -- Certificado
    certificate_issued BOOLEAN NOT NULL DEFAULT false,
    certificate_number VARCHAR(100),
    certificate_issue_date DATE,
    certificate_expiry_date DATE,
    certificate_url TEXT,  -- URL del certificado en S3

    -- Feedback del participante
    satisfaction_rating INTEGER,  -- Escala 1-5
    feedback_comments TEXT,

    -- Metadata
    metadata JSONB DEFAULT '{}',

    -- Auditoría
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    created_by UUID REFERENCES core_users.users(id),
    updated_by UUID REFERENCES core_users.users(id),

    -- Soft delete
    is_active BOOLEAN NOT NULL DEFAULT true,
    deleted_at TIMESTAMPTZ,
    deleted_by UUID REFERENCES core_users.users(id),

    -- Constraints
    CONSTRAINT uq_training_attendance_worker UNIQUE (training_id, worker_id),
    CONSTRAINT chk_training_attendance_score CHECK (
        score IS NULL OR (score >= 0 AND score <= 100)
    ),
    CONSTRAINT chk_training_attendance_rating CHECK (
        satisfaction_rating IS NULL OR
        (satisfaction_rating >= 1 AND satisfaction_rating <= 5)
    ),
    CONSTRAINT chk_training_attendance_times CHECK (
        check_out_time IS NULL OR
        check_in_time IS NULL OR
        check_out_time >= check_in_time
    )
);

-- Comentarios
COMMENT ON TABLE safety_management.training_attendance IS 'Registro de asistencia y evaluaciones de capacitaciones';
COMMENT ON COLUMN safety_management.training_attendance.attendance_method IS 'Método de registro: manual, QR, biométrico, firma digital';
COMMENT ON COLUMN safety_management.training_attendance.certificate_expiry_date IS 'Fecha de vencimiento del certificado';

-- ============================================================================
-- ÍNDICES
-- ============================================================================

-- Índice obligatorio para RLS
CREATE INDEX idx_training_attendance_tenant_id ON safety_management.training_attendance(tenant_id);

-- Índices de relaciones
CREATE INDEX idx_training_attendance_training ON safety_management.training_attendance(training_id);
CREATE INDEX idx_training_attendance_worker ON safety_management.training_attendance(worker_id);

-- Índices de búsqueda
CREATE INDEX idx_training_attendance_attended ON safety_management.training_attendance(attended);
CREATE INDEX idx_training_attendance_passed ON safety_management.training_attendance(passed)
    WHERE evaluation_taken = true;

-- Índice para certificados próximos a vencer
CREATE INDEX idx_training_attendance_expiry ON safety_management.training_attendance(certificate_expiry_date)
    WHERE certificate_issued = true AND is_active = true;

-- Índice para certificados vigentes por trabajador
CREATE INDEX idx_training_attendance_valid_certs ON safety_management.training_attendance(worker_id, certificate_expiry_date)
    WHERE certificate_issued = true AND certificate_expiry_date >= CURRENT_DATE;

-- Índice GIN para búsqueda en respuestas de evaluación
CREATE INDEX idx_training_attendance_answers ON safety_management.training_attendance
    USING gin (evaluation_answers jsonb_path_ops);

-- ============================================================================
-- RLS POLICIES
-- ============================================================================

ALTER TABLE safety_management.training_attendance ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation_select ON safety_management.training_attendance
    FOR SELECT
    USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

CREATE POLICY tenant_isolation_insert ON safety_management.training_attendance
    FOR INSERT
    WITH CHECK (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

CREATE POLICY tenant_isolation_update ON safety_management.training_attendance
    FOR UPDATE
    USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

CREATE POLICY tenant_isolation_delete ON safety_management.training_attendance
    FOR DELETE
    USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

-- ============================================================================
-- TRIGGERS
-- ============================================================================

-- Trigger para updated_at
CREATE TRIGGER trg_training_attendance_update_timestamp
    BEFORE UPDATE ON safety_management.training_attendance
    FOR EACH ROW
    EXECUTE FUNCTION core_shared.set_updated_at();

-- Trigger para calcular passed automáticamente al registrar score
CREATE TRIGGER trg_training_attendance_calculate_passed
    BEFORE INSERT OR UPDATE ON safety_management.training_attendance
    FOR EACH ROW
    WHEN (NEW.score IS NOT NULL)
    EXECUTE FUNCTION safety_management.calculate_training_passed();

-- Trigger para actualizar contadores de la capacitación
CREATE TRIGGER trg_training_attendance_update_training_stats
    AFTER INSERT OR UPDATE ON safety_management.training_attendance
    FOR EACH ROW
    EXECUTE FUNCTION safety_management.update_training_statistics();

-- Trigger para alertar vencimiento de certificados
CREATE TRIGGER trg_training_attendance_alert_expiry
    AFTER INSERT OR UPDATE ON safety_management.training_attendance
    FOR EACH ROW
    WHEN (NEW.certificate_expiry_date BETWEEN CURRENT_DATE AND CURRENT_DATE + INTERVAL '30 days')
    EXECUTE FUNCTION safety_management.alert_certificate_expiry();

---

Funciones Auxiliares

1. calculate_epp_expiry_date()

-- ============================================================================
-- Función: calculate_epp_expiry_date
-- Descripción: Calcula automáticamente la fecha de vencimiento del EPP
-- ============================================================================

CREATE OR REPLACE FUNCTION safety_management.calculate_epp_expiry_date()
RETURNS TRIGGER AS $$
DECLARE
    v_useful_life_days INTEGER;
BEGIN
    -- Obtener vida útil del catálogo
    SELECT useful_life_days INTO v_useful_life_days
    FROM safety_management.epp_catalog
    WHERE id = NEW.epp_catalog_id;

    -- Calcular fecha de vencimiento
    IF v_useful_life_days IS NOT NULL THEN
        NEW.expiry_date := NEW.assignment_date + v_useful_life_days;
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

COMMENT ON FUNCTION safety_management.calculate_epp_expiry_date() IS
'Calcula automáticamente la fecha de vencimiento del EPP basado en la vida útil del catálogo';

2. calculate_inspection_score()

-- ============================================================================
-- Función: calculate_inspection_score
-- Descripción: Calcula el score de cumplimiento de la inspección
-- ============================================================================

CREATE OR REPLACE FUNCTION safety_management.calculate_inspection_score()
RETURNS TRIGGER AS $$
BEGIN
    -- Calcular porcentaje de cumplimiento
    IF NEW.total_items_checked > 0 THEN
        NEW.overall_score := ROUND(
            (NEW.conforming_items::DECIMAL / NEW.total_items_checked::DECIMAL) * 100,
            2
        );
    ELSE
        NEW.overall_score := 0;
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

COMMENT ON FUNCTION safety_management.calculate_inspection_score() IS
'Calcula el porcentaje de cumplimiento de la inspección automáticamente';

3. notify_serious_incident()

-- ============================================================================
-- Función: notify_serious_incident
-- Descripción: Envía notificaciones automáticas para incidentes graves
-- ============================================================================

CREATE OR REPLACE FUNCTION safety_management.notify_serious_incident()
RETURNS TRIGGER AS $$
BEGIN
    -- Insertar notificación en tabla de notificaciones del core
    -- Notificar a: Coordinador de Seguridad, Residente, Director General

    -- TODO: Implementar lógica de notificación usando sistema MGN-008
    -- Por ahora, solo registramos en log
    RAISE NOTICE 'INCIDENTE GRAVE REPORTADO: % - Severidad: %', NEW.incident_number, NEW.severity;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

COMMENT ON FUNCTION safety_management.notify_serious_incident() IS
'Envía notificaciones automáticas cuando se reporta un incidente grave o fatal';

4. update_incident_status_investigated()

-- ============================================================================
-- Función: update_incident_status_investigated
-- Descripción: Actualiza estado del incidente al completar investigación
-- ============================================================================

CREATE OR REPLACE FUNCTION safety_management.update_incident_status_investigated()
RETURNS TRIGGER AS $$
BEGIN
    -- Actualizar estado del incidente
    UPDATE safety_management.incidents
    SET
        status = 'investigation_completed',
        updated_at = now()
    WHERE id = NEW.incident_id;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

COMMENT ON FUNCTION safety_management.update_incident_status_investigated() IS
'Actualiza automáticamente el estado del incidente cuando se completa la investigación';

5. calculate_training_passed()

-- ============================================================================
-- Función: calculate_training_passed
-- Descripción: Determina si el participante aprobó la evaluación
-- ============================================================================

CREATE OR REPLACE FUNCTION safety_management.calculate_training_passed()
RETURNS TRIGGER AS $$
DECLARE
    v_passing_score DECIMAL(5,2);
BEGIN
    -- Obtener calificación mínima aprobatoria
    SELECT passing_score INTO v_passing_score
    FROM safety_management.trainings
    WHERE id = NEW.training_id;

    -- Determinar si aprobó
    IF v_passing_score IS NOT NULL AND NEW.score IS NOT NULL THEN
        NEW.passed := NEW.score >= v_passing_score;
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

COMMENT ON FUNCTION safety_management.calculate_training_passed() IS
'Determina automáticamente si el participante aprobó basado en el passing_score';

6. update_training_statistics()

-- ============================================================================
-- Función: update_training_statistics
-- Descripción: Actualiza contadores de asistencia y resultados
-- ============================================================================

CREATE OR REPLACE FUNCTION safety_management.update_training_statistics()
RETURNS TRIGGER AS $$
BEGIN
    -- Actualizar estadísticas de la capacitación
    UPDATE safety_management.trainings
    SET
        registered_participants = (
            SELECT COUNT(*)
            FROM safety_management.training_attendance
            WHERE training_id = NEW.training_id AND is_active = true
        ),
        actual_attendees = (
            SELECT COUNT(*)
            FROM safety_management.training_attendance
            WHERE training_id = NEW.training_id AND attended = true AND is_active = true
        ),
        passing_participants = (
            SELECT COUNT(*)
            FROM safety_management.training_attendance
            WHERE training_id = NEW.training_id AND passed = true AND is_active = true
        ),
        failing_participants = (
            SELECT COUNT(*)
            FROM safety_management.training_attendance
            WHERE training_id = NEW.training_id AND passed = false AND is_active = true
        ),
        average_score = (
            SELECT AVG(score)
            FROM safety_management.training_attendance
            WHERE training_id = NEW.training_id AND score IS NOT NULL AND is_active = true
        ),
        updated_at = now()
    WHERE id = NEW.training_id;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

COMMENT ON FUNCTION safety_management.update_training_statistics() IS
'Actualiza automáticamente los contadores y promedios de la capacitación';

7. alert_certificate_expiry()

-- ============================================================================
-- Función: alert_certificate_expiry
-- Descripción: Genera alertas de certificados próximos a vencer
-- ============================================================================

CREATE OR REPLACE FUNCTION safety_management.alert_certificate_expiry()
RETURNS TRIGGER AS $$
BEGIN
    -- TODO: Integrar con sistema de notificaciones MGN-008
    -- Enviar notificación al trabajador y coordinador

    RAISE NOTICE 'ALERTA: Certificado próximo a vencer - Trabajador: %, Expira: %',
        NEW.worker_id, NEW.certificate_expiry_date;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

COMMENT ON FUNCTION safety_management.alert_certificate_expiry() IS
'Genera alertas cuando un certificado está próximo a vencer (30 días)';

8. notify_upcoming_training()

-- ============================================================================
-- Función: notify_upcoming_training
-- Descripción: Notifica capacitaciones próximas (7 días)
-- ============================================================================

CREATE OR REPLACE FUNCTION safety_management.notify_upcoming_training()
RETURNS TRIGGER AS $$
BEGIN
    -- TODO: Integrar con sistema de notificaciones MGN-008
    -- Notificar a participantes registrados e instructor

    RAISE NOTICE 'RECORDATORIO: Capacitación próxima - %, Fecha: %',
        NEW.training_name, NEW.scheduled_date;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

COMMENT ON FUNCTION safety_management.notify_upcoming_training() IS
'Notifica automáticamente capacitaciones programadas en los próximos 7 días';

---

Vistas Útiles

1. v_epp_expiring_soon

-- ============================================================================
-- Vista: v_epp_expiring_soon
-- Descripción: EPP próximo a vencer en los próximos 30 días
-- ============================================================================

CREATE OR REPLACE VIEW safety_management.v_epp_expiring_soon AS
SELECT
    ea.id,
    ea.tenant_id,
    ea.assignment_number,
    ea.worker_id,
    ec.name AS epp_name,
    ec.epp_type,
    ea.assignment_date,
    ea.expiry_date,
    (ea.expiry_date - CURRENT_DATE) AS days_until_expiry,
    ea.status
FROM safety_management.epp_assignments ea
JOIN safety_management.epp_catalog ec ON ea.epp_catalog_id = ec.id
WHERE ea.status IN ('assigned', 'in_use')
    AND ea.expiry_date BETWEEN CURRENT_DATE AND CURRENT_DATE + INTERVAL '30 days'
    AND ea.is_active = true
ORDER BY ea.expiry_date ASC;

COMMENT ON VIEW safety_management.v_epp_expiring_soon IS
'EPP asignado que vence en los próximos 30 días';

2. v_incident_kpi

-- ============================================================================
-- Vista: v_incident_kpi
-- Descripción: KPIs de incidentes por proyecto y período
-- ============================================================================

CREATE OR REPLACE VIEW safety_management.v_incident_kpi AS
SELECT
    tenant_id,
    project_id,
    DATE_TRUNC('month', incident_date) AS month,
    COUNT(*) AS total_incidents,
    COUNT(*) FILTER (WHERE severity = 'near_miss') AS near_miss_count,
    COUNT(*) FILTER (WHERE severity IN ('minor', 'first_aid')) AS minor_count,
    COUNT(*) FILTER (WHERE severity IN ('lost_time', 'serious')) AS serious_count,
    COUNT(*) FILTER (WHERE severity = 'fatal') AS fatal_count,
    SUM(work_days_lost) AS total_days_lost,
    SUM(total_costs) AS total_incident_costs,
    AVG(work_days_lost) FILTER (WHERE work_days_lost > 0) AS avg_days_lost
FROM safety_management.incidents
WHERE is_active = true
GROUP BY tenant_id, project_id, DATE_TRUNC('month', incident_date);

COMMENT ON VIEW safety_management.v_incident_kpi IS
'KPIs de incidentes agrupados por proyecto y mes';

3. v_training_compliance

-- ============================================================================
-- Vista: v_training_compliance
-- Descripción: Cumplimiento de capacitaciones por trabajador
-- ============================================================================

CREATE OR REPLACE VIEW safety_management.v_training_compliance AS
SELECT
    ta.tenant_id,
    ta.worker_id,
    ta.worker_name,
    COUNT(*) AS total_trainings_attended,
    COUNT(*) FILTER (WHERE ta.certificate_issued = true) AS certificates_obtained,
    COUNT(*) FILTER (
        WHERE ta.certificate_expiry_date >= CURRENT_DATE
    ) AS valid_certificates,
    COUNT(*) FILTER (
        WHERE ta.certificate_expiry_date < CURRENT_DATE
    ) AS expired_certificates,
    MAX(t.scheduled_date) AS last_training_date
FROM safety_management.training_attendance ta
JOIN safety_management.trainings t ON ta.training_id = t.id
WHERE ta.attended = true AND ta.is_active = true
GROUP BY ta.tenant_id, ta.worker_id, ta.worker_name;

COMMENT ON VIEW safety_management.v_training_compliance IS
'Resumen de cumplimiento de capacitaciones por trabajador';

---

Migraciones

Orden de Creación

-- 1. Crear schema
CREATE SCHEMA IF NOT EXISTS safety_management;

-- 2. Crear ENUMs
-- (Ver sección de ENUMs arriba)

-- 3. Crear tablas en orden de dependencias
-- 3.1 epp_catalog (sin dependencias externas al schema)
-- 3.2 epp_assignments (depende de epp_catalog)
-- 3.3 safety_inspections (sin dependencias internas)
-- 3.4 incidents (sin dependencias internas)
-- 3.5 incident_investigations (depende de incidents)
-- 3.6 trainings (sin dependencias internas)
-- 3.7 training_attendance (depende de trainings)

-- 4. Crear índices

-- 5. Habilitar RLS y crear policies

-- 6. Crear funciones auxiliares

-- 7. Crear triggers

-- 8. Crear vistas

Ejemplo de Migración Prisma

// prisma/migrations/20251206_create_safety_management_schema.sql

-- Step 1: Create schema
CREATE SCHEMA IF NOT EXISTS safety_management;

-- Step 2: Create ENUMs
CREATE TYPE safety_management.epp_type AS ENUM (
    'helmet', 'safety_glasses', 'goggles', 'face_shield',
    'ear_plugs', 'ear_muffs', 'respirator', 'dust_mask',
    'work_gloves', 'cut_resistant_gloves', 'electrical_gloves',
    'safety_boots', 'rubber_boots', 'safety_vest', 'reflective_vest',
    'harness', 'lifeline', 'rain_suit', 'coveralls', 'welding_jacket', 'other'
);

-- ... (resto de ENUMs)

-- Step 3: Create tables
-- ... (DDL de todas las tablas)

-- Step 4: Enable RLS
-- ... (Políticas RLS)

-- Step 5: Create functions and triggers
-- ... (Funciones y triggers)

---

Seeds de Datos Iniciales

1. Catálogo de EPP Básico

-- ============================================================================
-- Seed: Catálogo de EPP básico
-- ============================================================================

INSERT INTO safety_management.epp_catalog
    (tenant_id, code, name, epp_type, useful_life_days, replacement_alert_days, minimum_stock)
VALUES
    -- Protección de cabeza
    ('{tenant_uuid}', 'EPP-001', 'Casco de Seguridad Clase G', 'helmet', 730, 30, 50),
    ('{tenant_uuid}', 'EPP-002', 'Casco de Seguridad Clase E', 'helmet', 730, 30, 20),

    -- Protección visual
    ('{tenant_uuid}', 'EPP-003', 'Lentes de Seguridad Clara', 'safety_glasses', 180, 15, 100),
    ('{tenant_uuid}', 'EPP-004', 'Lentes de Seguridad Oscura', 'safety_glasses', 180, 15, 50),
    ('{tenant_uuid}', 'EPP-005', 'Goggles Antiempañante', 'goggles', 180, 15, 30),

    -- Protección auditiva
    ('{tenant_uuid}', 'EPP-006', 'Tapones Auditivos Desechables', 'ear_plugs', 30, 3, 500),
    ('{tenant_uuid}', 'EPP-007', 'Orejeras de Cancelación de Ruido', 'ear_muffs', 365, 30, 30),

    -- Protección respiratoria
    ('{tenant_uuid}', 'EPP-008', 'Cubrebocas N95', 'dust_mask', 7, 1, 500),
    ('{tenant_uuid}', 'EPP-009', 'Respirador de Media Cara', 'respirator', 365, 30, 20),

    -- Protección de manos
    ('{tenant_uuid}', 'EPP-010', 'Guantes de Carnaza', 'work_gloves', 60, 10, 200),
    ('{tenant_uuid}', 'EPP-011', 'Guantes Anticorte Nivel 5', 'cut_resistant_gloves', 90, 10, 50),
    ('{tenant_uuid}', 'EPP-012', 'Guantes Dieléctricos Clase 00', 'electrical_gloves', 180, 30, 20),

    -- Protección de pies
    ('{tenant_uuid}', 'EPP-013', 'Botas de Seguridad Dieléctrica', 'safety_boots', 365, 30, 100),
    ('{tenant_uuid}', 'EPP-014', 'Botas de Hule', 'rubber_boots', 365, 30, 50),

    -- Protección corporal
    ('{tenant_uuid}', 'EPP-015', 'Chaleco Reflejante Clase 2', 'reflective_vest', 180, 15, 200),
    ('{tenant_uuid}', 'EPP-016', 'Arnés de Cuerpo Completo', 'harness', 730, 60, 50),
    ('{tenant_uuid}', 'EPP-017', 'Línea de Vida Retráctil 6m', 'lifeline', 365, 30, 30);

---

Validaciones y Constraints

Reglas de Negocio Implementadas

1. EPP:

   • Vida útil debe ser > 0 días
   • Alerta de reemplazo debe ser antes del vencimiento
   • Código único por tenant
   • Cantidad asignada debe ser > 0

2. Inspecciones:

   • Total de items = conformes + no conformes + no aplicables
   • No conformes = suma de hallazgos por nivel de riesgo
   • Score entre 0 y 100
   • Número único por tenant

3. Incidentes:

   • Días perdidos >= 0
   • Costos >= 0
   • Número único por tenant
   • Solo una investigación por incidente

4. Capacitaciones:

   • Hora fin > hora inicio
   • Capacidad máxima >= mínima
   • Score aprobatorio entre 0 y 100
   • Asistentes <= registrados

5. Asistencia:

   • Score entre 0 y 100
   • Satisfacción entre 1 y 5
   • Hora salida >= hora entrada
   • Un registro por trabajador por capacitación

---

Consideraciones de Rendimiento

Particionamiento

Para tablas con alto volumen:

-- Particionar incidents por fecha (recomendado después de 1 año de uso)
CREATE TABLE safety_management.incidents (
    -- ... columnas
) PARTITION BY RANGE (incident_date);

-- Crear particiones trimestrales
CREATE TABLE safety_management.incidents_2025_q1
    PARTITION OF safety_management.incidents
    FOR VALUES FROM ('2025-01-01') TO ('2025-04-01');

CREATE TABLE safety_management.incidents_2025_q2
    PARTITION OF safety_management.incidents
    FOR VALUES FROM ('2025-04-01') TO ('2025-07-01');

Índices Optimizados

• Índices compuestos para consultas frecuentes de KPIs
• Índices parciales para filtrar solo registros activos
• Índices GIN para búsquedas en JSONB y arrays
• Índices trigram para búsqueda de texto

Mantenimiento

-- Reindexación periódica (mensual)
REINDEX SCHEMA safety_management;

-- Análisis de estadísticas (semanal)
ANALYZE safety_management.epp_assignments;
ANALYZE safety_management.safety_inspections;
ANALYZE safety_management.incidents;
ANALYZE safety_management.trainings;

---

Integración con Otros Módulos

Referencias a Tablas Externas

-- MAI-001: Fundamentos
worker_id UUID          -- Referencias a workers.id
project_id UUID         -- Referencias a projects.id

-- Core Users (MGN-001)
created_by UUID         -- Referencias a core_users.users.id
inspector_id UUID       -- Referencias a core_users.users.id
reported_by_id UUID     -- Referencias a core_users.users.id

-- Core Tenants (MGN-003)
tenant_id UUID NOT NULL -- Referencias a core_tenants.tenants.id

Sincronización con Apps Móviles

• Tablas optimizadas para sincronización offline
• Campos gps_latitude, gps_longitude para geolocalización
• Arrays de URLs para fotos capturadas en campo
• JSONB para datos flexibles de checklists

---

Checklist de Validación

• Schema safety_management creado
• 8 ENUMs definidos con valores apropiados
• 7 tablas principales con estructura completa
• Todas las tablas tienen tenant_id
• RLS habilitado en todas las tablas
• 4 políticas RLS por tabla (SELECT, INSERT, UPDATE, DELETE)
• Índices obligatorios en tenant_id
• Índices de performance en columnas de búsqueda
• Triggers para updated_at
• Triggers de negocio (alertas, cálculos)
• Funciones auxiliares documentadas
• Vistas para KPIs y reportes
• Constraints de integridad y reglas de negocio
• Comentarios en tablas y columnas críticas
• Seeds de datos iniciales
• Consideraciones de rendimiento

---

Próximos Pasos

1. Implementación:

   • Crear migración Prisma
   • Ejecutar en ambiente de desarrollo
   • Validar con datos de prueba

2. Integración:

   • Definir DTOs de TypeScript
   • Crear Prisma schema
   • Implementar repositorios

3. Testing:

   • Tests unitarios de funciones
   • Tests de integridad referencial
   • Tests de performance con volumen

4. Documentación:

   • Diagramas ER
   • Diccionario de datos
   • Guía de consultas frecuentes

---

Generado: 2025-12-06 Versión: 1.0.0 Estado: Completo Mantenedor: @database-team