-- ============================================
-- Athena Contrôle - Schéma de Base de Données
-- ============================================
-- Version: 1.0.0
-- Compatible: MySQL 8.0+ / MariaDB 10.4+
-- Encodage: UTF8MB4
-- ============================================


-- ============================================
-- Table: clients
-- ============================================
CREATE TABLE IF NOT EXISTS `clients` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `company_name` VARCHAR(255) NOT NULL,
    `siret` VARCHAR(14) NULL,
    `address` VARCHAR(255) NULL,
    `postal_code` VARCHAR(10) NULL,
    `city` VARCHAR(100) NULL,
    `country` VARCHAR(100) NOT NULL DEFAULT 'France',
    `contact_first_name` VARCHAR(100) NULL,
    `contact_last_name` VARCHAR(100) NULL,
    `contact_email` VARCHAR(255) NULL,
    `contact_phone` VARCHAR(20) NULL,
    `billing_email` VARCHAR(255) NULL,
    `payment_terms` INT NOT NULL DEFAULT 30 COMMENT 'Délai de paiement en jours',
    `notes` TEXT NULL,
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `deleted_at` DATETIME NULL,
    INDEX `clients_company_name_index` (`company_name`),
    INDEX `clients_siret_index` (`siret`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- Table: orders (Commandes)
-- ============================================
CREATE TABLE IF NOT EXISTS `orders` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `client_id` INT UNSIGNED NOT NULL,
    `order_number` VARCHAR(50) NOT NULL,
    `reference` VARCHAR(100) NULL COMMENT 'Référence client',
    `operation_code` VARCHAR(20) NOT NULL,
    `operation_group` TINYINT UNSIGNED NOT NULL,
    `quantity` INT UNSIGNED NOT NULL DEFAULT 1,
    `unit_price` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    `total_amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `deadline` DATE NULL,
    `priority` ENUM('low', 'normal', 'high', 'urgent') NOT NULL DEFAULT 'normal',
    `status` ENUM('pending', 'confirmed', 'in_progress', 'completed', 'invoiced', 'cancelled') NOT NULL DEFAULT 'pending',
    `notes` TEXT NULL,
    `documents` JSON NULL COMMENT 'Documents joints',
    `assigned_inspector_id` INT UNSIGNED NULL,
    `confirmed_at` DATETIME NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY `orders_order_number_unique` (`order_number`),
    FOREIGN KEY (`client_id`) REFERENCES `clients`(`id`) ON DELETE RESTRICT,
    FOREIGN KEY (`assigned_inspector_id`) REFERENCES `users`(`id`) ON DELETE SET NULL,
    INDEX `orders_status_index` (`status`),
    INDEX `orders_deadline_index` (`deadline`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- Table: inspections
-- ============================================
CREATE TABLE IF NOT EXISTS `inspections` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `order_id` INT UNSIGNED NOT NULL,
    `inspector_id` INT UNSIGNED NOT NULL,
    `operation_code` VARCHAR(20) NOT NULL,
    `operation_group` TINYINT UNSIGNED NOT NULL,
    `beneficiary_name` VARCHAR(255) NOT NULL,
    `beneficiary_address` VARCHAR(255) NOT NULL,
    `beneficiary_postal_code` VARCHAR(10) NOT NULL,
    `beneficiary_city` VARCHAR(100) NOT NULL,
    `inspection_date` DATE NOT NULL,
    `inspection_start_time` TIME NULL,
    `inspection_end_time` TIME NULL,
    `form_data` JSON NULL COMMENT 'Données du formulaire d''inspection',
    `latitude` DECIMAL(10,8) NULL,
    `longitude` DECIMAL(11,8) NULL,
    `status` ENUM('draft', 'submitted', 'in_review', 'approved', 'rejected', 'signed') NOT NULL DEFAULT 'draft',
    `notes` TEXT NULL,
    `submitted_at` DATETIME NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`order_id`) REFERENCES `orders`(`id`) ON DELETE RESTRICT,
    FOREIGN KEY (`inspector_id`) REFERENCES `users`(`id`) ON DELETE RESTRICT,
    INDEX `inspections_status_index` (`status`),
    INDEX `inspections_date_index` (`inspection_date`),
    INDEX `inspections_operation_index` (`operation_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- Table: batches (Lots)
-- ============================================
CREATE TABLE IF NOT EXISTS `batches` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `client_id` INT UNSIGNED NOT NULL,
    `batch_number` VARCHAR(50) NOT NULL,
    `name` VARCHAR(255) NULL,
    `description` TEXT NULL,
    `operation_code` VARCHAR(20) NULL,
    `status` ENUM('open', 'finalized', 'delivered') NOT NULL DEFAULT 'open',
    `finalized_at` DATETIME NULL,
    `delivered_at` DATETIME NULL,
    `synthesis_pdf_path` VARCHAR(500) NULL,
    `synthesis_excel_path` VARCHAR(500) NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY `batches_batch_number_unique` (`batch_number`),
    FOREIGN KEY (`client_id`) REFERENCES `clients`(`id`) ON DELETE RESTRICT,
    INDEX `batches_status_index` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- Table: quotes (Devis)
-- ============================================
CREATE TABLE IF NOT EXISTS `quotes` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `client_id` INT UNSIGNED NOT NULL,
    `quote_number` VARCHAR(50) NOT NULL,
    `reference` VARCHAR(100) NULL,
    `issue_date` DATE NOT NULL,
    `validity_date` DATE NOT NULL,
    `items` JSON NOT NULL,
    `subtotal` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `tax_rate` DECIMAL(5,2) NOT NULL DEFAULT 20.00,
    `tax_amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `total_amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `notes` TEXT NULL,
    `conditions` TEXT NULL,
    `status` ENUM('draft', 'sent', 'accepted', 'rejected', 'expired', 'converted') NOT NULL DEFAULT 'draft',
    `pdf_path` VARCHAR(500) NULL,
    `sent_at` DATETIME NULL,
    `accepted_at` DATETIME NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY `quotes_quote_number_unique` (`quote_number`),
    FOREIGN KEY (`client_id`) REFERENCES `clients`(`id`) ON DELETE RESTRICT,
    INDEX `quotes_status_index` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- Table: invoices (Factures)
-- ============================================
CREATE TABLE IF NOT EXISTS `invoices` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `client_id` INT UNSIGNED NOT NULL,
    `order_id` INT UNSIGNED NULL,
    `quote_id` INT UNSIGNED NULL,
    `invoice_number` VARCHAR(50) NOT NULL,
    `reference` VARCHAR(100) NULL,
    `issue_date` DATE NOT NULL,
    `due_date` DATE NOT NULL,
    `items` JSON NOT NULL,
    `subtotal` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `tax_rate` DECIMAL(5,2) NOT NULL DEFAULT 20.00,
    `tax_amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `total_amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `paid_amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `payment_date` DATE NULL,
    `payment_method` VARCHAR(50) NULL,
    `notes` TEXT NULL,
    `conditions` TEXT NULL,
    `status` ENUM('draft', 'sent', 'paid', 'partial', 'overdue', 'cancelled') NOT NULL DEFAULT 'draft',
    `pdf_path` VARCHAR(500) NULL,
    `sent_at` DATETIME NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY `invoices_invoice_number_unique` (`invoice_number`),
    FOREIGN KEY (`client_id`) REFERENCES `clients`(`id`) ON DELETE RESTRICT,
    FOREIGN KEY (`order_id`) REFERENCES `orders`(`id`) ON DELETE SET NULL,
    FOREIGN KEY (`quote_id`) REFERENCES `quotes`(`id`) ON DELETE SET NULL,
    INDEX `invoices_status_index` (`status`),
    INDEX `invoices_due_date_index` (`due_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- Table: documents (Documentation technique)
-- ============================================
CREATE TABLE IF NOT EXISTS `documents` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `title` VARCHAR(255) NOT NULL,
    `description` TEXT NULL,
    `category` VARCHAR(100) NOT NULL,
    `filename` VARCHAR(255) NOT NULL,
    `original_filename` VARCHAR(255) NOT NULL,
    `file_path` VARCHAR(500) NOT NULL,
    `mime_type` VARCHAR(100) NOT NULL,
    `file_size` INT UNSIGNED NOT NULL,
    `version` VARCHAR(20) NULL,
    `uploaded_by` INT UNSIGNED NOT NULL,
    `is_public` TINYINT(1) NOT NULL DEFAULT 1,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`uploaded_by`) REFERENCES `users`(`id`) ON DELETE RESTRICT,
    INDEX `documents_category_index` (`category`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- Table: planning
-- ============================================
CREATE TABLE IF NOT EXISTS `planning` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `inspector_id` INT UNSIGNED NOT NULL,
    `order_id` INT UNSIGNED NULL,
    `title` VARCHAR(255) NOT NULL,
    `description` TEXT NULL,
    `start_datetime` DATETIME NOT NULL,
    `end_datetime` DATETIME NOT NULL,
    `location` VARCHAR(255) NULL,
    `latitude` DECIMAL(10,8) NULL,
    `longitude` DECIMAL(11,8) NULL,
    `status` ENUM('scheduled', 'confirmed', 'completed', 'cancelled') NOT NULL DEFAULT 'scheduled',
    `created_by` INT UNSIGNED NOT NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`inspector_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`order_id`) REFERENCES `orders`(`id`) ON DELETE SET NULL,
    FOREIGN KEY (`created_by`) REFERENCES `users`(`id`) ON DELETE RESTRICT,
    INDEX `planning_datetime_index` (`start_datetime`, `end_datetime`),
    INDEX `planning_inspector_index` (`inspector_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- Table: audit_logs
-- ============================================
CREATE TABLE IF NOT EXISTS `audit_logs` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT UNSIGNED NULL,
    `user_email` VARCHAR(255) NULL,
    `action` VARCHAR(50) NOT NULL,
    `entity_type` VARCHAR(50) NULL,
    `entity_id` INT UNSIGNED NULL,
    `old_values` JSON NULL,
    `new_values` JSON NULL,
    `description` TEXT NULL,
    `ip_address` VARCHAR(45) NULL,
    `user_agent` VARCHAR(500) NULL,
    `url` VARCHAR(500) NULL,
    `method` VARCHAR(10) NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX `audit_logs_user_index` (`user_id`),
    INDEX `audit_logs_action_index` (`action`),
    INDEX `audit_logs_entity_index` (`entity_type`, `entity_id`),
    INDEX `audit_logs_created_index` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- Table: rate_limits
-- ============================================
CREATE TABLE IF NOT EXISTS `rate_limits` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `rate_key` VARCHAR(255) NOT NULL,
    `ip_address` VARCHAR(45) NOT NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX `rate_limits_key_index` (`rate_key`),
    INDEX `rate_limits_created_index` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- Table: revoked_tokens
-- ============================================
CREATE TABLE IF NOT EXISTS `revoked_tokens` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `jti` VARCHAR(64) NOT NULL,
    `expires_at` DATETIME NOT NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY `revoked_tokens_jti_unique` (`jti`),
    INDEX `revoked_tokens_expires_index` (`expires_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- Table: sessions
-- ============================================
CREATE TABLE IF NOT EXISTS `sessions` (
    `id` VARCHAR(128) PRIMARY KEY,
    `user_id` INT UNSIGNED NULL,
    `ip_address` VARCHAR(45) NULL,
    `user_agent` TEXT NULL,
    `payload` TEXT NOT NULL,
    `last_activity` INT NOT NULL,
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    INDEX `sessions_user_index` (`user_id`),
    INDEX `sessions_last_activity_index` (`last_activity`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

