
CREATE SCHEMA IF NOT EXISTS platform;
CREATE SCHEMA IF NOT EXISTS ops;

CREATE TABLE IF NOT EXISTS platform.follow_ups_operativos_responsables (
    id_follow_up_responsable BIGSERIAL PRIMARY KEY
);

ALTER TABLE platform.follow_ups_operativos_responsables
    ADD COLUMN IF NOT EXISTS id_follow_up BIGINT;

ALTER TABLE platform.follow_ups_operativos_responsables
    ADD COLUMN IF NOT EXISTS id_login INTEGER;

ALTER TABLE platform.follow_ups_operativos_responsables
    ADD COLUMN IF NOT EXISTS id_usuario INTEGER;

ALTER TABLE platform.follow_ups_operativos_responsables
    ADD COLUMN IF NOT EXISTS is_creator BOOLEAN;

ALTER TABLE platform.follow_ups_operativos_responsables
    ADD COLUMN IF NOT EXISTS assignment_origin VARCHAR(40);

ALTER TABLE platform.follow_ups_operativos_responsables
    ADD COLUMN IF NOT EXISTS created_by_login_id INTEGER;

ALTER TABLE platform.follow_ups_operativos_responsables
    ADD COLUMN IF NOT EXISTS created_by_user_id INTEGER;

ALTER TABLE platform.follow_ups_operativos_responsables
    ADD COLUMN IF NOT EXISTS created_at TIMESTAMPTZ;

ALTER TABLE platform.follow_ups_operativos_responsables
    ADD COLUMN IF NOT EXISTS updated_at TIMESTAMPTZ;

ALTER TABLE platform.follow_ups_operativos_responsables
    ALTER COLUMN is_creator SET DEFAULT FALSE;

ALTER TABLE platform.follow_ups_operativos_responsables
    ALTER COLUMN created_at SET DEFAULT NOW();

ALTER TABLE platform.follow_ups_operativos_responsables
    ALTER COLUMN updated_at SET DEFAULT NOW();

UPDATE platform.follow_ups_operativos_responsables
SET is_creator = FALSE
WHERE is_creator IS NULL;

UPDATE platform.follow_ups_operativos_responsables
SET assignment_origin = 'backfill_legacy'
WHERE assignment_origin IS NULL
   OR length(trim(assignment_origin)) = 0;

UPDATE platform.follow_ups_operativos_responsables
SET created_at = NOW()
WHERE created_at IS NULL;

UPDATE platform.follow_ups_operativos_responsables
SET updated_at = COALESCE(updated_at, created_at, NOW())
WHERE updated_at IS NULL;

ALTER TABLE platform.follow_ups_operativos_responsables
    ALTER COLUMN id_follow_up SET NOT NULL;

ALTER TABLE platform.follow_ups_operativos_responsables
    ALTER COLUMN id_login SET NOT NULL;

ALTER TABLE platform.follow_ups_operativos_responsables
    ALTER COLUMN id_usuario SET NOT NULL;

ALTER TABLE platform.follow_ups_operativos_responsables
    ALTER COLUMN is_creator SET NOT NULL;

ALTER TABLE platform.follow_ups_operativos_responsables
    ALTER COLUMN assignment_origin SET NOT NULL;

ALTER TABLE platform.follow_ups_operativos_responsables
    ALTER COLUMN created_at SET NOT NULL;

ALTER TABLE platform.follow_ups_operativos_responsables
    ALTER COLUMN updated_at SET NOT NULL;

ALTER TABLE platform.follow_ups_operativos_responsables
    DROP CONSTRAINT IF EXISTS uq_follow_ups_operativos_responsables_follow_up_user;

ALTER TABLE platform.follow_ups_operativos_responsables
    ADD CONSTRAINT uq_follow_ups_operativos_responsables_follow_up_user
    UNIQUE (id_follow_up, id_usuario);

ALTER TABLE platform.follow_ups_operativos_responsables
    DROP CONSTRAINT IF EXISTS chk_follow_ups_operativos_responsables_follow_up_positive;

ALTER TABLE platform.follow_ups_operativos_responsables
    ADD CONSTRAINT chk_follow_ups_operativos_responsables_follow_up_positive
    CHECK (id_follow_up > 0);

ALTER TABLE platform.follow_ups_operativos_responsables
    DROP CONSTRAINT IF EXISTS chk_follow_ups_operativos_responsables_login_positive;

ALTER TABLE platform.follow_ups_operativos_responsables
    ADD CONSTRAINT chk_follow_ups_operativos_responsables_login_positive
    CHECK (id_login > 0);

ALTER TABLE platform.follow_ups_operativos_responsables
    DROP CONSTRAINT IF EXISTS chk_follow_ups_operativos_responsables_user_positive;

ALTER TABLE platform.follow_ups_operativos_responsables
    ADD CONSTRAINT chk_follow_ups_operativos_responsables_user_positive
    CHECK (id_usuario > 0);

ALTER TABLE platform.follow_ups_operativos_responsables
    DROP CONSTRAINT IF EXISTS chk_follow_ups_operativos_responsables_assignment_origin_not_empty;

