-- Migration: Création de la table settings
-- Date: 2025-09-09
-- Description: Table pour les paramètres globaux du système

CREATE TABLE `settings` (
    `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `setting_key` varchar(255) NOT NULL,
    `setting_value` longtext NULL DEFAULT NULL,
    `setting_type` enum('string', 'number', 'boolean', 'json', 'encrypted') NOT NULL DEFAULT 'string',
    `setting_group` varchar(100) NULL DEFAULT NULL,
    `is_public` tinyint(1) NOT NULL DEFAULT 0,
    `description` text NULL DEFAULT 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 `settings_key_unique` (`setting_key`),
    KEY `settings_group_index` (`setting_group`),
    KEY `settings_is_public_index` (`is_public`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table pour les logs d'audit
CREATE TABLE `audit_logs` (
    `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_id` bigint(20) UNSIGNED NULL DEFAULT NULL,
    `action` varchar(255) NOT NULL,
    `model_type` varchar(255) NULL DEFAULT NULL,
    `model_id` bigint(20) UNSIGNED NULL DEFAULT NULL,
    `old_values` json NULL DEFAULT NULL,
    `new_values` json NULL DEFAULT NULL,
    `ip_address` varchar(45) NULL DEFAULT NULL,
    `user_agent` text NULL DEFAULT NULL,
    `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `audit_logs_user_id_index` (`user_id`),
    KEY `audit_logs_action_index` (`action`),
    KEY `audit_logs_model_index` (`model_type`, `model_id`),
    FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table pour les services/produits
CREATE TABLE `services` (
    `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` varchar(255) NOT NULL,
    `slug` varchar(255) NOT NULL,
    `description` text NULL DEFAULT NULL,
    `features` json NULL DEFAULT NULL,
    `pricing` json NOT NULL,
    `category` varchar(100) NOT NULL,
    `image` varchar(255) NULL DEFAULT NULL,
    `is_active` tinyint(1) NOT NULL DEFAULT 1,
    `sort_order` int(11) NOT NULL DEFAULT 0,
    `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 `services_slug_unique` (`slug`),
    KEY `services_category_index` (`category`),
    KEY `services_is_active_index` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table pour les commandes
CREATE TABLE `orders` (
    `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_id` bigint(20) UNSIGNED NOT NULL,
    `service_id` bigint(20) UNSIGNED NOT NULL,
    `order_number` varchar(100) NOT NULL,
    `status` enum('pending', 'processing', 'completed', 'cancelled', 'refunded') NOT NULL DEFAULT 'pending',
    `total_amount` decimal(10,2) NOT NULL,
    `currency` varchar(3) NOT NULL DEFAULT 'EUR',
    `payment_method` varchar(50) NULL DEFAULT NULL,
    `payment_status` enum('pending', 'paid', 'failed', 'refunded') NOT NULL DEFAULT 'pending',
    `billing_data` json NOT NULL,
    `service_config` json NULL DEFAULT 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 `orders_order_number_unique` (`order_number`),
    KEY `orders_user_id_index` (`user_id`),
    KEY `orders_service_id_index` (`service_id`),
    KEY `orders_status_index` (`status`),
    FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE RESTRICT,
    FOREIGN KEY (`service_id`) REFERENCES `services` (`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
