feldman/init_libros_contables.sql

-- Los Libros Contables - FELDMAN v3.0
-- Tablas para milestones, bloques y validaciones con Merkle Tree

-- MILESTONES
CREATE TABLE IF NOT EXISTS milestones (
    id BIGSERIAL PRIMARY KEY,
    h_milestone VARCHAR(64) NOT NULL UNIQUE,
    h_instancia VARCHAR(64) NOT NULL,
    secuencia BIGINT NOT NULL,
    hash_previo VARCHAR(64),
    hash_contenido VARCHAR(64) NOT NULL,
    alias VARCHAR(200) NOT NULL,
    tipo_item VARCHAR(50) NOT NULL,
    descripcion TEXT,
    datos JSONB DEFAULT '{}' ,
    etiqueta_principal VARCHAR(64),
    proyecto_tag VARCHAR(64),
    id_padre_milestone BIGINT REFERENCES milestones(id),
    id_bloque_asociado BIGINT,
    merkle_leaf_hash VARCHAR(64),
    merkle_batch_id VARCHAR(64),
    blockchain_pending BOOLEAN DEFAULT TRUE,
    blockchain_tx_ref VARCHAR(128),
    notario_batch_id VARCHAR(64),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    created_by VARCHAR(64),
    CONSTRAINT milestone_secuencia_unica UNIQUE (h_instancia, secuencia)
);

-- BLOQUES
CREATE TABLE IF NOT EXISTS bloques (
    id BIGSERIAL PRIMARY KEY,
    h_bloque VARCHAR(64) NOT NULL UNIQUE,
    h_instancia VARCHAR(64) NOT NULL,
    secuencia BIGINT NOT NULL,
    hash_previo VARCHAR(64),
    hash_contenido VARCHAR(64) NOT NULL,
    alias VARCHAR(200) NOT NULL,
    tipo_accion VARCHAR(50) NOT NULL,
    descripcion TEXT,
    datos JSONB DEFAULT '{}' ,
    evidencia_hash VARCHAR(64) NOT NULL,
    evidencia_url VARCHAR(500) NOT NULL,
    evidencia_tipo VARCHAR(50) NOT NULL,
    etiqueta_principal VARCHAR(64),
    proyecto_tag VARCHAR(64),
    id_padre_bloque BIGINT REFERENCES bloques(id),
    id_milestone_asociado BIGINT REFERENCES milestones(id),
    merkle_leaf_hash VARCHAR(64),
    merkle_batch_id VARCHAR(64),
    blockchain_pending BOOLEAN DEFAULT TRUE,
    blockchain_tx_ref VARCHAR(128),
    notario_batch_id VARCHAR(64),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    created_by VARCHAR(64),
    CONSTRAINT bloque_secuencia_unica UNIQUE (h_instancia, secuencia)
);

-- MERKLE BATCHES
CREATE TABLE IF NOT EXISTS merkle_batches (
    id BIGSERIAL PRIMARY KEY,
    batch_id VARCHAR(64) NOT NULL UNIQUE,
    h_instancia VARCHAR(64) NOT NULL,
    tipo VARCHAR(20) NOT NULL,
    merkle_root VARCHAR(64) NOT NULL,
    merkle_tree JSONB NOT NULL,
    total_registros INT NOT NULL,
    blockchain_pending BOOLEAN DEFAULT TRUE,
    blockchain_tx_ref VARCHAR(128),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- COLA DE VALIDACION
CREATE TABLE IF NOT EXISTS feldman_cola (
    id BIGSERIAL PRIMARY KEY,
    h_entrada VARCHAR(64) NOT NULL UNIQUE,
    h_instancia VARCHAR(64) NOT NULL,
    origen VARCHAR(50) NOT NULL,
    h_origen VARCHAR(64),
    tipo_destino VARCHAR(20) NOT NULL,
    datos JSONB NOT NULL,
    estado VARCHAR(20) DEFAULT 'pendiente',
    h_registro VARCHAR(64),
    validacion_ok BOOLEAN,
    reglas_aplicadas JSONB,
    error_mensaje TEXT,
    intentos INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    processed_at TIMESTAMP,
    validated_at TIMESTAMP
);

-- INDICES
CREATE INDEX IF NOT EXISTS idx_milestones_pending ON milestones(blockchain_pending) WHERE blockchain_pending = TRUE;
CREATE INDEX IF NOT EXISTS idx_bloques_pending ON bloques(blockchain_pending) WHERE blockchain_pending = TRUE;
CREATE INDEX IF NOT EXISTS idx_milestones_proyecto ON milestones(proyecto_tag);
CREATE INDEX IF NOT EXISTS idx_bloques_proyecto ON bloques(proyecto_tag);
CREATE INDEX IF NOT EXISTS idx_milestones_batch ON milestones(merkle_batch_id);
CREATE INDEX IF NOT EXISTS idx_bloques_batch ON bloques(merkle_batch_id);
CREATE INDEX IF NOT EXISTS idx_feldman_estado ON feldman_cola(estado);
CREATE INDEX IF NOT EXISTS idx_feldman_instancia ON feldman_cola(h_instancia);

-- FUNCIONES
CREATE OR REPLACE FUNCTION get_ultimo_hash_milestone(p_h_instancia VARCHAR)
RETURNS VARCHAR AS \$\$
DECLARE v_hash VARCHAR;
BEGIN
    SELECT hash_contenido INTO v_hash FROM milestones
    WHERE h_instancia = p_h_instancia ORDER BY secuencia DESC LIMIT 1;
    RETURN COALESCE(v_hash, 'GENESIS');
END;
\$\$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION get_ultimo_hash_bloque(p_h_instancia VARCHAR)
RETURNS VARCHAR AS \$\$
DECLARE v_hash VARCHAR;
BEGIN
    SELECT hash_contenido INTO v_hash FROM bloques
    WHERE h_instancia = p_h_instancia ORDER BY secuencia DESC LIMIT 1;
    RETURN COALESCE(v_hash, 'GENESIS');
END;
\$\$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION get_siguiente_secuencia_milestone(p_h_instancia VARCHAR)
RETURNS BIGINT AS \$\$
BEGIN
    RETURN (SELECT COALESCE(MAX(secuencia), 0) + 1 FROM milestones WHERE h_instancia = p_h_instancia);
END;
\$\$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION get_siguiente_secuencia_bloque(p_h_instancia VARCHAR)
RETURNS BIGINT AS \$\$
BEGIN
    RETURN (SELECT COALESCE(MAX(secuencia), 0) + 1 FROM bloques WHERE h_instancia = p_h_instancia);
END;
\$\$ LANGUAGE plpgsql;