-- ============================================================
--  DATA SENTINEL — Script d'authentification SQL Server
--  Tables [USER] + JOURNAL_AUDIT + comptes de démonstration
--  Auteur  : COYAUD Anthony
--  Version : 1.1 — Juin 2026
--  À exécuter APRÈS data_sentinel_init.sql (même base DataSentinel)
-- ============================================================
--
--  Ce script crée la couche de sécurité référencée par l'API
--  (main.py / auth.py) :
--    - [USER]         : comptes applicatifs (RBAC : Admin / Superviseur / Consultant)
--    - JOURNAL_AUDIT  : journal d'audit (connexions + actions d'administration)
--
--  Les mots de passe sont stockés hachés en bcrypt (passlib[bcrypt]).
--  Les hachages ci-dessous correspondent aux comptes de démonstration
--  documentés dans le README (à régénérer en production).
-- ============================================================

USE DataSentinel;
GO

-- ============================================================
--  SUPPRESSION (ordre inverse des dépendances)
-- ============================================================
IF OBJECT_ID('JOURNAL_AUDIT', 'U') IS NOT NULL DROP TABLE JOURNAL_AUDIT;
IF OBJECT_ID('[USER]',        'U') IS NOT NULL DROP TABLE [USER];
GO

-- ============================================================
--  1. TABLE [USER] — comptes applicatifs
--     ([USER] entre crochets car USER est un mot réservé SQL Server)
-- ============================================================
CREATE TABLE [USER] (
    id_user       INT           NOT NULL IDENTITY(1,1),
    username      NVARCHAR(100) NOT NULL,
    email         NVARCHAR(200) NULL,
    password_hash NVARCHAR(255) NOT NULL,        -- bcrypt
    role          NVARCHAR(20)  NOT NULL,        -- Admin | Superviseur | Consultant
    actif         BIT           NOT NULL DEFAULT 1,
    created_at    DATETIME2     NOT NULL DEFAULT SYSDATETIME(),
    last_login    DATETIME2     NULL,
    CONSTRAINT PK_USER       PRIMARY KEY (id_user),
    CONSTRAINT UQ_USER_NAME  UNIQUE (username),
    CONSTRAINT CK_USER_ROLE  CHECK (role IN ('Admin', 'Superviseur', 'Consultant'))
);
GO

CREATE INDEX IX_USER_USERNAME ON [USER] (username);
GO

-- ============================================================
--  2. TABLE JOURNAL_AUDIT — journal d'audit applicatif
--     Alimenté à chaque login + action d'administration.
--     username/id_user nullables (anonymisation RGPD : droit à l'oubli).
-- ============================================================
CREATE TABLE JOURNAL_AUDIT (
    id_audit    INT           NOT NULL IDENTITY(1,1),
    id_user     INT           NULL,
    username    NVARCHAR(100) NULL,
    action      NVARCHAR(50)  NOT NULL,          -- LOGIN | CREATE_USER | UPDATE_USER | DELETE_USER | RESET_PASSWORD
    detail      NVARCHAR(500) NULL,
    ip          NVARCHAR(50)  NULL,
    date_action DATETIME2     NOT NULL DEFAULT SYSDATETIME(),
    CONSTRAINT PK_JOURNAL_AUDIT PRIMARY KEY (id_audit)
);
GO

CREATE INDEX IX_AUDIT_DATE ON JOURNAL_AUDIT (date_action DESC);
GO

-- ============================================================
--  3. COMPTES DE DÉMONSTRATION
--     ⚠️ Mots de passe en clair (à usage de recette / soutenance) :
--        Juré        / 123456        → rôle Admin  (compte de visite / évaluation)
--        admin       / Admin2026!     → rôle Admin
--        superviseur / Super2026!     → rôle Superviseur
--        consultant  / Conseil2026!   → rôle Consultant
--     Les hachages bcrypt ci-dessous sont fonctionnels tels quels.
-- ============================================================
INSERT INTO [USER] (username, email, password_hash, role, actif) VALUES
    (N'Juré',       'JURE@NEXA.com',
     '$2b$12$nnWMOCjSiUq4rAyPGbC3W.HGdsvWKu9kEfLst6zBIEIcsgP97W94m', 'Admin',       1),
    ('admin',       'admin@xefi-fictif.fr',
     '$2b$12$XJobnqM0cHwekv7UYWXUfuxVjMKLZI6VLzpDvRzDdLJgnYHLRNRCy', 'Admin',       1),
    ('superviseur', 'superviseur@xefi-fictif.fr',
     '$2b$12$gMVg2j6wngZZ2KXH2jF6HuvlcAkGIJsbZ05JEQdDYNnIu7fDxOXri', 'Superviseur', 1),
    ('consultant',  'consultant@xefi-fictif.fr',
     '$2b$12$s4Gl3UDq56HOw/GdjeHCrOFjXN/QrV1OEj3wmHhf3T8mFVfijJkty', 'Consultant',  1);
GO

-- Entrée d'audit initiale (création du jeu de comptes)
INSERT INTO JOURNAL_AUDIT (id_user, username, action, detail)
SELECT id_user, username, 'CREATE_USER', 'Compte de démonstration (seed)'
FROM [USER];
GO

-- ============================================================
--  4. VÉRIFICATIONS
-- ============================================================
SELECT 'USER' AS [Table], COUNT(*) AS [Lignes] FROM [USER]
UNION ALL SELECT 'JOURNAL_AUDIT', COUNT(*) FROM JOURNAL_AUDIT;
GO

SELECT id_user, username, email, role, actif, created_at FROM [USER] ORDER BY id_user;
GO