-- ============================================
-- 1. TIPOS ENUM (Personalizados para PostgreSQL)
-- ============================================

CREATE TYPE tipo_documento AS ENUM ('CC', 'TI', 'CE', 'TE', 'PPT', 'Pasaporte');
CREATE TYPE prioridad_enum AS ENUM ('urgente', 'alta', 'media', 'baja');
CREATE TYPE estado_usuario AS ENUM ('activo', 'inactivo');
CREATE TYPE rol_usuario AS ENUM ('donante', 'asistente', 'administrador');
CREATE TYPE estado_evento AS ENUM ('borrador', 'publicado', 'en_curso', 'finalizado', 'cancelado');
CREATE TYPE estado_donacion AS ENUM ('pendiente', 'aprobada', 'cancelada', 'completada', 'rechazada');
CREATE TYPE estado_solicitud AS ENUM ('aprobada', 'pendiente', 'rechazada', 'cancelada', 'completada');
CREATE TYPE estado_visita AS ENUM ('pendiente', 'aprobada', 'rechazada', 'realizada', 'cancelada');
CREATE TYPE campo_correccion AS ENUM ('nombre', 'apellido', 'tipoDocumento', 'numDocumento', 'fechaNacimiento');
CREATE TYPE estado_correccion AS ENUM ('pendiente', 'aprobada', 'rechazada');

-- ============================================
-- 2. TABLA USUARIO
-- ============================================

CREATE TABLE usuario (
    idUsuario             BIGSERIAL PRIMARY KEY,
    nombre                VARCHAR(100) NOT NULL,
    apellido              VARCHAR(100) NOT NULL DEFAULT '',
    tipoDocumento         tipo_documento NOT NULL,
    numDocumento          VARCHAR(20) UNIQUE NOT NULL,
    fechaNacimiento       DATE NOT NULL,
    direccion             VARCHAR(255) NOT NULL,
    email                 VARCHAR(150) UNIQUE NOT NULL,
    contrasena            VARCHAR(255) NOT NULL,
    telefono              VARCHAR(15) NOT NULL,
    necesidad             VARCHAR(255) NULL,
    prioridad             prioridad_enum NULL,
    observacion_visita    VARCHAR(500) NULL,
    estado                estado_usuario DEFAULT 'activo',
    rol                   rol_usuario DEFAULT 'donante',
    reset_token           VARCHAR(64) NULL,
    reset_token_expira    TIMESTAMP NULL,

    CONSTRAINT chk_solo_donante_necesidad CHECK (
        (rol = 'donante') OR (rol <> 'donante' AND necesidad IS NULL AND prioridad IS NULL)
    )
);

CREATE OR REPLACE FUNCTION fn_validar_edad_minima()
RETURNS TRIGGER AS $$
BEGIN
    IF EXTRACT(YEAR FROM AGE(CURRENT_DATE, NEW.fechaNacimiento)) < 14 THEN
        RAISE EXCEPTION 'Error: el usuario debe tener al menos 14 años.';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER tg_validar_edad_minima_insert_update
BEFORE INSERT OR UPDATE ON usuario
FOR EACH ROW
EXECUTE FUNCTION fn_validar_edad_minima();

-- ============================================
-- 3. TABLA CATEGORIA
-- ============================================

CREATE TABLE categoria (
    idCategoria SERIAL PRIMARY KEY,
    nombre      VARCHAR(100) NOT NULL,
    idUsuario   BIGINT NULL,
    FOREIGN KEY (idUsuario) REFERENCES usuario(idUsuario) ON DELETE SET NULL
);

-- ============================================
-- 4. TABLA EVENTO
-- ============================================

CREATE TABLE evento (
    idEvento          BIGSERIAL PRIMARY KEY,
    Nombre            VARCHAR(150) NOT NULL,
    contenido         TEXT NULL,
    imagen            BYTEA NULL,
    fechaPublicacion  DATE NULL,
    fechaInicio       DATE NULL,
    fechaFin          DATE NULL,
    lugar             VARCHAR(255) NULL DEFAULT 'Transversal 73 H Bis #75B 46 Sur, Barrio Sierra Morena V Sector',
    estado            estado_evento DEFAULT 'borrador' NOT NULL
);

-- ============================================
-- 5. TABLA DONACION
-- ============================================

CREATE TABLE donacion (
    idDonacion    BIGSERIAL PRIMARY KEY,
    descripcion   VARCHAR(200) NOT NULL,
    imagen        BYTEA NULL,
    estado        estado_donacion DEFAULT 'pendiente',
    fechaCreacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    observacion   VARCHAR(200) NULL,
    stock         INT NOT NULL DEFAULT 0,
    idCategoria   INT NULL,
    FOREIGN KEY (idCategoria) REFERENCES categoria(idCategoria) ON DELETE SET NULL
);

