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

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

CREATE TABLE IF NOT EXISTS crm.historiales_conversacion_importados (
    id_historial BIGSERIAL PRIMARY KEY,
    business_id VARCHAR(100) NOT NULL,
    sucursal_id INTEGER NOT NULL,
    id_cliente INTEGER NOT NULL,
    uploaded_by_login_id INTEGER NOT NULL,
    uploaded_by_user_id INTEGER,
    contenido_txt TEXT NOT NULL,
    contexto_extra TEXT,
    coverage_started_at TIMESTAMPTZ,
    coverage_ended_at TIMESTAMPTZ,
    focus_windows JSONB NOT NULL DEFAULT '[]'::jsonb,
    revision_number INTEGER NOT NULL DEFAULT 1,
    previous_historial_id BIGINT,
    id_estadistica BIGINT,
    ruta_relativa VARCHAR(500),
    nombre_archivo VARCHAR(255),
    nombre_original VARCHAR(255),
    mime_type VARCHAR(100),
    sha256 VARCHAR(64),
    tamanio_bytes INTEGER,
    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_historiales_conversacion_importados_cliente
        FOREIGN KEY (id_cliente)
        REFERENCES crm.clientes (id_cliente)
        ON UPDATE CASCADE
        ON DELETE RESTRICT,
    CONSTRAINT fk_historiales_conversacion_importados_previous
        FOREIGN KEY (previous_historial_id)
        REFERENCES crm.historiales_conversacion_importados (id_historial)
        ON UPDATE CASCADE
        ON DELETE SET NULL,
    CONSTRAINT fk_historiales_conversacion_importados_estadistica
        FOREIGN KEY (id_estadistica)
        REFERENCES platform.estadisticas (id_estadistica)
        ON UPDATE CASCADE
        ON DELETE SET NULL,
    CONSTRAINT chk_historiales_conversacion_importados_sucursal_non_negative
        CHECK (sucursal_id >= 0),
    CONSTRAINT chk_historiales_conversacion_importados_contenido_not_empty
        CHECK (length(trim(contenido_txt)) > 0),
    CONSTRAINT chk_historiales_conversacion_importados_focus_windows_array
        CHECK (jsonb_typeof(focus_windows) = 'array'),
    CONSTRAINT chk_historiales_conversacion_importados_revision_number
        CHECK (revision_number >= 1),
    CONSTRAINT chk_historiales_conversacion_importados_coverage
        CHECK (
            coverage_started_at IS NULL
            OR coverage_ended_at IS NULL
            OR coverage_ended_at >= coverage_started_at
        ),
    CONSTRAINT chk_historiales_conversacion_importados_tamanio_bytes
        CHECK (tamanio_bytes IS NULL OR tamanio_bytes >= 0)
);

CREATE UNIQUE INDEX IF NOT EXISTS uq_historiales_conversacion_importados_activo
    ON crm.historiales_conversacion_importados (
        business_id,
        sucursal_id,
        id_cliente,
        uploaded_by_login_id
    )
    WHERE soft_delete IS NOT TRUE;

CREATE INDEX IF NOT EXISTS historiales_conversacion_importados_scope_created_idx
    ON crm.historiales_conversacion_importados (
        business_id,
        sucursal_id,
        id_cliente,
        uploaded_by_login_id,
        created_at DESC
    );

CREATE INDEX IF NOT EXISTS historiales_conversacion_importados_login_created_idx
    ON crm.historiales_conversacion_importados (
        business_id,
        uploaded_by_login_id,
        created_at DESC
    )
    WHERE soft_delete IS NOT TRUE;

CREATE INDEX IF NOT EXISTS historiales_conversacion_importados_estadistica_idx
    ON crm.historiales_conversacion_importados (id_estadistica)
    WHERE id_estadistica IS NOT NULL;

CREATE INDEX IF NOT EXISTS historiales_conversacion_importados_previous_idx
    ON crm.historiales_conversacion_importados (previous_historial_id)
    WHERE previous_historial_id IS NOT NULL;

CREATE INDEX IF NOT EXISTS historiales_conversacion_importados_focus_windows_idx
    ON crm.historiales_conversacion_importados USING gin (focus_windows);