ALTER TABLE platform.follow_ups_operativos_responsables
    ADD CONSTRAINT chk_follow_ups_operativos_responsables_assignment_origin_not_empty
    CHECK (length(trim(assignment_origin)) > 0);

ALTER TABLE platform.follow_ups_operativos_responsables
    DROP CONSTRAINT IF EXISTS chk_follow_ups_operativos_responsables_created_by_login_positive;

ALTER TABLE platform.follow_ups_operativos_responsables
    ADD CONSTRAINT chk_follow_ups_operativos_responsables_created_by_login_positive
    CHECK (created_by_login_id IS NULL OR created_by_login_id > 0);

ALTER TABLE platform.follow_ups_operativos_responsables
    DROP CONSTRAINT IF EXISTS chk_follow_ups_operativos_responsables_created_by_user_positive;

ALTER TABLE platform.follow_ups_operativos_responsables
    ADD CONSTRAINT chk_follow_ups_operativos_responsables_created_by_user_positive
    CHECK (created_by_user_id IS NULL OR created_by_user_id > 0);

ALTER TABLE platform.follow_ups_operativos_responsables
    DROP CONSTRAINT IF EXISTS fk_follow_ups_operativos_responsables_follow_up;

ALTER TABLE platform.follow_ups_operativos_responsables
    ADD CONSTRAINT fk_follow_ups_operativos_responsables_follow_up
    FOREIGN KEY (id_follow_up)
    REFERENCES platform.follow_ups_operativos (id_follow_up)
    ON UPDATE CASCADE
    ON DELETE CASCADE;

ALTER TABLE platform.follow_ups_operativos_responsables
    DROP CONSTRAINT IF EXISTS fk_follow_ups_operativos_responsables_login;

ALTER TABLE platform.follow_ups_operativos_responsables
    ADD CONSTRAINT fk_follow_ups_operativos_responsables_login
    FOREIGN KEY (id_login)
    REFERENCES auth.login (id_login)
    ON UPDATE CASCADE
    ON DELETE RESTRICT;

ALTER TABLE platform.follow_ups_operativos_responsables
    DROP CONSTRAINT IF EXISTS fk_follow_ups_operativos_responsables_user;

ALTER TABLE platform.follow_ups_operativos_responsables
    ADD CONSTRAINT fk_follow_ups_operativos_responsables_user
    FOREIGN KEY (id_usuario)
    REFERENCES workforce.usuarios (id_usuario)
    ON UPDATE CASCADE
    ON DELETE RESTRICT;

ALTER TABLE platform.follow_ups_operativos_responsables
    DROP CONSTRAINT IF EXISTS fk_follow_ups_operativos_responsables_created_by_login;

ALTER TABLE platform.follow_ups_operativos_responsables
    ADD CONSTRAINT fk_follow_ups_operativos_responsables_created_by_login
    FOREIGN KEY (created_by_login_id)
    REFERENCES auth.login (id_login)
    ON UPDATE CASCADE
    ON DELETE SET NULL;

ALTER TABLE platform.follow_ups_operativos_responsables
    DROP CONSTRAINT IF EXISTS fk_follow_ups_operativos_responsables_created_by_user;

ALTER TABLE platform.follow_ups_operativos_responsables
    ADD CONSTRAINT fk_follow_ups_operativos_responsables_created_by_user
    FOREIGN KEY (created_by_user_id)
    REFERENCES workforce.usuarios (id_usuario)
    ON UPDATE CASCADE
    ON DELETE SET NULL;

CREATE INDEX IF NOT EXISTS follow_ups_operativos_responsables_user_follow_up_idx
    ON platform.follow_ups_operativos_responsables (
        id_usuario,
        id_follow_up
    );

CREATE INDEX IF NOT EXISTS follow_ups_operativos_responsables_follow_up_creator_idx
    ON platform.follow_ups_operativos_responsables (
        id_follow_up,
        is_creator
    );

CREATE UNIQUE INDEX IF NOT EXISTS follow_ups_operativos_responsables_creator_uq
    ON platform.follow_ups_operativos_responsables (id_follow_up)
    WHERE is_creator IS TRUE;

