-- SpesaMIA — schema MySQL 8 / MariaDB 10.4+
-- Da importare DENTRO un database già esistente (su cPanel: crealo da «Database MySQL»,
-- poi in phpMyAdmin selezionalo a sinistra e usa Importa). Non contiene CREATE DATABASE né USE.
-- È ripetibile: le tabelle già presenti non vengono toccate.
-- Principio: nessun prezzo è mostrato se non ha provenienza (source_id), data di rilevazione
-- e stato 'verified'. I dati incerti restano in price_staging fino alla revisione.

-- ---------- Anagrafiche ----------

CREATE TABLE IF NOT EXISTS chains (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name        VARCHAR(120) NOT NULL,
  slug        VARCHAR(120) NOT NULL UNIQUE,
  website     VARCHAR(255) NULL,
  is_demo     TINYINT(1) NOT NULL DEFAULT 0,
  created_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS stores (
  id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  chain_id      INT UNSIGNED NULL,
  name          VARCHAR(160) NOT NULL,
  address       VARCHAR(200) NULL,
  city          VARCHAR(100) NOT NULL,
  province      CHAR(2) NULL,
  postal_code   VARCHAR(10) NULL,
  lat           DECIMAL(9,6) NOT NULL,
  lng           DECIMAL(9,6) NOT NULL,
  opening_hours JSON NULL,
  is_active     TINYINT(1) NOT NULL DEFAULT 1,
  is_demo       TINYINT(1) NOT NULL DEFAULT 0,
  created_at    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_store_chain FOREIGN KEY (chain_id) REFERENCES chains(id) ON DELETE SET NULL,
  INDEX idx_store_geo (is_active, lat, lng),
  INDEX idx_store_city (city, province)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS products (
  id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  ean           VARCHAR(14) NULL UNIQUE,
  name          VARCHAR(200) NOT NULL,
  brand         VARCHAR(100) NULL,
  category      VARCHAR(100) NULL,
  size_label    VARCHAR(60) NULL,                 -- es. "6×1,5 l"
  base_unit     ENUM('kg','l','pz') NOT NULL,     -- unità di confronto
  base_qty      DECIMAL(10,4) NOT NULL,           -- quantità totale espressa in base_unit
  search_text   VARCHAR(500) NOT NULL,            -- nome+marca+alias normalizzati (minuscolo, senza accenti)
  is_demo       TINYINT(1) NOT NULL DEFAULT 0,
  created_at    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_prod_brand (brand),
  FULLTEXT KEY ft_prod_search (search_text)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS product_aliases (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  product_id  INT UNSIGNED NOT NULL,
  alias       VARCHAR(160) NOT NULL,
  CONSTRAINT fk_alias_prod FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
  INDEX idx_alias (alias)
) ENGINE=InnoDB;

-- ---------- Fonti e importazioni ----------

CREATE TABLE IF NOT EXISTS sources (
  id             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name           VARCHAR(160) NOT NULL,
  type           ENUM('feed','flyer','catalog','partner_api','manual') NOT NULL,
  url            VARCHAR(500) NULL,
  license_note   VARCHAR(500) NULL,               -- base giuridica/autorizzazione all'uso
  is_authorized  TINYINT(1) NOT NULL DEFAULT 0,   -- 0 = non utilizzabile in produzione
  is_demo        TINYINT(1) NOT NULL DEFAULT 0,
  last_import_at DATETIME NULL,
  created_at     TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS admin_users (
  id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  email         VARCHAR(190) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  role          ENUM('admin','editor','viewer') NOT NULL DEFAULT 'editor',
  totp_secret   VARCHAR(64) NULL,
  created_at    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS import_batches (
  id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  source_id     INT UNSIGNED NOT NULL,
  status        ENUM('running','review','approved','rejected') NOT NULL DEFAULT 'running',
  rows_total    INT UNSIGNED NOT NULL DEFAULT 0,
  rows_ok       INT UNSIGNED NOT NULL DEFAULT 0,
  rows_flagged  INT UNSIGNED NOT NULL DEFAULT 0,
  notes         TEXT NULL,
  started_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  finished_at   DATETIME NULL,
  CONSTRAINT fk_batch_source FOREIGN KEY (source_id) REFERENCES sources(id)
) ENGINE=InnoDB;

-- Area di quarantena: ogni riga importata passa da qui. Le righe con confidenza bassa
-- o abbinamenti ambigui restano 'pending' finché un revisore non le approva.
CREATE TABLE IF NOT EXISTS price_staging (
  id                 BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  batch_id           INT UNSIGNED NOT NULL,
  raw_data           JSON NOT NULL,
  matched_product_id INT UNSIGNED NULL,
  matched_store_id   INT UNSIGNED NULL,
  confidence         DECIMAL(4,3) NOT NULL DEFAULT 0,
  status             ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending',
  reviewer_id        INT UNSIGNED NULL,
  reviewed_at        DATETIME NULL,
  CONSTRAINT fk_stg_batch FOREIGN KEY (batch_id) REFERENCES import_batches(id) ON DELETE CASCADE,
  INDEX idx_stg_status (status, confidence)
) ENGINE=InnoDB;

-- ---------- Prezzi ----------

CREATE TABLE IF NOT EXISTS prices (
  id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  product_id      INT UNSIGNED NOT NULL,
  store_id        INT UNSIGNED NOT NULL,
  source_id       INT UNSIGNED NOT NULL,          -- provenienza obbligatoria
  batch_id        INT UNSIGNED NULL,
  price           DECIMAL(8,2) NOT NULL,
  unit_price      DECIMAL(9,3) NOT NULL,          -- prezzo per base_unit (€/kg, €/l, €/pz)
  is_promo        TINYINT(1) NOT NULL DEFAULT 0,
  promo_label     VARCHAR(120) NULL,
  valid_from      DATE NULL,
  valid_to        DATE NULL,                      -- NULL = prezzo a scaffale senza scadenza dichiarata
  observed_at     DATETIME NOT NULL,              -- data di rilevazione/aggiornamento
  verified_status ENUM('verified','unverified') NOT NULL DEFAULT 'unverified',
  source_url      VARCHAR(500) NULL,
  is_demo         TINYINT(1) NOT NULL DEFAULT 0,
  CONSTRAINT fk_price_prod   FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
  CONSTRAINT fk_price_store  FOREIGN KEY (store_id)   REFERENCES stores(id)   ON DELETE CASCADE,
  CONSTRAINT fk_price_source FOREIGN KEY (source_id)  REFERENCES sources(id),
  INDEX idx_price_lookup (product_id, store_id, verified_status, valid_to),
  INDEX idx_price_store (store_id, verified_status)
) ENGINE=InnoDB;

-- ---------- Utenti facoltativi, liste, newsletter ----------

CREATE TABLE IF NOT EXISTS users (
  id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  email         VARCHAR(190) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NULL,                -- NULL se accesso con link via email
  home_lat      DECIMAL(9,6) NULL,
  home_lng      DECIMAL(9,6) NULL,
  created_at    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  deleted_at    DATETIME NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS shopping_lists (
  id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id    INT UNSIGNED NOT NULL,
  name       VARCHAR(120) NOT NULL DEFAULT 'La mia spesa',
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_list_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS shopping_list_items (
  id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  list_id    INT UNSIGNED NOT NULL,
  item_json  JSON NOT NULL,                       -- {query, brand, size_value, size_unit, qty, ean}
  CONSTRAINT fk_item_list FOREIGN KEY (list_id) REFERENCES shopping_lists(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Consenso newsletter specifico, documentabile e revocabile (GDPR).
CREATE TABLE IF NOT EXISTS newsletter_consents (
  id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  email         VARCHAR(190) NOT NULL,
  consent_text  VARCHAR(500) NOT NULL,            -- testo esatto mostrato all'utente
  ip_hash       CHAR(64) NULL,                    -- SHA-256 con sale, non l'IP in chiaro
  confirmed_at  DATETIME NULL,                    -- double opt-in
  given_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  revoked_at    DATETIME NULL,
  INDEX idx_consent_email (email)
) ENGINE=InnoDB;

-- ---------- Pubblicità (nessuna influenza sulle classifiche) ----------

CREATE TABLE IF NOT EXISTS advertisers (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name        VARCHAR(160) NOT NULL,
  contact     VARCHAR(190) NULL,
  created_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS ad_campaigns (
  id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  advertiser_id INT UNSIGNED NOT NULL,
  placement     ENUM('home_banner','results_inline') NOT NULL,
  title         VARCHAR(120) NOT NULL,
  body          VARCHAR(200) NULL,
  image_url     VARCHAR(500) NULL,
  click_url     VARCHAR(500) NOT NULL,
  starts_on     DATE NOT NULL,
  ends_on       DATE NOT NULL,
  geo_lat       DECIMAL(9,6) NULL,                -- targeting opzionale per area
  geo_lng       DECIMAL(9,6) NULL,
  geo_radius_km SMALLINT UNSIGNED NULL,
  weight        TINYINT UNSIGNED NOT NULL DEFAULT 1,
  status        ENUM('draft','active','paused','ended') NOT NULL DEFAULT 'draft',
  CONSTRAINT fk_camp_adv FOREIGN KEY (advertiser_id) REFERENCES advertisers(id),
  INDEX idx_camp_active (status, placement, starts_on, ends_on)
) ENGINE=InnoDB;

-- Solo contatori giornalieri aggregati: nessun tracciamento individuale.
CREATE TABLE IF NOT EXISTS ad_stats_daily (
  campaign_id INT UNSIGNED NOT NULL,
  day         DATE NOT NULL,
  impressions INT UNSIGNED NOT NULL DEFAULT 0,
  clicks      INT UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (campaign_id, day),
  CONSTRAINT fk_stat_camp FOREIGN KEY (campaign_id) REFERENCES ad_campaigns(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Statistiche di ricerca aggregate (nessun testo libero, nessun identificativo utente).
CREATE TABLE IF NOT EXISTS search_stats_daily (
  day        DATE NOT NULL,
  channel    ENUM('text','photo','voice','barcode','list') NOT NULL,
  area_code  VARCHAR(20) NOT NULL DEFAULT '',
  searches   INT UNSIGNED NOT NULL DEFAULT 0,
  no_results INT UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (day, channel, area_code)
) ENGINE=InnoDB;