<?php
namespace ARCHM\Features;

use ARCHM\Helpers\Logger;
use ARCHM\Helpers\DBHelper;

/**
 * SQLite Database Handler with Google Drive Sync Hooks
 *
 * Available Hooks for Google Drive Synchronization:
 * - archm_sqlite_database_initialized: Triggered when SQLite database is first created
 * - archm_sqlite_data_inserted: Triggered after order is archived to SQLite
 * - archm_sqlite_data_deleted: Triggered after archived order is deleted from SQLite
 */
class Sqlite {
    use DBHelper;

    /**
     * SQLite database instance
     *
     * @var object
     */
    public $sqdb;

    /**
     * SQLite database instance
     *
     * @var string
     */
    protected $table_name;

    /**
     * Singleton instance
     *
     * @var self
     */
    private static $instance;

    /**
     * Returns the singleton instance
     *
     * @return self
     */
    public static function get_instance() {
        if ( ! self::$instance ) {
            self::$instance = new self();
        }

        return self::$instance;
    }

    public function __construct() {
        try {
            global $wpdb;
            $this->table_name = $wpdb->prefix . 'archived_orders';

            // Check for old database file first
            $old_file = $this->get_db_directory() . '/archive-master-sqlite.db';
            if ( file_exists($old_file) ) {
                // If old file exists, call create_database_for_archiving to handle migration
                $this->create_database_for_archiving();
                return;
            }

            // Only connect to database if it exists, don't create it
            $db_file = $this->get_db_file_path();
            if ( file_exists($db_file) ) {
                $this->connect_to_database($db_file);
            }
		} catch ( \PDOException $e ) {
            echo esc_html($e->getMessage());
        }
    }

