<?php

namespace ComptaFlowPro\Services;

defined('ABSPATH') || exit;

class DB {
    
    public static function createTables(): void {
        global $wpdb;
        
        $charset_collate = $wpdb->get_charset_collate();
        $prefix = $wpdb->prefix . 'cfp_';
        
        $sql = "
        -- Table des comptes (plan comptable)
        CREATE TABLE IF NOT EXISTS {$prefix}accounts (
            compte_num varchar(20) NOT NULL,
            label varchar(120) NOT NULL,
            type enum('ACTIF','PASSIF','CHARGE','PRODUIT','HORS_BILAN') NOT NULL,
            is_system tinyint(1) NOT NULL DEFAULT 0,
            PRIMARY KEY (compte_num)
        ) $charset_collate;
        
        -- Table des écritures comptables
        CREATE TABLE IF NOT EXISTS {$prefix}journal_entries (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            uuid char(36) NOT NULL,
            date_ecriture date NOT NULL,
            journal_code varchar(10) NOT NULL,
            line_no int NOT NULL,
            piece_ref varchar(50) DEFAULT NULL,
            piece_date date DEFAULT NULL,
            compte_num varchar(20) NOT NULL,
            compte_lib varchar(100) DEFAULT NULL,
            libelle varchar(200) NOT NULL,
            debit decimal(15,2) NOT NULL DEFAULT 0.00,
            credit decimal(15,2) NOT NULL DEFAULT 0.00,
            ecriture_num bigint DEFAULT NULL,
            code_tva varchar(10) DEFAULT NULL,
            partner_code varchar(60) DEFAULT NULL,
            analytic varchar(60) DEFAULT NULL,
            currency char(3) NOT NULL DEFAULT 'EUR',
            locked_at datetime DEFAULT NULL,
            created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            created_by bigint(20) unsigned DEFAULT NULL,
            source varchar(30) NOT NULL,
            transaction_id bigint(20) unsigned DEFAULT NULL COMMENT 'Lien vers l''ID de la table wp_cfp_transactions',
            txn_uuid char(36) DEFAULT NULL,
            meta_json longtext DEFAULT NULL,
            PRIMARY KEY (id),
            UNIQUE KEY uuid (uuid),
            KEY idx_date_ecriture (date_ecriture),
            KEY idx_compte_num (compte_num),
            KEY idx_txn_uuid (txn_uuid),
            KEY idx_journal_code (journal_code),
            KEY idx_transaction_id (transaction_id)
        ) $charset_collate;
        
        -- Table des transactions
        CREATE TABLE IF NOT EXISTS {$prefix}transactions (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            uuid char(36) NOT NULL,
            type varchar(50) NOT NULL COMMENT 'Type de transaction métier (SALE, EXPENSE, VAT_PAYMENT, etc.)',
            date_txn date NOT NULL,
            label varchar(200) NOT NULL,
            account_code varchar(20) DEFAULT NULL COMMENT 'Compte de charge/produit principal',
            amount_ht decimal(15,2) DEFAULT NULL,
            amount_tva decimal(15,2) DEFAULT NULL,
            amount_ttc decimal(15,2) NOT NULL,
            vat_rate decimal(5,2) DEFAULT NULL,
            vat_code varchar(10) DEFAULT NULL,
            payment_method varchar(20) DEFAULT NULL,
            payment_status enum('PENDING','PAID','PARTIAL') NOT NULL DEFAULT 'PENDING' COMMENT 'Statut de paiement de la transaction',
            payment_date date DEFAULT NULL,
            partner_name varchar(120) DEFAULT NULL,
            partner_code varchar(60) DEFAULT NULL,
            piece_ref varchar(20) DEFAULT NULL,
            journal_code varchar(10) NOT NULL DEFAULT 'VE',
            status enum('DRAFT','POSTED') NOT NULL DEFAULT 'POSTED',
            fec_synced tinyint(1) NOT NULL DEFAULT 0,
            created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            created_by bigint(20) unsigned DEFAULT NULL,
            meta_json longtext DEFAULT NULL,
            PRIMARY KEY (id),
            UNIQUE KEY uuid (uuid),
            KEY idx_date_txn (date_txn),
            KEY idx_type (type),
            KEY idx_status (status),
            KEY idx_vat_code (vat_code)
        ) $charset_collate;
        
        -- Table des taux de TVA
        CREATE TABLE IF NOT EXISTS {$prefix}vat_rates (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            country_code varchar(2) NOT NULL DEFAULT 'FR',
            rate_code varchar(10) NOT NULL,
            rate_value decimal(5,2) NOT NULL,
            rate_name varchar(50) NOT NULL,
            code varchar(10) NOT NULL,
            rate decimal(5,2) NOT NULL,
            label varchar(60) NOT NULL,
            is_default tinyint(1) NOT NULL DEFAULT 0,
            is_active tinyint(1) NOT NULL DEFAULT 1,
            valid_from date DEFAULT NULL,
            valid_to date DEFAULT NULL,
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            created_by bigint(20) unsigned DEFAULT NULL,
            PRIMARY KEY (id),
            UNIQUE KEY unique_country_rate (country_code, rate_code)
        ) $charset_collate;
        
        -- Table des clients
        CREATE TABLE IF NOT EXISTS {$prefix}clients (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            uuid char(36) NOT NULL,
            name varchar(120) NOT NULL,
            email varchar(120) NOT NULL,
            address varchar(200) DEFAULT NULL,
            postal_code varchar(10) DEFAULT NULL,
            city varchar(80) DEFAULT NULL,
            country varchar(60) NOT NULL DEFAULT 'France',
            siret varchar(20) DEFAULT NULL,
            vat_number varchar(30) DEFAULT NULL,
            phone varchar(30) DEFAULT NULL,
            notes text DEFAULT NULL,
            created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            created_by bigint(20) unsigned DEFAULT NULL,
            PRIMARY KEY (id),
            UNIQUE KEY uuid (uuid),
            KEY idx_name (name),
            KEY idx_created_by (created_by)
        ) $charset_collate;

        -- Table des factures
        CREATE TABLE IF NOT EXISTS {$prefix}invoices (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            uuid char(36) NOT NULL,
            invoice_number varchar(30) NOT NULL,
            client_id bigint(20) unsigned NOT NULL,
            date_invoice date NOT NULL,
            date_due date NOT NULL,
            status enum('DRAFT','PENDING','PAID','OVERDUE','CANCELLED') NOT NULL DEFAULT 'DRAFT',
            payment_date date DEFAULT NULL,
            payment_method varchar(20) DEFAULT NULL,
            total_ht decimal(15,2) NOT NULL DEFAULT 0.00,
            total_tva decimal(15,2) NOT NULL DEFAULT 0.00,
            total_ttc decimal(15,2) NOT NULL DEFAULT 0.00,
            transaction_id bigint(20) unsigned DEFAULT NULL COMMENT 'Lien vers cfp_transactions quand enregistree en compta',
            notes text DEFAULT NULL,
            created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            created_by bigint(20) unsigned DEFAULT NULL,
            PRIMARY KEY (id),
            UNIQUE KEY uuid (uuid),
            UNIQUE KEY invoice_number (invoice_number),
            KEY idx_client_id (client_id),
            KEY idx_status (status),
            KEY idx_date_invoice (date_invoice),
            KEY idx_created_by (created_by)
        ) $charset_collate;

        -- Table des lignes de facture
        CREATE TABLE IF NOT EXISTS {$prefix}invoice_lines (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            invoice_id bigint(20) unsigned NOT NULL,
            description varchar(255) NOT NULL,
            account_code varchar(10) NOT NULL DEFAULT '706',
            quantity decimal(10,3) NOT NULL DEFAULT 1.000,
            unit_price_ht decimal(15,2) NOT NULL DEFAULT 0.00,
            vat_rate decimal(5,2) NOT NULL DEFAULT 20.00,
            total_ht decimal(15,2) NOT NULL DEFAULT 0.00,
            total_tva decimal(15,2) NOT NULL DEFAULT 0.00,
            total_ttc decimal(15,2) NOT NULL DEFAULT 0.00,
            sort_order int NOT NULL DEFAULT 0,
            PRIMARY KEY (id),
            KEY idx_invoice_id (invoice_id)
        ) $charset_collate;

        -- Table des transactions bancaires
        CREATE TABLE IF NOT EXISTS {$prefix}bank_transactions (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            uuid varchar(36) NOT NULL,
            line_hash varchar(64) DEFAULT NULL,
            txn_date date NOT NULL,
            label varchar(255) NOT NULL,
            amount decimal(15,2) NOT NULL,
            currency varchar(3) NOT NULL DEFAULT 'EUR',
            bank_ref varchar(120) DEFAULT NULL,
            account_hint varchar(60) DEFAULT NULL,
            status enum('NEW','MATCHED','IGNORED') NOT NULL DEFAULT 'NEW',
            matched_txn_uuid varchar(36) DEFAULT NULL,
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            created_by bigint(20) unsigned DEFAULT NULL,
            meta_json longtext DEFAULT NULL,
            PRIMARY KEY (id),
            UNIQUE KEY uuid (uuid),
            UNIQUE KEY unique_line_hash (created_by, line_hash),
            KEY idx_date (txn_date),
            KEY idx_status (status),
            KEY idx_amount (amount)
        ) $charset_collate;
        
        -- Table des rapprochements
        CREATE TABLE IF NOT EXISTS {$prefix}reconciliations (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            bank_uuid varchar(36) NOT NULL,
            txn_uuid varchar(36) NOT NULL,
            reconciled_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            reconciled_by bigint(20) unsigned DEFAULT NULL,
            PRIMARY KEY (id),
            UNIQUE KEY uniq_link (bank_uuid, txn_uuid)
        ) $charset_collate;
        
        -- Table des correspondances de rapprochement
        CREATE TABLE IF NOT EXISTS {$prefix}reconciliation_matches (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            user_id bigint(20) unsigned NOT NULL,
            bank_transaction_id bigint(20) unsigned NOT NULL,
            object_type enum('SALE','EXPENSE','IMMOBILIZATION','PAYROLL','FINANCIAL') NOT NULL,
            object_id bigint(20) unsigned NOT NULL,
            match_type enum('AUTO_EXACT','AUTO_SUGGESTED','MANUAL') NOT NULL,
            status enum('PROPOSED','ACCEPTED') NOT NULL DEFAULT 'PROPOSED',
            created_at timestamp NULL DEFAULT CURRENT_TIMESTAMP,
            accepted_by bigint(20) unsigned DEFAULT NULL,
            accepted_at datetime DEFAULT NULL,
            PRIMARY KEY (id),
            KEY bank_transaction_id (bank_transaction_id)
        ) $charset_collate;
        
        -- Table des actions de rapprochement
        CREATE TABLE IF NOT EXISTS {$prefix}reconciliation_actions (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            user_id bigint(20) unsigned NOT NULL,
            bank_transaction_id bigint(20) unsigned NOT NULL,
            action_type enum('WAIT_INVOICE','CREATE_EXPENSE','CREATE_SALE','TO_INVESTIGATE') NOT NULL,
            status enum('PENDING','COMPLETED','CANCELLED') NOT NULL DEFAULT 'PENDING',
            notes text DEFAULT NULL,
            due_date date DEFAULT NULL,
            created_at timestamp NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY bank_transaction_id (bank_transaction_id),
            KEY status (status)
        ) $charset_collate;
        
        -- Table des immobilisations
        CREATE TABLE IF NOT EXISTS {$prefix}immobilizations (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            uuid varchar(36) NOT NULL,
            asset_name varchar(100) NOT NULL,
            asset_category varchar(50) NOT NULL,
            supplier_name varchar(100) DEFAULT NULL,
            invoice_ref varchar(50) DEFAULT NULL,
            acquisition_date date NOT NULL,
            acquisition_ht decimal(10,2) NOT NULL,
            acquisition_tva decimal(10,2) NOT NULL DEFAULT 0.00,
            acquisition_ttc decimal(10,2) NOT NULL,
            vat_rate decimal(5,2) NOT NULL DEFAULT 20.00,
            vat_code varchar(10) DEFAULT NULL,
            depreciation_method enum('LINEAR','DEGRESSIVE','UNITS') NOT NULL DEFAULT 'LINEAR',
            depreciation_duration smallint NOT NULL,
            depreciation_start_date date NOT NULL,
            depreciation_rate decimal(5,2) DEFAULT NULL,
            residual_value decimal(10,2) DEFAULT 0.00,
            disposal_date date DEFAULT NULL,
            disposal_price_ht decimal(10,2) DEFAULT NULL,
            disposal_price_tva decimal(10,2) DEFAULT NULL,
            disposal_price_ttc decimal(10,2) DEFAULT NULL,
            capital_gain decimal(10,2) DEFAULT NULL,
            account_code varchar(10) NOT NULL,
            depreciation_account varchar(10) NOT NULL,
            status enum('ACTIVE','DISPOSED','SCRAPPED') NOT NULL DEFAULT 'ACTIVE',
            payment_status enum('PAID','PENDING') NOT NULL DEFAULT 'PAID',
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            created_by bigint(20) unsigned DEFAULT NULL,
            PRIMARY KEY (id),
            UNIQUE KEY uuid (uuid),
            KEY idx_acquisition_date (acquisition_date),
            KEY idx_status (status),
            KEY idx_account_code (account_code),
            KEY idx_vat_code (vat_code)
        ) $charset_collate;
        
        -- Table de la paie
        CREATE TABLE IF NOT EXISTS {$prefix}payrolls (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            user_id bigint(20) unsigned NOT NULL,
            employee_name varchar(255) NOT NULL,
            pay_period_start date NOT NULL,
            pay_period_end date NOT NULL,
            pay_date date DEFAULT NULL,
            gross_salary decimal(15,2) NOT NULL DEFAULT 0.00,
            employee_contributions decimal(15,2) NOT NULL DEFAULT 0.00,
            employer_contributions decimal(15,2) NOT NULL DEFAULT 0.00,
            net_before_tax decimal(15,2) NOT NULL DEFAULT 0.00,
            withholding_tax decimal(15,2) NOT NULL DEFAULT 0.00,
            net_pay decimal(15,2) NOT NULL DEFAULT 0.00,
            comment text DEFAULT NULL,
            status enum('PENDING_PAYMENT','PAID') NOT NULL DEFAULT 'PENDING_PAYMENT',
            journal_entry_uuid varchar(36) DEFAULT NULL,
            created_at timestamp NULL DEFAULT CURRENT_TIMESTAMP,
            created_by bigint(20) unsigned NOT NULL,
            PRIMARY KEY (id),
            KEY user_id (user_id)
        ) $charset_collate;
        
        -- Table des paramètres fiscaux utilisateur
        CREATE TABLE IF NOT EXISTS {$prefix}user_fiscal_settings (
            id bigint(20) NOT NULL AUTO_INCREMENT,
            user_id bigint(20) NOT NULL,
            vat_regime enum('FRANCHISE','SIMPLIFIE','REEL') DEFAULT 'FRANCHISE',
            legal_status varchar(20) DEFAULT NULL,
            vat_number varchar(50) DEFAULT NULL,
            fiscal_year_end varchar(5) DEFAULT '12-31' COMMENT 'MM-DD format',
            advance_calculation_method enum('PERCENTAGE','FIXED','MANUAL') DEFAULT 'PERCENTAGE',
            july_advance_rate decimal(5,2) DEFAULT 55.00,
            december_advance_rate decimal(5,2) DEFAULT 40.00,
            notification_advance_days int DEFAULT 15,
            notification_declaration_days int DEFAULT 7,
            auto_calculate_advances tinyint(1) DEFAULT 1,
            settings json DEFAULT NULL COMMENT 'Autres paramètres flexibles',
            created_at timestamp NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY user_id (user_id)
        ) $charset_collate;
        
        -- Table des périodes TVA
        CREATE TABLE IF NOT EXISTS {$prefix}vat_periods (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            user_id bigint(20) unsigned NOT NULL,
            country_code char(2) NOT NULL DEFAULT 'FR',
            regime enum('FRANCHISE','SIMPLIFIE','REEL_NORMAL','REEL_ANNUEL') NOT NULL,
            period varchar(10) NOT NULL,
            start_date date NOT NULL,
            end_date date NOT NULL,
            due_date date NOT NULL,
            status enum('OPEN','DECLARED','LOCKED') NOT NULL DEFAULT 'OPEN',
            created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime DEFAULT NULL,
            PRIMARY KEY (id),
            UNIQUE KEY uniq_user_period (user_id, country_code, regime, period),
            KEY idx_user_period (user_id, period),
            KEY idx_due_date (due_date)
        ) $charset_collate;
        
        -- Table des déclarations TVA
        CREATE TABLE IF NOT EXISTS {$prefix}vat_declarations (
            id bigint(20) NOT NULL AUTO_INCREMENT,
            user_id bigint(20) NOT NULL,
            declaration_type enum('CA3','CA12') NOT NULL,
            period_start date NOT NULL,
            period_end date NOT NULL,
            fiscal_year int NOT NULL,
            declared_date date NOT NULL,
            tva_collected decimal(10,2) NOT NULL DEFAULT 0.00,
            tva_deductible decimal(10,2) NOT NULL DEFAULT 0.00,
            tva_due_before_advances decimal(10,2) NOT NULL DEFAULT 0.00,
            advances_used decimal(10,2) NOT NULL DEFAULT 0.00,
            final_balance decimal(10,2) NOT NULL DEFAULT 0.00,
            balance_type enum('DUE','CREDIT') DEFAULT 'DUE',
            credit_strategy enum('PENDING_CHOICE','REFUND','CARRYOVER','ADVANCE_OFFSET') DEFAULT NULL,
            status enum('DRAFT','DECLARED','PAID','ARCHIVED') DEFAULT 'DRAFT',
            declaration_reference varchar(50) DEFAULT NULL,
            payment_deadline date DEFAULT NULL,
            notes text DEFAULT NULL,
            validation_data json DEFAULT NULL COMMENT 'Données de validation pour audit',
            journal_entry_source varchar(50) DEFAULT NULL,
            created_at timestamp NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            created_by bigint(20) NOT NULL,
            PRIMARY KEY (id),
            UNIQUE KEY user_period_type (user_id, period_start, period_end, declaration_type),
            KEY idx_user_fiscal (user_id, fiscal_year),
            KEY idx_status (status),
            KEY idx_type (declaration_type)
        ) $charset_collate;
        
        -- Table des acomptes TVA
        CREATE TABLE IF NOT EXISTS {$prefix}vat_advances (
            id bigint(20) NOT NULL AUTO_INCREMENT,
            user_id bigint(20) NOT NULL,
            fiscal_year int NOT NULL,
            july_expected decimal(10,2) DEFAULT 0.00,
            december_expected decimal(10,2) DEFAULT 0.00,
            july_paid decimal(10,2) DEFAULT 0.00,
            july_credit_applied decimal(10,2) DEFAULT 0.00,
            december_paid decimal(10,2) DEFAULT 0.00,
            december_credit_applied decimal(10,2) DEFAULT 0.00,
            july_paid_date date DEFAULT NULL,
            december_paid_date date DEFAULT NULL,
            calculated_from_year int DEFAULT NULL,
            created_at timestamp NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY user_year (user_id, fiscal_year)
        ) $charset_collate;
        
        -- Table des paiements TVA
        CREATE TABLE IF NOT EXISTS {$prefix}vat_payments (
            id bigint(20) NOT NULL AUTO_INCREMENT,
            user_id bigint(20) NOT NULL,
            declaration_id bigint(20) DEFAULT NULL,
            advance_id bigint(20) DEFAULT NULL,
            payment_type enum('DECLARATION_BALANCE','ADVANCE','REFUND') NOT NULL,
            amount decimal(10,2) NOT NULL,
            payment_date date NOT NULL,
            payment_method varchar(20) DEFAULT 'BANK_TRANSFER',
            bank_account varchar(20) DEFAULT '512',
            reference varchar(100) DEFAULT NULL,
            description text DEFAULT NULL,
            journal_entry_id bigint(20) DEFAULT NULL,
            status enum('PENDING','COMPLETED','CANCELLED') DEFAULT 'PENDING',
            created_at timestamp NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            created_by bigint(20) NOT NULL,
            PRIMARY KEY (id),
            KEY idx_user_date (user_id, payment_date),
            KEY idx_declaration (declaration_id),
            KEY idx_advance (advance_id),
            KEY idx_type (payment_type),
            CONSTRAINT fk_vat_payments_advance FOREIGN KEY (advance_id) REFERENCES {$prefix}vat_advances (id) ON DELETE SET NULL,
            CONSTRAINT fk_vat_payments_declaration FOREIGN KEY (declaration_id) REFERENCES {$prefix}vat_declarations (id) ON DELETE SET NULL
        ) $charset_collate;
        
        -- Table du suivi des crédits TVA
        CREATE TABLE IF NOT EXISTS {$prefix}vat_credit_tracking (
            id bigint(20) NOT NULL AUTO_INCREMENT,
            declaration_id bigint(20) NOT NULL,
            user_id bigint(20) NOT NULL,
            credit_amount decimal(10,2) NOT NULL,
            strategy enum('REFUND','CARRYOVER','ADVANCE_OFFSET') NOT NULL,
            remaining_amount decimal(10,2) NOT NULL,
            status enum('ACTIVE','USED','EXPIRED') NOT NULL DEFAULT 'ACTIVE',
            applications json 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),
            KEY idx_user_status (user_id, status),
            KEY idx_declaration (declaration_id),
            KEY idx_strategy_status (strategy, status),
            CONSTRAINT fk_vat_credit_declaration FOREIGN KEY (declaration_id) REFERENCES {$prefix}vat_declarations (id) ON DELETE CASCADE
        ) $charset_collate;
        
