-- ============================================================
-- GreenIta – Database Schema
-- Italian Green/Environmental Grants and News Platform
-- Migration: 001_schema.sql
-- Engine: InnoDB | Charset: utf8mb4_unicode_ci
-- ============================================================

SET NAMES utf8mb4;
SET CHARACTER SET utf8mb4;
SET collation_connection = 'utf8mb4_unicode_ci';
SET FOREIGN_KEY_CHECKS = 0;

CREATE DATABASE IF NOT EXISTS greenita
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE greenita;

-- ============================================================
-- 1. admin – Admin users
-- ============================================================
CREATE TABLE IF NOT EXISTS `admin` (
  `id`                    INT           NOT NULL AUTO_INCREMENT,
  `username`              VARCHAR(100)  NOT NULL,
  `email`                 VARCHAR(255)  NOT NULL,
  `password_hash`         VARCHAR(512)  NOT NULL COMMENT 'Argon2 hashed password',
  `ruolo`                 ENUM('superadmin','admin') NOT NULL DEFAULT 'admin',
  `stato`                 ENUM('attivo','bloccato','disattivato') NOT NULL DEFAULT 'attivo',
  `tentativi_login`       INT           NOT NULL DEFAULT 0,
  `ultimo_login`          DATETIME      DEFAULT NULL,
  `ultimo_ip`             VARCHAR(45)   DEFAULT NULL,
  `token_reset`           VARCHAR(255)  DEFAULT NULL,
  `token_reset_scadenza`  DATETIME      DEFAULT NULL,
  `created_at`            TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`            TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_admin_username` (`username`),
  UNIQUE KEY `uq_admin_email`    (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='Utenti amministratori della piattaforma GreenIta';

-- ============================================================
-- 2. fonti_ai – AI / Crawler sources (referenced by content tables)
-- ============================================================
CREATE TABLE IF NOT EXISTS `fonti_ai` (
  `id`                    INT           NOT NULL AUTO_INCREMENT,
  `nome`                  VARCHAR(255)  NOT NULL,
  `url`                   VARCHAR(2048) NOT NULL,
  `tipo`                  ENUM('istituzionale','regionale','rss','pdf','meteo_allerta','europeo') DEFAULT NULL,
  `formato`               ENUM('html','pdf','rss','json','xml') DEFAULT NULL,
  `frequenza_scansione`   INT           NOT NULL DEFAULT 3600 COMMENT 'Frequenza in secondi',
  `priorita`              INT           NOT NULL DEFAULT 5,
  `stato`                 ENUM('attiva','disattiva','errore') NOT NULL DEFAULT 'attiva',
  `ultimo_crawl`          DATETIME      DEFAULT NULL,
  `ultimo_successo`       DATETIME      DEFAULT NULL,
  `errori_ultimi_30gg`    INT           NOT NULL DEFAULT 0,
  `configurazione`        JSON          DEFAULT NULL,
  `selettori_css`         JSON          DEFAULT NULL,
  `created_at`            TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`            TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  INDEX `idx_fonti_ai_stato` (`stato`),
  INDEX `idx_fonti_ai_tipo`  (`tipo`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='Fonti dati per il crawler e l''elaborazione AI';

-- ============================================================
-- 3. bandi – Grants / funding opportunities
-- ============================================================
CREATE TABLE IF NOT EXISTS `bandi` (
  `id`                INT           NOT NULL AUTO_INCREMENT,
  `titolo`            VARCHAR(500)  NOT NULL,
  `titolo_ai`         VARCHAR(500)  DEFAULT NULL,
  `descrizione`       TEXT          DEFAULT NULL,
  `descrizione_ai`    TEXT          DEFAULT NULL,
  `ente`              VARCHAR(255)  DEFAULT NULL,
  `area_geografica`   VARCHAR(255)  DEFAULT NULL,
  `regione`           VARCHAR(100)  DEFAULT NULL,
  `tipologia`         VARCHAR(100)  DEFAULT NULL,
  `importo_min`       DECIMAL(15,2) DEFAULT NULL,
  `importo_max`       DECIMAL(15,2) DEFAULT NULL,
  `data_apertura`     DATE          DEFAULT NULL,
  `data_scadenza`     DATE          DEFAULT NULL,
  `url_originale`     VARCHAR(2048) DEFAULT NULL,
  `url_documento`     VARCHAR(2048) DEFAULT NULL,
  `stato`             ENUM('importato','elaborazione_ai','elaborato','da_revisione','approvato','pubblicato','scartato') NOT NULL DEFAULT 'importato',
  `confidenza_ai`     DECIMAL(5,4)  DEFAULT NULL,
  `fonte_id`          INT           DEFAULT NULL,
  `hash_contenuto`    VARCHAR(64)   DEFAULT NULL,
  `tag`               JSON          DEFAULT NULL,
  `dati_estratti`     JSON          DEFAULT NULL,
  `approvato_da`      INT           DEFAULT NULL,
  `approvato_il`      DATETIME      DEFAULT NULL,
  `pubblicato_il`     DATETIME      DEFAULT NULL,
  `created_at`        TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`        TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_bandi_hash` (`hash_contenuto`),
  INDEX `idx_bandi_stato`           (`stato`),
  INDEX `idx_bandi_data_scadenza`   (`data_scadenza`),
  INDEX `idx_bandi_regione`         (`regione`),
  INDEX `idx_bandi_area_geografica` (`area_geografica`),
  INDEX `idx_bandi_fonte_id`        (`fonte_id`),
  CONSTRAINT `fk_bandi_fonte`      FOREIGN KEY (`fonte_id`)     REFERENCES `fonti_ai`(`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `fk_bandi_approvato`  FOREIGN KEY (`approvato_da`) REFERENCES `admin`(`id`)    ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='Bandi e opportunità di finanziamento green';

-- ============================================================
-- 4. notizie – News
-- ============================================================
CREATE TABLE IF NOT EXISTS `notizie` (
  `id`                INT           NOT NULL AUTO_INCREMENT,
  `titolo`            VARCHAR(500)  NOT NULL,
  `titolo_ai`         VARCHAR(500)  DEFAULT NULL,
  `contenuto`         TEXT          DEFAULT NULL,
  `contenuto_ai`      TEXT          DEFAULT NULL,
  `sommario`          TEXT          DEFAULT NULL,
  `sommario_ai`       TEXT          DEFAULT NULL,
  `fonte`             VARCHAR(255)  DEFAULT NULL,
  `autore`            VARCHAR(255)  DEFAULT NULL,
  `categoria`         VARCHAR(100)  DEFAULT NULL,
  `area_geografica`   VARCHAR(255)  DEFAULT NULL,
  `regione`           VARCHAR(100)  DEFAULT NULL,
  `url_originale`     VARCHAR(2048) DEFAULT NULL,
  `immagine_url`      VARCHAR(2048) DEFAULT NULL,
  `stato`             ENUM('importato','elaborazione_ai','elaborato','da_revisione','approvato','pubblicato','scartato') NOT NULL DEFAULT 'importato',
  `confidenza_ai`     DECIMAL(5,4)  DEFAULT NULL,
  `fonte_id`          INT           DEFAULT NULL,
  `hash_contenuto`    VARCHAR(64)   DEFAULT NULL,
  `tag`               JSON          DEFAULT NULL,
  `approvato_da`      INT           DEFAULT NULL,
  `approvato_il`      DATETIME      DEFAULT NULL,
  `pubblicato_il`     DATETIME      DEFAULT NULL,
  `created_at`        TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`        TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_notizie_hash` (`hash_contenuto`),
  INDEX `idx_notizie_stato`           (`stato`),
  INDEX `idx_notizie_categoria`       (`categoria`),
  INDEX `idx_notizie_regione`         (`regione`),
  INDEX `idx_notizie_area_geografica` (`area_geografica`),
  INDEX `idx_notizie_fonte_id`        (`fonte_id`),
  INDEX `idx_notizie_pubblicato_il`   (`pubblicato_il`),
  CONSTRAINT `fk_notizie_fonte`      FOREIGN KEY (`fonte_id`)     REFERENCES `fonti_ai`(`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `fk_notizie_approvato`  FOREIGN KEY (`approvato_da`) REFERENCES `admin`(`id`)    ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='Notizie ambientali e green';

-- ============================================================
-- 5. alert – Alerts / warnings
-- ============================================================
CREATE TABLE IF NOT EXISTS `alert` (
  `id`                INT           NOT NULL AUTO_INCREMENT,
  `titolo`            VARCHAR(500)  NOT NULL,
  `titolo_ai`         VARCHAR(500)  DEFAULT NULL,
  `messaggio`         TEXT          DEFAULT NULL,
  `messaggio_ai`      TEXT          DEFAULT NULL,
  `tipo_alert`        ENUM('ambientale','meteo','normativo','scadenza','altro') DEFAULT NULL,
  `livello`           ENUM('info','attenzione','urgente','critico') DEFAULT NULL,
  `area_geografica`   VARCHAR(255)  DEFAULT NULL,
  `regione`           VARCHAR(100)  DEFAULT NULL,
  `data_inizio`       DATETIME      DEFAULT NULL,
  `data_fine`         DATETIME      DEFAULT NULL,
  `url_originale`     VARCHAR(2048) DEFAULT NULL,
  `stato`             ENUM('importato','elaborazione_ai','elaborato','da_revisione','approvato','pubblicato','scartato') NOT NULL DEFAULT 'importato',
  `confidenza_ai`     DECIMAL(5,4)  DEFAULT NULL,
  `fonte_id`          INT           DEFAULT NULL,
  `hash_contenuto`    VARCHAR(64)   DEFAULT NULL,
  `tag`               JSON          DEFAULT NULL,
  `approvato_da`      INT           DEFAULT NULL,
  `approvato_il`      DATETIME      DEFAULT NULL,
  `pubblicato_il`     DATETIME      DEFAULT NULL,
  `created_at`        TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`        TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_alert_hash` (`hash_contenuto`),
  INDEX `idx_alert_stato`           (`stato`),
  INDEX `idx_alert_tipo_alert`      (`tipo_alert`),
  INDEX `idx_alert_livello`         (`livello`),
  INDEX `idx_alert_regione`         (`regione`),
  INDEX `idx_alert_area_geografica` (`area_geografica`),
  INDEX `idx_alert_data_inizio`     (`data_inizio`),
  INDEX `idx_alert_fonte_id`        (`fonte_id`),
  CONSTRAINT `fk_alert_fonte`      FOREIGN KEY (`fonte_id`)     REFERENCES `fonti_ai`(`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `fk_alert_approvato`  FOREIGN KEY (`approvato_da`) REFERENCES `admin`(`id`)    ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='Allerte ambientali, meteo e normative';

-- ============================================================
-- 6. news_supertop – Featured / highlighted news
-- ============================================================
CREATE TABLE IF NOT EXISTS `news_supertop` (
  `id`                      INT           NOT NULL AUTO_INCREMENT,
  `titolo`                  VARCHAR(500)  NOT NULL,
  `titolo_ai`               VARCHAR(500)  DEFAULT NULL,
  `contenuto`               TEXT          DEFAULT NULL,
  `contenuto_ai`            TEXT          DEFAULT NULL,
  `sommario`                TEXT          DEFAULT NULL,
  `sommario_ai`             TEXT          DEFAULT NULL,
  `priorita`                INT           NOT NULL DEFAULT 0,
  `immagine_url`            VARCHAR(2048) DEFAULT NULL,
  `url_originale`           VARCHAR(2048) DEFAULT NULL,
  `stato`                   ENUM('importato','elaborazione_ai','elaborato','da_revisione','approvato','pubblicato','scartato') NOT NULL DEFAULT 'importato',
  `confidenza_ai`           DECIMAL(5,4)  DEFAULT NULL,
  `fonte_id`                INT           DEFAULT NULL,
  `hash_contenuto`          VARCHAR(64)   DEFAULT NULL,
  `tag`                     JSON          DEFAULT NULL,
  `data_inizio_evidenza`    DATETIME      DEFAULT NULL,
  `data_fine_evidenza`      DATETIME      DEFAULT NULL,
  `approvato_da`            INT           DEFAULT NULL,
  `approvato_il`            DATETIME      DEFAULT NULL,
  `pubblicato_il`           DATETIME      DEFAULT NULL,
  `created_at`              TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`              TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_news_supertop_hash` (`hash_contenuto`),
  INDEX `idx_news_supertop_stato`       (`stato`),
  INDEX `idx_news_supertop_priorita`    (`priorita`),
  INDEX `idx_news_supertop_evidenza`    (`data_inizio_evidenza`, `data_fine_evidenza`),
  INDEX `idx_news_supertop_fonte_id`    (`fonte_id`),
  CONSTRAINT `fk_news_supertop_fonte`     FOREIGN KEY (`fonte_id`)     REFERENCES `fonti_ai`(`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `fk_news_supertop_approvato` FOREIGN KEY (`approvato_da`) REFERENCES `admin`(`id`)    ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='Notizie in evidenza / super-top';

-- ============================================================
-- 7. impostazioni_ai – AI settings
-- ============================================================
CREATE TABLE IF NOT EXISTS `impostazioni_ai` (
  `id`            INT           NOT NULL AUTO_INCREMENT,
  `chiave`        VARCHAR(100)  NOT NULL,
  `valore`        TEXT          DEFAULT NULL,
  `tipo`          ENUM('modello','parametro','prompt','sistema') DEFAULT NULL,
  `task`          VARCHAR(100)  DEFAULT NULL,
  `descrizione`   TEXT          DEFAULT NULL,
  `updated_at`    TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `updated_by`    INT           DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_impostazioni_chiave` (`chiave`),
  CONSTRAINT `fk_impostazioni_updated_by` FOREIGN KEY (`updated_by`) REFERENCES `admin`(`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='Impostazioni e parametri per l''elaborazione AI';

-- ============================================================
-- 8. log_attivita – Activity log
-- ============================================================
CREATE TABLE IF NOT EXISTS `log_attivita` (
  `id`            BIGINT        NOT NULL AUTO_INCREMENT,
  `admin_id`      INT           DEFAULT NULL,
  `azione`        VARCHAR(100)  NOT NULL,
  `tipo_evento`   ENUM('login','logout','modifica_impostazione','modifica_fonte','approvazione','scarto','rielaborazione_ai','modifica_contenuto','errore_ai','errore_crawler','sistema') DEFAULT NULL,
  `entita_tipo`   VARCHAR(50)   DEFAULT NULL,
  `entita_id`     INT           DEFAULT NULL,
  `dettagli`      JSON          DEFAULT NULL,
  `ip`            VARCHAR(45)   DEFAULT NULL,
  `user_agent`    VARCHAR(500)  DEFAULT NULL,
  `created_at`    TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  INDEX `idx_log_admin_id`     (`admin_id`),
  INDEX `idx_log_tipo_evento`  (`tipo_evento`),
  INDEX `idx_log_entita`       (`entita_tipo`, `entita_id`),
  INDEX `idx_log_created_at`   (`created_at`),
  CONSTRAINT `fk_log_admin` FOREIGN KEY (`admin_id`) REFERENCES `admin`(`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='Log delle attività degli amministratori e del sistema';

-- ============================================================
-- 9. versioni_contenuti – Content versioning
-- ============================================================
CREATE TABLE IF NOT EXISTS `versioni_contenuti` (
  `id`              BIGINT        NOT NULL AUTO_INCREMENT,
  `entita_tipo`     ENUM('bando','notizia','alert','news_supertop') NOT NULL,
  `entita_id`       INT           NOT NULL,
  `versione`        INT           NOT NULL,
  `tipo_versione`   ENUM('originale','ai','admin') DEFAULT NULL,
  `contenuto_json`  JSON          NOT NULL,
  `autore_id`       INT           DEFAULT NULL,
  `note`            TEXT          DEFAULT NULL,
  `created_at`      TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_versione` (`entita_tipo`, `entita_id`, `versione`),
  INDEX `idx_versioni_entita` (`entita_tipo`, `entita_id`),
  CONSTRAINT `fk_versioni_autore` FOREIGN KEY (`autore_id`) REFERENCES `admin`(`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='Storico versioni dei contenuti (bandi, notizie, alert, news_supertop)';

-- ============================================================
-- 10. contenuti_originali – Original raw content from crawler
-- ============================================================
CREATE TABLE IF NOT EXISTS `contenuti_originali` (
  `id`                  BIGINT        NOT NULL AUTO_INCREMENT,
  `fonte_id`            INT           NOT NULL,
  `url`                 VARCHAR(2048) DEFAULT NULL,
  `titolo_originale`    TEXT          DEFAULT NULL,
  `contenuto_grezzo`    LONGTEXT      DEFAULT NULL,
  `contenuto_pulito`    TEXT          DEFAULT NULL,
  `formato_originale`   VARCHAR(50)   DEFAULT NULL,
  `hash_contenuto`      VARCHAR(64)   DEFAULT NULL,
  `metadata`            JSON          DEFAULT NULL,
  `elaborato`           BOOLEAN       NOT NULL DEFAULT FALSE,
  `created_at`          TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_contenuti_originali_hash` (`hash_contenuto`),
  INDEX `idx_contenuti_originali_fonte`    (`fonte_id`),
  INDEX `idx_contenuti_originali_elaborato`(`elaborato`),
  CONSTRAINT `fk_contenuti_originali_fonte` FOREIGN KEY (`fonte_id`) REFERENCES `fonti_ai`(`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='Contenuti grezzi scaricati dal crawler';

-- ============================================================
-- 11. contenuti_ai – AI processed content
-- ============================================================
CREATE TABLE IF NOT EXISTS `contenuti_ai` (
  `id`                      BIGINT        NOT NULL AUTO_INCREMENT,
  `contenuto_originale_id`  BIGINT        DEFAULT NULL,
  `task`                    VARCHAR(100)  NOT NULL,
  `modello_usato`           VARCHAR(50)   DEFAULT NULL,
  `prompt_usato`            TEXT          DEFAULT NULL,
  `input_json`              JSON          DEFAULT NULL,
  `output_json`             JSON          DEFAULT NULL,
  `token_input`             INT           DEFAULT NULL,
  `token_output`            INT           DEFAULT NULL,
  `tempo_risposta_ms`       INT           DEFAULT NULL,
  `confidenza`              DECIMAL(5,4)  DEFAULT NULL,
  `stato`                   ENUM('completato','errore','bassa_confidenza','dati_mancanti') DEFAULT NULL,
  `errore`                  TEXT          DEFAULT NULL,
  `created_at`              TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  INDEX `idx_contenuti_ai_originale`  (`contenuto_originale_id`),
  INDEX `idx_contenuti_ai_task`       (`task`),
  INDEX `idx_contenuti_ai_stato`      (`stato`),
  CONSTRAINT `fk_contenuti_ai_originale` FOREIGN KEY (`contenuto_originale_id`) REFERENCES `contenuti_originali`(`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='Risultati delle elaborazioni AI sui contenuti';

-- ============================================================
-- 12. scheduler_ai – AI scheduler / cron jobs
-- ============================================================
CREATE TABLE IF NOT EXISTS `scheduler_ai` (
  `id`                    INT           NOT NULL AUTO_INCREMENT,
  `nome`                  VARCHAR(100)  NOT NULL,
  `tipo`                  VARCHAR(50)   DEFAULT NULL,
  `configurazione`        JSON          DEFAULT NULL,
  `frequenza`             VARCHAR(50)   DEFAULT NULL,
  `ultimo_esecuzione`     DATETIME      DEFAULT NULL,
  `prossima_esecuzione`   DATETIME      DEFAULT NULL,
  `stato`                 ENUM('attivo','disattivo','in_esecuzione') NOT NULL DEFAULT 'attivo',
  `created_at`            TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`            TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  INDEX `idx_scheduler_stato`    (`stato`),
  INDEX `idx_scheduler_prossima` (`prossima_esecuzione`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='Pianificazione dei job AI e crawler';

-- ============================================================
-- 13. ai_jobs – AI job queue
-- ============================================================
CREATE TABLE IF NOT EXISTS `ai_jobs` (
  `id`              BIGINT        NOT NULL AUTO_INCREMENT,
  `tipo_job`        VARCHAR(100)  NOT NULL,
  `priorita`        INT           NOT NULL DEFAULT 5,
  `payload`         JSON          NOT NULL,
  `stato`           ENUM('in_attesa','in_esecuzione','completato','fallito','annullato') NOT NULL DEFAULT 'in_attesa',
  `tentativi`       INT           NOT NULL DEFAULT 0,
  `max_tentativi`   INT           NOT NULL DEFAULT 3,
  `errore`          TEXT          DEFAULT NULL,
  `risultato`       JSON          DEFAULT NULL,
  `worker_id`       VARCHAR(100)  DEFAULT NULL,
  `iniziato_il`     DATETIME      DEFAULT NULL,
  `completato_il`   DATETIME      DEFAULT NULL,
  `created_at`      TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  INDEX `idx_ai_jobs_stato`    (`stato`),
  INDEX `idx_ai_jobs_priorita` (`priorita`),
  INDEX `idx_ai_jobs_tipo`     (`tipo_job`),
  INDEX `idx_ai_jobs_queue`    (`stato`, `priorita`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='Coda dei job di elaborazione AI';

-- ============================================================
-- 14. ai_errori – AI errors
-- ============================================================
CREATE TABLE IF NOT EXISTS `ai_errori` (
  `id`              BIGINT        NOT NULL AUTO_INCREMENT,
  `job_id`          BIGINT        DEFAULT NULL,
  `tipo_errore`     VARCHAR(100)  DEFAULT NULL,
  `messaggio`       TEXT          DEFAULT NULL,
  `codice_errore`   VARCHAR(50)   DEFAULT NULL,
  `request_json`    JSON          DEFAULT NULL,
  `response_json`   JSON          DEFAULT NULL,
  `stack_trace`     TEXT          DEFAULT NULL,
  `risolto`         BOOLEAN       NOT NULL DEFAULT FALSE,
  `risolto_da`      INT           DEFAULT NULL,
  `risolto_il`      DATETIME      DEFAULT NULL,
  `created_at`      TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  INDEX `idx_ai_errori_job`      (`job_id`),
  INDEX `idx_ai_errori_tipo`     (`tipo_errore`),
  INDEX `idx_ai_errori_risolto`  (`risolto`),
  CONSTRAINT `fk_ai_errori_job`       FOREIGN KEY (`job_id`)     REFERENCES `ai_jobs`(`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `fk_ai_errori_risolto_da` FOREIGN KEY (`risolto_da`) REFERENCES `admin`(`id`)   ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='Log errori delle elaborazioni AI';

-- ============================================================
-- 15. ai_metriche – AI metrics
-- ============================================================
CREATE TABLE IF NOT EXISTS `ai_metriche` (
  `id`                      BIGINT        NOT NULL AUTO_INCREMENT,
  `data`                    DATE          NOT NULL,
  `task`                    VARCHAR(100)  DEFAULT NULL,
  `modello`                 VARCHAR(50)   DEFAULT NULL,
  `richieste_totali`        INT           NOT NULL DEFAULT 0,
  `richieste_successo`      INT           NOT NULL DEFAULT 0,
  `richieste_errore`        INT           NOT NULL DEFAULT 0,
  `token_totali_input`      BIGINT        NOT NULL DEFAULT 0,
  `token_totali_output`     BIGINT        NOT NULL DEFAULT 0,
  `tempo_medio_risposta_ms` INT           DEFAULT NULL,
  `confidenza_media`        DECIMAL(5,4)  DEFAULT NULL,
  `costo_stimato`           DECIMAL(10,4) DEFAULT NULL,
  `created_at`              TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_ai_metriche_giorno` (`data`, `task`, `modello`),
  INDEX `idx_ai_metriche_data` (`data`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='Metriche giornaliere delle elaborazioni AI';


-- ============================================================
-- TRIGGERS – Auto-versioning on content updates
-- ============================================================

-- Helper: compute next version number for a given entity
-- MySQL does not support functions inside triggers that query the
-- same table being modified, so we store the version count inside
-- versioni_contenuti which is a separate table – safe to query.

DELIMITER $$

-- ---- bandi ----
CREATE TRIGGER `trg_bandi_after_update`
AFTER UPDATE ON `bandi`
FOR EACH ROW
BEGIN
  DECLARE v_next INT;

  SELECT COALESCE(MAX(`versione`), 0) + 1
    INTO v_next
    FROM `versioni_contenuti`
   WHERE `entita_tipo` = 'bando'
     AND `entita_id`   = NEW.`id`;

  INSERT INTO `versioni_contenuti`
    (`entita_tipo`, `entita_id`, `versione`, `tipo_versione`, `contenuto_json`, `autore_id`)
  VALUES (
    'bando',
    NEW.`id`,
    v_next,
    CASE
      WHEN NOT (NEW.`titolo_ai` <=> OLD.`titolo_ai`) OR NOT (NEW.`descrizione_ai` <=> OLD.`descrizione_ai`) THEN 'ai'
      ELSE 'admin'
    END,
    JSON_OBJECT(
      'titolo',         NEW.`titolo`,
      'titolo_ai',      NEW.`titolo_ai`,
      'descrizione',    NEW.`descrizione`,
      'descrizione_ai', NEW.`descrizione_ai`,
      'stato',          NEW.`stato`,
      'tag',            NEW.`tag`
    ),
    NEW.`approvato_da`
  );
END$$

-- ---- notizie ----
CREATE TRIGGER `trg_notizie_after_update`
AFTER UPDATE ON `notizie`
FOR EACH ROW
BEGIN
  DECLARE v_next INT;

  SELECT COALESCE(MAX(`versione`), 0) + 1
    INTO v_next
    FROM `versioni_contenuti`
   WHERE `entita_tipo` = 'notizia'
     AND `entita_id`   = NEW.`id`;

  INSERT INTO `versioni_contenuti`
    (`entita_tipo`, `entita_id`, `versione`, `tipo_versione`, `contenuto_json`, `autore_id`)
  VALUES (
    'notizia',
    NEW.`id`,
    v_next,
    CASE
      WHEN NOT (NEW.`titolo_ai` <=> OLD.`titolo_ai`) OR NOT (NEW.`contenuto_ai` <=> OLD.`contenuto_ai`) THEN 'ai'
      ELSE 'admin'
    END,
    JSON_OBJECT(
      'titolo',       NEW.`titolo`,
      'titolo_ai',    NEW.`titolo_ai`,
      'contenuto',    NEW.`contenuto`,
      'contenuto_ai', NEW.`contenuto_ai`,
      'sommario',     NEW.`sommario`,
      'sommario_ai',  NEW.`sommario_ai`,
      'stato',        NEW.`stato`,
      'tag',          NEW.`tag`
    ),
    NEW.`approvato_da`
  );
END$$

-- ---- alert ----
CREATE TRIGGER `trg_alert_after_update`
AFTER UPDATE ON `alert`
FOR EACH ROW
BEGIN
  DECLARE v_next INT;

  SELECT COALESCE(MAX(`versione`), 0) + 1
    INTO v_next
    FROM `versioni_contenuti`
   WHERE `entita_tipo` = 'alert'
     AND `entita_id`   = NEW.`id`;

  INSERT INTO `versioni_contenuti`
    (`entita_tipo`, `entita_id`, `versione`, `tipo_versione`, `contenuto_json`, `autore_id`)
  VALUES (
    'alert',
    NEW.`id`,
    v_next,
    CASE
      WHEN NOT (NEW.`titolo_ai` <=> OLD.`titolo_ai`) OR NOT (NEW.`messaggio_ai` <=> OLD.`messaggio_ai`) THEN 'ai'
      ELSE 'admin'
    END,
    JSON_OBJECT(
      'titolo',       NEW.`titolo`,
      'titolo_ai',    NEW.`titolo_ai`,
      'messaggio',    NEW.`messaggio`,
      'messaggio_ai', NEW.`messaggio_ai`,
      'tipo_alert',   NEW.`tipo_alert`,
      'livello',      NEW.`livello`,
      'stato',        NEW.`stato`,
      'tag',          NEW.`tag`
    ),
    NEW.`approvato_da`
  );
END$$

-- ---- news_supertop ----
CREATE TRIGGER `trg_news_supertop_after_update`
AFTER UPDATE ON `news_supertop`
FOR EACH ROW
BEGIN
  DECLARE v_next INT;

  SELECT COALESCE(MAX(`versione`), 0) + 1
    INTO v_next
    FROM `versioni_contenuti`
   WHERE `entita_tipo` = 'news_supertop'
     AND `entita_id`   = NEW.`id`;

  INSERT INTO `versioni_contenuti`
    (`entita_tipo`, `entita_id`, `versione`, `tipo_versione`, `contenuto_json`, `autore_id`)
  VALUES (
    'news_supertop',
    NEW.`id`,
    v_next,
    CASE
      WHEN NOT (NEW.`titolo_ai` <=> OLD.`titolo_ai`) OR NOT (NEW.`contenuto_ai` <=> OLD.`contenuto_ai`) THEN 'ai'
      ELSE 'admin'
    END,
    JSON_OBJECT(
      'titolo',       NEW.`titolo`,
      'titolo_ai',    NEW.`titolo_ai`,
      'contenuto',    NEW.`contenuto`,
      'contenuto_ai', NEW.`contenuto_ai`,
      'sommario',     NEW.`sommario`,
      'sommario_ai',  NEW.`sommario_ai`,
      'priorita',     NEW.`priorita`,
      'stato',        NEW.`stato`,
      'tag',          NEW.`tag`
    ),
    NEW.`approvato_da`
  );
END$$


-- ============================================================
-- TRIGGERS – Activity logging for important state changes
-- ============================================================

-- Log approval and publication events on bandi
CREATE TRIGGER `trg_bandi_log_stato`
AFTER UPDATE ON `bandi`
FOR EACH ROW
BEGIN
  IF NEW.`stato` <> OLD.`stato` THEN
    INSERT INTO `log_attivita`
      (`admin_id`, `azione`, `tipo_evento`, `entita_tipo`, `entita_id`, `dettagli`)
    VALUES (
      NEW.`approvato_da`,
      CONCAT('stato_bando_', NEW.`stato`),
      CASE
        WHEN NEW.`stato` IN ('approvato','pubblicato') THEN 'approvazione'
        WHEN NEW.`stato` = 'scartato'                  THEN 'scarto'
        WHEN NEW.`stato` = 'elaborazione_ai'           THEN 'rielaborazione_ai'
        ELSE 'modifica_contenuto'
      END,
      'bando',
      NEW.`id`,
      JSON_OBJECT('stato_precedente', OLD.`stato`, 'stato_nuovo', NEW.`stato`)
    );
  END IF;
END$$

-- Log approval and publication events on notizie
CREATE TRIGGER `trg_notizie_log_stato`
AFTER UPDATE ON `notizie`
FOR EACH ROW
BEGIN
  IF NEW.`stato` <> OLD.`stato` THEN
    INSERT INTO `log_attivita`
      (`admin_id`, `azione`, `tipo_evento`, `entita_tipo`, `entita_id`, `dettagli`)
    VALUES (
      NEW.`approvato_da`,
      CONCAT('stato_notizia_', NEW.`stato`),
      CASE
        WHEN NEW.`stato` IN ('approvato','pubblicato') THEN 'approvazione'
        WHEN NEW.`stato` = 'scartato'                  THEN 'scarto'
        WHEN NEW.`stato` = 'elaborazione_ai'           THEN 'rielaborazione_ai'
        ELSE 'modifica_contenuto'
      END,
      'notizia',
      NEW.`id`,
      JSON_OBJECT('stato_precedente', OLD.`stato`, 'stato_nuovo', NEW.`stato`)
    );
  END IF;
END$$

-- Log state changes on alert
CREATE TRIGGER `trg_alert_log_stato`
AFTER UPDATE ON `alert`
FOR EACH ROW
BEGIN
  IF NEW.`stato` <> OLD.`stato` THEN
    INSERT INTO `log_attivita`
      (`admin_id`, `azione`, `tipo_evento`, `entita_tipo`, `entita_id`, `dettagli`)
    VALUES (
      NEW.`approvato_da`,
      CONCAT('stato_alert_', NEW.`stato`),
      CASE
        WHEN NEW.`stato` IN ('approvato','pubblicato') THEN 'approvazione'
        WHEN NEW.`stato` = 'scartato'                  THEN 'scarto'
        WHEN NEW.`stato` = 'elaborazione_ai'           THEN 'rielaborazione_ai'
        ELSE 'modifica_contenuto'
      END,
      'alert',
      NEW.`id`,
      JSON_OBJECT('stato_precedente', OLD.`stato`, 'stato_nuovo', NEW.`stato`)
    );
  END IF;
END$$

-- Log state changes on news_supertop
CREATE TRIGGER `trg_news_supertop_log_stato`
AFTER UPDATE ON `news_supertop`
FOR EACH ROW
BEGIN
  IF NEW.`stato` <> OLD.`stato` THEN
    INSERT INTO `log_attivita`
      (`admin_id`, `azione`, `tipo_evento`, `entita_tipo`, `entita_id`, `dettagli`)
    VALUES (
      NEW.`approvato_da`,
      CONCAT('stato_news_supertop_', NEW.`stato`),
      CASE
        WHEN NEW.`stato` IN ('approvato','pubblicato') THEN 'approvazione'
        WHEN NEW.`stato` = 'scartato'                  THEN 'scarto'
        WHEN NEW.`stato` = 'elaborazione_ai'           THEN 'rielaborazione_ai'
        ELSE 'modifica_contenuto'
      END,
      'news_supertop',
      NEW.`id`,
      JSON_OBJECT('stato_precedente', OLD.`stato`, 'stato_nuovo', NEW.`stato`)
    );
  END IF;
END$$

DELIMITER ;

-- ============================================================
-- Re-enable foreign key checks
-- ============================================================
SET FOREIGN_KEY_CHECKS = 1;