-- ============================================
-- 6. TABLA SOLICITUD
-- ============================================

CREATE TABLE solicitud (
    idSolicitud   BIGSERIAL PRIMARY KEY,
    descripcion   VARCHAR(300) NOT NULL,
    estado        estado_solicitud DEFAULT 'pendiente',
    fechaCreacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    observacion   VARCHAR(300) NULL,
    idSolicitante BIGINT NULL,
    idCategoria   INT NULL,
    idGestor      BIGINT NULL,
    FOREIGN KEY (idCategoria)   REFERENCES categoria(idCategoria) ON DELETE SET NULL,
    FOREIGN KEY (idGestor)      REFERENCES usuario(idUsuario) ON DELETE SET NULL,
    FOREIGN KEY (idSolicitante) REFERENCES usuario(idUsuario) ON DELETE SET NULL
);

-- ============================================
-- 7. TABLA DONACIONUSUARIO
-- ============================================

CREATE TABLE DonacionUsuario (
    idDonacionUsuario BIGSERIAL PRIMARY KEY,
    FechaCreacion     DATE,
    idDonante         BIGINT NULL,
    idDonacion        BIGINT NULL,
    FOREIGN KEY (idDonante)  REFERENCES usuario(idUsuario) ON DELETE SET NULL,
    FOREIGN KEY (idDonacion) REFERENCES donacion(idDonacion) ON DELETE SET NULL
);

-- ============================================
-- 8. TABLA VISITA_DOMICILIARIA
-- ============================================

CREATE TABLE visita_domiciliaria (
    idVisita         BIGSERIAL PRIMARY KEY,
    idUsuario        BIGINT NOT NULL,
    idGestor         BIGINT NULL,
    direccion        VARCHAR(255) NOT NULL,
    motivo           VARCHAR(300) NOT NULL,
    fechaPreferida   DATE NULL,
    estado           estado_visita DEFAULT 'pendiente',
    observacion      VARCHAR(300) NULL,
    fechaSolicitud   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    fechaResolucion  TIMESTAMP NULL,
    FOREIGN KEY (idUsuario) REFERENCES usuario(idUsuario) ON DELETE CASCADE,
    FOREIGN KEY (idGestor)  REFERENCES usuario(idUsuario) ON DELETE SET NULL
);

CREATE INDEX idx_visita_usuario_estado ON visita_domiciliaria (idUsuario, estado);

-- ============================================
-- 9. TABLA VISITA_OBSERVACION
-- ============================================

CREATE TABLE visita_observacion (
    idObservacion BIGSERIAL PRIMARY KEY,
    idVisita      BIGINT NOT NULL,
    idUsuario     BIGINT NULL,
    texto         VARCHAR(500) NOT NULL,
    fecha         TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (idVisita)  REFERENCES visita_domiciliaria(idVisita) ON DELETE CASCADE,
    FOREIGN KEY (idUsuario) REFERENCES usuario(idUsuario) ON DELETE SET NULL
);

INSERT INTO visita_observacion (idVisita, idUsuario, texto, fecha)
SELECT idVisita, idGestor, observacion, COALESCE(fechaResolucion, fechaSolicitud)
FROM visita_domiciliaria
WHERE observacion IS NOT NULL AND observacion <> '';

-- ============================================
-- 10. TABLA CORRECCION_DATOS
-- ============================================

CREATE TABLE correccion_datos (
    idCorreccion            BIGSERIAL PRIMARY KEY,
    idUsuario               BIGINT NOT NULL,
    idSolicitante           BIGINT NULL,
    campo                   campo_correccion NOT NULL,
    valorAnterior           VARCHAR(255) NOT NULL,
    valorNuevo              VARCHAR(255) NOT NULL,
    justificacion           VARCHAR(300) NOT NULL,
    estado                  estado_correccion DEFAULT 'pendiente',
    idAprobador             BIGINT NULL,
    fechaSolicitud          TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    fechaResolucion         TIMESTAMP NULL,
    observacionResolucion   VARCHAR(300) NULL,
    soporteRuta             VARCHAR(255) NULL,
    soporteMime             VARCHAR(100) NULL,
    soporteHash             CHAR(64) NULL,
    consentimiento          SMALLINT NOT NULL DEFAULT 0,
    FOREIGN KEY (idUsuario)     REFERENCES usuario(idUsuario) ON DELETE CASCADE,
    FOREIGN KEY (idSolicitante) REFERENCES usuario(idUsuario) ON DELETE SET NULL,
    FOREIGN KEY (idAprobador)   REFERENCES usuario(idUsuario) ON DELETE SET NULL
);

