<?php
// File: includes/class-database.php

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

class PC_HFAP_Database {
    
    private static $table_name = 'pc_hfap_snippets';
    
    public static function create_tables() {
        global $wpdb;
        
        $table_name = $wpdb->prefix . self::$table_name;
        $charset_collate = $wpdb->get_charset_collate();
        
        $sql = "CREATE TABLE IF NOT EXISTS $table_name (
            id mediumint(9) NOT NULL AUTO_INCREMENT,
            title varchar(255) NOT NULL,
            location enum('header','footer','body') NOT NULL DEFAULT 'header',
            code longtext NOT NULL,
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id)
        ) $charset_collate;";
        
        require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
        dbDelta($sql);
    }
    
    public static function get_table_name() {
        global $wpdb;
        return $wpdb->prefix . self::$table_name;
    }

    private static function ensure_table_exists() {
        global $wpdb;
        
        $table_name = self::get_table_name();
        
        // Simple check using WordPress's get_var
        $table_exists = $wpdb->get_var($wpdb->prepare(
            "SHOW TABLES LIKE %s",
            $table_name
        ));
        
        if ($table_name !== $table_exists) {
            // Table doesn't exist, create it
            self::create_tables();
            
            // Flush query cache
            $wpdb->flush();
        }
    }
    
    public static function get_all_snippets() {
        global $wpdb;
        $table_name = self::get_table_name();

        self::ensure_table_exists();
        
        return $wpdb->get_results(
            "SELECT * FROM $table_name ORDER BY location, title ASC",
            ARRAY_A
        );
    }
    
    public static function get_snippet($id) {
        global $wpdb;
        $table_name = self::get_table_name();

        self::ensure_table_exists();
        
        return $wpdb->get_row(
            $wpdb->prepare("SELECT * FROM $table_name WHERE id = %d", $id),
            ARRAY_A
        );
    }
    
    public static function insert_snippet($data) {
        global $wpdb;
        
        $table_name = self::get_table_name();
        
        self::ensure_table_exists();
        
        // Clean data
        $title = isset($data['title']) ? sanitize_text_field($data['title']) : '';
        $location = isset($data['location']) && in_array($data['location'], array('header', 'footer', 'body')) 
            ? $data['location'] 
            : 'header';
        $code = isset($data['code']) ? $data['code'] : '';
        
        // Handle slashes
        $code = wp_unslash($code);
        
        // Perform the insert
        $result = $wpdb->insert(
            $table_name,
            array(
                'title' => $title,
                'location' => $location,
                'code' => $code
            ),
            array('%s', '%s', '%s')
        );
        
        if ($result === false) {
            return false;
        }
        
        return $wpdb->insert_id;
    }
    
    public static function update_snippet($id, $data) {
        global $wpdb;
        $table_name = self::get_table_name();
        
        error_log('PC HFAP: update_snippet called for ID: ' . $id);

        self::ensure_table_exists();
        
        // Clean data properly
        $title = isset($data['title']) ? sanitize_text_field($data['title']) : '';
        $location = isset($data['location']) && in_array($data['location'], array('header', 'footer', 'body')) 
            ? $data['location'] 
            : 'header';
        $code = isset($data['code']) ? $data['code'] : '';
        
        // Handle slashes
        $code = wp_unslash($code);
        
        $result = $wpdb->update(
            $table_name,
            array(
                'title' => $title,
                'location' => $location,
                'code' => $code
            ),
            array('id' => $id),
            array('%s', '%s', '%s'),
            array('%d')
        );
        
        error_log('PC HFAP: Update result: ' . ($result === false ? 'false' : $result));
        error_log('PC HFAP: Last error: ' . $wpdb->last_error);
        
        return $result;
    }
    
    public static function delete_snippet($id) {
        global $wpdb;
        $table_name = self::get_table_name();

        self::ensure_table_exists();
        
        return $wpdb->delete(
            $table_name,
            array('id' => $id),
            array('%d')
        );
    }
    
    public static function get_header_snippets() {
        global $wpdb;
        $table_name = self::get_table_name();

        self::ensure_table_exists();
        
        return $wpdb->get_results(
            $wpdb->prepare("SELECT * FROM $table_name WHERE location = %s ORDER BY title ASC", 'header'),
            ARRAY_A
        );
    }
    
    public static function get_footer_snippets() {
        global $wpdb;
        $table_name = self::get_table_name();

        self::ensure_table_exists();
        
        return $wpdb->get_results(
            $wpdb->prepare("SELECT * FROM $table_name WHERE location = %s ORDER BY title ASC", 'footer'),
            ARRAY_A
        );
    }

    public static function get_body_snippets() {
        global $wpdb;
        $table_name = self::get_table_name();

        self::ensure_table_exists();

        return $wpdb->get_results(
            $wpdb->prepare("SELECT * FROM $table_name WHERE location = %s ORDER BY title ASC", 'body'),
            ARRAY_A
        );
    }
}