COMMENT ON TABLE crm.historiales_conversacion_importados IS 'Historiales crudos de conversaciones humanas importadas por empleado para analisis asistido de clientes.';
COMMENT ON COLUMN crm.historiales_conversacion_importados.id_historial IS 'Identificador tecnico del historial importado.';
COMMENT ON COLUMN crm.historiales_conversacion_importados.business_id IS 'Tenant del negocio para aislamiento multiempresa.';
COMMENT ON COLUMN crm.historiales_conversacion_importados.sucursal_id IS 'Sucursal operativa del historial importado. 0 representa alcance global controlado.';
COMMENT ON COLUMN crm.historiales_conversacion_importados.id_cliente IS 'Cliente al que pertenece la conversacion importada.';
COMMENT ON COLUMN crm.historiales_conversacion_importados.uploaded_by_login_id IS 'Login humano propietario del historial activo dentro del scope business+sucursal+cliente.';
COMMENT ON COLUMN crm.historiales_conversacion_importados.uploaded_by_user_id IS 'Usuario workforce vinculado al login humano que subio o reemplazo el historial.';
COMMENT ON COLUMN crm.historiales_conversacion_importados.contenido_txt IS 'Contenido crudo del archivo .txt importado y fuente canonica del historial humano.';
COMMENT ON COLUMN crm.historiales_conversacion_importados.contexto_extra IS 'Contexto adicional escrito por el operador para guiar el analisis posterior.';
COMMENT ON COLUMN crm.historiales_conversacion_importados.coverage_started_at IS 'Inicio del periodo temporal cubierto por el historial importado.';
COMMENT ON COLUMN crm.historiales_conversacion_importados.coverage_ended_at IS 'Fin del periodo temporal cubierto por el historial importado.';
COMMENT ON COLUMN crm.historiales_conversacion_importados.focus_windows IS 'Ventanas temporales nuevas o prioritarias que la IA debe atender cuando un historial reemplaza a otro.';
COMMENT ON COLUMN crm.historiales_conversacion_importados.revision_number IS 'Numero secuencial de revision dentro del scope activo del mismo empleado/cliente/sucursal.';
COMMENT ON COLUMN crm.historiales_conversacion_importados.previous_historial_id IS 'Referencia al historial activo previo que fue reemplazado por este upload.';
COMMENT ON COLUMN crm.historiales_conversacion_importados.id_estadistica IS 'Proyeccion materializada de la conversacion en platform.estadisticas.';
COMMENT ON COLUMN crm.historiales_conversacion_importados.ruta_relativa IS 'Ruta relativa del archivo original cuando se conserva el artefacto fisico.';
COMMENT ON COLUMN crm.historiales_conversacion_importados.nombre_archivo IS 'Nombre interno persistido del archivo original.';
COMMENT ON COLUMN crm.historiales_conversacion_importados.nombre_original IS 'Nombre original del archivo cargado por el operador.';
COMMENT ON COLUMN crm.historiales_conversacion_importados.mime_type IS 'MIME type detectado del archivo original.';
COMMENT ON COLUMN crm.historiales_conversacion_importados.sha256 IS 'Hash SHA-256 del archivo original para auditoria e integridad.';
COMMENT ON COLUMN crm.historiales_conversacion_importados.tamanio_bytes IS 'Tamano del archivo original en bytes.';
COMMENT ON COLUMN crm.historiales_conversacion_importados.created_at IS 'Timestamp oficial del upload activo o de cada revision historica.';
COMMENT ON COLUMN crm.historiales_conversacion_importados.updated_at IS 'Timestamp de la ultima modificacion del registro.';
COMMENT ON COLUMN crm.historiales_conversacion_importados.soft_delete IS 'Bandera de borrado logico para preservar revisiones previas.';
COMMENT ON COLUMN crm.historiales_conversacion_importados.soft_delete_date IS 'Timestamp de borrado logico del historial reemplazado o retirado.';

INSERT INTO ops.canonical_table_map (table_name, canonical_schema, notes)
VALUES ('historiales_conversacion_importados', 'crm', 'Fuente canonica del historial crudo importado por empleado para analisis asistido de clientes')
ON CONFLICT (table_name) DO UPDATE
SET canonical_schema = EXCLUDED.canonical_schema,
    notes = EXCLUDED.notes;

ANALYZE crm.historiales_conversacion_importados;