        -- Table des validations TVA
        CREATE TABLE IF NOT EXISTS {$prefix}vat_validations (
            id bigint(20) NOT NULL AUTO_INCREMENT,
            user_id bigint(20) NOT NULL,
            declaration_type varchar(10) NOT NULL,
            period_from date NOT NULL,
            period_to date NOT NULL,
            validated_at datetime NOT NULL,
            validation_method varchar(20) DEFAULT 'COMPTAFLOW_AUTO',
            cancelled_at datetime DEFAULT NULL,
            created_at timestamp NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY idx_user_period (user_id, period_from, period_to)
        ) $charset_collate;
        ";
        
        require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
        
        $statements = explode(';', $sql);
        foreach ($statements as $statement) {
            $statement = trim($statement);
            if (!empty($statement)) {
                dbDelta($statement . ';');
            }
        }
        
        // Migrations pour ajouter les colonnes manquantes
        self::migrateAllTables();
        
        // Seed des données par défaut (comptes et taux TVA)
        self::seedDefaultData();
        
        // NOUVEAU : Seed des options WordPress par défaut
        self::seedDefaultOptions();
    }
    
    /**
     * Migration complète de toutes les tables
     */
    private static function migrateAllTables(): void {
        global $wpdb;
        $prefix = $wpdb->prefix . 'cfp_';
        
        // Migration vat_rates : ajouter colonnes si manquantes
        $vat_columns = $wpdb->get_results("SHOW COLUMNS FROM {$prefix}vat_rates");
        $vat_existing = array_column($vat_columns, 'Field');
        
        if (!in_array('code', $vat_existing)) {
            $wpdb->query("ALTER TABLE {$prefix}vat_rates ADD COLUMN code VARCHAR(10) NOT NULL AFTER rate_name");
        }
        if (!in_array('rate', $vat_existing)) {
            $wpdb->query("ALTER TABLE {$prefix}vat_rates ADD COLUMN rate DECIMAL(5,2) NOT NULL AFTER code");
        }
        if (!in_array('label', $vat_existing)) {
            $wpdb->query("ALTER TABLE {$prefix}vat_rates ADD COLUMN label VARCHAR(60) NOT NULL AFTER rate");
        }
        if (!in_array('is_default', $vat_existing)) {
            $wpdb->query("ALTER TABLE {$prefix}vat_rates ADD COLUMN is_default TINYINT(1) NOT NULL DEFAULT 0 AFTER label");
        }
        
        // Migration bank_transactions : ajouter line_hash
        $bank_columns = $wpdb->get_results("SHOW COLUMNS FROM {$prefix}bank_transactions");
        $bank_existing = array_column($bank_columns, 'Field');
        
        if (!in_array('line_hash', $bank_existing)) {
            $wpdb->query("ALTER TABLE {$prefix}bank_transactions ADD COLUMN line_hash VARCHAR(64) DEFAULT NULL AFTER uuid");
            $wpdb->query("ALTER TABLE {$prefix}bank_transactions ADD UNIQUE KEY unique_line_hash (created_by, line_hash)");
        }
        
        // Migration journal_entries : ajuster types
        $je_columns = $wpdb->get_results("SHOW COLUMNS FROM {$prefix}journal_entries");
        $je_existing = array_column($je_columns, 'Field');
        
        if (!in_array('partner_code', $je_existing)) {
            $wpdb->query("ALTER TABLE {$prefix}journal_entries ADD COLUMN partner_code VARCHAR(60) DEFAULT NULL AFTER code_tva");
        }
        if (!in_array('analytic', $je_existing)) {
            $wpdb->query("ALTER TABLE {$prefix}journal_entries ADD COLUMN analytic VARCHAR(60) DEFAULT NULL AFTER partner_code");
        }
        if (!in_array('currency', $je_existing)) {
            $wpdb->query("ALTER TABLE {$prefix}journal_entries ADD COLUMN currency CHAR(3) NOT NULL DEFAULT 'EUR' AFTER analytic");
        }
        if (!in_array('locked_at', $je_existing)) {
            $wpdb->query("ALTER TABLE {$prefix}journal_entries ADD COLUMN locked_at DATETIME DEFAULT NULL AFTER currency");
        }
        if (!in_array('transaction_id', $je_existing)) {
            $wpdb->query("ALTER TABLE {$prefix}journal_entries ADD COLUMN transaction_id BIGINT(20) UNSIGNED DEFAULT NULL COMMENT 'Lien vers l''ID de la table wp_cfp_transactions' AFTER source");
            $wpdb->query("ALTER TABLE {$prefix}journal_entries ADD KEY idx_transaction_id (transaction_id)");
        }
        
        // Migration transactions : ajuster type ENUM -> VARCHAR
        $tx_columns = $wpdb->get_results("SHOW COLUMNS FROM {$prefix}transactions");
        $tx_existing = array_column($tx_columns, 'Field');
        
        if (!in_array('payment_date', $tx_existing)) {
            $wpdb->query("ALTER TABLE {$prefix}transactions ADD COLUMN payment_date DATE DEFAULT NULL AFTER payment_status");
        }
        if (!in_array('partner_code', $tx_existing)) {
            $wpdb->query("ALTER TABLE {$prefix}transactions ADD COLUMN partner_code VARCHAR(60) DEFAULT NULL AFTER partner_name");
        }
        if (!in_array('status', $tx_existing)) {
            $wpdb->query("ALTER TABLE {$prefix}transactions ADD COLUMN status ENUM('DRAFT','POSTED') NOT NULL DEFAULT 'POSTED' AFTER journal_code");
        }
        if (!in_array('fec_synced', $tx_existing)) {
            $wpdb->query("ALTER TABLE {$prefix}transactions ADD COLUMN fec_synced TINYINT(1) NOT NULL DEFAULT 0 AFTER status");
        }
    }
    
    /**
     * Seed des données par défaut (comptes et taux TVA)
     */
    private static function seedDefaultData(): void {
        global $wpdb;
        $prefix = $wpdb->prefix . 'cfp_';
        
        // Seed des comptes de base
        $accounts = [
            ['411', 'Clients', 'ACTIF'],
            ['401', 'Fournisseurs', 'PASSIF'],
            ['512', 'Banques', 'ACTIF'],
            ['53', 'Caisse', 'ACTIF'],
            ['706', 'Prestations de services', 'PRODUIT'],
            ['707', 'Ventes de marchandises', 'PRODUIT'],
            ['7082', 'Commissions et courtages', 'PRODUIT'],
            ['7516', 'Droits d\'auteur et de reproduction', 'PRODUIT'],
            ['6063', 'Fournitures d\'entretien', 'CHARGE'],
            ['6061', 'Fournitures non stockables', 'CHARGE'],
            ['6251', 'Voyages et déplacements', 'CHARGE'],
            ['6226', 'Honoraires', 'CHARGE'],
            ['6278', 'Autres frais et commissions bancaires', 'CHARGE'],
            ['6511', 'Redevances pour logiciels', 'CHARGE'],
            ['44571', 'TVA collectée', 'PASSIF'],
            ['44566', 'TVA déductible', 'ACTIF'],
            ['641', 'Rémunérations du personnel', 'CHARGE'],
            ['645', 'Charges de sécurité sociale', 'CHARGE'],
            ['2154', 'Matériel industriel', 'ACTIF'],
            ['2182', 'Matériel de transport', 'ACTIF'],
            ['2183', 'Matériel de bureau et informatique', 'ACTIF'],
            ['28154', 'Amortissements matériel industriel', 'ACTIF'],
            ['28182', 'Amortissements matériel de transport', 'ACTIF'],
            ['28183', 'Amortissements matériel de bureau', 'ACTIF'],
            ['681', 'Dotations aux amortissements', 'CHARGE']
        ];
        
        foreach ($accounts as $account) {
            $existing = $wpdb->get_var($wpdb->prepare(
                "SELECT compte_num FROM {$prefix}accounts WHERE compte_num = %s",
                $account[0]
            ));
            
            if (!$existing) {
                $wpdb->insert($prefix . 'accounts', [
                    'compte_num' => $account[0],
                    'label' => $account[1],
                    'type' => $account[2],
                    'is_system' => 1
                ]);
            }
        }
        
        // Seed des taux de TVA
        $current_date = date('Y-m-d');
        $vat_rates = [
            // France
            ['FR', 'FR-20', 20.00, 'Taux normal', 'FR-20', 20.00, 'Taux normal FR 20%', 1],
            ['FR', 'FR-10', 10.00, 'Taux réduit', 'FR-10', 10.00, 'Taux réduit FR 10%', 0],
            ['FR', 'FR-5_5', 5.50, 'Taux super réduit', 'FR-5_5', 5.50, 'Taux super réduit FR 5,5%', 0],
            ['FR', 'FR-2_1', 2.10, 'Taux ultra réduit', 'FR-2_1', 2.10, 'Taux particulier FR 2,1%', 0],
            ['FR', 'FR-0', 0.00, 'Exonéré', 'FR-0', 0.00, 'Exonéré FR 0%', 0],
            
            // Belgique
            ['BE', 'BE-21', 21.00, 'Taux normal', 'BE-21', 21.00, 'Taux normal BE 21%', 0],
            ['BE', 'BE-12', 12.00, 'Taux réduit', 'BE-12', 12.00, 'Taux intermédiaire BE 12%', 0],
            ['BE', 'BE-6', 6.00, 'Taux super réduit', 'BE-6', 6.00, 'Taux réduit BE 6%', 0],
            ['BE', 'BE-0', 0.00, 'Exonéré', 'BE-0', 0.00, 'Exonéré BE 0%', 0],
            
            // Suisse
            ['CH', 'CH-7_7', 7.70, 'Taux normal', 'CH-7_7', 7.70, 'Taux normal CH 7,7%', 0],
            ['CH', 'CH-3_7', 3.70, 'Taux réduit hôtellerie', 'CH-3_7', 3.70, 'Taux spécial CH 3,7%', 0],
            ['CH', 'CH-2_5', 2.50, 'Taux réduit biens', 'CH-2_5', 2.50, 'Taux réduit CH 2,5%', 0],
            ['CH', 'CH-0', 0.00, 'Exonéré', 'CH-0', 0.00, 'Exonéré CH 0%', 0],
            
            // Luxembourg
            ['LU', 'LU-17', 17.00, 'Taux normal', 'LU-17', 17.00, 'Taux normal LU 17%', 0],
            ['LU', 'LU-14', 14.00, 'Taux intermédiaire', 'LU-14', 14.00, 'Taux intermédiaire LU 14%', 0],
            ['LU', 'LU-8', 8.00, 'Taux réduit', 'LU-8', 8.00, 'Taux réduit LU 8%', 0],
            ['LU', 'LU-3', 3.00, 'Taux super réduit', 'LU-3', 3.00, 'Taux super réduit LU 3%', 0],
            
            // Monaco (aligné sur France)
            ['MC', 'MC-20', 20.00, 'Taux normal', 'MC-20', 20.00, 'Taux normal MC 20%', 0],
            ['MC', 'MC-10', 10.00, 'Taux réduit', 'MC-10', 10.00, 'Taux réduit MC 10%', 0],
            ['MC', 'MC-5_5', 5.50, 'Taux super réduit', 'MC-5_5', 5.50, 'Taux super réduit MC 5,5%', 0],
            
            // DOM (taux spécifiques)
            ['GP', 'GP-8_5', 8.50, 'Taux normal Guadeloupe', 'GP-8_5', 8.50, 'Taux normal GP 8,5%', 0],
            ['GP', 'GP-2_1', 2.10, 'Taux réduit', 'GP-2_1', 2.10, 'Taux réduit GP 2,1%', 0],
            ['MQ', 'MQ-8_5', 8.50, 'Taux normal Martinique', 'MQ-8_5', 8.50, 'Taux normal MQ 8,5%', 0],
            ['GF', 'GF-8_5', 8.50, 'Taux normal Guyane', 'GF-8_5', 8.50, 'Taux normal GF 8,5%', 0],
            ['RE', 'RE-8_5', 8.50, 'Taux normal Réunion', 'RE-8_5', 8.50, 'Taux normal RE 8,5%', 0],
            ['YT', 'YT-8_5', 8.50, 'Taux normal Mayotte', 'YT-8_5', 8.50, 'Taux normal YT 8,5%', 0],
            
            // Territoires français d'outre-mer
            ['NC', 'NC-11', 11.00, 'TGC Nouvelle-Calédonie', 'NC-11', 11.00, 'TGC normal NC 11%', 0],
            ['NC', 'NC-3', 3.00, 'TGC réduit', 'NC-3', 3.00, 'TGC réduit NC 3%', 0],
            ['PF', 'PF-16', 16.00, 'TVA Polynésie', 'PF-16', 16.00, 'TVA normale PF 16%', 0],
            ['PF', 'PF-5', 5.00, 'TVA réduite', 'PF-5', 5.00, 'TVA réduite PF 5%', 0],
            
            // Maroc
            ['MA', 'MA-20', 20.00, 'Taux normal', 'MA-20', 20.00, 'Taux normal MA 20%', 0],
            ['MA', 'MA-14', 14.00, 'Taux réduit', 'MA-14', 14.00, 'Taux réduit MA 14%', 0],
            ['MA', 'MA-10', 10.00, 'Taux super réduit', 'MA-10', 10.00, 'Taux super réduit MA 10%', 0],
            ['MA', 'MA-7', 7.00, 'Taux minimum', 'MA-7', 7.00, 'Taux minimum MA 7%', 0],
            
            // Tunisie
            ['TN', 'TN-19', 19.00, 'Taux normal', 'TN-19', 19.00, 'Taux normal TN 19%', 0],
            ['TN', 'TN-13', 13.00, 'Taux réduit', 'TN-13', 13.00, 'Taux réduit TN 13%', 0],
            ['TN', 'TN-7', 7.00, 'Taux minimum', 'TN-7', 7.00, 'Taux minimum TN 7%', 0],
            
            // Sénégal
            ['SN', 'SN-18', 18.00, 'Taux normal', 'SN-18', 18.00, 'Taux normal SN 18%', 0],
            ['SN', 'SN-10', 10.00, 'Taux réduit', 'SN-10', 10.00, 'Taux réduit SN 10%', 0],
            
            // Côte d'Ivoire
            ['CI', 'CI-18', 18.00, 'Taux normal', 'CI-18', 18.00, 'Taux normal CI 18%', 0],
            ['CI', 'CI-9', 9.00, 'Taux réduit', 'CI-9', 9.00, 'Taux réduit CI 9%', 0],
            
            // Cameroun
            ['CM', 'CM-19_25', 19.25, 'Taux normal', 'CM-19_25', 19.25, 'Taux normal CM 19,25%', 0],
            ['CM', 'CM-0', 0.00, 'Exonéré', 'CM-0', 0.00, 'Exonéré CM 0%', 0],
            
            // Maurice
            ['MU', 'MU-15', 15.00, 'Taux normal', 'MU-15', 15.00, 'Taux normal MU 15%', 0],
            ['MU', 'MU-0', 0.00, 'Exonéré', 'MU-0', 0.00, 'Exonéré MU 0%', 0]
        ];
        
        foreach ($vat_rates as $rate) {
            $existing = $wpdb->get_var($wpdb->prepare(
                "SELECT id FROM {$prefix}vat_rates WHERE country_code = %s AND rate_code = %s",
                $rate[0], $rate[1]
            ));
            
            if (!$existing) {
                $wpdb->insert($prefix . 'vat_rates', [
                    'country_code' => $rate[0],
                    'rate_code' => $rate[1],
                    'rate_value' => $rate[2],
                    'rate_name' => $rate[3],
                    'code' => $rate[4],
                    'rate' => $rate[5],
                    'label' => $rate[6],
                    'is_default' => $rate[7],
                    'is_active' => 1,
                    'valid_from' => $current_date,
                    'valid_to' => NULL,
                    'created_by' => get_current_user_id()
                ]);
            }
        }
    }
    
    /**
     * NOUVEAU : Seed des options WordPress par défaut
     * Cette fonction insère automatiquement les paramètres essentiels dans wp_options
     * pour que chaque nouvel utilisateur ait un plugin 100% fonctionnel dès l'installation
     */
    private static function seedDefaultOptions(): void {
        // Vérifier si les options existent déjà (pour ne pas écraser les données utilisateur)
        $existing_settings = get_option('comptaflow_company_settings');
        
        if (!$existing_settings) {
            // Paramètres d'entreprise par défaut
            $default_company_settings = [
                'status' => 'EI', // Entreprise Individuelle par défaut
                'siren' => '',
                'siret' => '',
                'vat_number' => '',
                'vat_liable' => true, // Assujetti TVA par défaut
                'vat_regime' => 'REEL', // Régime Réel par défaut
                'exercise_type' => 'civil',
                'exercise_end_date' => date('Y') . '-12-31',
                'exercise_end_month' => '12',
                'ca12_base_n1' => 0,
                'ca12_advances_paid_n1' => 0,
                'company_name' => get_bloginfo('name'),
                'address' => '',
                'postal_code' => '',
                'city' => '',
                'naf_code' => ''
            ];
            
            update_option('comptaflow_company_settings', $default_company_settings, true);
        }
        
        // Version de la base de données
        if (!get_option('comptaflow_db_version')) {
            update_option('comptaflow_db_version', '1.0.0', true);
        }
        
        // Plan par défaut (Gratuit)
        if (!get_option('comptaflow_current_plan')) {
            update_option('comptaflow_current_plan', 'GRATUIT', true);
        }
        
        // Type de plan
        if (!get_option('comptaflow_plan_type')) {
            update_option('comptaflow_plan_type', 'GRATUIT', true);
        }
        
        // Date d'activation du plan
        if (!get_option('comptaflow_plan_activated_at')) {
            update_option('comptaflow_plan_activated_at', current_time('mysql'), true);
        }
        
        // Date de dernière migration
        if (!get_option('comptaflow_last_migration')) {
            update_option('comptaflow_last_migration', current_time('mysql'), true);
        }
    }
    
    /**
     * Obtenir les taux TVA actifs pour un pays
     */
    public static function getActiveVATRates($country_code = 'FR') {
        global $wpdb;
        $prefix = $wpdb->prefix . 'cfp_';
        $current_date = date('Y-m-d');
        
        return $wpdb->get_results($wpdb->prepare("
            SELECT * FROM {$prefix}vat_rates 
            WHERE country_code = %s 
            AND is_active = 1 
            AND (valid_from IS NULL OR valid_from <= %s)
            AND (valid_to IS NULL OR valid_to >= %s)
            ORDER BY rate_value DESC
        ", $country_code, $current_date, $current_date), ARRAY_A);
    }
    
    /**
     * Obtenir un taux TVA par son code
     */
    public static function getVATRateByCode($rate_code, $country_code = 'FR') {
        global $wpdb;
        $prefix = $wpdb->prefix . 'cfp_';
        
        return $wpdb->get_row($wpdb->prepare("
            SELECT * FROM {$prefix}vat_rates 
            WHERE country_code = %s AND rate_code = %s AND is_active = 1
        ", $country_code, $rate_code), ARRAY_A);
    }
    
    /**
     * Obtenir tous les comptes actifs
     */
    public static function getActiveAccounts() {
        global $wpdb;
        $prefix = $wpdb->prefix . 'cfp_';
        
        return $wpdb->get_results("
            SELECT * FROM {$prefix}accounts 
            ORDER BY compte_num ASC
        ", ARRAY_A);
    }
    
    /**
     * Obtenir un compte par son numéro
     */
    public static function getAccountByNum($compte_num) {
        global $wpdb;
        $prefix = $wpdb->prefix . 'cfp_';
        
        return $wpdb->get_row($wpdb->prepare("
            SELECT * FROM {$prefix}accounts 
            WHERE compte_num = %s
        ", $compte_num), ARRAY_A);
    }
    
    /**
     * Obtenir les paramètres fiscaux d'un utilisateur
     */
    public static function getUserFiscalSettings($user_id) {
        global $wpdb;
        $prefix = $wpdb->prefix . 'cfp_';
        
        $settings = $wpdb->get_row($wpdb->prepare("
            SELECT * FROM {$prefix}user_fiscal_settings 
            WHERE user_id = %d
        ", $user_id), ARRAY_A);
        
        // Créer les paramètres par défaut si inexistants
        if (!$settings) {
            $wpdb->insert($prefix . 'user_fiscal_settings', [
                'user_id' => $user_id,
                'vat_regime' => 'REEL',
                'fiscal_year_end' => '12-31',
                'advance_calculation_method' => 'PERCENTAGE',
                'july_advance_rate' => 55.00,
                'december_advance_rate' => 40.00,
                'notification_advance_days' => 15,
                'notification_declaration_days' => 7,
                'auto_calculate_advances' => 1
            ]);
            
            return self::getUserFiscalSettings($user_id);
        }
        
        return $settings;
    }
    
    /**
     * Mettre à jour les paramètres fiscaux d'un utilisateur
     */
    public static function updateUserFiscalSettings($user_id, $data) {
        global $wpdb;
        $prefix = $wpdb->prefix . 'cfp_';
        
        $existing = $wpdb->get_var($wpdb->prepare(
            "SELECT id FROM {$prefix}user_fiscal_settings WHERE user_id = %d",
            $user_id
        ));
        
        if ($existing) {
            return $wpdb->update(
                $prefix . 'user_fiscal_settings',
                $data,
                ['user_id' => $user_id]
            );
        } else {
            $data['user_id'] = $user_id;
            return $wpdb->insert($prefix . 'user_fiscal_settings', $data);
        }
    }
    
    /**
     * Supprimer toutes les tables (utile pour désinstallation)
     */
    public static function dropTables(): void {
        global $wpdb;
        $prefix = $wpdb->prefix . 'cfp_';
        
        $tables = [
            'vat_validations',
            'vat_credit_tracking',
            'vat_payments',
            'vat_declarations',
            'vat_advances',
            'vat_periods',
            'user_fiscal_settings',
            'payrolls',
            'immobilizations',
            'reconciliation_actions',
            'reconciliation_matches',
            'reconciliations',
            'bank_transactions',
            'invoice_lines',
            'invoices',
            'clients',
            'journal_entries',
            'transactions',
            'vat_rates',
            'accounts'
        ];
        
        // Désactiver les contraintes de clés étrangères
        $wpdb->query("SET FOREIGN_KEY_CHECKS = 0");
        
        foreach ($tables as $table) {
            $wpdb->query("DROP TABLE IF EXISTS {$prefix}{$table}");
        }
        
        // Réactiver les contraintes
        $wpdb->query("SET FOREIGN_KEY_CHECKS = 1");
        
        // Supprimer les options WordPress
        delete_option('comptaflow_company_settings');
        delete_option('comptaflow_db_version');
        delete_option('comptaflow_current_plan');
        delete_option('comptaflow_plan_type');
        delete_option('comptaflow_plan_activated_at');
        delete_option('comptaflow_last_migration');
    }
}