SET lock_timeout = '10s';
SET statement_timeout = '120000';

CREATE SCHEMA IF NOT EXISTS platform;
CREATE SCHEMA IF NOT EXISTS ai;
CREATE SCHEMA IF NOT EXISTS auth;
CREATE SCHEMA IF NOT EXISTS workforce;
CREATE SCHEMA IF NOT EXISTS ops;

CREATE TABLE IF NOT EXISTS ops.canonical_table_map (
    table_name TEXT PRIMARY KEY,
    canonical_schema TEXT NOT NULL,
    notes TEXT
);

CREATE TABLE IF NOT EXISTS platform.follow_ups_operativos (
    id_follow_up BIGSERIAL PRIMARY KEY,
    business_id VARCHAR(100) NOT NULL,
    sucursal INT4[] NOT NULL,
    id_empleado INTEGER NOT NULL,
    tipo_follow_up VARCHAR(20) NOT NULL,
    estatus SMALLINT NOT NULL DEFAULT 1,
    canal_origen VARCHAR(30) NOT NULL DEFAULT 'whatsapp_staff',
    dedupe_key VARCHAR(160) NOT NULL,
    source_message_id VARCHAR(160),
    fecha_mensaje_origen TIMESTAMPTZ NOT NULL,
    mensaje_original TEXT NOT NULL,
    contenido_formateado TEXT NOT NULL,
    rfc3339_fecha_limite TIMESTAMPTZ,
    rfc3339_resuelto_en TIMESTAMPTZ,
    id_login_registra INTEGER NOT NULL,
    id_usuario_registra INTEGER NOT NULL,
    updated_by_login_id INTEGER,
    updated_by_user_id INTEGER,
    metadata JSONB NOT NULL DEFAULT '{}'::jsonb,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    soft_delete BOOLEAN NOT NULL DEFAULT FALSE,
    soft_delete_date TIMESTAMPTZ,
    CONSTRAINT fk_follow_ups_operativos_empleado
        FOREIGN KEY (id_empleado)
        REFERENCES ai.empleado_digital (id_empleado)
        ON UPDATE CASCADE
        ON DELETE RESTRICT,
    CONSTRAINT fk_follow_ups_operativos_login_registra
        FOREIGN KEY (id_login_registra)
        REFERENCES auth.login (id_login)
        ON UPDATE CASCADE
        ON DELETE RESTRICT,
    CONSTRAINT fk_follow_ups_operativos_usuario_registra
        FOREIGN KEY (id_usuario_registra)
        REFERENCES workforce.usuarios (id_usuario)
        ON UPDATE CASCADE
        ON DELETE RESTRICT,
    CONSTRAINT fk_follow_ups_operativos_updated_login
        FOREIGN KEY (updated_by_login_id)
        REFERENCES auth.login (id_login)
        ON UPDATE CASCADE
        ON DELETE SET NULL,
    CONSTRAINT fk_follow_ups_operativos_updated_user
        FOREIGN KEY (updated_by_user_id)
        REFERENCES workforce.usuarios (id_usuario)
        ON UPDATE CASCADE
        ON DELETE SET NULL,
    CONSTRAINT uq_follow_ups_operativos_business_dedupe
        UNIQUE (business_id, dedupe_key),
    CONSTRAINT chk_follow_ups_operativos_business_id_not_empty
        CHECK (length(trim(business_id)) > 0),
    CONSTRAINT chk_follow_ups_operativos_sucursal_singleton
        CHECK (cardinality(sucursal) = 1),
    CONSTRAINT chk_follow_ups_operativos_sucursal_positive
        CHECK (COALESCE(sucursal[1], 0) >= 1),
    CONSTRAINT chk_follow_ups_operativos_id_empleado_positive
        CHECK (id_empleado > 0),
    CONSTRAINT chk_follow_ups_operativos_tipo
        CHECK (tipo_follow_up IN ('pendiente', 'aviso')),
    CONSTRAINT chk_follow_ups_operativos_estatus
        CHECK (estatus IN (1, 2, 3, 4)),
    CONSTRAINT chk_follow_ups_operativos_canal
        CHECK (canal_origen IN ('whatsapp_staff', 'web_chat_staff')),
    CONSTRAINT chk_follow_ups_operativos_dedupe_not_empty
        CHECK (length(trim(dedupe_key)) > 0),
    CONSTRAINT chk_follow_ups_operativos_source_message_not_empty
        CHECK (source_message_id IS NULL OR length(trim(source_message_id)) > 0),
    CONSTRAINT chk_follow_ups_operativos_mensaje_original_not_empty
        CHECK (length(trim(mensaje_original)) > 0),
    CONSTRAINT chk_follow_ups_operativos_contenido_formateado_not_empty
        CHECK (length(trim(contenido_formateado)) > 0),
    CONSTRAINT chk_follow_ups_operativos_fecha_limite_tipo
        CHECK (
            (tipo_follow_up = 'pendiente' AND rfc3339_fecha_limite IS NOT NULL)
            OR
            (tipo_follow_up = 'aviso' AND rfc3339_fecha_limite IS NULL)
        ),
    CONSTRAINT chk_follow_ups_operativos_login_registra_positive
        CHECK (id_login_registra > 0),
    CONSTRAINT chk_follow_ups_operativos_usuario_registra_positive
        CHECK (id_usuario_registra > 0),
    CONSTRAINT chk_follow_ups_operativos_updated_login_positive
        CHECK (updated_by_login_id IS NULL OR updated_by_login_id > 0),
    CONSTRAINT chk_follow_ups_operativos_updated_user_positive
        CHECK (updated_by_user_id IS NULL OR updated_by_user_id > 0),
    CONSTRAINT chk_follow_ups_operativos_metadata_object
        CHECK (jsonb_typeof(metadata) = 'object')
);

