<?php
/**
 * SQLite integration for WordPress
 * This file enables WordPress to use SQLite database instead of MySQL
 */

// Prevent direct access
if (!defined('ABSPATH')) {
    exit;
}

// Check if SQLite extension is available
if (!extension_loaded('pdo_sqlite')) {
    wp_die('SQLite PDO extension is not available. Please install php-sqlite3.');
}

// Define SQLite database path
if (!defined('DB_FILE')) {
    define('DB_FILE', ABSPATH . 'wp-content/database/wordpress.db');
}

// Create database directory if it doesn't exist
$db_dir = dirname(DB_FILE);
if (!file_exists($db_dir)) {
    wp_mkdir_p($db_dir);
}

/**
 * Custom database class for SQLite - WordPress wpdb compatible
 */
class SQLite_DB {
    public $pdo;
    public $last_error = '';
    public $insert_id = 0;
    public $num_rows = 0;
    public $last_query = '';
    public $last_result = null;
    public $prefix = 'wp_';
    
    // WordPress compatibility properties
    public $posts;
    public $users;
    public $options;
    public $postmeta;
    public $usermeta;
    public $terms;
    public $term_taxonomy;
    public $term_relationships;
    public $termmeta;
    public $comments;
    public $commentmeta;
    public $links;
    public $field_types = array();
    public $charset;
    public $collate;
    public $dbname;
    public $ready = false;
    
    public function __construct() {
        global $table_prefix;
        $this->prefix = isset($table_prefix) ? $table_prefix : 'wp_';
        $this->charset = 'utf8';
        $this->collate = '';
        $this->dbname = 'wordpress';
        $this->set_table_names();
        $this->connect();
        $this->ready = true;
    }
    
    private function set_table_names() {
        $this->posts = $this->prefix . 'posts';
        $this->users = $this->prefix . 'users';
        $this->options = $this->prefix . 'options';
        $this->postmeta = $this->prefix . 'postmeta';
        $this->usermeta = $this->prefix . 'usermeta';
        $this->terms = $this->prefix . 'terms';
        $this->term_taxonomy = $this->prefix . 'term_taxonomy';
        $this->term_relationships = $this->prefix . 'term_relationships';
        $this->termmeta = $this->prefix . 'termmeta';
        $this->comments = $this->prefix . 'comments';
        $this->commentmeta = $this->prefix . 'commentmeta';
        $this->links = $this->prefix . 'links';
    }
    
    private function connect() {
        try {
            $this->pdo = new PDO('sqlite:' . DB_FILE);
            $this->pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            $this->pdo->exec('PRAGMA foreign_keys = ON');
        } catch (PDOException $e) {
            $this->last_error = $e->getMessage();
            wp_die('Database connection failed: ' . $this->last_error);
        }
    }
    
    public function query($sql) {
        $this->last_query = $sql;
        $this->last_error = '';
        $this->last_result = null;
        
        try {
            // Convert MySQL syntax to SQLite
            $sql = $this->mysql_to_sqlite($sql);
            
            $stmt = $this->pdo->prepare($sql);
            $result = $stmt->execute();
            
            if ($result) {
                $this->insert_id = $this->pdo->lastInsertId();
                $this->num_rows = $stmt->rowCount();
                $this->last_result = $stmt;
                
                // For SELECT queries, return the number of rows
                if (stripos(trim($sql), 'SELECT') === 0) {
                    return $this->num_rows;
                }
                // For INSERT, UPDATE, DELETE, return true
                return true;
            }
            
            return false;
        } catch (PDOException $e) {
            $this->last_error = $e->getMessage();
            return false;
        }
    }
    