-- ============================================
-- 11. INSERCIÓN USUARIOS BASE
-- ============================================

INSERT INTO usuario
(nombre, apellido, tipoDocumento, numDocumento, fechaNacimiento, direccion, email, contrasena, telefono, necesidad, prioridad, estado, rol)
VALUES
('Luisa V', '', 'CC', '1016834260', '2006-12-20', 'Ciudad Bolivar', 'luisav0801@gmail.com', '$2y$10$q2FxURJl/.N9CKorMy6wMu8ld3NN7AZNlA7JkS1NL11LBbTwSMnmC', '3222074569', NULL, NULL, 'activo', 'administrador'),
('DuvanT', '', 'TI', '1023381752', '2008-10-16', 'Lisboa', 'duvantimote16@gmail.com', '$2y$10$kRS/B6yhX/rOiXB4D81sCeYMBG7nGB8u9TCotUWX32k/VO3mA49qq', '3213669365', NULL, NULL, 'activo', 'administrador'),
('Pepito Perez', '', 'CC', '1234567890', '1995-05-10', 'Calle Falsa 123', 'pepito@correo.com', 'hash', '300123', 'Busco apoyo alimentario', NULL, 'activo', 'donante'),
('Asistente', '', 'CC', '12345677655', '1995-05-11', 'Calle Falsa 1231', 'asistente@correo.com', 'hash', '300122233', NULL, NULL, 'activo', 'asistente');

-- ============================================
-- 12. INSERCIÓN CATEGORÍAS
-- ============================================

INSERT INTO categoria (nombre, idUsuario)
VALUES
('Alimentos No Perecederos', 1), ('Granos y Legumbres', 2), ('Aceites y Grasas', 1),
('Enlatados Varios', 2), ('Leche en Polvo o Larga Vida', 1), ('Suplementos Nutricionales Infantiles', 2),
('Frutas y Verduras Frescas', 1),
('Ropa para Bebés', 2), ('Ropa para Niños y Niñas', 1), ('Ropa para Adultos', 2),
('Ropa Térmica y Chaquetas', 1), ('Calzado Deportivo', 2), ('Calzado Escolar', 1), ('Calzado para Adultos', 2),
('Muebles de Alcoba', 1), ('Muebles de Sala y Comedor', 2), ('Colchones y Colchonetas', 1),
('Cobijas y Mantas', 2), ('Sábanas y Fundas', 1), ('Toallas y Paños', 2),
('Utensilios de Cocina, Ollas y Vajilla', 1), ('Electrodomésticos Pequeños', 2),
('Cuadernos y Papelería', 1), ('Libros de Texto Escolares', 2), ('Literatura y Cuentos', 1),
('Maletas y Morrales', 2), ('Diccionarios y Enciclopedias', 1), ('Instrumentos Musicales', 2),
('Kits de Arte y Pintura', 1),
('Computadores de Escritorio', 2), ('Portátiles y Laptops', 1), ('Tablets y Tablets Educativas', 2),
('Teléfonos Móviles', 1), ('Periféricos: Mouse, Teclado, Cámaras', 2),
('Pañales Desechables', 1), ('Productos de Aseo Personal', 2), ('Higiene Femenina', 1),
('Jabones y Detergentes', 2), ('Elementos de Bioseguridad: Tapabocas', 1),
('Juguetería Didáctica', 2), ('Artículos Deportivos: Balones y Lazos', 1), ('Juegos de Mesa', 2);

-- ============================================
-- 13. INSERCIÓN EVENTOS
-- ============================================

INSERT INTO evento (Nombre, contenido, fechaPublicacion, fechaInicio, fechaFin, lugar, estado)
VALUES
('Entrega de Mercados', 'Jornada de entrega de mercados a familias beneficiarias', '2026-01-20', '2026-02-03', '2026-02-03', 'Transversal 73 H Bis #75B 46 Sur, Barrio Sierra Morena V Sector', 'publicado'),
('Entrega de Celulares', 'Jornada de entrega de dispositivos móviles donados', NULL, '2026-03-01', '2026-03-02', 'Transversal 73 H Bis #75B 46 Sur, Barrio Sierra Morena V Sector', 'borrador');

-- ============================================
-- 14. INSERCIÓN DONACIONES
-- ============================================

