-- Tabelle applicative del modulo crmp_ai_agent (agente AI "005").
-- Vedi progetto_agente_ai_crmprestito.md: §6.1 (job), §10.4 (message), §9.3 (tool call),
-- §11.2 (audit).
-- Nessun hook_schema, nessun file .install: il codice runtime assume che queste tabelle
-- esistano e lo segnala esplicitamente se mancano.
--
-- File unico e completo: va eseguito su un ambiente che non ha ancora le tabelle di 005. Non
-- contiene ALTER, quindi non e' rigiocabile dove sono gia' presenti — li' si cancella prima, oppure
-- si scrive un file numerato nuovo con la sola differenza.
--
-- **Da rieseguire dopo ogni sync del DB di staging da produzione.** Lo script di allineamento
-- droppa l'intero schema e importa il dump di prod, dove il modulo non e' mai stato abilitato e le
-- tabelle crmp_ai_* non esistono: senza questo file, dopo un sync 005 non ha piu' dove scrivere.
-- Insieme vanno rifatti gli altri passi dell'allineamento, elencati in CLAUDE.md (riabilitare il
-- modulo, riscrivere crmp_ai_agent.settings, riportare le spunte di field_utilizzabile_da_005,
-- rilanciare crmp_ai_agent_descrizioni_tipi_doc.php).


