<?php

namespace ComptaFlowUK\Services;

defined('ABSPATH') || exit;

class DB {
    
    public static function createTables(): void {
        global $wpdb;
        
        $charset_collate = $wpdb->get_charset_collate();
        $prefix = $wpdb->prefix . 'cfuk_';
        
        $sql = "
        -- Nominal accounts (chart of accounts)
        CREATE TABLE IF NOT EXISTS {$prefix}accounts (
            compte_num varchar(20) NOT NULL,
            label varchar(120) NOT NULL,
            type enum('ASSET','LIABILITY','EXPENSE','INCOME','OFF_BALANCE') NOT NULL,
            is_system tinyint(1) NOT NULL DEFAULT 0,
            PRIMARY KEY (compte_num)
        ) $charset_collate;
        
        -- Journal entries
        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 'GBP',
            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 'Link to transaction ID in cfuk_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;
        
        -- 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 'Business transaction type (SALE, EXPENSE, VAT_PAYMENT, etc.)',
            date_txn date NOT NULL,
            label varchar(200) NOT NULL,
            account_code varchar(20) DEFAULT NULL COMMENT 'Main expense/income nominal code',
            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 'Payment status',
            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 'SL',
            status enum('DRAFT','POSTED') NOT NULL DEFAULT 'POSTED',
            hmrc_synced tinyint(1) NOT NULL DEFAULT 0,
            invoice_id bigint(20) unsigned DEFAULT NULL COMMENT 'Link to cfuk_invoices if created from invoice',
            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),
            KEY idx_invoice_id (invoice_id)
        ) $charset_collate;
        
        -- VAT rates
        CREATE TABLE IF NOT EXISTS {$prefix}vat_rates (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            country_code varchar(2) NOT NULL DEFAULT 'GB',
            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;
        
        -- Clients
        CREATE TABLE IF NOT EXISTS {$prefix}clients (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            uuid char(36) NOT NULL,
            client_type enum('INDIVIDUAL','COMPANY') NOT NULL DEFAULT 'COMPANY',
            company_name varchar(200) DEFAULT NULL,
            contact_name varchar(200) DEFAULT NULL,
            email varchar(200) DEFAULT NULL,
            phone varchar(50) DEFAULT NULL,
            address_line_1 varchar(200) DEFAULT NULL,
            address_line_2 varchar(200) DEFAULT NULL,
            city varchar(100) DEFAULT NULL,
            county varchar(100) DEFAULT NULL,
            postcode varchar(10) DEFAULT NULL,
            country varchar(2) DEFAULT 'GB',
            company_number varchar(20) DEFAULT NULL,
            vat_number varchar(20) DEFAULT NULL,
            payment_terms int DEFAULT 30,
            notes text DEFAULT NULL,
            created_by bigint(20) unsigned DEFAULT NULL,
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY uuid (uuid),
            KEY idx_company_name (company_name),
            KEY idx_created_by (created_by)
        ) $charset_collate;

        -- Invoices
        CREATE TABLE IF NOT EXISTS {$prefix}invoices (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            uuid char(36) NOT NULL,
            invoice_type enum('INVOICE','CREDIT_NOTE') NOT NULL DEFAULT 'INVOICE',
            invoice_number varchar(30) NOT NULL,
            status enum('DRAFT','SENT','PAID','OVERDUE','CANCELLED') NOT NULL DEFAULT 'DRAFT',
            client_id bigint(20) unsigned DEFAULT NULL,
            issue_date date NOT NULL,
            due_date date DEFAULT NULL,
            payment_terms int DEFAULT 30,
            currency char(3) DEFAULT 'GBP',
            subtotal decimal(15,2) NOT NULL DEFAULT 0.00,
            total_vat decimal(15,2) NOT NULL DEFAULT 0.00,
            total decimal(15,2) NOT NULL DEFAULT 0.00,
            amount_paid decimal(15,2) NOT NULL DEFAULT 0.00,
            notes text DEFAULT NULL,
            terms text DEFAULT NULL,
            payment_instructions text DEFAULT NULL,
            converted_from bigint(20) unsigned DEFAULT NULL,
            transaction_id bigint(20) unsigned DEFAULT NULL,
            created_by bigint(20) unsigned DEFAULT NULL,
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            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_type (invoice_type),
            KEY idx_due_date (due_date),
            KEY idx_created_by (created_by)
        ) $charset_collate;

        -- Invoice lines
        CREATE TABLE IF NOT EXISTS {$prefix}invoice_lines (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            invoice_id bigint(20) unsigned NOT NULL,
            line_order smallint NOT NULL DEFAULT 0,
            description varchar(500) NOT NULL,
            quantity decimal(10,3) NOT NULL DEFAULT 1.000,
            unit_price decimal(15,2) NOT NULL DEFAULT 0.00,
            vat_rate decimal(5,2) NOT NULL DEFAULT 20.00,
            vat_code varchar(10) DEFAULT 'GB-20',
            account_code varchar(20) DEFAULT '4010',
            line_total decimal(15,2) NOT NULL DEFAULT 0.00,
            vat_amount decimal(15,2) NOT NULL DEFAULT 0.00,
            gross_total decimal(15,2) NOT NULL DEFAULT 0.00,
            PRIMARY KEY (id),
            KEY idx_invoice_id (invoice_id)
        ) $charset_collate;

        -- Fixed assets
        CREATE TABLE IF NOT EXISTS {$prefix}fixed_assets (
            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','REDUCING_BALANCE','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;
        
        -- User tax settings
        CREATE TABLE IF NOT EXISTS {$prefix}user_fiscal_settings (
            id bigint(20) NOT NULL AUTO_INCREMENT,
            user_id bigint(20) NOT NULL,
            vat_scheme enum('NOT_REGISTERED','STANDARD','CASH_ACCOUNTING','FLAT_RATE') DEFAULT 'NOT_REGISTERED',
            business_type varchar(20) DEFAULT NULL,
            vat_number varchar(50) DEFAULT NULL,
            fiscal_year_end varchar(5) DEFAULT '04-05' COMMENT 'MM-DD format (UK tax year ends 5 April)',
            advance_calculation_method enum('PERCENTAGE','FIXED','MANUAL') DEFAULT 'PERCENTAGE',
            q1_advance_rate decimal(5,2) DEFAULT 25.00,
            q2_advance_rate decimal(5,2) DEFAULT 25.00,
            q3_advance_rate decimal(5,2) DEFAULT 25.00,
            q4_advance_rate decimal(5,2) DEFAULT 25.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 'Additional flexible settings',
            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;
        
        -- VAT periods
        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 'GB',
            scheme enum('NOT_REGISTERED','STANDARD','CASH_ACCOUNTING','FLAT_RATE') 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, scheme, period),
            KEY idx_user_period (user_id, period),
            KEY idx_due_date (due_date)
        ) $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 . ';');
            }
        }
        
        // Run migrations for missing columns
        self::migrateAllTables();
        
        // Seed default data (accounts and VAT rates)
        self::seedDefaultData();
        
        // Seed default WordPress options
        self::seedDefaultOptions();
    }
    
    /**
     * Run all table migrations
     */
    private static function migrateAllTables(): void {
        global $wpdb;
        $prefix = $wpdb->prefix . 'cfuk_';
        $charset_collate = $wpdb->get_charset_collate();
        
        // Migration vat_rates: add columns if missing
        $vat_columns = $wpdb->get_results("SHOW COLUMNS FROM {$prefix}vat_rates");
        $vat_existing = array_column($vat_columns, 'Field');
        
        if (!in_array('code', $vat_existing)) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
            $wpdb->query("ALTER TABLE {$prefix}vat_rates ADD COLUMN code VARCHAR(10) NOT NULL AFTER rate_name");
        }
        if (!in_array('rate', $vat_existing)) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
            $wpdb->query("ALTER TABLE {$prefix}vat_rates ADD COLUMN rate DECIMAL(5,2) NOT NULL AFTER code");
        }
        if (!in_array('label', $vat_existing)) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
            $wpdb->query("ALTER TABLE {$prefix}vat_rates ADD COLUMN label VARCHAR(60) NOT NULL AFTER rate");
        }
        if (!in_array('is_default', $vat_existing)) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
            $wpdb->query("ALTER TABLE {$prefix}vat_rates ADD COLUMN is_default TINYINT(1) NOT NULL DEFAULT 0 AFTER label");
        }
        
        // Migration journal_entries: add extra columns
        $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)) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
            $wpdb->query("ALTER TABLE {$prefix}journal_entries ADD COLUMN partner_code VARCHAR(60) DEFAULT NULL AFTER code_tva");
        }
        if (!in_array('analytic', $je_existing)) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
            $wpdb->query("ALTER TABLE {$prefix}journal_entries ADD COLUMN analytic VARCHAR(60) DEFAULT NULL AFTER partner_code");
        }
        if (!in_array('currency', $je_existing)) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
            $wpdb->query("ALTER TABLE {$prefix}journal_entries ADD COLUMN currency CHAR(3) NOT NULL DEFAULT 'GBP' AFTER analytic");
        }
        if (!in_array('locked_at', $je_existing)) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
            $wpdb->query("ALTER TABLE {$prefix}journal_entries ADD COLUMN locked_at DATETIME DEFAULT NULL AFTER currency");
        }
        if (!in_array('transaction_id', $je_existing)) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
            $wpdb->query("ALTER TABLE {$prefix}journal_entries ADD COLUMN transaction_id BIGINT(20) UNSIGNED DEFAULT NULL COMMENT 'Link to transaction ID in cfuk_transactions' AFTER source");
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
            $wpdb->query("ALTER TABLE {$prefix}journal_entries ADD KEY idx_transaction_id (transaction_id)");
        }
        
        // Migration transactions: add extra columns
        $tx_columns = $wpdb->get_results("SHOW COLUMNS FROM {$prefix}transactions");
        $tx_existing = array_column($tx_columns, 'Field');
        
        if (!in_array('payment_date', $tx_existing)) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
            $wpdb->query("ALTER TABLE {$prefix}transactions ADD COLUMN payment_date DATE DEFAULT NULL AFTER payment_status");
        }
        if (!in_array('partner_code', $tx_existing)) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
            $wpdb->query("ALTER TABLE {$prefix}transactions ADD COLUMN partner_code VARCHAR(60) DEFAULT NULL AFTER partner_name");
        }
        if (!in_array('status', $tx_existing)) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
            $wpdb->query("ALTER TABLE {$prefix}transactions ADD COLUMN status ENUM('DRAFT','POSTED') NOT NULL DEFAULT 'POSTED' AFTER journal_code");
        }
        if (!in_array('hmrc_synced', $tx_existing)) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
            $wpdb->query("ALTER TABLE {$prefix}transactions ADD COLUMN hmrc_synced TINYINT(1) NOT NULL DEFAULT 0 AFTER status");
        }
        if (!in_array('invoice_id', $tx_existing)) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
            $wpdb->query("ALTER TABLE {$prefix}transactions ADD COLUMN invoice_id BIGINT(20) UNSIGNED DEFAULT NULL COMMENT 'Link to cfuk_invoices if created from invoice' AFTER hmrc_synced");
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
            $wpdb->query("ALTER TABLE {$prefix}transactions ADD KEY idx_invoice_id (invoice_id)");
        }
    }
    
    /**
     * Seed default data (accounts and VAT rates)
     */
    private static function seedDefaultData(): void {
        global $wpdb;
        $prefix = $wpdb->prefix . 'cfuk_';
        
        // Seed UK nominal accounts
        $accounts = [
            ['1100', 'Trade Debtors', 'ASSET'],
            ['2100', 'Trade Creditors', 'LIABILITY'],
            ['1200', 'Bank Current Account', 'ASSET'],
            ['1210', 'Bank Deposit Account', 'ASSET'],
            ['1230', 'Petty Cash', 'ASSET'],
            ['4010', 'Sales - Services', 'INCOME'],
            ['4020', 'Sales - Goods', 'INCOME'],
            ['4030', 'Sales - Other', 'INCOME'],
            ['5010', 'Purchases - Materials', 'EXPENSE'],
            ['5020', 'Purchases - Goods for Resale', 'EXPENSE'],
            ['6010', 'Rent & Rates', 'EXPENSE'],
            ['6040', 'Insurance', 'EXPENSE'],
            ['6100', 'Electricity', 'EXPENSE'],
            ['6200', 'Advertising', 'EXPENSE'],
            ['6300', 'Accountancy Fees', 'EXPENSE'],
            ['6310', 'Legal Fees', 'EXPENSE'],
            ['6400', 'Travel & Subsistence', 'EXPENSE'],
            ['6540', 'Motor Fuel', 'EXPENSE'],
            ['6700', 'Telephone & Internet', 'EXPENSE'],
            ['6900', 'Depreciation - Office Equipment', 'EXPENSE'],
            ['6910', 'Depreciation - Motor Vehicles', 'EXPENSE'],
            ['7000', 'Wages & Salaries', 'EXPENSE'],
            ['7010', 'Employer NI Contributions', 'EXPENSE'],
            ['7020', 'Employer Pension Contributions', 'EXPENSE'],
            ['2200', 'Output VAT', 'LIABILITY'],
            ['2201', 'Input VAT', 'ASSET'],
            ['2202', 'VAT Liability', 'LIABILITY'],
            ['2203', 'VAT Refund Due', 'ASSET'],
            ['2204', 'VAT Payments on Account', 'ASSET'],
            ['2220', 'Corporation Tax', 'LIABILITY'],
            ['0030', 'Office Equipment', 'ASSET'],
            ['0050', 'Motor Vehicles', 'ASSET'],
            ['0031', 'Office Equipment Depreciation', 'ASSET'],
            ['0051', 'Motor Vehicles Depreciation', 'ASSET']
        ];
        
        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 UK VAT rates
        $current_date = gmdate('Y-m-d');
        $vat_rates = [
            // United Kingdom
            ['GB', 'GB-20', 20.00, 'Standard Rate', 'GB-20', 20.00, 'UK Standard Rate 20%', 1],
            ['GB', 'GB-5', 5.00, 'Reduced Rate', 'GB-5', 5.00, 'UK Reduced Rate 5%', 0],
            ['GB', 'GB-0', 0.00, 'Zero Rate', 'GB-0', 0.00, 'UK Zero Rate 0%', 0],
            ['GB', 'GB-EX', 0.00, 'Exempt', 'GB-EX', 0.00, 'UK Exempt', 0],
            ['GB', 'GB-RC', 0.00, 'Reverse Charge', 'GB-RC', 0.00, 'UK Reverse Charge', 0],
            ['GB', 'GB-OS', 0.00, 'Outside Scope', 'GB-OS', 0.00, 'Outside Scope of VAT', 0],
            
            // Ireland (common trade partner)
            ['IE', 'IE-23', 23.00, 'Standard Rate', 'IE-23', 23.00, 'Ireland Standard Rate 23%', 0],
            ['IE', 'IE-13_5', 13.50, 'Reduced Rate', 'IE-13_5', 13.50, 'Ireland Reduced Rate 13.5%', 0],
            ['IE', 'IE-9', 9.00, 'Second Reduced', 'IE-9', 9.00, 'Ireland Second Reduced 9%', 0],
            ['IE', 'IE-0', 0.00, 'Zero Rate', 'IE-0', 0.00, 'Ireland Zero Rate 0%', 0],
            
            // EU Reverse Charge
            ['EU', 'EU-RC', 0.00, 'Reverse Charge', 'EU-RC', 0.00, 'EU Reverse Charge', 0],
            
            // Non-EU (Rest of World)
            ['RW', 'RW-0', 0.00, 'Outside Scope', 'RW-0', 0.00, 'Non-EU Outside Scope', 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()
                ]);
            }
        }
    }
    
    /**
     * Seed default WordPress options
     */
    private static function seedDefaultOptions(): void {
        $existing_settings = get_option('comptaflow_uk_company_settings');
        
        if (!$existing_settings) {
            $default_company_settings = [
                'business_type' => 'SOLE_TRADER',
                'utr' => '',
                'company_number' => '',
                'vat_number' => '',
                'vat_liable' => false,
                'vat_scheme' => 'NOT_REGISTERED',
                'exercise_type' => 'tax_year',
                'exercise_end_date' => gmdate('Y') . '-04-05',
                'exercise_end_month' => '04',
                'company_name' => get_bloginfo('name'),
                'address_line_1' => '',
                'address_line_2' => '',
                'city' => '',
                'county' => '',
                'postcode' => '',
                'country' => 'GB',
                'currency' => 'GBP'
            ];
            
            update_option('comptaflow_uk_company_settings', $default_company_settings, true);
        }
        
        if (!get_option('comptaflow_uk_db_version')) {
            update_option('comptaflow_uk_db_version', '1.0.0', true);
        }
        
        if (!get_option('comptaflow_uk_last_migration')) {
            update_option('comptaflow_uk_last_migration', current_time('mysql'), true);
        }
    }
    
    /**
     * Get active VAT rates for a country
     */
    public static function getActiveVATRates($country_code = 'GB') {
        global $wpdb;
        $prefix = $wpdb->prefix . 'cfuk_';
        $current_date = gmdate('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);
    }
    
    /**
     * Get a VAT rate by its code
     */
    public static function getVATRateByCode($rate_code, $country_code = 'GB') {
        global $wpdb;
        $prefix = $wpdb->prefix . 'cfuk_';
        
        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);
    }
    
    /**
     * Get all active accounts
     */
    public static function getActiveAccounts() {
        global $wpdb;
        $prefix = $wpdb->prefix . 'cfuk_';
        
        return $wpdb->get_results("
            SELECT * FROM {$prefix}accounts 
            ORDER BY compte_num ASC
        ", ARRAY_A);
    }
    
    public static function get_transaction_by_id(int $id): ?array {
        global $wpdb;
        $prefix = $wpdb->prefix . 'cfuk_';
        
        $result = $wpdb->get_row($wpdb->prepare(
            "SELECT * FROM {$prefix}transactions WHERE id = %d",
            $id
        ), ARRAY_A);
        
        return $result ?: null;
    }

    /**
     * Get an account by its nominal code
     */
    public static function getAccountByNum($account_num) {
        global $wpdb;
        $prefix = $wpdb->prefix . 'cfuk_';
        
        return $wpdb->get_row($wpdb->prepare("
            SELECT * FROM {$prefix}accounts 
            WHERE compte_num = %s
        ", $account_num), ARRAY_A);
    }
    
    /**
     * Get user tax settings
     */
    public static function getUserFiscalSettings($user_id) {
        global $wpdb;
        $prefix = $wpdb->prefix . 'cfuk_';
        
        $settings = $wpdb->get_row($wpdb->prepare("
            SELECT * FROM {$prefix}user_fiscal_settings 
            WHERE user_id = %d
        ", $user_id), ARRAY_A);
        
        if (!$settings) {
            $wpdb->insert($prefix . 'user_fiscal_settings', [
                'user_id' => $user_id,
                'vat_scheme' => 'STANDARD',
                'fiscal_year_end' => '04-05',
                'advance_calculation_method' => 'PERCENTAGE',
                'q1_advance_rate' => 25.00,
                'q2_advance_rate' => 25.00,
                'q3_advance_rate' => 25.00,
                'q4_advance_rate' => 25.00,
                'notification_advance_days' => 15,
                'notification_declaration_days' => 7,
                'auto_calculate_advances' => 1
            ]);
            
            return self::getUserFiscalSettings($user_id);
        }
        
        return $settings;
    }
    
    /**
     * Update user tax settings
     */
    public static function updateUserFiscalSettings($user_id, $data) {
        global $wpdb;
        $prefix = $wpdb->prefix . 'cfuk_';
        
        $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);
        }
    }

    public static function dropTables(): void {
        global $wpdb;
        $prefix = $wpdb->prefix . 'cfuk_';
        
        $tables = [
            'invoice_lines',
            'invoices',
            'clients',
            'vat_periods',
            'user_fiscal_settings',
            'fixed_assets',
            'journal_entries',
            'transactions',
            'vat_rates',
            'accounts'
        ];
        
        $wpdb->query("SET FOREIGN_KEY_CHECKS = 0");
        
        foreach ($tables as $table) {
            $wpdb->query("DROP TABLE IF EXISTS {$prefix}{$table}");
        }
        
        $wpdb->query("SET FOREIGN_KEY_CHECKS = 1");
        
        // Delete WordPress options
        delete_option('comptaflow_uk_company_settings');
        delete_option('comptaflow_uk_db_version');
        delete_option('comptaflow_uk_last_migration');
    }
}