CREATE INDEX IF NOT EXISTS follow_ups_operativos_scope_created_idx
    ON platform.follow_ups_operativos (
        business_id,
        id_empleado,
        created_at DESC
    )
    WHERE soft_delete IS NOT TRUE;

CREATE INDEX IF NOT EXISTS follow_ups_operativos_scope_tipo_estatus_due_idx
    ON platform.follow_ups_operativos (
        business_id,
        id_empleado,
        tipo_follow_up,
        estatus,
        rfc3339_fecha_limite,
        created_at DESC
    )
    WHERE soft_delete IS NOT TRUE;

CREATE INDEX IF NOT EXISTS follow_ups_operativos_scope_tipo_created_idx
    ON platform.follow_ups_operativos (
        business_id,
        id_empleado,
        tipo_follow_up,
        created_at DESC
    )
    WHERE soft_delete IS NOT TRUE;

CREATE INDEX IF NOT EXISTS follow_ups_operativos_sucursal_idx
    ON platform.follow_ups_operativos
    USING gin (sucursal);

CREATE INDEX IF NOT EXISTS follow_ups_operativos_source_message_idx
    ON platform.follow_ups_operativos (
        business_id,
        source_message_id
    )
    WHERE soft_delete IS NOT TRUE
      AND source_message_id IS NOT NULL;

COMMENT ON TABLE platform.follow_ups_operativos IS 'Follow ups operativos internos del empleado digital. Unifica pendientes y avisos registrados desde mensajes del personal del negocio.';
COMMENT ON COLUMN platform.follow_ups_operativos.id_follow_up IS 'Identificador tecnico del follow up operativo.';
COMMENT ON COLUMN platform.follow_ups_operativos.business_id IS 'Tenant canonico del negocio propietario del follow up.';
COMMENT ON COLUMN platform.follow_ups_operativos.sucursal IS 'Scope de sucursal del follow up. El arreglo siempre debe contener exactamente una sucursal canonica.';
COMMENT ON COLUMN platform.follow_ups_operativos.id_empleado IS 'Empleado digital receptor del mensaje y responsable contextual del follow up.';
COMMENT ON COLUMN platform.follow_ups_operativos.tipo_follow_up IS 'Tipo canonico del registro: pendiente o aviso.';
COMMENT ON COLUMN platform.follow_ups_operativos.estatus IS 'Estado visible del follow up: 1 nuevo, 2 pendiente, 3 completado, 4 cancelado.';
COMMENT ON COLUMN platform.follow_ups_operativos.canal_origen IS 'Canal interno que origino el follow up: whatsapp_staff o web_chat_staff.';
COMMENT ON COLUMN platform.follow_ups_operativos.dedupe_key IS 'Llave deterministica de idempotencia para reintentos del finalize same-origin.';
COMMENT ON COLUMN platform.follow_ups_operativos.source_message_id IS 'Identificador externo o sintetico del mensaje fuente cuando exista.';
COMMENT ON COLUMN platform.follow_ups_operativos.fecha_mensaje_origen IS 'Timestamp RFC3339 del mensaje original que origino el follow up.';
COMMENT ON COLUMN platform.follow_ups_operativos.mensaje_original IS 'Texto original emitido por el personal del negocio sin reinterpretacion semantica.';
COMMENT ON COLUMN platform.follow_ups_operativos.contenido_formateado IS 'Version legible del mensaje original, permitiendo bullets o numeracion sin alterar el significado.';
COMMENT ON COLUMN platform.follow_ups_operativos.rfc3339_fecha_limite IS 'Fecha limite RFC3339 obligatoria para pendientes y nula para avisos.';
COMMENT ON COLUMN platform.follow_ups_operativos.rfc3339_resuelto_en IS 'Timestamp RFC3339 de cierre efectivo cuando el follow up se completa o cancela.';
COMMENT ON COLUMN platform.follow_ups_operativos.id_login_registra IS 'Login humano reconciliado del miembro del personal que origino el follow up.';
COMMENT ON COLUMN platform.follow_ups_operativos.id_usuario_registra IS 'Usuario workforce reconciliado del miembro del personal que origino el follow up.';
COMMENT ON COLUMN platform.follow_ups_operativos.updated_by_login_id IS 'Ultimo login humano que cambio el estatus del follow up desde OpHub.';
COMMENT ON COLUMN platform.follow_ups_operativos.updated_by_user_id IS 'Ultimo usuario workforce que cambio el estatus del follow up desde OpHub.';
COMMENT ON COLUMN platform.follow_ups_operativos.metadata IS 'Metadata JSONB extensible para router, parse temporal, conversation ids y trazabilidad tecnica.';
COMMENT ON COLUMN platform.follow_ups_operativos.created_at IS 'Timestamp oficial de creacion del follow up operativo.';
COMMENT ON COLUMN platform.follow_ups_operativos.updated_at IS 'Timestamp de ultima actualizacion del follow up operativo.';
COMMENT ON COLUMN platform.follow_ups_operativos.soft_delete IS 'Bandera de borrado logico del follow up.';
COMMENT ON COLUMN platform.follow_ups_operativos.soft_delete_date IS 'Timestamp de borrado logico del follow up.';

INSERT INTO ops.canonical_table_map (table_name, canonical_schema, notes)
VALUES (
    'follow_ups_operativos',
    'platform',
    'Fuente canonica durable para pendientes y avisos operativos internos del empleado digital'
)
ON CONFLICT (table_name) DO UPDATE
SET canonical_schema = EXCLUDED.canonical_schema,
    notes = EXCLUDED.notes;

ANALYZE platform.follow_ups_operativos;
