-- ============================================
-- SCHEMA SQL COMPLET - TRACKER ANALYTICS
-- ============================================
-- Compatible MySQL 5.7+
-- À importer dans phpMyAdmin

-- Créer la base de données
CREATE DATABASE IF NOT EXISTS tracker_analytics CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

USE tracker_analytics;

-- ============================================
-- TABLE: admin_users
-- Utilisateurs du dashboard admin
-- ============================================
CREATE TABLE IF NOT EXISTS admin_users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(100) NOT NULL UNIQUE,
    email VARCHAR(255) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_login_at TIMESTAMP NULL,
    is_active TINYINT(1) DEFAULT 1,
    INDEX idx_username (username),
    INDEX idx_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- TABLE: visitors
-- Visiteurs anonymes uniques
-- ============================================
CREATE TABLE IF NOT EXISTS visitors (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    visitor_id VARCHAR(64) NOT NULL UNIQUE,
    first_seen_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_seen_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    total_sessions INT UNSIGNED DEFAULT 0,
    total_events INT UNSIGNED DEFAULT 0,
    user_agent TEXT,
    initial_referrer TEXT,
    initial_landing_page TEXT,
    INDEX idx_visitor_id (visitor_id),
    INDEX idx_first_seen (first_seen_at),
    INDEX idx_last_seen (last_seen_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- TABLE: sessions
-- Sessions de navigation
-- ============================================
CREATE TABLE IF NOT EXISTS sessions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    session_id VARCHAR(64) NOT NULL UNIQUE,
    visitor_id VARCHAR(64) NOT NULL,
    started_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    ended_at TIMESTAMP NULL,
    duration_seconds INT UNSIGNED DEFAULT 0,
    page_views INT UNSIGNED DEFAULT 0,
    total_events INT UNSIGNED DEFAULT 0,
    landing_page TEXT,
    exit_page TEXT,
    referrer TEXT,
    utm_source VARCHAR(255),
    utm_medium VARCHAR(255),
    utm_campaign VARCHAR(255),
    utm_term VARCHAR(255),
    utm_content VARCHAR(255),
    device_type VARCHAR(50),
    browser VARCHAR(100),
    os VARCHAR(100),
    country VARCHAR(2),
    ip_hash VARCHAR(64),
    INDEX idx_session_id (session_id),
    INDEX idx_visitor_id (visitor_id),
    INDEX idx_started_at (started_at),
    INDEX idx_utm_source (utm_source),
    INDEX idx_utm_campaign (utm_campaign),
    FOREIGN KEY (visitor_id) REFERENCES visitors(visitor_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- TABLE: pages
-- Cache des URLs visitées
-- ============================================
CREATE TABLE IF NOT EXISTS pages (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    page_url TEXT NOT NULL,
    page_url_hash VARCHAR(64) NOT NULL UNIQUE,
    page_title VARCHAR(500),
    first_seen_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    total_views BIGINT UNSIGNED DEFAULT 0,
    unique_visitors INT UNSIGNED DEFAULT 0,
    INDEX idx_url_hash (page_url_hash),
    INDEX idx_total_views (total_views)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- TABLE: events
-- Tous les événements collectés
-- ============================================
CREATE TABLE IF NOT EXISTS events (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    event_name VARCHAR(255) NOT NULL,
    visitor_id VARCHAR(64) NOT NULL,
    session_id VARCHAR(64) NOT NULL,
    occurred_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    page_url TEXT,
    page_url_hash VARCHAR(64),
    page_title VARCHAR(500),
    referrer TEXT,
    user_agent TEXT,
    ip_hash VARCHAR(64),
    properties JSON,
    INDEX idx_event_name (event_name),
    INDEX idx_visitor_id (visitor_id),
    INDEX idx_session_id (session_id),
    INDEX idx_occurred_at (occurred_at),
    INDEX idx_page_url_hash (page_url_hash),
    INDEX idx_event_occurred (event_name, occurred_at),
    FOREIGN KEY (visitor_id) REFERENCES visitors(visitor_id) ON DELETE CASCADE,
    FOREIGN KEY (session_id) REFERENCES sessions(session_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- TABLE: event_properties
-- Propriétés déstructurées des events (pour requêtes plus rapides)
-- ============================================
CREATE TABLE IF NOT EXISTS event_properties (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    event_id BIGINT UNSIGNED NOT NULL,
    property_key VARCHAR(255) NOT NULL,
    property_value TEXT,
    property_type ENUM('string', 'number', 'boolean', 'null') DEFAULT 'string',
    INDEX idx_event_id (event_id),
    INDEX idx_property_key (property_key),
    INDEX idx_key_value (property_key, property_value(100)),
    FOREIGN KEY (event_id) REFERENCES events(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- TABLE: funnels
-- Définition des funnels
-- ============================================
CREATE TABLE IF NOT EXISTS funnels (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    funnel_name VARCHAR(255) NOT NULL,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    is_active TINYINT(1) DEFAULT 1,
    INDEX idx_funnel_name (funnel_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- TABLE: funnel_steps
-- Étapes de chaque funnel
-- ============================================
CREATE TABLE IF NOT EXISTS funnel_steps (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    funnel_id INT UNSIGNED NOT NULL,
    step_order INT UNSIGNED NOT NULL,
    event_name VARCHAR(255) NOT NULL,
    conditions JSON,
    step_name VARCHAR(255),
    INDEX idx_funnel_id (funnel_id),
    INDEX idx_step_order (funnel_id, step_order),
    FOREIGN KEY (funnel_id) REFERENCES funnels(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- TABLE: anomalies (pour V3)
-- Détection d'anomalies
-- ============================================
CREATE TABLE IF NOT EXISTS anomalies (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    detected_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    metric_name VARCHAR(255) NOT NULL,
    date_detected DATE NOT NULL,
    observed_value DECIMAL(15, 2),
    expected_value DECIMAL(15, 2),
    standard_deviation DECIMAL(15, 2),
    deviation_multiplier DECIMAL(5, 2),
    severity ENUM('low', 'medium', 'high') DEFAULT 'medium',
    is_acknowledged TINYINT(1) DEFAULT 0,
    INDEX idx_detected_at (detected_at),
    INDEX idx_metric_name (metric_name),
    INDEX idx_severity (severity)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- FIN DU SCHEMA
-- ============================================
