--
-- @project       : Domuscan App
-- @filename      : 003_token_monouso_tentativi.sql
-- @author        : Damiano | <dagneni@gmail.com>
-- @description   : Token monouso (conferma email, reimpostazione password), salvati solo come
--                  hash SHA-256, e registro dei tentativi di accesso per limitarne il numero.
-- @creation date : 15-10-2026
-- @last update   : 15-10-2026
--
-- @version: 0.1.0
--

CREATE TABLE token_monouso (
    id                 INTEGER     NOT NULL AUTO_INCREMENT,
    amministratore_id  INTEGER     NOT NULL,
    scopo              VARCHAR(30) NOT NULL,
    hash_token         CHAR(64)    NOT NULL,
    creato_il          DATETIME    NOT NULL,
    scade_il           DATETIME    NOT NULL,
    usato_il           DATETIME    NULL,
    CONSTRAINT pk_token_monouso PRIMARY KEY (id),
    CONSTRAINT uq_token_monouso_hash UNIQUE (hash_token),
    CONSTRAINT fk_token_monouso_amministratore FOREIGN KEY (amministratore_id) REFERENCES amministratori (id),
    CONSTRAINT ck_token_monouso_scopo CHECK (scopo IN ('verifica_email', 'reimposta_password'))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE INDEX ix_token_monouso_amministratore_scopo ON token_monouso (amministratore_id, scopo);

CREATE TABLE tentativi_accesso (
    id            INTEGER      NOT NULL AUTO_INCREMENT,
    email         VARCHAR(254) NOT NULL,
    indirizzo_ip  VARCHAR(45)  NULL,
    esito         VARCHAR(20)  NOT NULL,
    tentato_il    DATETIME     NOT NULL,
    CONSTRAINT pk_tentativi_accesso PRIMARY KEY (id),
    CONSTRAINT ck_tentativi_accesso_esito CHECK (esito IN ('riuscito', 'fallito', 'bloccato'))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE INDEX ix_tentativi_accesso_email ON tentativi_accesso (email, tentato_il);
CREATE INDEX ix_tentativi_accesso_ip ON tentativi_accesso (indirizzo_ip, tentato_il);
