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

CREATE SCHEMA IF NOT EXISTS platform;
CREATE SCHEMA IF NOT EXISTS crm;
CREATE SCHEMA IF NOT EXISTS ai;
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.borradores_operativos (
    id_borrador BIGSERIAL PRIMARY KEY,
    business_id VARCHAR(100) NOT NULL,
    sucursal_id INTEGER NOT NULL,
    owner_login_id INTEGER NOT NULL,
    owner_user_id INTEGER,
    entity_type VARCHAR(64) NOT NULL,
    entity_id BIGINT NOT NULL,
    draft_namespace VARCHAR(80) NOT NULL,
    artifact_type VARCHAR(80) NOT NULL,
    artifact_key VARCHAR(160) NOT NULL,
    status VARCHAR(32) NOT NULL,
    source_job_id VARCHAR(255),
    id_historial BIGINT,
    id_estadistica BIGINT,
    id_analisis INTEGER,
    previous_borrador_id BIGINT,
    applied_record_id BIGINT,
    payload_jsonb JSONB NOT NULL DEFAULT '{}'::jsonb,
    config_snapshot_jsonb JSONB NOT NULL DEFAULT '{}'::jsonb,
    links_jsonb JSONB NOT NULL DEFAULT '{}'::jsonb,
    meta_jsonb JSONB NOT NULL DEFAULT '{}'::jsonb,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    applied_at TIMESTAMPTZ,
    soft_delete BOOLEAN NOT NULL DEFAULT FALSE,
    soft_delete_date TIMESTAMPTZ,
    CONSTRAINT fk_borradores_operativos_previous
        FOREIGN KEY (previous_borrador_id)
        REFERENCES platform.borradores_operativos (id_borrador)
        ON UPDATE CASCADE
        ON DELETE SET NULL,
    CONSTRAINT fk_borradores_operativos_historial
        FOREIGN KEY (id_historial)
        REFERENCES crm.historiales_conversacion_importados (id_historial)
        ON UPDATE CASCADE
        ON DELETE SET NULL,
    CONSTRAINT fk_borradores_operativos_estadistica
        FOREIGN KEY (id_estadistica)
        REFERENCES platform.estadisticas (id_estadistica)
        ON UPDATE CASCADE
        ON DELETE SET NULL,
    CONSTRAINT fk_borradores_operativos_analisis
        FOREIGN KEY (id_analisis)
        REFERENCES ai.analisis_conversacion (id_analisis)
        ON UPDATE CASCADE
        ON DELETE SET NULL,
    CONSTRAINT chk_borradores_operativos_business_id_not_empty
        CHECK (length(trim(business_id)) > 0),
    CONSTRAINT chk_borradores_operativos_sucursal_non_negative
        CHECK (sucursal_id >= 0),
    CONSTRAINT chk_borradores_operativos_owner_login_positive
        CHECK (owner_login_id > 0),
    CONSTRAINT chk_borradores_operativos_owner_user_positive
        CHECK (owner_user_id IS NULL OR owner_user_id > 0),
    CONSTRAINT chk_borradores_operativos_entity_type_not_empty
        CHECK (length(trim(entity_type)) > 0),
    CONSTRAINT chk_borradores_operativos_entity_id_positive
        CHECK (entity_id > 0),
    CONSTRAINT chk_borradores_operativos_namespace_not_empty
        CHECK (length(trim(draft_namespace)) > 0),
    CONSTRAINT chk_borradores_operativos_artifact_type_not_empty
        CHECK (length(trim(artifact_type)) > 0),
    CONSTRAINT chk_borradores_operativos_artifact_key_not_empty
        CHECK (length(trim(artifact_key)) > 0),
    CONSTRAINT chk_borradores_operativos_status
        CHECK (status IN ('generating', 'ready', 'applied', 'discarded', 'superseded', 'error')),
    CONSTRAINT chk_borradores_operativos_source_job_not_empty
        CHECK (source_job_id IS NULL OR length(trim(source_job_id)) > 0),
    CONSTRAINT chk_borradores_operativos_id_historial_positive
        CHECK (id_historial IS NULL OR id_historial > 0),
    CONSTRAINT chk_borradores_operativos_id_estadistica_positive
        CHECK (id_estadistica IS NULL OR id_estadistica > 0),
    CONSTRAINT chk_borradores_operativos_id_analisis_positive
        CHECK (id_analisis IS NULL OR id_analisis > 0),
    CONSTRAINT chk_borradores_operativos_previous_positive
        CHECK (previous_borrador_id IS NULL OR previous_borrador_id > 0),
    CONSTRAINT chk_borradores_operativos_applied_record_positive
        CHECK (applied_record_id IS NULL OR applied_record_id > 0),
    CONSTRAINT chk_borradores_operativos_payload_object
        CHECK (jsonb_typeof(payload_jsonb) = 'object'),
    CONSTRAINT chk_borradores_operativos_config_snapshot_object
        CHECK (jsonb_typeof(config_snapshot_jsonb) = 'object'),
    CONSTRAINT chk_borradores_operativos_links_object
        CHECK (jsonb_typeof(links_jsonb) = 'object'),
    CONSTRAINT chk_borradores_operativos_meta_object
        CHECK (jsonb_typeof(meta_jsonb) = 'object')
);

