-- ==============================================================================
-- ATHENA CONTRÔLE - Script d'initialisation complète
-- Base de données: athena_controle
-- Exécuter sur: da489391-002.eu.clouddb.ovh.net:35173
-- ==============================================================================

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

USE athena_controle;

-- ==============================================================================
-- TABLE: user_qualifications (Qualifications inspecteurs)
-- ==============================================================================
CREATE TABLE user_qualifications (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    operation_code VARCHAR(20) NOT NULL COMMENT 'Code opération CEE (ex: BAR-EN-101)',
    qualified_at DATE NOT NULL,
    expires_at DATE,
    certificate_path VARCHAR(500),
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    UNIQUE KEY uk_user_operation (user_id, operation_code),
    INDEX idx_operation (operation_code),
    INDEX idx_expires (expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ==============================================================================
-- TABLE: clients (Clients/Donneurs d'ordre)
-- ==============================================================================
CREATE TABLE clients (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(20) NOT NULL UNIQUE COMMENT 'Code client interne',
    name VARCHAR(255) NOT NULL,
    legal_name VARCHAR(255),
    siret VARCHAR(14),
    vat_number VARCHAR(20),
    address VARCHAR(255),
    address2 VARCHAR(255),
    postal_code VARCHAR(10),
    city VARCHAR(100),
    country VARCHAR(2) DEFAULT 'FR',
    phone VARCHAR(20),
    email VARCHAR(255),
    website VARCHAR(255),
    type ENUM('obligé', 'délégataire', 'mandataire', 'installateur', 'autre') DEFAULT 'obligé',
    billing_email VARCHAR(255),
    payment_terms INT DEFAULT 30 COMMENT 'Délai de paiement en jours',
    notes TEXT,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,
    
    INDEX idx_code (code),
    INDEX idx_siret (siret),
    INDEX idx_type (type),
    INDEX idx_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ==============================================================================
-- TABLE: client_contacts (Contacts clients)
-- ==============================================================================
CREATE TABLE client_contacts (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    client_id INT UNSIGNED NOT NULL,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    job_title VARCHAR(100),
    email VARCHAR(255),
    phone VARCHAR(20),
    mobile VARCHAR(20),
    is_primary BOOLEAN DEFAULT FALSE,
    is_billing_contact BOOLEAN DEFAULT FALSE,
    is_technical_contact BOOLEAN DEFAULT FALSE,
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE,
    INDEX idx_primary (client_id, is_primary)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ==============================================================================
-- TABLE: orders (Commandes/Dossiers)
-- ==============================================================================
CREATE TABLE orders (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    reference VARCHAR(50) NOT NULL UNIQUE COMMENT 'Référence commande',
    client_id INT UNSIGNED NOT NULL,
    client_reference VARCHAR(100) COMMENT 'Référence client',
    status ENUM('draft', 'confirmed', 'in_progress', 'completed', 'invoiced', 'cancelled') DEFAULT 'draft',
    operation_code VARCHAR(20) NOT NULL COMMENT 'Code opération CEE',
    description TEXT,
    quantity INT DEFAULT 1 COMMENT 'Nombre de sites',
    unit_price DECIMAL(10,2) COMMENT 'Prix unitaire HT',
    total_ht DECIMAL(12,2),
    vat_rate DECIMAL(4,2) DEFAULT 20.00,
    total_ttc DECIMAL(12,2),
    ordered_at DATE,
    deadline DATE COMMENT 'Date limite de réalisation',
    completed_at DATE,
    notes TEXT,
    created_by INT UNSIGNED,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (client_id) REFERENCES clients(id),
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_reference (reference),
    INDEX idx_client (client_id),
    INDEX idx_status (status),
    INDEX idx_operation (operation_code),
    INDEX idx_deadline (deadline)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ==============================================================================
-- TABLE: sites (Sites d'inspection)
-- ==============================================================================
CREATE TABLE sites (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    order_id INT UNSIGNED NOT NULL,
    reference VARCHAR(100) COMMENT 'Référence site client',
    beneficiary_name VARCHAR(255) NOT NULL COMMENT 'Nom du bénéficiaire',
    beneficiary_type ENUM('particulier', 'professionnel', 'collectivite') DEFAULT 'particulier',
    address VARCHAR(255) NOT NULL,
    address2 VARCHAR(255),
    postal_code VARCHAR(10) NOT NULL,
    city VARCHAR(100) NOT NULL,
    country VARCHAR(2) DEFAULT 'FR',
    latitude DECIMAL(10, 8),
    longitude DECIMAL(11, 8),
    contact_name VARCHAR(200),
    contact_phone VARCHAR(20),
    contact_email VARCHAR(255),
    access_info TEXT COMMENT 'Informations accès',
    work_date DATE COMMENT 'Date des travaux',
    work_description TEXT,
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    INDEX idx_order (order_id),
    INDEX idx_postal (postal_code),
    INDEX idx_beneficiary (beneficiary_name(100))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ==============================================================================
-- TABLE: inspections (Inspections)
-- ==============================================================================
CREATE TABLE inspections (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    reference VARCHAR(50) NOT NULL UNIQUE COMMENT 'Référence inspection',
    order_id INT UNSIGNED NOT NULL,
    site_id INT UNSIGNED,
    inspector_id INT UNSIGNED COMMENT 'Inspecteur assigné',
    status ENUM('draft', 'scheduled', 'in_progress', 'completed', 'cancelled') DEFAULT 'draft',
    type ENUM('initial', 'complementary', 'contradictory') DEFAULT 'initial',
    scheduled_date DATE,
    scheduled_time TIME,
    start_latitude DECIMAL(10, 8),
    start_longitude DECIMAL(11, 8),
    start_accuracy FLOAT,
    end_latitude DECIMAL(10, 8),
    end_longitude DECIMAL(11, 8),
    started_at TIMESTAMP NULL,
    completed_at TIMESTAMP NULL,
    duration_minutes INT,
    notes TEXT,
    client_signature VARCHAR(500),
    client_signatory_name VARCHAR(100),
    inspector_signature VARCHAR(500),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    synced_at TIMESTAMP NULL,
    
    FOREIGN KEY (order_id) REFERENCES orders(id),
    FOREIGN KEY (site_id) REFERENCES sites(id) ON DELETE SET NULL,
    FOREIGN KEY (inspector_id) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_reference (reference),
    INDEX idx_order (order_id),
    INDEX idx_inspector (inspector_id),
    INDEX idx_status (status),
    INDEX idx_scheduled (scheduled_date),
    INDEX idx_mobile (inspector_id, status, scheduled_date),
    INDEX idx_sync (inspector_id, updated_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ==============================================================================
-- TABLE: inspection_checkpoints (Points de contrôle)
-- ==============================================================================
CREATE TABLE inspection_checkpoints (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    inspection_id INT UNSIGNED NOT NULL,
    code VARCHAR(50) NOT NULL COMMENT 'Code du checkpoint',
    result ENUM('conforme', 'non_conforme', 'non_applicable', 'non_verifie') DEFAULT 'non_verifie',
    value VARCHAR(255) COMMENT 'Valeur mesurée',
    unit VARCHAR(20),
    comment TEXT,
    checked_at TIMESTAMP NULL,
    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: inspection_photos (Photos)
-- ==============================================================================
CREATE TABLE inspection_photos (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    inspection_id INT UNSIGNED NOT NULL,
    checkpoint_code VARCHAR(50),
    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,
    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: reports (Rapports d'inspection)
-- ==============================================================================
CREATE TABLE reports (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    reference VARCHAR(50) NOT NULL UNIQUE,
    inspection_id INT UNSIGNED NOT NULL,
    status ENUM('draft', 'pending_review', 'reviewed', 'approved', 'signed', 'sent', 'archived') DEFAULT 'draft',
    conformity ENUM('conforme', 'non_conforme', 'reserve') DEFAULT 'conforme',
    conclusion TEXT,
    non_conformities JSON COMMENT 'Liste des non-conformités',
    recommendations TEXT,
    reviewed_by INT UNSIGNED,
    reviewed_at TIMESTAMP NULL,
    approved_by INT UNSIGNED,
    approved_at TIMESTAMP NULL,
    signed_at TIMESTAMP NULL,
    pdf_path VARCHAR(500),
    pdf_generated_at TIMESTAMP NULL,
    sent_at TIMESTAMP NULL,
    sent_to JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (inspection_id) REFERENCES inspections(id),
    FOREIGN KEY (reviewed_by) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (approved_by) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_reference (reference),
    INDEX idx_inspection (inspection_id),
    INDEX idx_status (status),
    INDEX idx_conformity (conformity)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ==============================================================================
-- TABLE: batches (Lots de rapports)
-- ==============================================================================
CREATE TABLE batches (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    reference VARCHAR(50) NOT NULL UNIQUE,
    client_id INT UNSIGNED NOT NULL,
    status ENUM('draft', 'ready', 'sent', 'acknowledged') DEFAULT 'draft',
    reports_count INT DEFAULT 0,
    zip_path VARCHAR(500),
    zip_generated_at TIMESTAMP NULL,
    sent_at TIMESTAMP NULL,
    sent_to JSON,
    acknowledged_at TIMESTAMP NULL,
    notes TEXT,
    created_by INT UNSIGNED,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (client_id) REFERENCES clients(id),
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_reference (reference),
    INDEX idx_client (client_id),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ==============================================================================
-- TABLE: batch_reports (Liaison lots-rapports)
-- ==============================================================================
CREATE TABLE batch_reports (
    batch_id INT UNSIGNED NOT NULL,
    report_id INT UNSIGNED NOT NULL,
    added_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    PRIMARY KEY (batch_id, report_id),
    FOREIGN KEY (batch_id) REFERENCES batches(id) ON DELETE CASCADE,
    FOREIGN KEY (report_id) REFERENCES reports(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ==============================================================================
-- TABLE: invoices (Factures)
-- ==============================================================================
CREATE TABLE invoices (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    number VARCHAR(50) NOT NULL UNIQUE COMMENT 'Numéro facture',
    client_id INT UNSIGNED NOT NULL,
    type ENUM('invoice', 'credit_note') DEFAULT 'invoice',
    status ENUM('draft', 'sent', 'paid', 'partial', 'overdue', 'cancelled') DEFAULT 'draft',
    issue_date DATE NOT NULL,
    due_date DATE NOT NULL,
    total_ht DECIMAL(12,2) NOT NULL DEFAULT 0,
    vat_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
    total_ttc DECIMAL(12,2) NOT NULL DEFAULT 0,
    paid_amount DECIMAL(12,2) DEFAULT 0,
    currency VARCHAR(3) DEFAULT 'EUR',
    payment_method VARCHAR(50),
    payment_reference VARCHAR(100),
    notes TEXT,
    pdf_path VARCHAR(500),
    sent_at TIMESTAMP NULL,
    paid_at TIMESTAMP NULL,
    created_by INT UNSIGNED,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (client_id) REFERENCES clients(id),
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_number (number),
    INDEX idx_client (client_id),
    INDEX idx_status (status),
    INDEX idx_due (due_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ==============================================================================
-- TABLE: invoice_lines (Lignes de facture)
-- ==============================================================================
CREATE TABLE invoice_lines (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    invoice_id INT UNSIGNED NOT NULL,
    order_id INT UNSIGNED,
    description VARCHAR(500) NOT NULL,
    quantity DECIMAL(10,2) DEFAULT 1,
    unit_price DECIMAL(10,2) NOT NULL,
    vat_rate DECIMAL(4,2) DEFAULT 20.00,
    total_ht DECIMAL(12,2) NOT NULL,
    sort_order INT DEFAULT 0,
    
    FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE CASCADE,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE SET NULL,
    INDEX idx_invoice (invoice_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ==============================================================================
-- TABLE: invoice_payments (Paiements)
-- ==============================================================================
CREATE TABLE invoice_payments (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    invoice_id INT UNSIGNED NOT NULL,
    amount DECIMAL(12,2) NOT NULL,
    payment_date DATE NOT NULL,
    payment_method VARCHAR(50),
    reference VARCHAR(100),
    notes TEXT,
    created_by INT UNSIGNED,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE CASCADE,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_invoice (invoice_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ==============================================================================
-- TABLE: audit_logs (Journal d'audit)
-- ==============================================================================
CREATE TABLE audit_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED,
    action VARCHAR(100) NOT NULL,
    entity_type VARCHAR(50),
    entity_id INT UNSIGNED,
    old_values JSON,
    new_values JSON,
    ip_address VARCHAR(45),
    user_agent VARCHAR(500),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_user (user_id),
    INDEX idx_action (action),
    INDEX idx_entity (entity_type, entity_id),
    INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ==============================================================================
-- TABLE: notifications (Notifications)
-- ==============================================================================
CREATE TABLE notifications (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    type VARCHAR(50) NOT NULL,
    title VARCHAR(255) NOT NULL,
    body TEXT,
    data JSON,
    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;

-- ==============================================================================
-- TABLE: sync_logs (Logs de synchronisation mobile)
-- ==============================================================================
CREATE TABLE 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,
    records_pulled INT DEFAULT 0,
    records_pushed INT DEFAULT 0,
    errors TEXT,
    duration_ms INT,
    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: sessions (Sessions utilisateurs)
-- ==============================================================================
CREATE TABLE sessions (
    id VARCHAR(128) PRIMARY KEY,
    user_id INT UNSIGNED,
    ip_address VARCHAR(45),
    user_agent TEXT,
    payload TEXT NOT NULL,
    last_activity INT UNSIGNED NOT NULL,
    
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user (user_id),
    INDEX idx_activity (last_activity)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ==============================================================================
-- FIN DU SCRIPT
-- ==============================================================================