    /**
     * Connect to existing database file
     *
     * @param string $db_file
     * @return void
     */
    private function connect_to_database( $db_file ) {
        // Create a new PDO connection with the SQLite database file
        $this->sqdb = new \PDO('sqlite:' . $db_file);                           // phpcs:ignore WordPress.DB.RestrictedClasses.mysql__PDO
        $this->sqdb->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION); // phpcs:ignore WordPress.DB.RestrictedClasses.mysql__PDO
    }

    /**
     * Create and initialize database file during archiving process
     *
     * @return bool True on success, false on failure
     */
    public function create_database_for_archiving() {
        try {
            // Get database directory and file path
            $db_directory = $this->get_db_directory();
            $db_file = $this->get_db_file_path();

            // Check if database already exists
            if ( file_exists($db_file) ) {
                // Database exists, just connect to it
                $this->connect_to_database($db_file);
                return true;
            }

            // Create the directory if it doesn't exist
            if ( ! file_exists($db_directory) ) {
                wp_mkdir_p($db_directory);
            }

            // Handle migration from old filename to new hash-based filename
            $old_file = $db_directory . '/archive-master-sqlite.db';
            if ( file_exists($old_file) ) {
                global $wp_filesystem;
                if ( empty( $wp_filesystem ) ) {
                    require_once ABSPATH . 'wp-admin/includes/file.php';
                    WP_Filesystem();
                }

                if ( $wp_filesystem->move( $old_file, $db_file, true ) ) {
                    // Logger::logMessage('Successfully migrated SQLite database to hash-based filename.');
                    $this->connect_to_database($db_file);
                    return true;
                }
            }

            // Create new database file and connect
            $this->connect_to_database($db_file);

            // Initialize database schema for new database
            $this->initialize_database_schema();

            return true;
        } catch ( \PDOException $e ) {
            // Logger::logMessage('Failed to create database for archiving: ' . $e->getMessage());
            return false;
        }
    }

    /**
     * Initialize the database schema
     * This ensures tables are created only once when the database is first created
     */
    private function initialize_database_schema() {
        try {
            // Begin transaction
            $this->sqdb->beginTransaction();

            // Create the main archived_orders table
            $create_table_query = "
                CREATE TABLE IF NOT EXISTS {$this->table_name} (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    archived_order_id INTEGER NOT NULL UNIQUE,
                    order_data TEXT NOT NULL,
                    ordered_products TEXT NOT NULL,
                    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
                );
            ";
            $this->sqdb->exec($create_table_query);

            // Create indexes for better performance
            $create_index = "CREATE INDEX IF NOT EXISTS idx_archived_order_id ON {$this->table_name} (archived_order_id);";
            $this->sqdb->exec($create_index);

            // Commit transaction
            $this->sqdb->commit();

            do_action('archm_sqlite_database_initialized');

        } catch ( \PDOException $e ) {
            // Rollback transaction on error
            $this->sqdb->rollBack();
            // Logger::logMessage('Failed to initialize database schema: ' . $e->getMessage());
            throw $e;
        }
    }

    /**
     * Check if database connection exists
     *
     * @return bool
     */
    public function is_connected() {
        return null !== $this->sqdb;
    }

    /**
     * Insert order archived order table
     *
     * @param object
     * @return mixed
     */
    public function insert_archived_order( $order, $order_products = [] ) {
        if ( ! $this->is_connected() ) {
            // Logger::logMessage('SQLite database not connected. Cannot insert archived order.');
            return false;
        }
        $order_id = $order->get_id();

        // Check if the order already exists in the SQLite table
        $check_stmt = $this->sqdb->prepare("SELECT COUNT(*) FROM {$this->table_name} WHERE archived_order_id = :archived_order_id");
        $check_stmt->bindValue(':archived_order_id', $order_id);
        $check_stmt->execute();

        if ( $check_stmt->fetchColumn() > 0 ) {
            // Logger::logMessage('Order already archived | ID No: ' . $order_id);
            return false; // Order already exists, no need to insert again
        }

        // Get order data and enhance it with complete coupon and tax information
        $order_data = $order->get_data();

        // Extract coupon data for proper archiving
        $coupon_data = [];
        foreach ( $order->get_items('coupon') as $item_id => $coupon_item ) {
            if ( is_a($coupon_item, 'WC_Order_Item_Coupon') ) {
                $coupon_meta_data = [];
                foreach ( $coupon_item->get_meta_data() as $meta ) {
                    $coupon_meta_data[] = [
                        'key'   => $meta->key,
                        'value' => $meta->value,
                    ];
                }
                $coupon_data[] = [
                    'code'         => $coupon_item->get_code(),
                    'discount'     => $coupon_item->get_discount(),
                    'discount_tax' => $coupon_item->get_discount_tax(),
                    'meta_data'    => $coupon_meta_data,
                ];
            }
        }

        // Extract tax data for proper archiving
        $tax_data = [];
        foreach ( $order->get_items('tax') as $item_id => $tax_item ) {
            if ( is_a($tax_item, 'WC_Order_Item_Tax') ) {
                $tax_meta_data = [];
                foreach ( $tax_item->get_meta_data() as $meta ) {
                    $tax_meta_data[] = [
                        'key'   => $meta->key,
                        'value' => $meta->value,
                    ];
                }
                $tax_data[] = [
                    'rate_code'          => $tax_item->get_rate_code(),
                    'rate_id'            => $tax_item->get_rate_id(),
                    'label'              => $tax_item->get_label(),
                    'compound'           => $tax_item->get_compound(),
                    'tax_total'          => $tax_item->get_tax_total(),
                    'shipping_tax_total' => $tax_item->get_shipping_tax_total(),
                    'meta_data'          => $tax_meta_data,
                ];
            }
        }

        // Replace coupon_lines with complete coupon data
        if ( ! empty($coupon_data) ) {
            $order_data['coupon_lines'] = $coupon_data;
        }

        // Replace tax_lines with complete tax data
        if ( ! empty($tax_data) ) {
            $order_data['tax_lines'] = $tax_data;
        }

        // Include custom order meta keys if enabled and selected
        $enable_custom_meta = get_option('archm_enable_related_order_metadata_archiving', false);
        if ( $enable_custom_meta ) {
            $selected_meta_keys = get_option('archm_related_order_metadata_selected', []);
            if ( ! empty($selected_meta_keys) && is_array($selected_meta_keys) ) {
                $custom_meta = [];
                foreach ( $selected_meta_keys as $meta_key ) {
                    $meta_value = $order->get_meta($meta_key);
                    if ( '' !== $meta_value ) {
                        $custom_meta[ $meta_key ] = $meta_value;
                    }
                }
                if ( ! empty($custom_meta) ) {
                    $order_data['custom_meta'] = $custom_meta;
                }
            }
        }

        // Proceed to insert since it doesn't exist
        $stmt = $this->sqdb->prepare("INSERT INTO {$this->table_name} (archived_order_id, order_data, ordered_products) VALUES (:archived_order_id, :order_data, :ordered_products)");
        $stmt->bindValue(':archived_order_id', $order->get_id());
        $stmt->bindValue(':order_data', wp_json_encode($order_data));    // Store as JSON with complete coupon data
        $stmt->bindValue(':ordered_products', wp_json_encode($order_products)); // Store as JSON for flexibility
        $stmt->execute();

        if ( $stmt->rowCount() > 0 ) {
            // Logger::logMessage('successfully inserted to SQLite | ID No: ' . $order->get_id());
            return true;
        }

        return false; // Return false if insertion failed
    }

    public function fetch_archived_orders( $orders_per_page = 10, $start_index = 0, $sort = 'ASC' ) {
        if ( ! $this->is_connected() ) {
            return array();
        }
        // Build the query with placeholders
        $order_query = "SELECT * FROM {$this->table_name}
        ORDER BY ID $sort
        LIMIT :limit OFFSET :offset";

        // Prepare the query
        $stmt = $this->sqdb->prepare($order_query);

                                                                       // Bind the parameters securely
        $stmt->bindParam(':limit', $orders_per_page, \PDO::PARAM_INT); // phpcs:ignore WordPress.DB.RestrictedClasses.mysql__PDO
        $stmt->bindParam(':offset', $start_index, \PDO::PARAM_INT);    // phpcs:ignore WordPress.DB.RestrictedClasses.mysql__PDO

        // Execute the query
        $stmt->execute();

                                                   // Fetch results
        return $stmt->fetchAll(\PDO::FETCH_ASSOC); // phpcs:ignore WordPress.DB.RestrictedClasses.mysql__PDO
    }

    /**
     * Get archive order according to wc order search
     *
     * @param int $order_id
     * @return mixed
     */
    public function get_archived_order( $order_id ) {
        if ( ! $this->is_connected() ) {
            return false;
        }
        $query = $this->sqdb->prepare("SELECT * FROM {$this->table_name} WHERE archived_order_id = :archived_order_id");
        $query->bindValue(':archived_order_id', $order_id, \PDO::PARAM_STR); // phpcs:ignore WordPress.DB.RestrictedClasses.mysql__PDO
        $query->execute();

                                                        // Fetch a single row as an associative array
        $order_data = $query->fetch(\PDO::FETCH_ASSOC); // phpcs:ignore WordPress.DB.RestrictedClasses.mysql__PDO

        if ( $order_data ) {
            return $order_data;
        }

        return false;
    }

    /**
     * Delete archived order from SQLite database
     *
     * @param int $order_id
     * @return mixed
     */
    public function delete_archived_order( $order_id ) {
        if ( ! $this->is_connected() ) {
            return false;
        }
        $query = $this->sqdb->prepare("DELETE FROM {$this->table_name} WHERE archived_order_id = :archived_order_id");
        $query->bindParam(':archived_order_id', $order_id);
        $query->execute();

        // Check the number of affected rows
        if ( $query->rowCount() > 0 ) {
            // Logger::logMessage('Order deleted successfully.');
            return true;
        } else {
            // Logger::logMessage('No matching record found to delete.');
            return false;
        }
    }

    /**
     * Search archived orders with filters
     *
     * @param string $search_term
     * @param string $status
     * @param int $limit
     * @param int $offset
     * @return array
     */
    public function search_archived_orders( $search_term, $status, $limit, $offset ) {
        if ( ! $this->is_connected() ) {
            return array();
        }
        $where_conditions = [];
        $params           = [];

        if ( ! empty($search_term) ) {
            $where_conditions[] = '(order_data LIKE :search)';
            $where_conditions[] = '(archived_order_id LIKE :id)';
            $params[':search']  = '%' . $search_term . '%';
            $params[':id']      = '%' . $search_term . '%';
        }

        if ( ! empty($status) ) {
            $where_conditions[] = '(order_data LIKE :status)';
            $params[':status']  = '%"status":"' . $status . '"%';
        }

        $where_clause = ! empty($where_conditions) ? 'WHERE ' . implode(' AND ', $where_conditions) : '';

        $query = "SELECT * FROM {$this->table_name}
              {$where_clause}
              ORDER BY id DESC
              LIMIT :limit OFFSET :offset";

        $stmt = $this->sqdb->prepare($query);

        // Bind search parameters
        foreach ( $params as $key => $value ) {
            $stmt->bindValue($key, $value, \PDO::PARAM_STR); // phpcs:ignore WordPress.DB.RestrictedClasses.mysql__PDO
        }

                                                               // Bind pagination parameters
        $stmt->bindValue(':limit', $limit, \PDO::PARAM_INT);   // phpcs:ignore WordPress.DB.RestrictedClasses.mysql__PDO
        $stmt->bindValue(':offset', $offset, \PDO::PARAM_INT); // phpcs:ignore WordPress.DB.RestrictedClasses.mysql__PDO

        $stmt->execute();
        return $stmt->fetchAll(\PDO::FETCH_ASSOC); // phpcs:ignore WordPress.DB.RestrictedClasses.mysql__PDO
    }

    /**
     * Count total search results
     *
     * @param string $search_term
     * @param string $status
     * @return int
     */
    public function count_search_results( $search_term, $status ) {
        if ( ! $this->is_connected() ) {
            return 0;
        }
        $where_conditions = [];
        $params           = [];

        if ( ! empty($search_term) ) {
            $where_conditions[] = '(order_data LIKE :search)';
            $where_conditions[] = '(archived_order_id LIKE :id)';
            $params[':search']  = '%' . $search_term . '%';
            $params[':id']      = '%' . $search_term . '%';
        }

        if ( ! empty($status) ) {
            $where_conditions[] = '(order_data LIKE :status)';
            $params[':status']  = '%"status":"' . $status . '"%';
        }

        $where_clause = ! empty($where_conditions) ? 'WHERE ' . implode(' AND ', $where_conditions) : '';

        $query = "SELECT COUNT(*) as count FROM {$this->table_name} {$where_clause}";
        $stmt  = $this->sqdb->prepare($query);

        foreach ( $params as $key => $value ) {
            $stmt->bindValue($key, $value, \PDO::PARAM_STR); // phpcs:ignore WordPress.DB.RestrictedClasses.mysql__PDO
        }

        $stmt->execute();
        $result = $stmt->fetch(\PDO::FETCH_ASSOC); // phpcs:ignore WordPress.DB.RestrictedClasses.mysql__PDO
        return (int) $result['count'];
    }

    /**
     * Count total order
     *
     * @param string $search_term
     * @param string $status
     * @return int
     */
    public function count_total_archived_order() {
        if ( ! $this->is_connected() ) {
            return 0;
        }
        $query = "SELECT COUNT(*) as count FROM {$this->table_name}";
        $stmt  = $this->sqdb->prepare($query);

        $stmt->execute();
        $result = $stmt->fetch(\PDO::FETCH_ASSOC); // phpcs:ignore WordPress.DB.RestrictedClasses.mysql__PDO
        return (int) $result['count'];
    }
}