CREATE INDEX IF NOT EXISTS borradores_operativos_scope_status_created_idx
    ON platform.borradores_operativos (
        business_id,
        sucursal_id,
        owner_login_id,
        entity_type,
        entity_id,
        draft_namespace,
        status,
        created_at DESC
    )
    WHERE soft_delete IS NOT TRUE;

CREATE INDEX IF NOT EXISTS borradores_operativos_artifact_created_idx
    ON platform.borradores_operativos (
        business_id,
        sucursal_id,
        owner_login_id,
        entity_type,
        entity_id,
        draft_namespace,
        artifact_type,
        artifact_key,
        created_at DESC
    )
    WHERE soft_delete IS NOT TRUE;

CREATE INDEX IF NOT EXISTS borradores_operativos_analysis_created_idx
    ON platform.borradores_operativos (
        business_id,
        id_historial,
        id_estadistica,
        id_analisis,
        created_at DESC
    )
    WHERE soft_delete IS NOT TRUE
      AND (
          id_historial IS NOT NULL
          OR id_estadistica IS NOT NULL
          OR id_analisis IS NOT NULL
      );

CREATE INDEX IF NOT EXISTS borradores_operativos_job_created_idx
    ON platform.borradores_operativos (
        business_id,
        source_job_id,
        created_at DESC
    )
    WHERE soft_delete IS NOT TRUE
      AND source_job_id IS NOT NULL;

CREATE INDEX IF NOT EXISTS borradores_operativos_previous_idx
    ON platform.borradores_operativos (previous_borrador_id)
    WHERE previous_borrador_id IS NOT NULL;

CREATE UNIQUE INDEX IF NOT EXISTS borradores_operativos_job_artifact_uq
    ON platform.borradores_operativos (
        business_id,
        draft_namespace,
        source_job_id,
        artifact_type,
        artifact_key
    )
    WHERE soft_delete IS NOT TRUE
      AND source_job_id IS NOT NULL;

