-- ============================================================================
-- ATHENA CONTRÔLE - Tables additionnelles pour API Mobile
-- Migration: 002_mobile_api.sql
-- ============================================================================

-- Table des devices enregistrés pour notifications push
CREATE TABLE IF NOT EXISTS user_devices (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    device_id VARCHAR(255) NOT NULL COMMENT 'Identifiant unique du device',
    fcm_token VARCHAR(500) COMMENT 'Token Firebase Cloud Messaging',
    platform ENUM('android', 'ios') DEFAULT 'android',
    device_name VARCHAR(100),
    app_version VARCHAR(20),
    os_version VARCHAR(50),
    last_active_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    UNIQUE KEY uk_user_device (user_id, device_id),
    INDEX idx_fcm_token (fcm_token(191))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table des checkpoints d'inspection
CREATE TABLE IF NOT EXISTS inspection_checkpoints (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    inspection_id INT UNSIGNED NOT NULL,
    code VARCHAR(50) NOT NULL COMMENT 'Code du checkpoint (ex: EN101-01)',
    result ENUM('conforme', 'non_conforme', 'non_applicable', 'non_verifie') DEFAULT 'non_verifie',
    value VARCHAR(255) COMMENT 'Valeur mesurée ou saisie',
    unit VARCHAR(20) COMMENT 'Unité de mesure',
    comment TEXT,
    checked_at TIMESTAMP NULL COMMENT 'Date/heure du contrôle',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (inspection_id) REFERENCES inspections(id) ON DELETE CASCADE,
    UNIQUE KEY uk_inspection_checkpoint (inspection_id, code),
    INDEX idx_result (result)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table des photos
CREATE TABLE IF NOT EXISTS inspection_photos (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    inspection_id INT UNSIGNED NOT NULL,
    checkpoint_code VARCHAR(50) COMMENT 'Lié à un checkpoint spécifique',
    filename VARCHAR(255) NOT NULL,
    path VARCHAR(500) NOT NULL,
    category ENUM('general', 'before', 'after', 'detail', 'label', 'signature') DEFAULT 'general',
    description TEXT,
    latitude DECIMAL(10, 8),
    longitude DECIMAL(11, 8),
    taken_at TIMESTAMP NULL,
    file_size INT UNSIGNED,
    mime_type VARCHAR(50),
    uploaded_by INT UNSIGNED,
    synced_at TIMESTAMP NULL COMMENT 'Date de sync avec le serveur',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (inspection_id) REFERENCES inspections(id) ON DELETE CASCADE,
    FOREIGN KEY (uploaded_by) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_checkpoint (inspection_id, checkpoint_code),
    INDEX idx_category (category)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table de synchronisation (tracking)
CREATE TABLE IF NOT EXISTS sync_logs (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    device_id VARCHAR(255),
    sync_type ENUM('pull', 'push', 'full') NOT NULL,
    entities_synced TEXT COMMENT 'JSON des entités synchronisées',
    records_pulled INT DEFAULT 0,
    records_pushed INT DEFAULT 0,
    errors TEXT COMMENT 'JSON des erreurs',
    duration_ms INT COMMENT 'Durée en millisecondes',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_sync (user_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table des notifications
CREATE TABLE IF NOT EXISTS notifications (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    type VARCHAR(50) NOT NULL COMMENT 'Type de notification',
    title VARCHAR(255) NOT NULL,
    body TEXT,
    data JSON COMMENT 'Données additionnelles',
    read_at TIMESTAMP NULL,
    sent_via_push BOOLEAN DEFAULT FALSE,
    push_sent_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_unread (user_id, read_at),
    INDEX idx_type (type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Ajout de colonnes à la table inspections pour le mobile
ALTER TABLE inspections
    ADD COLUMN IF NOT EXISTS start_latitude DECIMAL(10, 8) AFTER scheduled_time,
    ADD COLUMN IF NOT EXISTS start_longitude DECIMAL(11, 8) AFTER start_latitude,
    ADD COLUMN IF NOT EXISTS start_accuracy FLOAT AFTER start_longitude,
    ADD COLUMN IF NOT EXISTS end_latitude DECIMAL(10, 8) AFTER start_accuracy,
    ADD COLUMN IF NOT EXISTS end_longitude DECIMAL(11, 8) AFTER end_latitude,
    ADD COLUMN IF NOT EXISTS client_signature VARCHAR(500) AFTER notes,
    ADD COLUMN IF NOT EXISTS client_signatory_name VARCHAR(100) AFTER client_signature,
    ADD COLUMN IF NOT EXISTS synced_at TIMESTAMP NULL AFTER updated_at;

-- Index pour optimiser les requêtes mobiles
CREATE INDEX IF NOT EXISTS idx_inspections_mobile 
    ON inspections(inspector_id, status, scheduled_date);

CREATE INDEX IF NOT EXISTS idx_inspections_sync 
    ON inspections(inspector_id, updated_at);
