<?php
/**
 * Database management for KleinBooks
 * Table prefix: kb_ (KleinBooks)
 */

if (!defined('ABSPATH')) {
    exit;
}

class KleinBooks_Database {

    const TABLE_PREFIX = 'kb_';

    public static function get_table_name($table) {
        global $wpdb;
        return $wpdb->prefix . self::TABLE_PREFIX . $table;
    }

    /**
     * Clean up old tables (for reinstall/reset)
     */
    public static function cleanup_old_tables() {
        global $wpdb;

        // Drop kb_ tables if they exist (for clean reinstall)
        $tables = array('offer_items', 'offers', 'invoice_items', 'invoices', 'clients', 'settings', 'expenses', 'emails');

        foreach ($tables as $table) {
            $table_name = $wpdb->prefix . self::TABLE_PREFIX . $table;
            $wpdb->query("DROP TABLE IF EXISTS $table_name");
        }

        // Clean up options
        delete_option('kleinbooks_version');
    }

    public static function create_tables() {
        global $wpdb;

        $charset_collate = $wpdb->get_charset_collate();

        $clients_table = self::get_table_name('clients');
        $invoices_table = self::get_table_name('invoices');
        $invoice_items_table = self::get_table_name('invoice_items');
        $offers_table = self::get_table_name('offers');
        $offer_items_table = self::get_table_name('offer_items');
        $settings_table = self::get_table_name('settings');
        $expenses_table = self::get_table_name('expenses');

        // Clients table
        $sql_clients = "CREATE TABLE $clients_table (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            company_name varchar(255) NOT NULL,
            client_code varchar(10) NOT NULL,
            contact_name varchar(255) DEFAULT '',
            email varchar(255) DEFAULT '',
            phone varchar(50) DEFAULT '',
            address_line1 varchar(255) DEFAULT '',
            address_line2 varchar(255) DEFAULT '',
            city varchar(100) DEFAULT '',
            postal_code varchar(20) DEFAULT '',
            country varchar(100) DEFAULT 'Deutschland',
            tax_number varchar(100) DEFAULT '',
            notes text,
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY client_code (client_code),
            KEY company_name (company_name)
        ) $charset_collate;";