-- §6.1 — coda dei job. Una riga per unita' di lavoro: un file da smistare, un turno di
-- conversazione, una lettura di dati.
CREATE TABLE crmp_ai_job (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  batch_id VARCHAR(64) NOT NULL,                  -- raggruppa i job di un singolo upload
  parent_id BIGINT UNSIGNED NULL,                 -- sub-job -> job orchestratore
  job_type VARCHAR(64) NOT NULL,                  -- 'smistamento' | 'agente_005' | 'lettura_dati'
  profile VARCHAR(64) NOT NULL,                   -- profilo agente (§5.4)
  cache_key VARCHAR(64) NOT NULL DEFAULT '',      -- identita' del prefisso di prompt (§5.3): audit, non schedulazione
  status VARCHAR(32) NOT NULL,                    -- §6.2
  priority TINYINT UNSIGNED NOT NULL DEFAULT 3,   -- fascia base del tipo di job (§6.3)
  seen_at INT UNSIGNED NULL,                      -- quando l'operatore ha visto l'esito (§10.5, sezione 3 vs storico)
  messaggi_puliti_at INT UNSIGNED NULL,           -- quando la retention ha rimosso il discorso del thread (§11.3): distingue "conversazione scaduta" da "agente che non ha mai parlato"
  chiusa_at INT UNSIGNED NULL,                    -- thread chiuso: dall'operatore o da se' dopo 24h. Un thread chiuso non si riapre, il messaggio successivo ne apre uno nuovo con contesto pulito
  claimed_by VARCHAR(64) NULL,                    -- consumatore che lo sta eseguendo
  payload JSON NOT NULL,                          -- percorsi file, pagine, parametri
  result JSON NULL,                               -- output strutturato validato
  nid_anagrafica VARCHAR(32) NULL,                -- valorizzato appena noto
  nid_lavorazione VARCHAR(32) NULL,
  nid_documento VARCHAR(32) NULL,
  uid INT UNSIGNED NOT NULL,                      -- chi ha avviato
  attempts SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  unavailable_attempts SMALLINT UNSIGNED NOT NULL DEFAULT 0, -- riaccodamenti per server di inferenza spento: separati da attempts, non portano a FAILED (§6.4)
  next_attempt_at INT UNSIGNED NULL,              -- backoff
  lease_until INT UNSIGNED NULL,                  -- scadenza della presa in carico
  model_id VARCHAR(128) NULL,                     -- modello che ha prodotto result
  skill_version VARCHAR(32) NULL,
  chiamate_modello INT UNSIGNED NOT NULL DEFAULT 0, -- richieste al server di inferenza spese dall'ultimo turno: e' il numero che dice se un loop agentico converge
  token_totali INT UNSIGNED NOT NULL DEFAULT 0,     -- token dichiarati dal server per quelle richieste, tentativi falliti compresi
  durata_turno INT UNSIGNED NOT NULL DEFAULT 0,     -- secondi spesi dall'ultimo turno, dalla presa in carico alla registrazione dell'esito
  error TEXT NULL,
  created INT UNSIGNED NOT NULL,
  updated INT UNSIGNED NOT NULL,
  PRIMARY KEY (id),
  KEY idx_claim (status, next_attempt_at),
  KEY idx_ordine (status, priority, created),
  KEY idx_batch (batch_id),
  KEY idx_anagrafica (nid_anagrafica, status),
  KEY idx_lavorazione (nid_lavorazione, status),
  KEY idx_parent (parent_id),
  KEY idx_badge (uid, status, seen_at),
  KEY idx_thread (job_type, nid_anagrafica, chiusa_at),
  KEY idx_storico (uid, updated)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


-- §10.4 — storia conversazionale, storage di DbChatHistory. La chiave di thread e' job_id.
-- Le immagini non entrano qui (§10.3): i tool di lettura restituiscono testo.
CREATE TABLE crmp_ai_message (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  job_id BIGINT UNSIGNED NOT NULL,
  role VARCHAR(32) NOT NULL,                      -- 'system' | 'user' | 'assistant' | 'tool'
  type VARCHAR(32) NULL,                          -- tipo Neuron AI: un messaggio di invocazione tool riletto come messaggio semplice perde le chiamate che portava (§10.4)
  content LONGTEXT NULL,
  tool_calls JSON NULL,                           -- chiamate a tool associate al messaggio
  usage_tokens INT UNSIGNED NULL,                 -- token dichiarati dal provider, per il troncamento
  uid INT UNSIGNED NULL,                          -- chi ha scritto il turno, per la sigla nel pannello: NULL su cio' che non l'ha scritto una persona
  created INT UNSIGNED NOT NULL,
  PRIMARY KEY (id),
  KEY idx_thread (job_id, id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


-- §9.3 — invocazioni dei tool. Base dell'undo e dell'audit.
-- Per le trasformazioni su file l'annullamento passa dalla conservazione dell'originale,
-- non da undo_data.
CREATE TABLE crmp_ai_tool_call (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  job_id BIGINT UNSIGNED NOT NULL,
  tool_id VARCHAR(128) NOT NULL,
  operation VARCHAR(32) NOT NULL,                 -- ToolOperation: Read | Transform | Trigger | Write
  destructive TINYINT(1) NOT NULL DEFAULT 0,
  arguments JSON NOT NULL,
  is_auto TINYINT(1) NOT NULL DEFAULT 0,          -- eseguito senza conferma per policy (§9.2)
  result JSON NULL,
  status VARCHAR(32) NOT NULL,                    -- 'pending' | 'confirmed' | 'rejected' | 'done' | 'failed' | 'undone'
  confirmed_by INT UNSIGNED NULL,
  confirmed_at INT UNSIGNED NULL,
  undo_data JSON NULL,
  created INT UNSIGNED NOT NULL,
  PRIMARY KEY (id),
  KEY idx_job (job_id),
  KEY idx_status (status),
  KEY idx_tool (tool_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


-- §11.2 — audit trail. Ogni transizione di stato, chiamata tool, conferma, rifiuto,
-- correzione. Retention di raw_output secondo §11.3.
CREATE TABLE crmp_ai_audit (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  job_id BIGINT UNSIGNED NOT NULL,
  ts INT UNSIGNED NOT NULL,
  uid INT UNSIGNED NOT NULL,
  event VARCHAR(64) NOT NULL,
  tool_id VARCHAR(128) NULL,
  entity_type VARCHAR(32) NULL,
  entity_id VARCHAR(32) NULL,
  model_id VARCHAR(128) NULL,
  skill_version VARCHAR(32) NULL,
  prompt_hash VARCHAR(64) NULL,
  raw_output JSON NULL,
  confirmed_by INT UNSIGNED NULL,
  confirmed_at INT UNSIGNED NULL,
  note TEXT NULL,
  PRIMARY KEY (id),
  KEY idx_job (job_id),
  KEY idx_ts (ts),
  KEY idx_entita (entity_type, entity_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;