-- ════════════════════════════════════════════════════════════════════════
--  Keedis — Schéma initial
--  Modèle zero-knowledge multi-tenant : le serveur ne stocke QUE du chiffré
--  pour tout ce qui touche aux secrets et aux clés privées.
-- ════════════════════════════════════════════════════════════════════════

-- ── Organisations (tenants) ─────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS organizations (
    id                  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    name                VARCHAR(190)    NOT NULL,
    slug                VARCHAR(190)    NOT NULL,
    -- Clé publique de récupération de l'org (escrow admin). Sa clé privée est
    -- distribuée chiffrée aux admins via org_recovery_keys.
    recovery_public_key TEXT            NULL,
    status              ENUM('active','suspended') NOT NULL DEFAULT 'active',
    created_at          TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at          TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_org_slug (slug)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Utilisateurs ────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS users (
    id                    BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    org_id                BIGINT UNSIGNED NOT NULL,
    email                 VARCHAR(190)    NOT NULL,
    name                  VARCHAR(190)    NULL,
    role                  ENUM('admin','manager','user') NOT NULL DEFAULT 'user',
    status                ENUM('invited','active','disabled') NOT NULL DEFAULT 'invited',

    -- Authentification zero-knowledge :
    -- le client dérive un authKey du mot de passe maître (Argon2id) ; le serveur
    -- n'en stocke qu'un hash (password_hash). Le mot de passe maître ne transite jamais.
    auth_hash             VARCHAR(255)    NULL,
    kdf_salt              VARBINARY(32)   NULL,            -- sel Argon2id (généré client)
    kdf_mem               INT UNSIGNED    NULL,            -- mémoire Argon2id (octets)
    kdf_ops               INT UNSIGNED    NULL,            -- itérations Argon2id

    -- Matériel cryptographique (tout est public OU chiffré) :
    public_key            TEXT            NULL,            -- clé publique X25519 (base64)
    enc_private_key       TEXT            NULL,            -- clé privée chiffrée par userKey
    enc_user_key          TEXT            NULL,            -- userKey chiffrée par masterKey
    enc_user_key_recovery TEXT            NULL,            -- userKey chiffrée par la clé de secours perso
    enc_user_key_org      TEXT            NULL,            -- userKey chiffrée vers la clé publique de récup de l'org (escrow)

    -- 2FA (TOTP) : secret chiffré au repos avec APP_KEY (côté serveur).
    totp_secret_enc       TEXT            NULL,
    totp_enabled          TINYINT(1)      NOT NULL DEFAULT 0,

    email_verified_at     TIMESTAMP       NULL,
    last_login_at         TIMESTAMP       NULL,
    created_at            TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at            TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_user_org_email (org_id, email),
    KEY idx_users_org (org_id),
    CONSTRAINT fk_users_org FOREIGN KEY (org_id) REFERENCES organizations(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Récupération d'entreprise (escrow) ──────────────────────────────────
-- La clé privée de récupération de l'org, chiffrée vers la clé publique de
-- chaque admin habilité. Permet de réinitialiser un user sans perte de données.
CREATE TABLE IF NOT EXISTS org_recovery_keys (
    id                       BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    org_id                   BIGINT UNSIGNED NOT NULL,
    admin_user_id            BIGINT UNSIGNED NOT NULL,
    enc_recovery_private_key TEXT            NOT NULL,
    created_at               TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_orgrec (org_id, admin_user_id),
    CONSTRAINT fk_orgrec_org   FOREIGN KEY (org_id)        REFERENCES organizations(id) ON DELETE CASCADE,
    CONSTRAINT fk_orgrec_admin FOREIGN KEY (admin_user_id) REFERENCES users(id)         ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Groupes ─────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS user_groups (
    id         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    org_id     BIGINT UNSIGNED NOT NULL,
    name       VARCHAR(190)    NOT NULL,
    public_key TEXT            NULL,                       -- clé publique X25519 du groupe
    created_at TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_group_org_name (org_id, name),
    CONSTRAINT fk_groups_org FOREIGN KEY (org_id) REFERENCES organizations(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Appartenance aux groupes : la clé privée du groupe est chiffrée vers la
-- clé publique de chaque membre (sealed box).
CREATE TABLE IF NOT EXISTS group_members (
    id                    BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    group_id              BIGINT UNSIGNED NOT NULL,
    user_id               BIGINT UNSIGNED NOT NULL,
    role                  ENUM('member','manager') NOT NULL DEFAULT 'member',
    enc_group_private_key TEXT            NULL,            -- NULL = en attente (user pas encore onboardé)
    created_at            TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_member (group_id, user_id),
    CONSTRAINT fk_gm_group FOREIGN KEY (group_id) REFERENCES user_groups(id) ON DELETE CASCADE,
    CONSTRAINT fk_gm_user  FOREIGN KEY (user_id)  REFERENCES users(id)  ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Dossiers (arborescence) ─────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS folders (
    id                   BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    org_id               BIGINT UNSIGNED NOT NULL,
    parent_id            BIGINT UNSIGNED NULL,
    name                 VARCHAR(190)    NOT NULL,
    inherit_permissions  TINYINT(1)      NOT NULL DEFAULT 1,  -- 0 = héritage désactivé
    created_by           BIGINT UNSIGNED NULL,
    created_at           TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at           TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_folders_org (org_id),
    KEY idx_folders_parent (parent_id),
    CONSTRAINT fk_folders_org    FOREIGN KEY (org_id)    REFERENCES organizations(id) ON DELETE CASCADE,
    CONSTRAINT fk_folders_parent FOREIGN KEY (parent_id) REFERENCES folders(id)       ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Attributions d'accès à un dossier (user OU groupe).
-- enc_folder_key = folderKey chiffrée vers la clé publique du bénéficiaire.
-- enc_folder_key NULL = attribution EN ATTENTE (bénéficiaire pas encore onboardé) :
--   un admin ayant accès la complétera quand la clé publique sera disponible.
CREATE TABLE IF NOT EXISTS folder_access (
    id             BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    folder_id      BIGINT UNSIGNED NOT NULL,
    grantee_type   ENUM('user','group') NOT NULL,
    grantee_id     BIGINT UNSIGNED NOT NULL,
    permission     ENUM('read','write','manage') NOT NULL DEFAULT 'read',
    enc_folder_key TEXT            NULL,
    created_at     TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_access (folder_id, grantee_type, grantee_id),
    KEY idx_access_grantee (grantee_type, grantee_id),
    CONSTRAINT fk_access_folder FOREIGN KEY (folder_id) REFERENCES folders(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Secrets (entrées de mot de passe) ───────────────────────────────────
-- enc_data : JSON {name,username,password,url,notes,totp,custom...} chiffré
--            avec la folderKey du dossier. Le serveur ne voit jamais le clair.
CREATE TABLE IF NOT EXISTS secrets (
    id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    org_id      BIGINT UNSIGNED NOT NULL,
    folder_id   BIGINT UNSIGNED NOT NULL,
    enc_data    MEDIUMTEXT      NOT NULL,
    favorite    TINYINT(1)      NOT NULL DEFAULT 0,
    created_by  BIGINT UNSIGNED NULL,
    updated_by  BIGINT UNSIGNED NULL,
    created_at  TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at  TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_secrets_folder (folder_id),
    KEY idx_secrets_org (org_id),
    CONSTRAINT fk_secrets_org    FOREIGN KEY (org_id)    REFERENCES organizations(id) ON DELETE CASCADE,
    CONSTRAINT fk_secrets_folder FOREIGN KEY (folder_id) REFERENCES folders(id)       ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Historique de versions d'un secret.
CREATE TABLE IF NOT EXISTS secret_versions (
    id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    secret_id   BIGINT UNSIGNED NOT NULL,
    enc_data    MEDIUMTEXT      NOT NULL,
    created_by  BIGINT UNSIGNED NULL,
    created_at  TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_versions_secret (secret_id),
    CONSTRAINT fk_versions_secret FOREIGN KEY (secret_id) REFERENCES secrets(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Jetons (vérif email, invitations, reset, récupération) ──────────────
CREATE TABLE IF NOT EXISTS tokens (
    id         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id    BIGINT UNSIGNED NOT NULL,
    type       ENUM('email_verify','invite','password_reset','recovery') NOT NULL,
    token_hash CHAR(64)        NOT NULL,                   -- sha256(token) ; le token brut n'est jamais stocké
    expires_at TIMESTAMP       NOT NULL,
    used_at    TIMESTAMP       NULL,
    created_at TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_token_hash (token_hash),
    KEY idx_tokens_user (user_id),
    CONSTRAINT fk_tokens_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Paramètres plateforme (clé/valeur) — dont la config SMTP ────────────
-- Les valeurs sensibles (mot de passe SMTP) sont chiffrées avec APP_KEY.
CREATE TABLE IF NOT EXISTS settings (
    k          VARCHAR(190) NOT NULL,
    v          TEXT         NULL,
    updated_at TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (k)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Journal d'audit ─────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS audit_logs (
    id         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    org_id     BIGINT UNSIGNED NULL,
    user_id    BIGINT UNSIGNED NULL,
    action     VARCHAR(100)    NOT NULL,
    target     VARCHAR(190)    NULL,
    ip         VARCHAR(45)     NULL,
    user_agent VARCHAR(255)    NULL,
    meta       JSON            NULL,
    created_at TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_audit_org (org_id),
    KEY idx_audit_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Tentatives de connexion (anti-bruteforce) ───────────────────────────
CREATE TABLE IF NOT EXISTS login_attempts (
    id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    email       VARCHAR(190)    NULL,
    ip          VARCHAR(45)     NULL,
    successful  TINYINT(1)      NOT NULL DEFAULT 0,
    created_at  TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_attempts_ip (ip, created_at),
    KEY idx_attempts_email (email, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