COMMENT ON TABLE platform.borradores_operativos IS 'Borradores operativos reutilizables del ERP. Persisten artefactos generados automaticamente antes de promoverlos a tablas reales del dominio.';
COMMENT ON COLUMN platform.borradores_operativos.id_borrador IS 'Identificador tecnico del borrador operativo.';
COMMENT ON COLUMN platform.borradores_operativos.business_id IS 'Tenant del negocio propietario del borrador.';
COMMENT ON COLUMN platform.borradores_operativos.sucursal_id IS 'Sucursal contextual del borrador. 0 representa alcance global controlado.';
COMMENT ON COLUMN platform.borradores_operativos.owner_login_id IS 'Login humano dueno del borrador dentro del popup o flujo que lo genero.';
COMMENT ON COLUMN platform.borradores_operativos.owner_user_id IS 'Usuario workforce asociado al login humano cuando exista.';
COMMENT ON COLUMN platform.borradores_operativos.entity_type IS 'Tipo de entidad objetivo del borrador, por ejemplo cliente.';
COMMENT ON COLUMN platform.borradores_operativos.entity_id IS 'ID de la entidad objetivo dentro del tipo indicado.';
COMMENT ON COLUMN platform.borradores_operativos.draft_namespace IS 'Namespace funcional estable del sistema que produce el borrador.';
COMMENT ON COLUMN platform.borradores_operativos.artifact_type IS 'Tipo de artefacto persistido, por ejemplo internal_note, contact_after, dynamic_fields_batch o dynamic_field_action.';
COMMENT ON COLUMN platform.borradores_operativos.artifact_key IS 'Slot funcional estable del artefacto dentro del namespace y la entidad.';
COMMENT ON COLUMN platform.borradores_operativos.status IS 'Estado del ciclo de vida del borrador: generating, ready, applied, discarded, superseded o error.';
COMMENT ON COLUMN platform.borradores_operativos.source_job_id IS 'Job remoto del backend orquestador que origino esta generacion cuando aplica.';
COMMENT ON COLUMN platform.borradores_operativos.id_historial IS 'Historial de conversacion importado vinculado al borrador cuando aplica.';
COMMENT ON COLUMN platform.borradores_operativos.id_estadistica IS 'Proyeccion de estadistica vinculada al borrador cuando aplica.';
COMMENT ON COLUMN platform.borradores_operativos.id_analisis IS 'Analisis conversacional vinculado al borrador cuando aplica.';
COMMENT ON COLUMN platform.borradores_operativos.previous_borrador_id IS 'Referencia a la version previa que fue reemplazada por esta generacion.';
COMMENT ON COLUMN platform.borradores_operativos.applied_record_id IS 'Registro primario real creado o actualizado al promover el borrador, cuando existe uno dominante.';
COMMENT ON COLUMN platform.borradores_operativos.payload_jsonb IS 'Contenido principal del borrador listo para render, edicion o promocion.';
COMMENT ON COLUMN platform.borradores_operativos.config_snapshot_jsonb IS 'Snapshot de configuracion usado para generar este borrador.';
COMMENT ON COLUMN platform.borradores_operativos.links_jsonb IS 'Mapa JSONB de trazabilidad hacia jobs, analisis, lotes, acciones e ids aplicados.';
COMMENT ON COLUMN platform.borradores_operativos.meta_jsonb IS 'Metadata extensible del borrador para evolucion futura sin redisenar la tabla.';
COMMENT ON COLUMN platform.borradores_operativos.created_at IS 'Timestamp oficial de creacion de esta generacion del borrador.';
COMMENT ON COLUMN platform.borradores_operativos.updated_at IS 'Timestamp de ultima actualizacion de la fila del borrador.';
COMMENT ON COLUMN platform.borradores_operativos.applied_at IS 'Timestamp en que el borrador fue promovido exitosamente a una tabla real.';
COMMENT ON COLUMN platform.borradores_operativos.soft_delete IS 'Bandera de borrado logico del borrador.';
COMMENT ON COLUMN platform.borradores_operativos.soft_delete_date IS 'Timestamp de borrado logico del borrador.';

INSERT INTO ops.canonical_table_map (table_name, canonical_schema, notes)
VALUES (
    'borradores_operativos',
    'platform',
    'Fuente canonica reusable para borradores operativos del ERP antes de su promocion a tablas reales'
)
ON CONFLICT (table_name) DO UPDATE
SET canonical_schema = EXCLUDED.canonical_schema,
    notes = EXCLUDED.notes;

ANALYZE platform.borradores_operativos;