INSERT INTO donacion (descripcion, estado, fechaCreacion, observacion, stock, idCategoria)
VALUES
('Un kilo de azúcar marca blanca', 'pendiente', '2025-06-20', NULL, 1, 1),
('Un celular Motorola azul en buen estado', 'pendiente', '2025-06-20', NULL, 1, 33);

INSERT INTO DonacionUsuario (FechaCreacion, idDonante, idDonacion)
VALUES ('2026-01-01', 3, 1), ('2026-02-02', 3, 2);

-- ============================================
-- 15. INSERCIÓN SOLICITUDES
-- ============================================

INSERT INTO solicitud (descripcion, estado, observacion, idSolicitante, idCategoria, idGestor)
VALUES
('Solicito comida', 'aprobada', 'Ninguna', 3, 1, 1),
('Solicito un celular', 'aprobada', 'Ninguna', 3, 33, 2);

-- ============================================
-- 16. INSERCIÓN VISITA DOMICILIARIA
-- ============================================

INSERT INTO visita_domiciliaria (idUsuario, direccion, motivo, fechaPreferida, estado)
VALUES (3, 'Calle Falsa 123', 'Verificar necesidad de alimentos reportada', '2026-02-10', 'pendiente');

-- ============================================
-- 17. FUNCIONES Y PROCEDIMIENTOS
-- ============================================

CREATE OR REPLACE FUNCTION ver_donaciones()
RETURNS TABLE (descripcion VARCHAR, estado estado_donacion, stock INT, categoria VARCHAR) AS $$
BEGIN
    RETURN QUERY
    SELECT d.descripcion, d.estado, d.stock, c.nombre
    FROM donacion d
    INNER JOIN categoria c ON d.idCategoria = c.idCategoria;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION solicitudes_por_usuario(id_input BIGINT)
RETURNS TABLE (descripcion VARCHAR, estado estado_solicitud) AS $$
BEGIN
    RETURN QUERY
    SELECT s.descripcion, s.estado
    FROM solicitud s
    WHERE s.idGestor = id_input;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION contar_solicitudes()
RETURNS TABLE (categoria VARCHAR, total BIGINT) AS $$
BEGIN
    RETURN QUERY
    SELECT c.nombre, COUNT(s.idSolicitud)
    FROM categoria c
    LEFT JOIN solicitud s ON c.idCategoria = s.idCategoria
    GROUP BY c.nombre;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE PROCEDURE actualizar_estado_donacion(
    idDon INT,
    nuevoEstado estado_donacion,
    obs VARCHAR
) AS $$
BEGIN
    UPDATE donacion
    SET estado = nuevoEstado,
        observacion = obs
    WHERE idDonacion = idDon;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION eventos_resumen()
RETURNS TABLE (idEvento BIGINT, Nombre VARCHAR, fechaInicio DATE, fechaFin DATE, lugar VARCHAR, estado estado_evento) AS $$
BEGIN
    RETURN QUERY
    SELECT e.idEvento, e.Nombre, e.fechaInicio, e.fechaFin, e.lugar, e.estado
    FROM evento e
    ORDER BY e.fechaInicio DESC;
END;
$$ LANGUAGE plpgsql;

-- ============================================
-- 18. TRIGGERS DE VALIDACIÓN
-- ============================================

CREATE OR REPLACE FUNCTION fn_validar_rol_donante_donacion()
RETURNS TRIGGER AS $$
DECLARE
    v_rol rol_usuario;
BEGIN
    SELECT rol INTO v_rol FROM usuario WHERE idUsuario = NEW.idDonante;
    IF v_rol <> 'donante' THEN
        RAISE EXCEPTION 'Error: Solo los usuarios con rol DONANTE pueden realizar donaciones.';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER tg_validar_rol_donante
BEFORE INSERT ON DonacionUsuario
FOR EACH ROW
EXECUTE FUNCTION fn_validar_rol_donante_donacion();

CREATE OR REPLACE FUNCTION fn_validar_rol_solicitante()
RETURNS TRIGGER AS $$
DECLARE
    v_rol rol_usuario;
BEGIN
    SELECT rol INTO v_rol FROM usuario WHERE idUsuario = NEW.idSolicitante;
    IF v_rol <> 'donante' THEN
        RAISE EXCEPTION 'Error: Solo los usuarios con rol DONANTE pueden crear solicitudes.';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER tg_validar_rol_solicitante
BEFORE INSERT ON solicitud
FOR EACH ROW
EXECUTE FUNCTION fn_validar_rol_solicitante();