-- FASE 2: ESTRUCTURA DE TABLAS (DDL)
-- RANGEL SALCEDO MELANIE VALERIA. SÁNCHEZ ESCOBAR YULIANA. 

-- 1. TABLAS DE DIMENSIÓN INDEPENDIENTES (SIN LLAVES FORÁNEAS)

CREATE TABLE Entidades_Federativas (
    id_entidad INT PRIMARY KEY,
    nombre_entidad VARCHAR(60) NOT NULL,
    poblacion_total BIGINT NOT NULL, 
    grado_urbanizacion_pct DECIMAL(5,2) NOT NULL CHECK (grado_urbanizacion_pct BETWEEN 0 AND 100),
    pib_participacion_pct DECIMAL(5,2) NOT NULL CHECK (pib_participacion_pct BETWEEN 0 AND 100)
);

CREATE TABLE Cat_Modelos_Dispositivos (
    id_modelo INT PRIMARY KEY,
    nombre_modelo VARCHAR(80) NOT NULL,
    tipo_aparato VARCHAR(40) NOT NULL,
    marca VARCHAR(40) NOT NULL,
    fecha_lanzamiento INT NOT NULL CHECK (fecha_lanzamiento >= 1990),
    vida_util_promedio DECIMAL(4,1) CHECK (vida_util_promedio > 0)
);

CREATE TABLE Empresas_Aseguradoras (
    id_aseguradora INT PRIMARY KEY,
    nombre_aseguradora VARCHAR(80) NOT NULL,
    registro_cnsf VARCHAR(20) UNIQUE
);

CREATE TABLE AUDIT_LOG (
    id_audit BIGINT PRIMARY KEY, -- O usando SERIAL/AUTO_INCREMENT según el gestor
    tabla_afectada VARCHAR(50) NOT NULL,
    accion VARCHAR(20) NOT NULL,
    fecha_modificacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    usuario_db VARCHAR(50) NOT NULL,
    detalle_cambio TEXT
);

-- 2. TABLAS DE DIMENSIÓN DEPENDIENTES

CREATE TABLE Centros_Acopio (
    id_centro INT PRIMARY KEY,
    nombre_centro VARCHAR(100) NOT NULL,
    id_entidad INT NOT NULL,
    direccion VARCHAR(150),
    capacidad_max_toneladas DECIMAL(8,2) NOT NULL CHECK (capacidad_max_toneladas > 0),
    fecha_apertura DATE,
    FOREIGN KEY (id_entidad) REFERENCES Entidades_Federativas(id_entidad) ON DELETE RESTRICT
);

CREATE TABLE Destinos_Finales (
    id_destino INT PRIMARY KEY,
    tipo_destino VARCHAR(30) NOT NULL,
    nombre_empresa VARCHAR(100) NOT NULL,
    id_entidad INT NOT NULL,
    licencia_ambiental VARCHAR(30) UNIQUE,
    FOREIGN KEY (id_entidad) REFERENCES Entidades_Federativas(id_entidad) ON DELETE RESTRICT
);

CREATE TABLE Cat_Componentes_Toxicos (
    id_componente INT PRIMARY KEY,
    id_modelo INT NOT NULL,
    material VARCHAR(40) NOT NULL,
    gramos_por_unidad DECIMAL(8,3) NOT NULL CHECK (gramos_por_unidad > 0),
    tipo_material VARCHAR(10) NOT NULL CHECK (tipo_material IN ('Tóxico', 'Valioso')),
    valor_mercado_kg DECIMAL(10,2) CHECK (valor_mercado_kg >= 0),
    FOREIGN KEY (id_modelo) REFERENCES Cat_Modelos_Dispositivos(id_modelo) ON DELETE RESTRICT
);

-- 3. TABLAS TRANSACCIONALES / HECHOS

