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

CREATE SCHEMA IF NOT EXISTS crm;
CREATE SCHEMA IF NOT EXISTS ai;
CREATE SCHEMA IF NOT EXISTS ops;

CREATE TABLE IF NOT EXISTS crm.contactos_programados (
    id_contacto_programado BIGSERIAL PRIMARY KEY,
    business_id VARCHAR(100) NOT NULL,
    id_cliente INTEGER NOT NULL,
    id_empleado INTEGER NOT NULL,
    sucursal INT4[] NOT NULL,
    creado_en TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    programado_para TIMESTAMPTZ NOT NULL,
    motivo_contacto TEXT NOT NULL,
    recomendacion_contacto TEXT,
    canal_contacto VARCHAR(20) NOT NULL DEFAULT 'humano',
    estado SMALLINT NOT NULL DEFAULT 1,
    resultado_contacto TEXT,
    completado_en TIMESTAMPTZ,
    cancelado_en TIMESTAMPTZ,
    metadata JSONB NOT NULL DEFAULT '{}'::jsonb,
    soft_delete BOOLEAN NOT NULL DEFAULT FALSE,
    soft_delete_date TIMESTAMPTZ,
    CONSTRAINT fk_contactos_programados_cliente
        FOREIGN KEY (id_cliente)
        REFERENCES crm.clientes (id_cliente)
        ON UPDATE CASCADE
        ON DELETE RESTRICT,
    CONSTRAINT fk_contactos_programados_empleado
        FOREIGN KEY (id_empleado)
        REFERENCES ai.empleado_digital (id_empleado)
        ON UPDATE CASCADE
        ON DELETE RESTRICT,
    CONSTRAINT chk_contactos_programados_sucursal_not_empty
        CHECK (cardinality(sucursal) >= 1),
    CONSTRAINT chk_contactos_programados_motivo_not_empty
        CHECK (length(trim(motivo_contacto)) > 0),
    CONSTRAINT chk_contactos_programados_canal
        CHECK (canal_contacto IN ('humano', 'empleado-digital')),
    CONSTRAINT chk_contactos_programados_estado
        CHECK (estado IN (1, 2, 3, 4))
);

CREATE INDEX IF NOT EXISTS contactos_programados_business_estado_programado_idx
    ON crm.contactos_programados (business_id, estado, programado_para)
    WHERE soft_delete IS NOT TRUE;

CREATE INDEX IF NOT EXISTS contactos_programados_business_cliente_programado_idx
    ON crm.contactos_programados (business_id, id_cliente, programado_para DESC)
    WHERE soft_delete IS NOT TRUE;

CREATE INDEX IF NOT EXISTS contactos_programados_business_empleado_programado_idx
    ON crm.contactos_programados (business_id, id_empleado, programado_para)
    WHERE soft_delete IS NOT TRUE;

CREATE INDEX IF NOT EXISTS contactos_programados_sucursal_idx
    ON crm.contactos_programados USING gin (sucursal);

CREATE INDEX IF NOT EXISTS contactos_programados_metadata_idx
    ON crm.contactos_programados USING gin (metadata);

COMMENT ON TABLE crm.contactos_programados IS 'Agenda operativa de contactos programados por cliente para la capacidad Contactar Despues.';
COMMENT ON COLUMN crm.contactos_programados.id_contacto_programado IS 'Identificador tecnico del contacto programado.';
COMMENT ON COLUMN crm.contactos_programados.business_id IS 'Tenant del negocio para aislamiento multiempresa.';
COMMENT ON COLUMN crm.contactos_programados.id_cliente IS 'Cliente objetivo a contactar, referencia a crm.clientes.id_cliente.';
COMMENT ON COLUMN crm.contactos_programados.id_empleado IS 'Empleado digital responsable de generar o gestionar el seguimiento, referencia a ai.empleado_digital.id_empleado.';
COMMENT ON COLUMN crm.contactos_programados.sucursal IS 'Sucursal operativa asociada al seguimiento (array int4).';
COMMENT ON COLUMN crm.contactos_programados.creado_en IS 'Timestamp RFC3339 de creacion del registro.';
COMMENT ON COLUMN crm.contactos_programados.programado_para IS 'Timestamp RFC3339 objetivo para realizar el contacto.';
COMMENT ON COLUMN crm.contactos_programados.motivo_contacto IS 'Texto explicando por que debe contactarse nuevamente al cliente.';
COMMENT ON COLUMN crm.contactos_programados.recomendacion_contacto IS 'Recomendacion opcional de como realizar el contacto de seguimiento.';
COMMENT ON COLUMN crm.contactos_programados.canal_contacto IS 'Canal operativo definido para seguimiento: humano o empleado-digital.';
COMMENT ON COLUMN crm.contactos_programados.estado IS 'Estado del seguimiento: 1 pendiente, 2 completado, 3 cancelado, 4 vencido.';
COMMENT ON COLUMN crm.contactos_programados.resultado_contacto IS 'Resultado final o notas de ejecucion del contacto.';
COMMENT ON COLUMN crm.contactos_programados.completado_en IS 'Timestamp cuando el contacto se marco como completado.';
COMMENT ON COLUMN crm.contactos_programados.cancelado_en IS 'Timestamp cuando el contacto se marco como cancelado.';
COMMENT ON COLUMN crm.contactos_programados.metadata IS 'JSONB extensible para datos complementarios del caso de seguimiento.';
COMMENT ON COLUMN crm.contactos_programados.soft_delete IS 'Bandera de borrado logico.';
COMMENT ON COLUMN crm.contactos_programados.soft_delete_date IS 'Timestamp de borrado logico.';

INSERT INTO ops.canonical_table_map (table_name, canonical_schema, notes)
VALUES ('contactos_programados', 'crm', 'CRM domain source of truth for Contactar Despues scheduling records')
ON CONFLICT (table_name) DO UPDATE
SET canonical_schema = EXCLUDED.canonical_schema,
    notes = EXCLUDED.notes;

ANALYZE crm.contactos_programados;
