-- Tabelle applicative del modulo crmp_comunicazione.
-- Nessun hook_schema, nessun file .install: il codice runtime assume che queste tabelle esistano e
-- lo segnala esplicitamente se mancano.
--
-- Sta qui e non nella root perche' non e' un file da eseguire su un ambiente: queste tabelle
-- esistono in produzione, quindi il sync del DB di staging le riporta da se'. Serve alla suite
-- Kernel, che lo esegue per costruire lo schema (`TabelleComunicazioneTrait`) invece di
-- ridichiarare le tabelle nel test: una copia scritta a parte seguirebbe il codice e mancherebbe
-- proprio la divergenza che il test dovrebbe scoprire. Resta comunque il modo per ricreare le
-- tabelle a mano dove mancassero.
--
-- Non contiene ALTER, quindi non e' rigiocabile dove le tabelle esistono gia'.


-- Coda degli invii WhatsApp richiesti da una procedura: una riga per invio da fare, che il wizard
-- serve all'operatore competente. La riga resta anche dopo l'invio (`sent_at` valorizzato), perche'
-- e' cio' che tiene lo storico di chi ha spedito cosa.
CREATE TABLE crmp_wa_queue (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  nid_lavorazione INT UNSIGNED NOT NULL,
  nid_anagrafica INT UNSIGNED NOT NULL,
  tid_template INT UNSIGNED NOT NULL,
  tid_azione_proc INT UNSIGNED NOT NULL,
  target_uid INT UNSIGNED NOT NULL DEFAULT 0,        -- destinatario personale, 0 se va al reparto
  target_reparto INT UNSIGNED NOT NULL DEFAULT 0,    -- coda di reparto, 0 se e' personale
  tid_mandato INT UNSIGNED NOT NULL DEFAULT 0,
  created INT UNSIGNED NOT NULL,
  created_uid INT UNSIGNED NOT NULL,
  sent_at INT UNSIGNED NOT NULL DEFAULT 0,           -- 0 = ancora da inviare
  sent_uid INT UNSIGNED NOT NULL DEFAULT 0,
  nid_comunicazione INT UNSIGNED DEFAULT NULL,       -- la comunicazione nata dall'invio
  dedup_key VARCHAR(120) DEFAULT NULL,
  PRIMARY KEY (id),
  -- L'unicita' sulla chiave di deduplica e' cio' che impedisce a due esecuzioni della stessa
  -- procedura di accodare due volte lo stesso invio.
  UNIQUE KEY uniq_pending (dedup_key),
  KEY idx_lavorazione (nid_lavorazione),
  KEY idx_coda_personale (target_uid, sent_at),
  KEY idx_coda_reparto (target_reparto, tid_mandato, sent_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


-- Prenotazioni del wizard: il contatto che un operatore sta lavorando, perche' due operatori non
-- ricevano lo stesso. Una riga per anagrafica e tipo di bacino; `booked_at` regge la scadenza (TTL).
CREATE TABLE wizard_bookings (
  anagrafica_id INT NOT NULL,
  entity_kind VARCHAR(16) NOT NULL DEFAULT 'crm',    -- 'crm' oppure il bacino contatti
  user_id INT UNSIGNED NOT NULL,
  nid_azioneCom INT NOT NULL,
  booked_at INT UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (entity_kind, anagrafica_id),
  KEY idx_ttl (booked_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


-- ---------------------------------------------------------------------------
-- Bacino dei contatti freddi (`fr__*`).
--
-- Anagrafiche mai entrate nel CRM: numeri e indirizzi acquistati o importati da archivi esterni,
-- che il wizard propone agli operatori. Sono normalizzate perche' lo stesso numero ricorre in piu'
-- archivi: telefoni ed email vivono in tabelle proprie e si collegano al contatto per associazione,
-- cosi' che marcare un numero come duplicato del CRM valga per tutti i contatti che lo portano.
-- ---------------------------------------------------------------------------

-- Il contatto freddo. Nessun vincolo di unicita': lo stesso nominativo puo' arrivare da piu'
-- archivi, ed e' il collegamento a telefono/email a dire se e' la stessa persona.
CREATE TABLE fr__anagrafica (
  ID INT NOT NULL AUTO_INCREMENT,
  cognome VARCHAR(255) DEFAULT NULL,
  nome VARCHAR(255) DEFAULT NULL,
  data_di_Nascita VARCHAR(255) DEFAULT NULL,
  sesso VARCHAR(10) DEFAULT NULL,
  cf VARCHAR(50) DEFAULT NULL,
  indirizzo VARCHAR(255) DEFAULT NULL,
  citta VARCHAR(100) DEFAULT NULL,
  cap VARCHAR(15) DEFAULT NULL,
  provincia VARCHAR(300) DEFAULT NULL,
  impegno_2006_2023 TINYINT(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (ID),
  KEY cognome (cognome),
  KEY nome (nome),
  KEY cf (cf),
  KEY impegno_2006_2023 (impegno_2006_2023)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Un numero di telefono, una volta sola. `not_valid_for_crmp` lo esclude dalle chiamate (blacklist),
-- `is_crmp_duplicate` segnala che quel numero esiste gia' su un'anagrafica del CRM.
CREATE TABLE fr__telefono (
  ID INT NOT NULL AUTO_INCREMENT,
  tel_value VARCHAR(60) DEFAULT NULL,
  not_valid_for_crmp TINYINT(1) DEFAULT NULL,
  is_crmp_duplicate TINYINT(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (ID),
  KEY tel_value (tel_value),
  KEY not_valid_for_crmp (not_valid_for_crmp, is_crmp_duplicate)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Come sopra per gli indirizzi email; `non_esiste` marca quelli respinti dal server destinatario.
CREATE TABLE fr__email (
  ID INT NOT NULL AUTO_INCREMENT,
  email_value VARCHAR(255) DEFAULT NULL,
  non_esiste TINYINT(1) DEFAULT NULL,
  is_crmp_duplicate TINYINT(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (ID),
  KEY email_value (email_value),
  KEY non_esiste (non_esiste, is_crmp_duplicate)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE fr__anagrafica_telefono (
  anagrafica_id INT NOT NULL,
  telefono_id INT NOT NULL,
  PRIMARY KEY (anagrafica_id, telefono_id),
  KEY telefono_id (telefono_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE fr__anagrafica_email (
  anagrafica_id INT NOT NULL,
  email_id INT NOT NULL,
  PRIMARY KEY (anagrafica_id, email_id),
  KEY email_id (email_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- L'archivio di provenienza. `tid_campagna_crmp` la lega alla campagna del CRM, quando esiste.
CREATE TABLE fr__campagna (
  ID INT NOT NULL AUTO_INCREMENT,
  nome_campagna VARCHAR(500) DEFAULT NULL,
  tid_campagna_crmp INT DEFAULT NULL,
  PRIMARY KEY (ID),
  UNIQUE KEY nome_campagna (nome_campagna),
  KEY tid_campagna_crmp (tid_campagna_crmp)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE fr__campagna_anagrafica (
  campagna_id INT NOT NULL,
  anagrafica_id INT NOT NULL,
  PRIMARY KEY (campagna_id, anagrafica_id),
  KEY anagrafica_id (anagrafica_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Storico dei contatti tentati su un numero: una riga per tentativo. `to_blacklist` e' la richiesta
-- di non richiamare piu' quel numero, `comunicazione_crm_id` il nodo comunicazione nato dal contatto
-- quando il freddo e' poi diventato un'anagrafica del CRM.
CREATE TABLE fr__esito_contatto (
  id INT NOT NULL AUTO_INCREMENT,
  telefono_id INT NOT NULL,
  user_id INT DEFAULT NULL,
  nid_azione_commerciale INT DEFAULT NULL,
  data INT NOT NULL,
  esito_contatto TINYINT(1) DEFAULT NULL,
  to_blacklist TINYINT(1) DEFAULT NULL,
  tid_campagna VARCHAR(300) DEFAULT NULL,
  num_usato VARCHAR(15) DEFAULT NULL,
  tid_template INT DEFAULT NULL,
  email_id INT DEFAULT NULL,
  tid_tipo_com INT DEFAULT NULL,
  comunicazione_crm_id INT DEFAULT NULL,
  PRIMARY KEY (id),
  KEY telefono_id (telefono_id, data, to_blacklist, email_id, tid_tipo_com),
  KEY idx_comunicazione_crm (comunicazione_crm_id),
  KEY idx_null_crm_tipo_data (comunicazione_crm_id, tid_tipo_com, data)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Il motivo per cui un contatto e' stato chiuso, testuale e deduplicato sul valore.
CREATE TABLE fr__motivo_chiusura (
  ID INT NOT NULL AUTO_INCREMENT,
  motivo_value VARCHAR(80) DEFAULT NULL,
  PRIMARY KEY (ID),
  UNIQUE KEY motivo_value (motivo_value)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE fr__anagrafica_motivo_chiusura (
  anagrafica_id INT NOT NULL,
  motivo_chiusura_id INT NOT NULL,
  PRIMARY KEY (anagrafica_id, motivo_chiusura_id),
  KEY motivo_chiusura_id (motivo_chiusura_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;