<?php

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

class AIGH_Database {

    private $plans_table;
    private $items_table;

    public function __construct() {
        global $wpdb;
        $this->plans_table = $wpdb->prefix . 'aigh_plans';
        $this->items_table = $wpdb->prefix . 'aigh_plan_items';
    }
    
    public function create_tables() {
        global $wpdb;
        
        $charset_collate = $wpdb->get_charset_collate();
        
        $plans_sql = "CREATE TABLE {$this->plans_table} (
            id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            topic varchar(255) NOT NULL,
            description text,
            default_workflow_id bigint(20) UNSIGNED DEFAULT 0,
            default_tone varchar(50) DEFAULT 'professional',
            default_length varchar(20) DEFAULT 'medium',
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY  (id)
        ) $charset_collate;";
        
        $items_sql = "CREATE TABLE {$this->items_table} (
            id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            plan_id bigint(20) UNSIGNED NOT NULL,
            title varchar(255) NOT NULL,
            outline text,
            category varchar(100),
            keywords varchar(255),
            status varchar(20) DEFAULT 'planned',
            scheduled_date datetime,
            post_id bigint(20) UNSIGNED,
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY  (id),
            KEY plan_id (plan_id),
            KEY status (status),
            KEY post_id (post_id)
        ) $charset_collate;";
        
        require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
        dbDelta($plans_sql);
        dbDelta($items_sql);
        
        $pipeline_db = new AIGH_Pipeline_Database();
        $pipeline_db->create_tables();
    }
    
    public function create_plan($topic, $description = '') {
        global $wpdb;

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- Custom plugin table
        $result = $wpdb->insert(
            $this->plans_table,
            array(
                'topic' => $topic,
                'description' => $description
            ),
            array('%s', '%s')
        );
        
        if ($result === false) {
            return false;
        }
        
        return $wpdb->insert_id;
    }
    
    public function add_plan_item($plan_id, $data) {
        global $wpdb;
        
        $defaults = array(
            'plan_id' => $plan_id,
            'title' => '',
            'outline' => '',
            'category' => '',
            'keywords' => '',
            'status' => 'planned',
            'scheduled_date' => null
        );
        
        $data = wp_parse_args($data, $defaults);

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- Custom plugin table
        $result = $wpdb->insert(
            $this->items_table,
            $data,
            array('%d', '%s', '%s', '%s', '%s', '%s', '%s')
        );
        
        if ($result === false) {
            return false;
        }
        
        return $wpdb->insert_id;
    }
    
    public function get_all_plans() {
        global $wpdb;

        $plans_table = $this->plans_table;
        $items_table = $this->items_table;

        // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table names are internally defined
        $query = "
            SELECT p.*,
                   COUNT(i.id) as total_items,
                   SUM(CASE WHEN i.status = 'published' THEN 1 ELSE 0 END) as published_items
            FROM {$plans_table} p
            LEFT JOIN {$items_table} i ON p.id = i.plan_id
            GROUP BY p.id
            ORDER BY p.created_at DESC
        ";
        // phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared

        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom plugin table, query has no user input
        return $wpdb->get_results($query);
    }
    
    public function get_plan($plan_id) {
        global $wpdb;

        $table = $this->plans_table;
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom plugin table
        return $wpdb->get_row(
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name is internally defined
            $wpdb->prepare( "SELECT * FROM {$table} WHERE id = %d", $plan_id )
        );
    }

    public function get_plan_items($plan_id, $status = null) {
        global $wpdb;

        $table = $this->items_table;

        if ($status) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom plugin table
            return $wpdb->get_results(
                // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name is internally defined
                $wpdb->prepare( "SELECT * FROM {$table} WHERE plan_id = %d AND status = %s ORDER BY scheduled_date ASC, created_at ASC", $plan_id, $status )
            );
        }

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom plugin table
        return $wpdb->get_results(
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name is internally defined
            $wpdb->prepare( "SELECT * FROM {$table} WHERE plan_id = %d ORDER BY scheduled_date ASC, created_at ASC", $plan_id )
        );
    }
    
    public function get_plan_item($item_id) {
        global $wpdb;

        $table = $this->items_table;
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom plugin table
        return $wpdb->get_row(
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name is internally defined
            $wpdb->prepare( "SELECT * FROM {$table} WHERE id = %d", $item_id )
        );
    }

    public function update_plan_item($item_id, $data) {
        global $wpdb;
        
        $formats = array();
        foreach ($data as $key => $value) {
            if (in_array($key, array('plan_id', 'post_id'))) {
                $formats[] = '%d';
            } else {
                $formats[] = '%s';
            }
        }
        
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom plugin table
        return $wpdb->update(
            $this->items_table,
            $data,
            array('id' => $item_id),
            $formats,
            array('%d')
        );
    }

    public function delete_plan_item($item_id) {
        global $wpdb;

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom plugin table
        return $wpdb->delete(
            $this->items_table,
            array('id' => $item_id),
            array('%d')
        );
    }
    
    public function delete_plan($plan_id) {
        global $wpdb;

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom plugin table
        $wpdb->delete($this->items_table, array('plan_id' => $plan_id), array('%d'));

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom plugin table
        return $wpdb->delete($this->plans_table, array('id' => $plan_id), array('%d'));
    }
    
    public function update_plan_settings($plan_id, $settings) {
        global $wpdb;
        
        $data = array();
        $format = array();
        
        if (isset($settings['default_workflow_id'])) {
            $data['default_workflow_id'] = absint($settings['default_workflow_id']);
            $format[] = '%d';
        }
        
        if (isset($settings['default_tone'])) {
            $data['default_tone'] = sanitize_text_field($settings['default_tone']);
            $format[] = '%s';
        }
        
        if (isset($settings['default_length'])) {
            $data['default_length'] = sanitize_text_field($settings['default_length']);
            $format[] = '%s';
        }
        
        if (empty($data)) {
            return false;
        }
        
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom plugin table
        return $wpdb->update(
            $this->plans_table,
            $data,
            array('id' => $plan_id),
            $format,
            array('%d')
        );
    }

    public function get_statistics() {
        global $wpdb;

        $plans_table = $this->plans_table;
        $items_table = $this->items_table;

        $stats = new stdClass();

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom plugin table
        $stats->total_plans = $wpdb->get_var( "SELECT COUNT(*) FROM {$plans_table}" ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name is internally defined

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom plugin table
        $stats->total_items = $wpdb->get_var( "SELECT COUNT(*) FROM {$items_table}" ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name is internally defined

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom plugin table
        $stats->planned_items = $wpdb->get_var( "SELECT COUNT(*) FROM {$items_table} WHERE status = 'planned'" ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name is internally defined

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom plugin table
        $stats->published_items = $wpdb->get_var( "SELECT COUNT(*) FROM {$items_table} WHERE status = 'published'" ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name is internally defined

        // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table names are internally defined
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom plugin table
        $stats->recent_plans = $wpdb->get_results("
            SELECT p.*, COUNT(i.id) as item_count
            FROM {$plans_table} p
            LEFT JOIN {$items_table} i ON p.id = i.plan_id
            GROUP BY p.id
            ORDER BY p.created_at DESC
            LIMIT 5
        ");
        // phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared

        return $stats;
    }
}