    private function mysql_to_sqlite($sql) {
        // Basic MySQL to SQLite conversion
        $sql = str_replace('`', '"', $sql);
        $sql = preg_replace('/AUTO_INCREMENT/i', 'AUTOINCREMENT', $sql);
        $sql = preg_replace('/INT\(\d+\)/i', 'INTEGER', $sql);
        $sql = preg_replace('/TINYINT\(\d+\)/i', 'INTEGER', $sql);
        $sql = preg_replace('/BIGINT\(\d+\)/i', 'INTEGER', $sql);
        $sql = preg_replace('/VARCHAR\((\d+)\)/i', 'TEXT', $sql);
        $sql = preg_replace('/LONGTEXT/i', 'TEXT', $sql);
        $sql = preg_replace('/DATETIME/i', 'TEXT', $sql);
        $sql = preg_replace('/TIMESTAMP/i', 'TEXT', $sql);
        
        return $sql;
    }
    
    public function get_results($sql, $output = OBJECT) {
        $this->query($sql);
        if (!$this->last_result) return null;
        
        $results = [];
        while ($row = $this->last_result->fetch(PDO::FETCH_ASSOC)) {
            if ($output === OBJECT) {
                $results[] = (object) $row;
            } else {
                $results[] = $row;
            }
        }
        
        return $results;
    }
    
    public function get_row($sql, $output = OBJECT) {
        $this->query($sql);
        if (!$this->last_result) return null;
        
        $row = $this->last_result->fetch(PDO::FETCH_ASSOC);
        if (!$row) return null;
        
        if ($output === OBJECT) {
            return (object) $row;
        }
        
        return $row;
    }
    
    public function get_var($sql) {
        $this->query($sql);
        if (!$this->last_result) return null;
        
        $row = $this->last_result->fetch(PDO::FETCH_NUM);
        return $row ? $row[0] : null;
    }
    
    public function insert_id() {
        return $this->insert_id;
    }
    
    public function last_error() {
        return $this->last_error;
    }
    
    public function num_rows() {
        return $this->num_rows;
    }
    
    // WordPress compatibility methods
    public function prepare($query, ...$args) {
        if (empty($args)) {
            return $query;
        }
        
        $query = str_replace("'%s'", '%s', $query);
        $query = str_replace('"%s"', '%s', $query);
        $query = str_replace('%s', "'%s'", $query);
        $query = str_replace("'%d'", '%d', $query);
        $query = str_replace('"%d"', '%d', $query);
        
        return vsprintf($query, $args);
    }
    
    public function get_charset_collate() {
        return '';
    }
    
    public function esc_like($text) {
        return addcslashes($text, '\\%_');
    }
    
    public function print_error($str = '') {
        if ($this->last_error) {
            echo '<div id="error"><p class="wpdberror"><strong>WordPress database error:</strong> [' . $this->last_error . ']<br /><code>' . $this->last_query . '</code></p></div>';
        }
    }
    
    // WordPress required methods
    public function set_prefix($prefix, $set_table_names = true) {
        if (preg_match('|[^a-z0-9_]|i', $prefix)) {
            // Return false instead of WP_Error since WP_Error might not be available yet
            return false;
        }
        
        $old_prefix = $this->prefix;
        $this->prefix = $prefix;
        
        if ($set_table_names) {
            $this->set_table_names();
        }
        
        return $old_prefix;
    }
    
    public function set_blog_id($blog_id, $network_id = 0) {
        // For single site, just return
        return $this->prefix;
    }
    
    public function get_blog_prefix($blog_id = null) {
        return $this->prefix;
    }
    
    public function tables($scope = 'all', $prefix = true, $blog_id = 0) {
        $tables = array(
            'posts', 'comments', 'links', 'options', 'postmeta',
            'terms', 'term_taxonomy', 'term_relationships', 'termmeta',
            'commentmeta', 'users', 'usermeta'
        );
        
        if ($prefix) {
            $prefixed_tables = array();
            foreach ($tables as $table) {
                $prefixed_tables[] = $this->prefix . $table;
            }
            return $prefixed_tables;
        }
        
        return $tables;
    }
    
    public function get_table_charset($table) {
        return 'utf8';
    }
    
    public function get_col_charset($table, $column) {
        return 'utf8';
    }
    
    public function check_connection($allow_bail = true) {
        return true;
    }
    
    public function db_version() {
        return $this->get_var('SELECT sqlite_version()');
    }
    
    public function flush() {
        $this->last_result = null;
        $this->last_query = null;
        $this->last_error = '';
    }
    
