<?php
/**
 * Database schema creation, migrations, and seed data.
 */

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

class ZINV_Installer {

    public static function create_tables(): void {
        global $wpdb;

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

        $charset = $wpdb->get_charset_collate();
        $prefix  = $wpdb->prefix;

        $tables = self::get_schema( $prefix, $charset );

        foreach ( $tables as $sql ) {
            dbDelta( $sql );
        }
    }

    public static function get_schema( string $prefix, string $charset ): array {
        return [
            // 2.1 zinv_clients
            "CREATE TABLE {$prefix}zinv_clients (
                id  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                wp_user_id  BIGINT UNSIGNED NULL DEFAULT NULL,
                name  VARCHAR(255) NOT NULL,
                email  VARCHAR(255) NOT NULL,
                phone  VARCHAR(50) NULL DEFAULT NULL,
                company  VARCHAR(255) NULL DEFAULT NULL,
                website  VARCHAR(255) NULL DEFAULT NULL,
                address_line1  VARCHAR(255) NULL DEFAULT NULL,
                address_line2  VARCHAR(255) NULL DEFAULT NULL,
                city  VARCHAR(100) NULL DEFAULT NULL,
                state  VARCHAR(100) NULL DEFAULT NULL,
                postal_code  VARCHAR(20) NULL DEFAULT NULL,
                country  VARCHAR(2) NULL DEFAULT NULL,
                account_type  VARCHAR(20) NOT NULL DEFAULT 'standard',
                tax_id  VARCHAR(50) NULL DEFAULT NULL,
                notes  TEXT NULL,
                total_ltv  DECIMAL(15,4) NOT NULL DEFAULT 0.0000,
                currency  VARCHAR(3) NOT NULL DEFAULT 'USD',
                status  VARCHAR(20) NOT NULL DEFAULT 'active',
                stripe_customer_id  VARCHAR(255) NULL DEFAULT NULL,
                tax_exempt  TINYINT(1) NOT NULL DEFAULT 0,
                version  INT UNSIGNED NOT NULL DEFAULT 1,
                created_at  DATETIME NOT NULL,
                updated_at  DATETIME NOT NULL,
                PRIMARY KEY  (id),
                UNIQUE KEY email (email),
                KEY wp_user_id (wp_user_id),
                KEY account_type (account_type),
                KEY status (status),
                KEY stripe_customer_id (stripe_customer_id)
            ) {$charset} ENGINE=InnoDB;",

            // 2.2 zinv_invoices
            "CREATE TABLE {$prefix}zinv_invoices (
                id  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                invoice_number  VARCHAR(50) NOT NULL,
                client_id  BIGINT UNSIGNED NOT NULL,
                subscription_id  BIGINT UNSIGNED NULL DEFAULT NULL,
                estimate_id  BIGINT UNSIGNED NULL DEFAULT NULL,
                status  VARCHAR(20) NOT NULL DEFAULT 'draft',
                issue_date  DATE NOT NULL,
                due_date  DATE NOT NULL,
                subtotal  DECIMAL(15,4) NOT NULL,
                tax_total  DECIMAL(15,4) NOT NULL DEFAULT 0.0000,
                discount_total  DECIMAL(15,4) NOT NULL DEFAULT 0.0000,
                total  DECIMAL(15,4) NOT NULL,
                amount_paid  DECIMAL(15,4) NOT NULL DEFAULT 0.0000,
                amount_due  DECIMAL(15,4) NOT NULL,
                currency  VARCHAR(3) NOT NULL DEFAULT 'USD',
                discount_type  VARCHAR(20) NULL DEFAULT NULL,
                discount_value  DECIMAL(15,4) NOT NULL DEFAULT 0.0000,
                template  VARCHAR(50) NOT NULL DEFAULT 'clean-sheet',
                document_title  VARCHAR(100) NULL DEFAULT NULL,
                notes  TEXT NULL,
                terms  TEXT NULL,
                footer_text  VARCHAR(255) NULL DEFAULT NULL,
                is_recurring  TINYINT(1) NOT NULL DEFAULT 0,
                recurring_interval  VARCHAR(20) NULL DEFAULT NULL,
                recurring_next_run  DATETIME NULL DEFAULT NULL,
                recurring_count  SMALLINT UNSIGNED NOT NULL DEFAULT 0,
                recurring_generated  SMALLINT UNSIGNED NOT NULL DEFAULT 0,
                recurring_parent_id  BIGINT UNSIGNED NULL DEFAULT NULL,
                viewed_at  DATETIME NULL DEFAULT NULL,
                paid_at  DATETIME NULL DEFAULT NULL,
                sent_at  DATETIME NULL DEFAULT NULL,
                due_reminder_sent  TINYINT(1) NOT NULL DEFAULT 0,
                pdf_path  VARCHAR(500) NULL DEFAULT NULL,
                version  INT UNSIGNED NOT NULL DEFAULT 1,
                metadata  JSON NULL,
                created_at  DATETIME NOT NULL,
                updated_at  DATETIME NOT NULL,
                PRIMARY KEY  (id),
                UNIQUE KEY invoice_number (invoice_number),
                KEY client_id (client_id),
                KEY status (status),
                KEY issue_date (issue_date),
                KEY due_date (due_date),
                KEY subscription_id (subscription_id),
                KEY estimate_id (estimate_id),
                KEY status_due (status, due_date),
                KEY client_status (client_id, status),
                KEY recurring_lookup (is_recurring, recurring_next_run),
                KEY recurring_parent_id (recurring_parent_id)
            ) {$charset} ENGINE=InnoDB;",