        // Invoices table
        // Note: invoice_number can repeat across years (MK_00001_TM exists in both 2024 and 2025)
        // Unique constraint is on (invoice_year, invoice_sequence) to allow yearly reset
        $sql_invoices = "CREATE TABLE $invoices_table (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            invoice_number varchar(50) NOT NULL,
            client_id bigint(20) unsigned NOT NULL,
            invoice_year int(4) NOT NULL,
            invoice_sequence int(11) NOT NULL,
            date_issued date NOT NULL,
            date_due date DEFAULT NULL,
            status varchar(20) DEFAULT 'draft',
            subtotal decimal(10,2) DEFAULT 0.00,
            tax_rate decimal(5,2) DEFAULT 0.00,
            tax_amount decimal(10,2) DEFAULT 0.00,
            total decimal(10,2) DEFAULT 0.00,
            notes text,
            performance_period varchar(255) DEFAULT '',
            pdf_path varchar(500) DEFAULT '',
            sent_at datetime DEFAULT NULL,
            paid_at datetime DEFAULT NULL,
            cancelled_at datetime DEFAULT NULL,
            cancellation_reason varchar(255) DEFAULT NULL,
            correction_invoice_id 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 year_sequence (invoice_year, invoice_sequence),
            KEY invoice_number (invoice_number),
            KEY client_id (client_id),
            KEY status (status),
            KEY invoice_year (invoice_year)
        ) $charset_collate;";

        // Invoice items table
        $sql_invoice_items = "CREATE TABLE $invoice_items_table (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            invoice_id bigint(20) unsigned NOT NULL,
            description text NOT NULL,
            details text,
            quantity decimal(10,2) DEFAULT 1.00,
            unit_price decimal(10,2) NOT NULL,
            total decimal(10,2) NOT NULL,
            sort_order int DEFAULT 0,
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY invoice_id (invoice_id)
        ) $charset_collate;";

        // Offers (Angebote) table — mirrors invoices, with quote-specific fields.
        // 'expired' is NOT stored: it's derived in queries (status='sent' AND valid_until < today),
        // so there is no cron and no status drift. Stored statuses: draft|sent|accepted|declined|converted.
        $sql_offers = "CREATE TABLE $offers_table (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            offer_number varchar(50) NOT NULL,
            client_id bigint(20) unsigned NOT NULL,
            offer_year int(4) NOT NULL,
            offer_sequence int(11) NOT NULL,
            date_issued date NOT NULL,
            valid_until date DEFAULT NULL,
            status varchar(20) DEFAULT 'draft',
            subtotal decimal(10,2) DEFAULT 0.00,
            tax_rate decimal(5,2) DEFAULT 0.00,
            tax_amount decimal(10,2) DEFAULT 0.00,
            total decimal(10,2) DEFAULT 0.00,
            notes text,
            performance_period varchar(255) DEFAULT '',
            pdf_path varchar(500) DEFAULT '',
            sent_at datetime DEFAULT NULL,
            accepted_at datetime DEFAULT NULL,
            declined_at datetime DEFAULT NULL,
            converted_at datetime DEFAULT NULL,
            converted_invoice_id 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 year_sequence (offer_year, offer_sequence),
            KEY offer_number (offer_number),
            KEY client_id (client_id),
            KEY status (status),
            KEY offer_year (offer_year),
            KEY valid_until (valid_until)
        ) $charset_collate;";

        // Offer items table (clone of invoice_items)
        $sql_offer_items = "CREATE TABLE $offer_items_table (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            offer_id bigint(20) unsigned NOT NULL,
            description text NOT NULL,
            details text,
            quantity decimal(10,2) DEFAULT 1.00,
            unit_price decimal(10,2) NOT NULL,
            total decimal(10,2) NOT NULL,
            sort_order int DEFAULT 0,
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY offer_id (offer_id)
        ) $charset_collate;";

        // Settings table
        $sql_settings = "CREATE TABLE $settings_table (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            setting_key varchar(100) NOT NULL,
            setting_value longtext,
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY setting_key (setting_key)
        ) $charset_collate;";

        // Expenses table
        $sql_expenses = "CREATE TABLE $expenses_table (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            expense_date date NOT NULL,
            category varchar(100) NOT NULL,
            description varchar(500) NOT NULL,
            amount decimal(10,2) NOT NULL,
            vendor varchar(255) DEFAULT '',
            payment_method varchar(50) DEFAULT 'bank',
            is_recurring tinyint(1) DEFAULT 0,
            recurring_frequency varchar(20) DEFAULT '',
            receipt_path varchar(500) DEFAULT '',
            notes text,
            expense_year int(4) NOT NULL,
            is_asset tinyint(1) DEFAULT 0,
            asset_type varchar(50) DEFAULT '',
            useful_life_years int DEFAULT 0,
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY expense_date (expense_date),
            KEY category (category),
            KEY expense_year (expense_year),
            KEY is_recurring (is_recurring),
            KEY is_asset (is_asset)
        ) $charset_collate;";

        // Email log table
        $emails_table = self::get_table_name('emails');
        $sql_emails = "CREATE TABLE $emails_table (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            invoice_id bigint(20) unsigned DEFAULT NULL,
            recipient_email varchar(255) NOT NULL,
            recipient_name varchar(255) DEFAULT '',
            subject varchar(500) NOT NULL,
            body longtext,
            status varchar(20) DEFAULT 'sent',
            error_message text,
            sent_at datetime DEFAULT CURRENT_TIMESTAMP,
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY invoice_id (invoice_id),
            KEY recipient_email (recipient_email),
            KEY status (status),
            KEY sent_at (sent_at)
        ) $charset_collate;";

        require_once(ABSPATH . 'wp-admin/includes/upgrade.php');

        dbDelta($sql_clients);
        dbDelta($sql_invoices);
        dbDelta($sql_invoice_items);
        dbDelta($sql_offers);
        dbDelta($sql_offer_items);
        dbDelta($sql_settings);
        dbDelta($sql_expenses);
        dbDelta($sql_emails);
    }

    public static function insert_default_settings() {
        $defaults = array(
            'company_name' => '',
            'company_owner' => '',
            'company_address' => '',
            'company_postal_code' => '',
            'company_city' => '',
            'company_country' => 'Deutschland',
            'company_email' => '',
            'company_phone' => '',
            'bank_name' => '',
            'bank_iban' => '',
            'bank_bic' => '',
            'invoice_prefix' => 'KB-',
            'payment_terms_days' => 14,
            'offer_prefix' => 'ANG-',
            'offer_validity_days' => 14,
            'kleinunternehmer' => '1',
            'kleinunternehmer_text' => 'Gemäß § 19 UStG wird keine Umsatzsteuer berechnet.',
            'threshold_previous_year' => '22000',
            'threshold_current_year' => '25000',
            'invoice_theme' => 'light',
            'steuernummer' => '',
            'zugferd_enabled' => '1',  // ZUGFeRD 2.2 e-invoicing ON by default (Pro gate still applies via can_use_premium_code() — free installs won't embed)
        );

        foreach ($defaults as $key => $value) {
            self::update_setting($key, $value);
        }
    }

    public static function get_setting($key, $default = null) {
        global $wpdb;
        $table = self::get_table_name('settings');

        $value = $wpdb->get_var($wpdb->prepare(
            "SELECT setting_value FROM $table WHERE setting_key = %s",
            $key
        ));

        if ($value === null) {
            return $default;
        }

        // Try to decode JSON
        $decoded = json_decode($value, true);
        if (json_last_error() === JSON_ERROR_NONE && is_array($decoded)) {
            return $decoded;
        }

        return $value;
    }

    public static function update_setting($key, $value) {
        global $wpdb;
        $table = self::get_table_name('settings');

        // Handle booleans explicitly (PHP casts false to "" which is problematic)
        if (is_bool($value)) {
            $encoded_value = $value ? '1' : '0';
        } elseif (is_array($value)) {
            $encoded_value = json_encode($value);
        } else {
            $encoded_value = (string)$value;
        }

        $existing = $wpdb->get_var($wpdb->prepare(
            "SELECT id FROM $table WHERE setting_key = %s",
            $key
        ));

        if ($existing) {
            return $wpdb->update(
                $table,
                array('setting_value' => $encoded_value),
                array('setting_key' => $key)
            );
        } else {
            return $wpdb->insert($table, array(
                'setting_key' => $key,
                'setting_value' => $encoded_value
            ));
        }
    }

    public static function get_all_settings() {
        global $wpdb;
        $table = self::get_table_name('settings');

        $results = $wpdb->get_results("SELECT setting_key, setting_value FROM $table", ARRAY_A);

        $settings = array();
        if ($results) {
            foreach ($results as $row) {
                $decoded = json_decode($row['setting_value'], true);
                if (json_last_error() === JSON_ERROR_NONE && is_array($decoded)) {
                    $settings[$row['setting_key']] = $decoded;
                } else {
                    $settings[$row['setting_key']] = $row['setting_value'];
                }
            }
        }

        return $settings;
    }
}
