-- ============================================
-- Script SQL pour créer toutes les tables Laravel
-- Base de données : LLMbots.fr
-- ============================================

-- Table des utilisateurs
CREATE TABLE IF NOT EXISTS `users` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(255) NOT NULL,
    `email` VARCHAR(255) NOT NULL,
    `password_hash` VARCHAR(255) NOT NULL,
    `role` ENUM('user', 'admin') NOT NULL DEFAULT 'user',
    `last_login` DATETIME NULL,
    `login_count` INT NOT NULL DEFAULT 0,
    `created_at` TIMESTAMP NULL,
    `updated_at` TIMESTAMP NULL,
    `deleted_at` TIMESTAMP NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `users_email_unique` (`email`),
    KEY `users_email_index` (`email`),
    KEY `users_deleted_at_index` (`deleted_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table des tokens de réinitialisation de mot de passe
CREATE TABLE IF NOT EXISTS `password_reset_tokens` (
    `email` VARCHAR(255) NOT NULL,
    `token` VARCHAR(255) NOT NULL,
    `created_at` TIMESTAMP NULL,
    PRIMARY KEY (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table des sessions (CRITIQUE pour Laravel)
CREATE TABLE IF NOT EXISTS `sessions` (
    `id` VARCHAR(255) NOT NULL,
    `user_id` BIGINT UNSIGNED NULL,
    `ip_address` VARCHAR(45) NULL,
    `user_agent` TEXT NULL,
    `payload` LONGTEXT NOT NULL,
    `last_activity` INT NOT NULL,
    PRIMARY KEY (`id`),
    KEY `sessions_user_id_index` (`user_id`),
    KEY `sessions_last_activity_index` (`last_activity`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table du cache
CREATE TABLE IF NOT EXISTS `cache` (
    `key` VARCHAR(255) NOT NULL,
    `value` MEDIUMTEXT NOT NULL,
    `expiration` INT NOT NULL,
    PRIMARY KEY (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table des verrous de cache
CREATE TABLE IF NOT EXISTS `cache_locks` (
    `key` VARCHAR(255) NOT NULL,
    `owner` VARCHAR(255) NOT NULL,
    `expiration` INT NOT NULL,
    PRIMARY KEY (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table des jobs en file d'attente
CREATE TABLE IF NOT EXISTS `jobs` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `queue` VARCHAR(255) NOT NULL,
    `payload` LONGTEXT NOT NULL,
    `attempts` TINYINT UNSIGNED NOT NULL,
    `reserved_at` INT UNSIGNED NULL,
    `available_at` INT UNSIGNED NOT NULL,
    `created_at` INT UNSIGNED NOT NULL,
    PRIMARY KEY (`id`),
    KEY `jobs_queue_index` (`queue`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table des lots de jobs
CREATE TABLE IF NOT EXISTS `job_batches` (
    `id` VARCHAR(255) NOT NULL,
    `name` VARCHAR(255) NOT NULL,
    `total_jobs` INT NOT NULL,
    `pending_jobs` INT NOT NULL,
    `failed_jobs` INT NOT NULL,
    `failed_job_ids` LONGTEXT NOT NULL,
    `options` MEDIUMTEXT NULL,
    `cancelled_at` INT NULL,
    `created_at` INT NOT NULL,
    `finished_at` INT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table des jobs échoués
CREATE TABLE IF NOT EXISTS `failed_jobs` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `uuid` VARCHAR(255) NOT NULL,
    `connection` TEXT NOT NULL,
    `queue` TEXT NOT NULL,
    `payload` LONGTEXT NOT NULL,
    `exception` LONGTEXT NOT NULL,
    `failed_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `failed_jobs_uuid_unique` (`uuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table des bots IA
CREATE TABLE IF NOT EXISTS `bots` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(100) NOT NULL,
    `user_agent_pattern` VARCHAR(500) NOT NULL,
    `description` TEXT NULL,
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `bots_is_active_index` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table des sites web
CREATE TABLE IF NOT EXISTS `sites` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_id` BIGINT UNSIGNED NOT NULL,
    `name` VARCHAR(255) NOT NULL,
    `domain` VARCHAR(255) NULL,
    `description` TEXT NULL,
    `created_at` TIMESTAMP NULL,
    `updated_at` TIMESTAMP NULL,
    `deleted_at` TIMESTAMP NULL,
    PRIMARY KEY (`id`),
    KEY `sites_user_id_index` (`user_id`),
    KEY `sites_deleted_at_index` (`deleted_at`),
    CONSTRAINT `sites_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table des analyses
CREATE TABLE IF NOT EXISTS `analyses` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_id` BIGINT UNSIGNED NOT NULL,
    `site_id` BIGINT UNSIGNED NULL,
    `filename` VARCHAR(255) NOT NULL,
    `total_hits` INT NOT NULL DEFAULT 0,
    `total_urls` INT NOT NULL DEFAULT 0,
    `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `analyses_user_id_index` (`user_id`),
    KEY `analyses_site_id_index` (`site_id`),
    KEY `analyses_created_at_index` (`created_at`),
    CONSTRAINT `analyses_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
    CONSTRAINT `analyses_site_id_foreign` FOREIGN KEY (`site_id`) REFERENCES `sites` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table des logs IA détectés
CREATE TABLE IF NOT EXISTS `logs_ia` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `analysis_id` BIGINT UNSIGNED NOT NULL,
    `bot_id` BIGINT UNSIGNED NOT NULL,
    `date_time` DATETIME NOT NULL,
    `url` TEXT NOT NULL,
    `user_agent` TEXT NOT NULL,
    `ip` VARCHAR(45) NULL,
    `http_status` INT NULL,
    `resource_size` INT NULL,
    `http_method` VARCHAR(10) NULL,
    `referer` TEXT NULL,
    `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `logs_ia_analysis_id_index` (`analysis_id`),
    KEY `logs_ia_bot_id_index` (`bot_id`),
    KEY `logs_ia_date_time_index` (`date_time`),
    KEY `logs_ia_http_method_index` (`http_method`),
    CONSTRAINT `logs_ia_analysis_id_foreign` FOREIGN KEY (`analysis_id`) REFERENCES `analyses` (`id`) ON DELETE CASCADE,
    CONSTRAINT `logs_ia_bot_id_foreign` FOREIGN KEY (`bot_id`) REFERENCES `bots` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table des tokens d'accès personnel (pour API)
CREATE TABLE IF NOT EXISTS `personal_access_tokens` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `tokenable_type` VARCHAR(255) NOT NULL,
    `tokenable_id` BIGINT UNSIGNED NOT NULL,
    `name` VARCHAR(255) NOT NULL,
    `token` VARCHAR(64) NOT NULL,
    `abilities` TEXT NULL,
    `last_used_at` TIMESTAMP NULL,
    `expires_at` TIMESTAMP NULL,
    `created_at` TIMESTAMP NULL,
    `updated_at` TIMESTAMP NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `personal_access_tokens_token_unique` (`token`),
    KEY `personal_access_tokens_tokenable_type_tokenable_id_index` (`tokenable_type`, `tokenable_id`),
    KEY `personal_access_tokens_expires_at_index` (`expires_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- Insertion des 20 bots IA pré-configurés
-- ============================================

INSERT INTO `bots` (`name`, `user_agent_pattern`, `description`, `is_active`) VALUES
('GPTBot', 'GPTBot', 'OpenAI GPTBot crawler', 1),
('ChatGPT-User', 'ChatGPT-User', 'OpenAI ChatGPT User Agent', 1),
('CCBot', 'CCBot', 'Common Crawl Bot', 1),
('anthropic-ai', 'anthropic-ai', 'Anthropic Claude AI', 1),
('Claude-Web', 'Claude-Web', 'Anthropic Claude Web', 1),
('Google-Extended', 'Google-Extended', 'Google AI Extended', 1),
('PerplexityBot', 'PerplexityBot', 'Perplexity AI Bot', 1),
('Perplexity', 'Perplexity', 'Perplexity AI', 1),
('Applebot-Extended', 'Applebot-Extended', 'Apple AI Extended', 1),
('BingPreview', 'BingPreview', 'Microsoft Bing Preview', 1),
('Bingbot', 'Bingbot', 'Microsoft Bing Bot', 1),
('facebookexternalhit', 'facebookexternalhit', 'Facebook Bot', 1),
('Twitterbot', 'Twitterbot', 'Twitter Bot', 1),
('LinkedInBot', 'LinkedInBot', 'LinkedIn Bot', 1),
('Slackbot', 'Slackbot', 'Slack Bot', 1),
('Discordbot', 'Discordbot', 'Discord Bot', 1),
('WhatsApp', 'WhatsApp', 'WhatsApp Bot', 1),
('TelegramBot', 'TelegramBot', 'Telegram Bot', 1),
('SemrushBot', 'SemrushBot', 'SEMrush Bot', 1),
('AhrefsBot', 'AhrefsBot', 'Ahrefs Bot', 1)
ON DUPLICATE KEY UPDATE `name`=VALUES(`name`);