COMMENT ON TABLE platform.follow_ups_operativos_responsables IS 'Fuente canonica plural de responsables reales por follow up operativo. Incluye siempre al creador y permite responsables adicionales del mismo scope.';
COMMENT ON COLUMN platform.follow_ups_operativos_responsables.id_follow_up_responsable IS 'Identificador tecnico de la relacion follow up -> responsable.';
COMMENT ON COLUMN platform.follow_ups_operativos_responsables.id_follow_up IS 'Follow up operativo al que pertenece el responsable.';
COMMENT ON COLUMN platform.follow_ups_operativos_responsables.id_login IS 'Login humano del responsable canonico.';
COMMENT ON COLUMN platform.follow_ups_operativos_responsables.id_usuario IS 'Usuario workforce del responsable canonico.';
COMMENT ON COLUMN platform.follow_ups_operativos_responsables.is_creator IS 'Marca si esta fila representa al creador original del follow up.';
COMMENT ON COLUMN platform.follow_ups_operativos_responsables.assignment_origin IS 'Origen funcional de la asignacion: creator_default, message_explicit, all_scope, backfill_legacy, etc.';
COMMENT ON COLUMN platform.follow_ups_operativos_responsables.created_by_login_id IS 'Login atribuible a la asignacion inicial de esta fila cuando exista contexto confiable.';
COMMENT ON COLUMN platform.follow_ups_operativos_responsables.created_by_user_id IS 'Usuario workforce atribuible a la asignacion inicial de esta fila cuando exista contexto confiable.';
COMMENT ON COLUMN platform.follow_ups_operativos_responsables.created_at IS 'Timestamp de creacion de la fila canonica de responsable.';
COMMENT ON COLUMN platform.follow_ups_operativos_responsables.updated_at IS 'Timestamp de ultima actualizacion de la fila canonica de responsable.';

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. La fuente canonica de responsables migra a platform.follow_ups_operativos_responsables.';
COMMENT ON COLUMN platform.follow_ups_operativos.id_login_responsable IS 'Campo legacy de compatibilidad para el login responsable singular. La fuente canonica de responsables migra a platform.follow_ups_operativos_responsables.';
COMMENT ON COLUMN platform.follow_ups_operativos.id_usuario_responsable IS 'Campo legacy de compatibilidad para el usuario responsable singular. La fuente canonica de responsables migra a platform.follow_ups_operativos_responsables.';
COMMENT ON COLUMN platform.follow_ups_operativos.seguidores IS 'Array JSONB de seguidores humanos del item. Debe mantenerse excluyente del set canonico de responsables.';

INSERT INTO platform.follow_ups_operativos_responsables (
    id_follow_up,
    id_login,
    id_usuario,
    is_creator,
    assignment_origin,
    created_by_login_id,
    created_by_user_id,
    created_at,
    updated_at
)
SELECT
    f.id_follow_up,
    f.id_login_registra,
    f.id_usuario_registra,
    TRUE,
    'creator_default',
    f.id_login_registra,
    f.id_usuario_registra,
    COALESCE(f.created_at, NOW()),
    COALESCE(f.updated_at, f.created_at, NOW())
FROM platform.follow_ups_operativos AS f
WHERE f.id_login_registra IS NOT NULL
  AND f.id_usuario_registra IS NOT NULL
ON CONFLICT (id_follow_up, id_usuario) DO NOTHING;

INSERT INTO platform.follow_ups_operativos_responsables (
    id_follow_up,
    id_login,
    id_usuario,
    is_creator,
    assignment_origin,
    created_by_login_id,
    created_by_user_id,
    created_at,
    updated_at
)
SELECT
    f.id_follow_up,
    f.id_login_responsable,
    f.id_usuario_responsable,
    FALSE,
    'backfill_legacy',
    f.id_login_registra,
    f.id_usuario_registra,
    COALESCE(f.created_at, NOW()),
    COALESCE(f.updated_at, f.created_at, NOW())
FROM platform.follow_ups_operativos AS f
WHERE f.id_login_responsable IS NOT NULL
  AND f.id_usuario_responsable IS NOT NULL
  AND f.id_usuario_responsable <> f.id_usuario_registra
ON CONFLICT (id_follow_up, id_usuario) DO NOTHING;

UPDATE platform.follow_ups_operativos AS f
SET seguidores = COALESCE(
    (
        SELECT jsonb_agg(seg.elem ORDER BY seg.ord)
        FROM jsonb_array_elements(COALESCE(f.seguidores, '[]'::jsonb)) WITH ORDINALITY AS seg(elem, ord)
        WHERE NOT EXISTS (
            SELECT 1
            FROM platform.follow_ups_operativos_responsables AS r
            WHERE r.id_follow_up = f.id_follow_up
              AND jsonb_typeof(seg.elem) = 'object'
              AND COALESCE(seg.elem->>'id_usuario', '') ~ '^[0-9]+$'
              AND (seg.elem->>'id_usuario')::INTEGER = r.id_usuario
        )
    ),
    '[]'::jsonb
)
WHERE jsonb_typeof(COALESCE(f.seguidores, '[]'::jsonb)) = 'array'
  AND EXISTS (
      SELECT 1
      FROM jsonb_array_elements(COALESCE(f.seguidores, '[]'::jsonb)) AS seg(elem)
      JOIN platform.follow_ups_operativos_responsables AS r
        ON r.id_follow_up = f.id_follow_up
       AND jsonb_typeof(seg.elem) = 'object'
       AND COALESCE(seg.elem->>'id_usuario', '') ~ '^[0-9]+$'
       AND (seg.elem->>'id_usuario')::INTEGER = r.id_usuario
  );

INSERT INTO ops.canonical_table_map (table_name, canonical_schema, notes)
VALUES (
    'follow_ups_operativos_responsables',
    'platform',
    'Fuente canonica plural de responsables reales por follow up operativo'
)
ON CONFLICT (table_name) DO UPDATE
SET canonical_schema = EXCLUDED.canonical_schema,
    notes = EXCLUDED.notes;

ANALYZE platform.follow_ups_operativos_responsables;
ANALYZE platform.follow_ups_operativos;