CREATE TABLE Dispositivos_Recibidos (
    id_dispositivo BIGINT PRIMARY KEY,
    id_modelo INT NOT NULL,
    id_centro INT NOT NULL,
    fecha_ingreso DATE NOT NULL,
    peso_kg DECIMAL(6,3) NOT NULL CHECK (peso_kg > 0),
    estado_dispositivo VARCHAR(10) NOT NULL CHECK (estado_dispositivo IN ('Funcional', 'Dañado', 'Chatarra')),
    id_destino_final INT, -- Es NULO al inicio. Se actualiza vía TRIGGER al finalizar procesamiento.
    FOREIGN KEY (id_modelo) REFERENCES Cat_Modelos_Dispositivos(id_modelo) ON DELETE RESTRICT,
    FOREIGN KEY (id_centro) REFERENCES Centros_Acopio(id_centro) ON DELETE RESTRICT,
    FOREIGN KEY (id_destino_final) REFERENCES Destinos_Finales(id_destino) ON DELETE RESTRICT
);

CREATE TABLE Rutas_Logisticas (
    id_ruta INT PRIMARY KEY,
    id_centro INT NOT NULL,
    id_destino INT NOT NULL,
    distancia_km DECIMAL(6,2) NOT NULL CHECK (distancia_km > 0),
    costo_transporte_est DECIMAL(10,2) NOT NULL CHECK (costo_transporte_est >= 0),
    FOREIGN KEY (id_centro) REFERENCES Centros_Acopio(id_centro) ON DELETE RESTRICT,
    FOREIGN KEY (id_destino) REFERENCES Destinos_Finales(id_destino) ON DELETE RESTRICT
);

CREATE TABLE Polizas_Riesgo_Ambiental (
    id_poliza BIGINT PRIMARY KEY,
    id_centro INT NOT NULL,
    id_aseguradora INT NOT NULL,
    suma_asegurada DECIMAL(12,2) NOT NULL CHECK (suma_asegurada > 0),
    prima_anual DECIMAL(10,2) NOT NULL CHECK (prima_anual > 0),
    fecha_inicio DATE NOT NULL,
    fecha_vencimiento DATE NOT NULL CHECK (fecha_vencimiento > fecha_inicio),
    deducible DECIMAL(10,2) NOT NULL CHECK (deducible >= 0),
    FOREIGN KEY (id_centro) REFERENCES Centros_Acopio(id_centro) ON DELETE RESTRICT,
    FOREIGN KEY (id_aseguradora) REFERENCES Empresas_Aseguradoras(id_aseguradora) ON DELETE RESTRICT
);

CREATE TABLE Siniestros_Ambientales (
    id_siniestro BIGINT PRIMARY KEY,
    id_poliza BIGINT NOT NULL,
    id_componente INT, -- Nulo si el siniestro fue logístico/general y no ligado a un material específico
    fecha_siniestro DATE NOT NULL,
    tipo_incidente VARCHAR(40) NOT NULL,
    toneladas_afectadas DECIMAL(8,3) CHECK (toneladas_afectadas >= 0),
    costo_estimado DECIMAL(12,2) NOT NULL CHECK (costo_estimado >= 0),
    estatus VARCHAR(20) NOT NULL CHECK (estatus IN ('Abierto', 'En revisión', 'Cerrado')),
    FOREIGN KEY (id_poliza) REFERENCES Polizas_Riesgo_Ambiental(id_poliza) ON DELETE RESTRICT,
    FOREIGN KEY (id_componente) REFERENCES Cat_Componentes_Toxicos(id_componente) ON DELETE RESTRICT
);

CREATE TABLE Beneficiarios_Poliza (
    id_beneficiario INT PRIMARY KEY,
    id_poliza BIGINT NOT NULL,
    nombre_beneficiario VARCHAR(100) NOT NULL,
    tipo_beneficiario VARCHAR(20) NOT NULL CHECK (tipo_beneficiario IN ('Gobierno', 'Comunidad', 'Empresa')),
    porcentaje_indemnizacion DECIMAL(5,2) NOT NULL CHECK (porcentaje_indemnizacion BETWEEN 0 AND 100),
    FOREIGN KEY (id_poliza) REFERENCES Polizas_Riesgo_Ambiental(id_poliza) ON DELETE RESTRICT
);





select * from Rutas_Logisticas;
select  * from Beneficiarios_Poliza ;
select * from Siniestros_Ambientales ;