-- =============================================================================
-- CAJA DE AHORROS — PLATAFORMA DE PRÉSTAMOS PERSONALES & MIDDLE OFFICE
-- Esquema Relacional PostgreSQL 16 (Cero Deriva · Catálogo Limestone D1..I1)
-- =============================================================================

CREATE EXTENSION IF NOT EXISTS "pgcrypto";

-- 1. Registro Gobernado de Piezas IA (Contratos Inmutables Limestone)
CREATE TABLE IF NOT EXISTS limestone_component_registry (
  code VARCHAR(8) PRIMARY KEY,
  name VARCHAR(160) NOT NULL,
  family VARCHAR(64) NOT NULL,
  maturity VARCHAR(32) NOT NULL CHECK (maturity IN ('PROBADO', 'EN_PILOTO', 'EN_DISENO')),
  usage_mode VARCHAR(32) NOT NULL CHECK (usage_mode IN ('TAL_CUAL', 'SE_CONFIGURA', 'SE_ADAPTA')),
  version VARCHAR(24) NOT NULL,
  scope_limits JSONB NOT NULL DEFAULT '[]'::jsonb,
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- 2. Conectores F3 hacia Fuentes Oficiales de Panamá y Core Bancario Datapro (e-IBS)
CREATE TABLE IF NOT EXISTS f3_connectors (
  id VARCHAR(48) PRIMARY KEY,
  component_code VARCHAR(8) NOT NULL DEFAULT 'F3',
  name VARCHAR(160) NOT NULL,
  system_target VARCHAR(160) NOT NULL,
  protocol VARCHAR(32) NOT NULL,
  endpoint_url TEXT NOT NULL DEFAULT '',
  auth_type VARCHAR(64) NOT NULL,
  connection_mode VARCHAR(64) NOT NULL DEFAULT 'WAITING_FOR_BANK_CREDENTIALS',
  timeout_ms INTEGER NOT NULL DEFAULT 4000,
  retry_policy VARCHAR(64) NOT NULL DEFAULT 'EXPONENTIAL_3X',
  sample_payload_shape JSONB NOT NULL DEFAULT '{}'::jsonb,
  last_tested_at TIMESTAMPTZ,
  last_test_status VARCHAR(32),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- 3. Catálogo de Reglas Deterministas y Neurosimbólicas (G1)
CREATE TABLE IF NOT EXISTS g1_rules (
  id VARCHAR(32) PRIMARY KEY,
  code VARCHAR(64) UNIQUE NOT NULL,
  category VARCHAR(64) NOT NULL,
  name VARCHAR(200) NOT NULL,
  formula TEXT NOT NULL,
  citation VARCHAR(240) NOT NULL,
  severity VARCHAR(24) NOT NULL CHECK (severity IN ('BLOCKER', 'REVIEW', 'INFO')),
  enabled BOOLEAN NOT NULL DEFAULT TRUE,
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- 4. Expedientes Únicos de Préstamos Personales (Art. 10 Acuerdo SBP 1-2026)
CREATE TABLE IF NOT EXISTS loan_cases (
  id VARCHAR(32) PRIMARY KEY,
  case_code VARCHAR(32) UNIQUE NOT NULL,
  customer_type VARCHAR(16) NOT NULL CHECK (customer_type IN ('EXISTENTE', 'NUEVO')),
  cif_datapro VARCHAR(64) NOT NULL,
  applicant_name VARCHAR(160) NOT NULL,
  cedula VARCHAR(32) NOT NULL,
  segment VARCHAR(48) NOT NULL,
  channel VARCHAR(120) NOT NULL,
  stage VARCHAR(64) NOT NULL,
  target_sla_minutes INTEGER NOT NULL,
  elapsed_minutes INTEGER NOT NULL,
  approved_amount NUMERIC(12, 2) NOT NULL,
  net_disbursement NUMERIC(12, 2) NOT NULL,
  contact_model_o4 VARCHAR(32) NOT NULL DEFAULT 'DIRECTO_MIDDLE_OFFICE',
  payload JSONB NOT NULL,
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- 5. Ingesta y Extracción Documental Multimodal (D1 + Enrutamiento I1)
CREATE TABLE IF NOT EXISTS d1_ingested_documents (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  case_id VARCHAR(32) REFERENCES loan_cases(id) ON DELETE SET NULL,
  filename VARCHAR(240) NOT NULL,
  classified_doc_type VARCHAR(64) NOT NULL,
  sha256 CHAR(64) NOT NULL,
  schema_valid BOOLEAN NOT NULL,
  extracted_fields JSONB NOT NULL,
  flagged_fields JSONB NOT NULL DEFAULT '[]'::jsonb,
  i1_routing_trace JSONB NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- 6. Solicitudes Únicas y Vencimientos (O4 · Directo MO o Vía Oficial de Ventas)
CREATE TABLE IF NOT EXISTS o4_single_requests (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  case_id VARCHAR(32) NOT NULL REFERENCES loan_cases(id) ON DELETE CASCADE,
  contact_model VARCHAR(32) NOT NULL CHECK (contact_model IN ('DIRECTO_MIDDLE_OFFICE', 'VIA_OFICIAL_VENTAS')),
  recipient_label VARCHAR(200) NOT NULL,
  consolidated_items JSONB NOT NULL,
  message_preview TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- 7. Bitácora Inmutable con Cadena de Hash SHA-256 (SBP Acuerdo 1-2026)
CREATE TABLE IF NOT EXISTS audit_hash_chain (
  seq BIGSERIAL PRIMARY KEY,
  case_id VARCHAR(32),
  component_code VARCHAR(16) NOT NULL,
  action_type VARCHAR(64) NOT NULL,
  actor VARCHAR(120) NOT NULL,
  detail TEXT NOT NULL,
  prev_hash CHAR(64) NOT NULL,
  event_hash CHAR(64) NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);