-- =========================================================================
-- SD Consulting v2 — Schéma de base de données assaini
-- MySQL / MariaDB, moteur InnoDB, encodage utf8mb4.
--
-- Toutes les tables sont préfixées « A_ticketing_v2_ » : elles cohabitent dans
-- la base « adp » à côté des tables V1 (A_ticketing_clients, _items, _comments,
-- _tracking) SANS collision, et restent regroupées par projet.
--
-- Différences clés avec le legacy (table unique A_ticketing_clients qui
-- mélangeait utilisateur + client + projet) :
--   - users / clients / projects / memberships séparés ;
--   - mots de passe HACHÉS (password_hash), plus jamais en clair ;
--   - abonnements (alertes) et lectures (vupar) dans des tables dédiées ;
--   - tokens « se souvenir » et « reset mot de passe » en base.
--
-- Les colonnes legacy_* servent uniquement à la migration (mapping/idempotence).
-- Les contraintes de clés étrangères sont préfixées « fk_atv2_ » car leur nom
-- doit être unique dans toute la base (InnoDB).
-- =========================================================================


-- --- Statuts (référentiel, ids alignés sur le legacy "etat" 1..8) ----------
CREATE TABLE A_ticketing_v2_statuses (
    id          TINYINT UNSIGNED NOT NULL,
    slug        VARCHAR(32)  NOT NULL,
    label       VARCHAR(64)  NOT NULL,
    color_bg    VARCHAR(16)  NOT NULL DEFAULT '#DDD',
    color_text  VARCHAR(16)  NOT NULL DEFAULT '#000',
    sort_order  TINYINT UNSIGNED NOT NULL DEFAULT 0,
    is_closed   TINYINT(1)   NOT NULL DEFAULT 0,
    PRIMARY KEY (id),
    UNIQUE KEY uniq_slug (slug)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO A_ticketing_v2_statuses (id, slug, label, color_bg, color_text, sort_order, is_closed) VALUES
    (1, 'a_traiter',  'A traiter',   '#fcac00', '#000', 2, 0),
    (2, 'en_cours',   'En cours',    '#DDD',    '#000', 3, 0),
    (3, 'en_attente', 'En attente',  '#fee21c', '#000', 4, 0),
    (4, 'stoppe',     'Stoppé',      'red',     '#fff', 7, 1),
    (5, 'fait',       'Fait',        '#79e200', '#000', 6, 1),
    (6, 'a_tester',   'A tester',    '#00bdd5', '#000', 5, 0),
    (7, 'suggestion', 'Suggestion',  '#003897', '#fff', 1, 0),
    (8, 'archives',   'Archives',    '#027100', '#fff', 8, 1);

-- --- Clients ---------------------------------------------------------------
CREATE TABLE A_ticketing_v2_clients (
    id         INT UNSIGNED NOT NULL AUTO_INCREMENT,
    name       VARCHAR(190) NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uniq_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --- Projets ---------------------------------------------------------------
CREATE TABLE A_ticketing_v2_projects (
    id              INT UNSIGNED NOT NULL AUTO_INCREMENT,
    client_id       INT UNSIGNED NOT NULL,
    name            VARCHAR(190) NOT NULL,
    legacy_idclient VARCHAR(64) NULL,           -- ancien A_ticketing_clients.idclient
    created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_client (client_id),
    UNIQUE KEY uniq_legacy (legacy_idclient),
    CONSTRAINT fk_atv2_projects_client FOREIGN KEY (client_id) REFERENCES A_ticketing_v2_clients (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --- Utilisateurs ----------------------------------------------------------
CREATE TABLE A_ticketing_v2_users (
    id            INT UNSIGNED NOT NULL AUTO_INCREMENT,
    email         VARCHAR(190) NOT NULL,
    password_hash VARCHAR(255) NULL,            -- NULL = pas encore défini (invitation)
    first_name    VARCHAR(120) NOT NULL DEFAULT '',
    last_name     VARCHAR(120) NOT NULL DEFAULT '',
    is_superadmin TINYINT(1)   NOT NULL DEFAULT 0,  -- éditeur global (Internautic)
    created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    last_login_at DATETIME NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uniq_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --- Appartenances (utilisateur x projet, avec rôle) -----------------------
CREATE TABLE A_ticketing_v2_memberships (
    id            INT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id       INT UNSIGNED NOT NULL,
    project_id    INT UNSIGNED NOT NULL,
    role          ENUM('member','editor') NOT NULL DEFAULT 'member',
    access_code   VARCHAR(32) NULL,             -- ancien "code" d'accès direct
    legacy_row_id INT UNSIGNED NULL,            -- ancien A_ticketing_clients.id
    created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uniq_user_project (user_id, project_id),
    UNIQUE KEY uniq_legacy_row (legacy_row_id),
    KEY idx_project (project_id),
    CONSTRAINT fk_atv2_memberships_user    FOREIGN KEY (user_id)    REFERENCES A_ticketing_v2_users (id)    ON DELETE CASCADE,
    CONSTRAINT fk_atv2_memberships_project FOREIGN KEY (project_id) REFERENCES A_ticketing_v2_projects (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --- Tickets (sujets) ------------------------------------------------------
CREATE TABLE A_ticketing_v2_tickets (
    id           INT UNSIGNED NOT NULL AUTO_INCREMENT,
    project_id   INT UNSIGNED NOT NULL,
    author_id    INT UNSIGNED NULL,
    title        VARCHAR(255) NOT NULL,
    body_html    MEDIUMTEXT NOT NULL,           -- HTML assaini (allowlist) à l'écriture
    impact       TINYINT UNSIGNED NOT NULL DEFAULT 1, -- 1 Faible .. 4 Bloquant
    status_id    TINYINT UNSIGNED NOT NULL DEFAULT 7, -- défaut : Suggestion
    restricted   TINYINT(1)   NOT NULL DEFAULT 0,     -- 1 = visible des seules personnes concernées + éditeurs
    legacy_id    INT UNSIGNED NULL,
    created_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_project (project_id),
    KEY idx_status (status_id),
    UNIQUE KEY uniq_legacy (legacy_id),
    CONSTRAINT fk_atv2_tickets_project FOREIGN KEY (project_id) REFERENCES A_ticketing_v2_projects (id) ON DELETE CASCADE,
    CONSTRAINT fk_atv2_tickets_author  FOREIGN KEY (author_id)  REFERENCES A_ticketing_v2_users (id)    ON DELETE SET NULL,
    CONSTRAINT fk_atv2_tickets_status  FOREIGN KEY (status_id)  REFERENCES A_ticketing_v2_statuses (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --- Commentaires ----------------------------------------------------------
CREATE TABLE A_ticketing_v2_comments (
    id          INT UNSIGNED NOT NULL AUTO_INCREMENT,
    ticket_id   INT UNSIGNED NOT NULL,
    author_id   INT UNSIGNED NULL,
    body_html   MEDIUMTEXT NOT NULL,
    legacy_id   INT UNSIGNED NULL,
    created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_ticket (ticket_id),
    UNIQUE KEY uniq_legacy (legacy_id),
    CONSTRAINT fk_atv2_comments_ticket FOREIGN KEY (ticket_id) REFERENCES A_ticketing_v2_tickets (id) ON DELETE CASCADE,
    CONSTRAINT fk_atv2_comments_author FOREIGN KEY (author_id) REFERENCES A_ticketing_v2_users (id)   ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --- Pièces jointes (polymorphes : ticket ou commentaire) ------------------
CREATE TABLE A_ticketing_v2_attachments (
    id            INT UNSIGNED NOT NULL AUTO_INCREMENT,
    owner_type    ENUM('ticket','comment') NOT NULL,
    owner_id      INT UNSIGNED NOT NULL,
    original_name VARCHAR(255) NOT NULL,
    stored_name   VARCHAR(255) NOT NULL,        -- nom généré, non exécutable, hors webroot
    mime          VARCHAR(127) NOT NULL,
    size_bytes    INT UNSIGNED NOT NULL DEFAULT 0,
    created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_owner (owner_type, owner_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --- Abonnements aux alertes d'un ticket (ancien champ "alertes") ----------
CREATE TABLE A_ticketing_v2_ticket_subscriptions (
    ticket_id INT UNSIGNED NOT NULL,
    user_id   INT UNSIGNED NOT NULL,
    PRIMARY KEY (ticket_id, user_id),
    KEY idx_user (user_id),
    CONSTRAINT fk_atv2_subs_ticket FOREIGN KEY (ticket_id) REFERENCES A_ticketing_v2_tickets (id) ON DELETE CASCADE,
    CONSTRAINT fk_atv2_subs_user   FOREIGN KEY (user_id)   REFERENCES A_ticketing_v2_users (id)   ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --- Lectures d'un ticket (ancien champ "vupar") ---------------------------
CREATE TABLE A_ticketing_v2_ticket_reads (
    ticket_id INT UNSIGNED NOT NULL,
    user_id   INT UNSIGNED NOT NULL,
    read_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (ticket_id, user_id),
    CONSTRAINT fk_atv2_reads_ticket FOREIGN KEY (ticket_id) REFERENCES A_ticketing_v2_tickets (id) ON DELETE CASCADE,
    CONSTRAINT fk_atv2_reads_user   FOREIGN KEY (user_id)   REFERENCES A_ticketing_v2_users (id)   ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --- « Se souvenir de moi » (selector + validator haché) -------------------
CREATE TABLE A_ticketing_v2_remember_tokens (
    id             INT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id        INT UNSIGNED NOT NULL,
    selector       CHAR(24) NOT NULL,
    validator_hash CHAR(64) NOT NULL,          -- sha256 du validateur
    expires_at     DATETIME NOT NULL,
    created_at     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uniq_selector (selector),
    KEY idx_user (user_id),
    CONSTRAINT fk_atv2_remember_user FOREIGN KEY (user_id) REFERENCES A_ticketing_v2_users (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --- Réinitialisation de mot de passe par token ----------------------------
CREATE TABLE A_ticketing_v2_password_resets (
    id         INT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id    INT UNSIGNED NOT NULL,
    token_hash CHAR(64) NOT NULL,              -- sha256 du token envoyé par email
    expires_at DATETIME NOT NULL,
    used_at    DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_user (user_id),
    KEY idx_token (token_hash),
    CONSTRAINT fk_atv2_reset_user FOREIGN KEY (user_id) REFERENCES A_ticketing_v2_users (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --- Journal de connexions (ancien A_ticketing_tracking) -------------------