    public function close() {
        $this->pdo = null;
        return true;
    }
    
    public function has_cap($db_cap) {
        switch (strtolower($db_cap)) {
            case 'collation':
            case 'group_concat':
            case 'subqueries':
                return true;
            default:
                return false;
        }
    }
    
    public function get_caller() {
        if (function_exists('wp_debug_backtrace_summary')) {
            return wp_debug_backtrace_summary(__CLASS__);
        }
        return '';
    }
    
    public function init_charset() {
        // SQLite uses UTF-8 by default
        return true;
    }
    
    public function set_charset($dbh, $charset = null, $collate = null) {
        // SQLite uses UTF-8 by default
        return true;
    }
    
    // WordPress data manipulation methods
    public function insert($table, $data, $format = null) {
        return $this->_insert_replace_helper($table, $data, $format, 'INSERT');
    }
    
    public function replace($table, $data, $format = null) {
        return $this->_insert_replace_helper($table, $data, $format, 'REPLACE');
    }
    
    public function update($table, $data, $where, $format = null, $where_format = null) {
        if (!is_array($data) || !is_array($where)) {
            return false;
        }
        
        $formats = $format = (array) $format;
        $bits = $wheres = array();
        foreach ((array) array_keys($data) as $field) {
            if (!empty($formats)) {
                $form = ($form = array_shift($formats)) ? $form : $formats[0];
            } elseif (isset($this->field_types[$field])) {
                $form = $this->field_types[$field];
            } else {
                $form = '%s';
            }
            $bits[] = "`$field` = {$form}";
        }
        
        $where_formats = $where_format = (array) $where_format;
        foreach ((array) array_keys($where) as $field) {
            if (!empty($where_formats)) {
                $form = ($form = array_shift($where_formats)) ? $form : $where_formats[0];
            } elseif (isset($this->field_types[$field])) {
                $form = $this->field_types[$field];
            } else {
                $form = '%s';
            }
            $wheres[] = "`$field` = {$form}";
        }
        
        $sql = "UPDATE `$table` SET " . implode(', ', $bits) . ' WHERE ' . implode(' AND ', $wheres);
        return $this->query($this->prepare($sql, array_merge(array_values($data), array_values($where))));
    }
    
    public function delete($table, $where, $where_format = null) {
        if (!is_array($where)) {
            return false;
        }
        
        $where_formats = $where_format = (array) $where_format;
        $wheres = array();
        foreach (array_keys($where) as $field) {
            if (!empty($where_formats)) {
                $form = ($form = array_shift($where_formats)) ? $form : $where_formats[0];
            } elseif (isset($this->field_types[$field])) {
                $form = $this->field_types[$field];
            } else {
                $form = '%s';
            }
            $wheres[] = "`$field` = {$form}";
        }
        
        $sql = "DELETE FROM `$table` WHERE " . implode(' AND ', $wheres);
        return $this->query($this->prepare($sql, array_values($where)));
    }
    
    private function _insert_replace_helper($table, $data, $format = null, $type = 'INSERT') {
        if (!in_array(strtoupper($type), array('INSERT', 'REPLACE'))) {
            return false;
        }
        
        if (!is_array($data)) {
            return false;
        }
        
        $formats = $format = (array) $format;
        $fields = array_keys($data);
        $formatted_fields = array();
        
        foreach ($fields as $field) {
            if (!empty($formats)) {
                $form = ($form = array_shift($formats)) ? $form : $formats[0];
            } elseif (isset($this->field_types[$field])) {
                $form = $this->field_types[$field];
            } else {
                $form = '%s';
            }
            $formatted_fields[] = $form;
        }
        
        $sql = "$type INTO `$table` (`" . implode('`,`', $fields) . '`) VALUES (' . implode(',', $formatted_fields) . ')';
        return $this->query($this->prepare($sql, array_values($data)));
    }
}

// Initialize SQLite database connection
$wpdb = new SQLite_DB();

// WordPress database compatibility
if (!isset($GLOBALS['wpdb'])) {
    $GLOBALS['wpdb'] = $wpdb;
}