-- ============================================================================ -- SpesaMIA — Flyer Engine v1.0 — migrazione 001 -- Si applica sopra le 17 tabelle esistenti (schema.sql). NON modifica né cancella dati: -- * crea 5 tabelle nuove (flyer_sources, flyers, flyer_source_stores, flyer_stores, flyer_checks); -- * aggiunge solo colonne/indici/chiavi NULLABLE a price_staging e prices (il motore di ricerca -- esistente elenca le colonne per nome, quindi non cambia comportamento); -- * è ripetibile: ogni ALTER parte solo se l'elemento non esiste già. -- Compatibile con MySQL 8 e MariaDB 10.x. Da importare in phpMyAdmin dentro il database già selezionato. -- Non contiene CREATE DATABASE né USE. I dati demo (is_demo = 1) non vengono toccati. -- ============================================================================ SET NAMES utf8mb4; -- ---------- 1. Registro delle fonti (un connettore per fonte) ---------- CREATE TABLE IF NOT EXISTS flyer_sources ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, slug VARCHAR(40) NOT NULL, name VARCHAR(160) NOT NULL, chain_id INT UNSIGNED NULL, source_id INT UNSIGNED NULL, -- riga in `sources` usata come provenienza dei prezzi connector VARCHAR(40) NOT NULL, -- mersi | boningros | superconveniente | restricted | inbox entry_url VARCHAR(500) NULL, allowed_hosts VARCHAR(500) NOT NULL DEFAULT '', -- host consentiti, separati da virgola acquisition ENUM('auto','manual_inbox','blocked') NOT NULL DEFAULT 'blocked', acquisition_note VARCHAR(500) NULL, -- perché la modalità è auto/manuale/bloccata match_keywords VARCHAR(200) NULL, -- se la pagina elenca più volantini (per zona), tiene solo quelli che citano una di queste parole (separate da virgola) is_enabled TINYINT(1) NOT NULL DEFAULT 0, -- 0 finché non lo abiliti tu da CLI next_check_at DATETIME NULL, last_checked_at DATETIME NULL, last_success_at DATETIME NULL, current_flyer_id INT UNSIGNED NULL, consecutive_misses SMALLINT UNSIGNED NOT NULL DEFAULT 0, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_flyer_source_slug (slug), INDEX idx_flyer_source_due (is_enabled, acquisition, next_check_at), CONSTRAINT fk_fsrc_chain FOREIGN KEY (chain_id) REFERENCES chains(id) ON DELETE SET NULL, CONSTRAINT fk_fsrc_source FOREIGN KEY (source_id) REFERENCES sources(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ---------- 2. Volantini: uno per documento, mai cancellati (storico) ---------- CREATE TABLE IF NOT EXISTS flyers ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, flyer_source_id INT UNSIGNED NOT NULL, title VARCHAR(200) NULL, url VARCHAR(500) NOT NULL, url_hash CHAR(64) NOT NULL, -- sha256 dell'URL format ENUM('pdf','images','html','viewer','unknown') NOT NULL DEFAULT 'unknown', file_hash CHAR(64) NULL, -- sha256 del contenuto scaricato: evita analisi ripetute file_path VARCHAR(255) NULL, -- relativo a storage/ (cartella privata) file_size INT UNSIGNED NULL, page_count SMALLINT UNSIGNED NULL, etag VARCHAR(200) NULL, last_modified VARCHAR(80) NULL, valid_from DATE NULL, valid_to DATE NULL, dates_inferred TINYINT(1) NOT NULL DEFAULT 0, -- 1 = anno non stampato, dedotto coverage_note VARCHAR(500) NULL, -- punti vendita dichiarati nel volantino status ENUM('discovered','downloaded','extracting','in_review','published','failed','skipped') NOT NULL DEFAULT 'discovered', status_note VARCHAR(500) NULL, batch_id INT UNSIGNED NULL, pages_extracted SMALLINT UNSIGNED NOT NULL DEFAULT 0, offers_found INT UNSIGNED NOT NULL DEFAULT 0, attempts SMALLINT UNSIGNED NOT NULL DEFAULT 0, discovered_at DATETIME NOT NULL, downloaded_at DATETIME NULL, extracted_at DATETIME NULL, expired_at DATETIME NULL, -- impostata alla scadenza; la riga resta next_check_at DATETIME NULL, -- prossima verifica di questo volantino UNIQUE KEY uq_flyer_file (flyer_source_id, file_hash), INDEX idx_flyer_url (flyer_source_id, url_hash), INDEX idx_flyer_status (status, valid_to), INDEX idx_flyer_next (next_check_at), CONSTRAINT fk_flyer_source FOREIGN KEY (flyer_source_id) REFERENCES flyer_sources(id) ON DELETE RESTRICT, CONSTRAINT fk_flyer_batch FOREIGN KEY (batch_id) REFERENCES import_batches(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ---------- 3. Punti vendita: candidati per fonte e conferma per volantino ---------- CREATE TABLE IF NOT EXISTS flyer_source_stores ( flyer_source_id INT UNSIGNED NOT NULL, store_id INT UNSIGNED NOT NULL, PRIMARY KEY (flyer_source_id, store_id), CONSTRAINT fk_fss_source FOREIGN KEY (flyer_source_id) REFERENCES flyer_sources(id) ON DELETE CASCADE, CONSTRAINT fk_fss_store FOREIGN KEY (store_id) REFERENCES stores(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS flyer_stores ( flyer_id INT UNSIGNED NOT NULL, store_id INT UNSIGNED NOT NULL, verified TINYINT(1) NOT NULL DEFAULT 0, -- solo i collegamenti verificati permettono la pubblicazione verified_by VARCHAR(80) NULL, verified_at DATETIME NULL, evidence VARCHAR(300) NULL, -- es. «Villafranca Tirrena elencata in copertina, pag. 1» PRIMARY KEY (flyer_id, store_id), CONSTRAINT fk_fs_flyer FOREIGN KEY (flyer_id) REFERENCES flyers(id) ON DELETE CASCADE, CONSTRAINT fk_fs_store FOREIGN KEY (store_id) REFERENCES stores(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ---------- 4. Registro dei controlli (audit privato) ---------- CREATE TABLE IF NOT EXISTS flyer_checks ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, flyer_source_id INT UNSIGNED NOT NULL, checked_at DATETIME NOT NULL, outcome ENUM('found_new','unchanged','not_found','blocked','manual','error','skipped') NOT NULL, http_status SMALLINT NULL, flyer_id INT UNSIGNED NULL, message VARCHAR(300) NULL, duration_ms INT UNSIGNED NULL, INDEX idx_fchk_source (flyer_source_id, checked_at), CONSTRAINT fk_fchk_source FOREIGN KEY (flyer_source_id) REFERENCES flyer_sources(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ---------- 5. Estensioni NULLABLE delle tabelle esistenti ---------- SET @s := (SELECT IF(COUNT(*) = 0, 'ALTER TABLE price_staging ADD COLUMN flyer_id INT UNSIGNED NULL', 'DO 0') FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'price_staging' AND COLUMN_NAME = 'flyer_id'); PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st; SET @s := (SELECT IF(COUNT(*) = 0, 'ALTER TABLE price_staging ADD COLUMN page_no SMALLINT UNSIGNED NULL', 'DO 0') FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'price_staging' AND COLUMN_NAME = 'page_no'); PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st; SET @s := (SELECT IF(COUNT(*) = 0, 'ALTER TABLE price_staging ADD COLUMN dedupe_hash CHAR(64) NULL', 'DO 0') FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'price_staging' AND COLUMN_NAME = 'dedupe_hash'); PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st; SET @s := (SELECT IF(COUNT(*) = 0, 'ALTER TABLE price_staging ADD COLUMN price_basis ENUM(''pack'',''kg'',''hg'',''l'',''piece'') NULL', 'DO 0') FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'price_staging' AND COLUMN_NAME = 'price_basis'); PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st; SET @s := (SELECT IF(COUNT(*) = 0, 'ALTER TABLE price_staging ADD COLUMN conditions JSON NULL', 'DO 0') FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'price_staging' AND COLUMN_NAME = 'conditions'); PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st; SET @s := (SELECT IF(COUNT(*) = 0, 'ALTER TABLE price_staging ADD COLUMN review_note VARCHAR(300) NULL', 'DO 0') FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'price_staging' AND COLUMN_NAME = 'review_note'); PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st; SET @s := (SELECT IF(COUNT(*) = 0, 'ALTER TABLE price_staging ADD COLUMN published_price_id BIGINT UNSIGNED NULL', 'DO 0') FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'price_staging' AND COLUMN_NAME = 'published_price_id'); PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st; SET @s := (SELECT IF(COUNT(*) = 0, 'ALTER TABLE price_staging ADD UNIQUE KEY uq_staging_dedupe (dedupe_hash)', 'DO 0') FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'price_staging' AND INDEX_NAME = 'uq_staging_dedupe'); PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st; SET @s := (SELECT IF(COUNT(*) = 0, 'ALTER TABLE price_staging ADD INDEX idx_staging_flyer (flyer_id, status)', 'DO 0') FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'price_staging' AND INDEX_NAME = 'idx_staging_flyer'); PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st; SET @s := (SELECT IF(COUNT(*) = 0, 'ALTER TABLE price_staging ADD CONSTRAINT fk_staging_flyer FOREIGN KEY (flyer_id) REFERENCES flyers(id) ON DELETE SET NULL', 'DO 0') FROM information_schema.TABLE_CONSTRAINTS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'price_staging' AND CONSTRAINT_NAME = 'fk_staging_flyer' AND CONSTRAINT_TYPE = 'FOREIGN KEY'); PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st; SET @s := (SELECT IF(COUNT(*) = 0, 'ALTER TABLE flyer_sources ADD COLUMN match_keywords VARCHAR(200) NULL', 'DO 0') FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'flyer_sources' AND COLUMN_NAME = 'match_keywords'); PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st; SET @s := (SELECT IF(COUNT(*) = 0, 'ALTER TABLE prices ADD COLUMN flyer_id INT UNSIGNED NULL', 'DO 0') FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'prices' AND COLUMN_NAME = 'flyer_id'); PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st; SET @s := (SELECT IF(COUNT(*) = 0, 'ALTER TABLE prices ADD COLUMN staging_id BIGINT UNSIGNED NULL', 'DO 0') FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'prices' AND COLUMN_NAME = 'staging_id'); PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st; SET @s := (SELECT IF(COUNT(*) = 0, 'ALTER TABLE prices ADD COLUMN price_basis ENUM(''pack'',''kg'',''hg'',''l'',''piece'') NULL', 'DO 0') FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'prices' AND COLUMN_NAME = 'price_basis'); PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st; SET @s := (SELECT IF(COUNT(*) = 0, 'ALTER TABLE prices ADD COLUMN conditions JSON NULL', 'DO 0') FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'prices' AND COLUMN_NAME = 'conditions'); PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st; SET @s := (SELECT IF(COUNT(*) = 0, 'ALTER TABLE prices ADD UNIQUE KEY uq_prices_staging_store (staging_id, store_id)', 'DO 0') FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'prices' AND INDEX_NAME = 'uq_prices_staging_store'); PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st; SET @s := (SELECT IF(COUNT(*) = 0, 'ALTER TABLE prices ADD INDEX idx_prices_valid_to (valid_to)', 'DO 0') FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'prices' AND INDEX_NAME = 'idx_prices_valid_to'); PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st; SET @s := (SELECT IF(COUNT(*) = 0, 'ALTER TABLE prices ADD INDEX idx_prices_flyer (flyer_id)', 'DO 0') FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'prices' AND INDEX_NAME = 'idx_prices_flyer'); PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st; SET @s := (SELECT IF(COUNT(*) = 0, 'ALTER TABLE prices ADD CONSTRAINT fk_prices_flyer FOREIGN KEY (flyer_id) REFERENCES flyers(id) ON DELETE SET NULL', 'DO 0') FROM information_schema.TABLE_CONSTRAINTS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'prices' AND CONSTRAINT_NAME = 'fk_prices_flyer' AND CONSTRAINT_TYPE = 'FOREIGN KEY'); PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st; -- Fine migrazione 001.