-- ============================================================
-- GESTION PARC AUTOMOBILE - Province Al Haouz v3.1
-- Migration 003 : Sécurité, Sauvegarde, Restrictions
-- ============================================================

USE gestion_parc;

-- ============================================================
-- TENTATIVES DE CONNEXION (Brute-force protection)
-- ============================================================
CREATE TABLE IF NOT EXISTS login_attempts (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    ip_address VARCHAR(45) NOT NULL,
    user_agent TEXT NULL,
    success TINYINT(1) DEFAULT 0,
    attempted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_username (username),
    INDEX idx_ip (ip_address),
    INDEX idx_time (attempted_at)
) ENGINE=InnoDB;

-- ============================================================
-- IPs / POSTES AUTORISÉS (restriction réseau local)
-- ============================================================
CREATE TABLE IF NOT EXISTS allowed_hosts (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    ip_address VARCHAR(45) NOT NULL,
    hostname VARCHAR(100) NULL,
    description VARCHAR(255) NULL,
    is_active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_ip (ip_address)
) ENGINE=InnoDB;

-- ============================================================
-- JOURNAL DE SÉCURITÉ (audit log)
-- ============================================================
CREATE TABLE IF NOT EXISTS security_logs (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NULL,
    action VARCHAR(80) NOT NULL,
    details TEXT NULL,
    ip_address VARCHAR(45) NOT NULL,
    user_agent TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user (user_id),
    INDEX idx_action (action),
    INDEX idx_time (created_at),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ============================================================
-- JOURNAL DES SAUVEGARDES
-- ============================================================
CREATE TABLE IF NOT EXISTS backup_logs (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    backup_type ENUM('database','application','full') NOT NULL,
    filename VARCHAR(255) NOT NULL,
    filesize BIGINT UNSIGNED DEFAULT 0,
    status ENUM('success','failed') DEFAULT 'success',
    user_id INT UNSIGNED NULL,
    notes TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ============================================================
-- SESSIONS ACTIVES
-- ============================================================
CREATE TABLE IF NOT EXISTS active_sessions (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    session_id VARCHAR(128) NOT NULL,
    ip_address VARCHAR(45) NOT NULL,
    user_agent TEXT NULL,
    fingerprint VARCHAR(64) NULL,
    last_activity TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_session (session_id),
    INDEX idx_user (user_id),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ============================================================
-- PARAMÈTRES SÉCURITÉ (dans app_settings)
-- ============================================================
INSERT IGNORE INTO app_settings (setting_key, setting_value) VALUES
-- Sécurité
('security_csrf_enabled',       '1'),
('security_bruteforce_enabled', '1'),
('security_max_login_attempts', '5'),
('security_lockout_minutes',    '15'),
('security_session_timeout',    '3600'),
('security_ip_restriction',     '0'),
('security_force_https',        '0'),
('security_password_min_length','8'),
('security_log_enabled',        '1'),
('security_2fa_enabled',        '0'),
('security_session_fingerprint','1'),
('security_clickjacking',       '1'),
('security_xss_protection',     '1'),
('security_content_sniffing',   '1'),
('security_referrer_policy',    '1'),
('security_concurrent_sessions','3'),
-- Sauvegarde
('backup_db_enabled',           '0'),
('backup_db_frequency',         'daily'),
('backup_db_time',              '02:00'),
('backup_db_retention_days',    '30'),
('backup_db_path',              ''),
('backup_app_enabled',          '0'),
('backup_app_frequency',        'weekly'),
('backup_app_retention_days',   '60'),
('backup_app_path',             '');

-- ============================================================
-- NOUVELLES PERMISSIONS
-- ============================================================
INSERT IGNORE INTO permissions (code, module, label_fr, description, ordre) VALUES
('param_security_view', 'Param. Sécurité',   'Voir les paramètres sécurité',    NULL, 110),
('param_security_edit', 'Param. Sécurité',   'Gérer les paramètres sécurité',   NULL, 111),
('param_backup_view',   'Param. Sauvegarde', 'Voir les sauvegardes',            NULL, 112),
('param_backup_exec',   'Param. Sauvegarde', 'Exécuter des sauvegardes',        NULL, 113),
('security_logs_view',  'Journal Sécurité',  'Consulter le journal de sécurité',NULL, 114);

-- SuperAdmin : toutes nouvelles permissions
INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT 1, id FROM permissions WHERE code IN ('param_security_view','param_security_edit','param_backup_view','param_backup_exec','security_logs_view');

-- Administrateur
INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT 2, id FROM permissions WHERE code IN ('param_security_view','param_backup_view','security_logs_view');