            // 2.3 zinv_invoice_line_items
            "CREATE TABLE {$prefix}zinv_invoice_line_items (
                id  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                invoice_id  BIGINT UNSIGNED NOT NULL,
                description  TEXT NOT NULL,
                quantity  DECIMAL(15,4) NOT NULL DEFAULT 1.0000,
                unit_price  DECIMAL(15,4) NOT NULL,
                total  DECIMAL(15,4) NOT NULL,
                sort_order  INT NOT NULL DEFAULT 0,
                tax_rate  DECIMAL(5,2) NOT NULL DEFAULT 0.00,
                tax_amount  DECIMAL(15,4) NOT NULL DEFAULT 0.0000,
                PRIMARY KEY  (id),
                KEY invoice_id (invoice_id)
            ) {$charset} ENGINE=InnoDB;",

            // 2.4 zinv_estimates
            "CREATE TABLE {$prefix}zinv_estimates (
                id  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                estimate_number  VARCHAR(50) NOT NULL,
                client_id  BIGINT UNSIGNED NOT NULL,
                status  VARCHAR(20) NOT NULL DEFAULT 'draft',
                issue_date  DATE NOT NULL,
                expiry_date  DATE NULL DEFAULT NULL,
                subtotal  DECIMAL(15,4) NOT NULL,
                tax_total  DECIMAL(15,4) NOT NULL DEFAULT 0.0000,
                discount_total  DECIMAL(15,4) NOT NULL DEFAULT 0.0000,
                total  DECIMAL(15,4) NOT NULL,
                currency  VARCHAR(3) NOT NULL DEFAULT 'USD',
                discount_type  VARCHAR(20) NULL DEFAULT NULL,
                discount_value  DECIMAL(15,4) NOT NULL DEFAULT 0.0000,
                template  VARCHAR(50) NOT NULL DEFAULT 'clean-sheet',
                document_title  VARCHAR(100) NULL DEFAULT NULL,
                notes  TEXT NULL,
                terms  TEXT NULL,
                sent_at  DATETIME NULL DEFAULT NULL,
                approved_at  DATETIME NULL DEFAULT NULL,
                declined_at  DATETIME NULL DEFAULT NULL,
                converted_invoice_id  BIGINT UNSIGNED NULL DEFAULT NULL,
                auto_convert  TINYINT(1) NOT NULL DEFAULT 0,
                version  INT UNSIGNED NOT NULL DEFAULT 1,
                created_at  DATETIME NOT NULL,
                updated_at  DATETIME NOT NULL,
                PRIMARY KEY  (id),
                UNIQUE KEY estimate_number (estimate_number),
                KEY client_id (client_id),
                KEY status (status),
                KEY status_expiry (status, expiry_date)
            ) {$charset} ENGINE=InnoDB;",

            // 2.5 zinv_estimate_line_items
            "CREATE TABLE {$prefix}zinv_estimate_line_items (
                id  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                estimate_id  BIGINT UNSIGNED NOT NULL,
                description  TEXT NOT NULL,
                quantity  DECIMAL(15,4) NOT NULL DEFAULT 1.0000,
                unit_price  DECIMAL(15,4) NOT NULL,
                total  DECIMAL(15,4) NOT NULL,
                sort_order  INT NOT NULL DEFAULT 0,
                tax_rate  DECIMAL(5,2) NOT NULL DEFAULT 0.00,
                tax_amount  DECIMAL(15,4) NOT NULL DEFAULT 0.0000,
                PRIMARY KEY  (id),
                KEY estimate_id (estimate_id)
            ) {$charset} ENGINE=InnoDB;",

            // 2.6 zinv_payments
            "CREATE TABLE {$prefix}zinv_payments (
                id  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                invoice_id  BIGINT UNSIGNED NOT NULL,
                client_id  BIGINT UNSIGNED NOT NULL,
                subscription_id  BIGINT UNSIGNED NULL DEFAULT NULL,
                amount  DECIMAL(15,4) NOT NULL,
                currency  VARCHAR(3) NOT NULL DEFAULT 'USD',
                status  VARCHAR(20) NOT NULL DEFAULT 'pending',
                method  VARCHAR(20) NOT NULL DEFAULT 'manual',
                stripe_charge_id  VARCHAR(255) NULL DEFAULT NULL,
                stripe_refund_id  VARCHAR(255) NULL DEFAULT NULL,
                refund_amount  DECIMAL(15,4) NOT NULL DEFAULT 0.0000,
                transaction_date  DATETIME NOT NULL,
                gateway_response  JSON NULL,
                notes  TEXT NULL,
                created_at  DATETIME NOT NULL,
                updated_at  DATETIME NOT NULL,
                PRIMARY KEY  (id),
                KEY invoice_id (invoice_id),
                KEY client_id (client_id),
                KEY status (status),
                KEY transaction_date (transaction_date),
                KEY subscription_id (subscription_id),
                KEY invoice_transaction (invoice_id, transaction_date)
            ) {$charset} ENGINE=InnoDB;",

            // 2.7 zinv_activities
            "CREATE TABLE {$prefix}zinv_activities (
                id  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                user_id  BIGINT UNSIGNED NULL DEFAULT NULL,
                action  VARCHAR(100) NOT NULL,
                object_type  VARCHAR(50) NOT NULL,
                object_id  BIGINT UNSIGNED NULL DEFAULT NULL,
                description  TEXT NOT NULL,
                status  VARCHAR(20) NOT NULL DEFAULT 'successful',
                ip_address  VARCHAR(45) NULL DEFAULT NULL,
                metadata  JSON NULL,
                created_at  DATETIME NOT NULL,
                PRIMARY KEY  (id),
                KEY user_id (user_id),
                KEY object_type_id (object_type, object_id),
                KEY action (action),
                KEY created_at (created_at)
            ) {$charset} ENGINE=InnoDB;",

            // 2.7b zinv_activities_archive (same schema as zinv_activities)
            "CREATE TABLE {$prefix}zinv_activities_archive (
                id  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                user_id  BIGINT UNSIGNED NULL DEFAULT NULL,
                action  VARCHAR(100) NOT NULL,
                object_type  VARCHAR(50) NOT NULL,
                object_id  BIGINT UNSIGNED NULL DEFAULT NULL,
                description  TEXT NOT NULL,
                status  VARCHAR(20) NOT NULL DEFAULT 'successful',
                ip_address  VARCHAR(45) NULL DEFAULT NULL,
                metadata  JSON NULL,
                created_at  DATETIME NOT NULL,
                PRIMARY KEY  (id),
                KEY user_id (user_id),
                KEY object_type_id (object_type, object_id),
                KEY action (action),
                KEY created_at (created_at)
            ) {$charset} ENGINE=InnoDB;",

            // 2.8 zinv_settings
            "CREATE TABLE {$prefix}zinv_settings (
                id  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                setting_key  VARCHAR(100) NOT NULL,
                setting_value  LONGTEXT NOT NULL,
                setting_group  VARCHAR(50) NOT NULL,
                updated_at  DATETIME NOT NULL,
                PRIMARY KEY  (id),
                UNIQUE KEY setting_key (setting_key),
                KEY setting_group (setting_group)
            ) {$charset} ENGINE=InnoDB;",

            // 2.9 zinv_summary_cache
            "CREATE TABLE {$prefix}zinv_summary_cache (
                id  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                cache_key  VARCHAR(100) NOT NULL,
                cache_value  VARCHAR(50) NOT NULL,
                period  VARCHAR(20) NULL DEFAULT NULL,
                computed_at  DATETIME NOT NULL,
                PRIMARY KEY  (id),
                UNIQUE KEY cache_key_period (cache_key, period),
                KEY period (period)
            ) {$charset} ENGINE=InnoDB;",

            // 2.19 zinv_sequences
            "CREATE TABLE {$prefix}zinv_sequences (
                id  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                sequence_key  VARCHAR(50) NOT NULL,
                current_value  INT UNSIGNED NOT NULL DEFAULT 0,
                created_at  DATETIME NOT NULL,
                updated_at  DATETIME NOT NULL,
                PRIMARY KEY  (id),
                UNIQUE KEY sequence_key (sequence_key)
            ) {$charset} ENGINE=InnoDB;",
        ];
    }

    public static function register_capabilities(): void {
        $capabilities = [
            'zinv_view_dashboard'       => 'View Zoventic Invoices dashboard',
            'zinv_manage_clients'       => 'Create, edit, delete clients',
            'zinv_manage_invoices'      => 'Create, edit, send, void invoices',
            'zinv_manage_estimates'     => 'Create, edit, send, convert estimates',
            'zinv_view_payments'        => 'View payment history',
            'zinv_record_payments'      => 'Record manual payments',
            'zinv_manage_settings'      => 'Edit plugin settings',
            'zinv_view_reports'         => 'View reports and analytics',
        ];

        $admin = get_role( 'administrator' );
        if ( $admin ) {
            foreach ( array_keys( $capabilities ) as $cap ) {
                $admin->add_cap( $cap );
            }
        }
    }

    public static function seed_defaults(): void {
        global $wpdb;

        $defaults = [
            // General.
            'company_name'                => [ 'value' => '', 'group' => 'general' ],
            'company_logo'                => [ 'value' => '', 'group' => 'general' ],
            'company_email'               => [ 'value' => '', 'group' => 'general' ],
            'company_phone'               => [ 'value' => '', 'group' => 'general' ],
            'tax_id'                      => [ 'value' => '', 'group' => 'general' ],
            'address'                     => [ 'value' => '', 'group' => 'general' ],
            'currency'                    => [ 'value' => 'USD', 'group' => 'general' ],

            // Invoicing.
            'invoice_prefix'              => [ 'value' => 'INV-', 'group' => 'invoicing' ],
            'invoice_starting_number'     => [ 'value' => '1', 'group' => 'invoicing' ],
            'default_terms'               => [ 'value' => 'Payment is due within 30 days.', 'group' => 'invoicing' ],
            'default_template'            => [ 'value' => 'clean-sheet', 'group' => 'invoicing' ],
            'estimate_prefix'             => [ 'value' => 'EST-', 'group' => 'invoicing' ],

            // Payment.
            'stripe_test_mode'            => [ 'value' => '1', 'group' => 'payment' ],
            'stripe_test_key'             => [ 'value' => '', 'group' => 'payment' ],
            'stripe_live_key'             => [ 'value' => '', 'group' => 'payment' ],
            'stripe_webhook_secret'       => [ 'value' => '', 'group' => 'payment' ],
        ];

        $now = current_time( 'mysql' );

        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        foreach ( $defaults as $key => $data ) {
            $exists = $wpdb->get_var(
                $wpdb->prepare(
                    "SELECT COUNT(*) FROM {$wpdb->prefix}zinv_settings WHERE setting_key = %s",
                    $key
                )
            );

            if ( ! $exists ) {
                $wpdb->insert(
                    "{$wpdb->prefix}zinv_settings",
                    [
                        'setting_key'   => $key,
                        'setting_value' => $data['value'],
                        'setting_group' => $data['group'],
                        'updated_at'    => $now,
                    ],
                    [ '%s', '%s', '%s', '%s' ]
                );
            }
        }
        // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
    }

    public static function maybe_migrate(): void {
        // One-time rename from the pre-1.0 "wif_" prefix to "zinv_".
        self::migrate_legacy_prefix();

        // Run dbDelta to catch any schema changes / create missing tables.
        self::create_tables();
        update_option( 'zinv_db_version', ZINV_VERSION );
    }

    /**
     * Migrate data created under the legacy "wif_" prefix to "zinv_".
     *
     * Renames DB tables, copies the wif_db_version option, copies capabilities
     * on every role, and renames wif_-prefixed options. Idempotent: if a target
     * already exists or no legacy artifact is present, the step is skipped.
     */
    private static function migrate_legacy_prefix(): void {
        global $wpdb;

        // Guard: only run once. The legacy version option is the sentinel.
        if ( get_option( 'zinv_legacy_migrated' ) ) {
            return;
        }

        $tables = [
            'clients', 'invoices', 'invoice_line_items', 'estimates',
            'estimate_line_items', 'payments', 'activities', 'activities_archive',
            'settings', 'summary_cache', 'sequences',
            // Pro tables (renamed here too so a Pro upgrade inherits the data).
            'plans', 'subscriptions', 'tax_zones', 'tax_rates',
            'dunning_campaigns', 'dunning_instances', 'audit_events',
            'webhook_events', 'monthly_summaries', 'portal_tokens',
            'api_keys', 'webhooks',
        ];

        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.DirectDatabaseQuery.SchemaChange
        foreach ( $tables as $name ) {
            $legacy = $wpdb->prefix . 'wif_' . $name;
            $target = $wpdb->prefix . 'zinv_' . $name;

            $legacy_exists = $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $wpdb->esc_like( $legacy ) ) ) === $legacy;
            $target_exists = $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $wpdb->esc_like( $target ) ) ) === $target;

            if ( $legacy_exists && ! $target_exists ) {
                $wpdb->query( "RENAME TABLE `{$legacy}` TO `{$target}`" );
            }
        }

        // Options: copy wif_-prefixed options to zinv_-prefixed equivalents.
        $legacy_options = $wpdb->get_col(
            $wpdb->prepare(
                "SELECT option_name FROM {$wpdb->options} WHERE option_name LIKE %s",
                $wpdb->esc_like( 'wif_' ) . '%'
            )
        );
        // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.DirectDatabaseQuery.SchemaChange
        foreach ( $legacy_options as $old_name ) {
            $new_name = 'zinv_' . substr( $old_name, 4 );
            if ( false === get_option( $new_name, false ) ) {
                update_option( $new_name, get_option( $old_name ) );
            }
        }

        // Capabilities: copy each legacy cap to its zinv_ equivalent on every role.
        $cap_map = [
            'wif_view_dashboard'   => 'zinv_view_dashboard',
            'wif_manage_clients'   => 'zinv_manage_clients',
            'wif_manage_invoices'  => 'zinv_manage_invoices',
            'wif_manage_estimates' => 'zinv_manage_estimates',
            'wif_view_payments'    => 'zinv_view_payments',
            'wif_record_payments'  => 'zinv_record_payments',
            'wif_manage_settings'  => 'zinv_manage_settings',
            'wif_view_reports'     => 'zinv_view_reports',
        ];
        foreach ( wp_roles()->roles as $role_name => $role_data ) {
            $role = get_role( $role_name );
            if ( ! $role ) {
                continue;
            }
            foreach ( $cap_map as $old_cap => $new_cap ) {
                if ( ! empty( $role_data['capabilities'][ $old_cap ] ) && ! $role->has_cap( $new_cap ) ) {
                    $role->add_cap( $new_cap );
                }
            }
        }

        update_option( 'zinv_legacy_migrated', time() );
    }
}
