<?php

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

/**
 * Pipeline Database Manager
 * 
 * Handles database schema for pipeline-related tables.
 * Extends existing database functionality.
 */
class AIGH_Pipeline_Database {
    
    private $pipelines_table;
    private $executions_table;
    private $execution_steps_table;
    
    public function __construct() {
        global $wpdb;
        $this->pipelines_table = $wpdb->prefix . 'aigh_pipelines';
        $this->executions_table = $wpdb->prefix . 'aigh_executions';
        $this->execution_steps_table = $wpdb->prefix . 'aigh_execution_steps';
    }
    
    /**
     * Create pipeline-related tables
     */
    public function create_tables() {
        global $wpdb;
        
        $charset_collate = $wpdb->get_charset_collate();
        
        // Pipelines table - stores workflow definitions
        $pipelines_sql = "CREATE TABLE {$this->pipelines_table} (
            id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            name varchar(255) NOT NULL,
            description text,
            steps_config longtext NOT NULL,
            is_template tinyint(1) DEFAULT 0,
            is_active tinyint(1) DEFAULT 1,
            created_by bigint(20) UNSIGNED,
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY  (id),
            KEY is_template (is_template),
            KEY is_active (is_active),
            KEY created_by (created_by)
        ) $charset_collate;";
        
        // Executions table - tracks pipeline runs
        $executions_sql = "CREATE TABLE {$this->executions_table} (
            id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            pipeline_id bigint(20) UNSIGNED,
            status varchar(20) DEFAULT 'pending',
            context_data longtext,
            error_message text,
            error_count int DEFAULT 0,
            started_at datetime,
            completed_at datetime,
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY  (id),
            KEY pipeline_id (pipeline_id),
            KEY status (status),
            KEY started_at (started_at)
        ) $charset_collate;";
        
        // Execution steps table - logs individual step execution
        $execution_steps_sql = "CREATE TABLE {$this->execution_steps_table} (
            id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            execution_id bigint(20) UNSIGNED NOT NULL,
            step_number int NOT NULL,
            step_type varchar(100) NOT NULL,
            step_name varchar(255) NOT NULL,
            step_config text,
            status varchar(20) DEFAULT 'pending',
            output_data longtext,
            error_message text,
            duration float,
            started_at datetime,
            completed_at datetime,
            PRIMARY KEY  (id),
            KEY execution_id (execution_id),
            KEY status (status),
            KEY step_type (step_type)
        ) $charset_collate;";
        
        require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
        dbDelta($pipelines_sql);
        dbDelta($executions_sql);
        dbDelta($execution_steps_sql);
    }
    
    /**
     * Save a pipeline
     *
     * @param string $name
     * @param array $steps_config
     * @param string $description
     * @param bool $is_template
     * @return int|false Pipeline ID or false on failure
     */
    public function save_pipeline($name, $steps_config, $description = '', $is_template = false) {
        global $wpdb;

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- Custom plugin table
        $result = $wpdb->insert(
            $this->pipelines_table,
            array(
                'name' => $name,
                'description' => $description,
                'steps_config' => json_encode($steps_config),
                'is_template' => $is_template ? 1 : 0,
                'created_by' => get_current_user_id()
            ),
            array('%s', '%s', '%s', '%d', '%d')
        );
        
        if ($result === false) {
            return false;
        }
        
        return $wpdb->insert_id;
    }
    
    /**
     * Update a pipeline
     *
     * @param int $pipeline_id
     * @param array $data
     * @return bool
     */
    public function update_pipeline($pipeline_id, $data) {
        global $wpdb;
        
        $update_data = array();
        $formats = array();
        
        if (isset($data['name'])) {
            $update_data['name'] = $data['name'];
            $formats[] = '%s';
        }
        if (isset($data['description'])) {
            $update_data['description'] = $data['description'];
            $formats[] = '%s';
        }
        if (isset($data['steps_config'])) {
            $update_data['steps_config'] = json_encode($data['steps_config']);
            $formats[] = '%s';
        }
        if (isset($data['is_active'])) {
            $update_data['is_active'] = $data['is_active'] ? 1 : 0;
            $formats[] = '%d';
        }
        
        if (empty($update_data)) {
            return false;
        }

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom plugin table
        return $wpdb->update(
            $this->pipelines_table,
            $update_data,
            array('id' => $pipeline_id),
            $formats,
            array('%d')
        ) !== false;
    }

    /**
     * Get a pipeline by ID
     *
     * @param int $pipeline_id
     * @return object|null
     */
    public function get_pipeline($pipeline_id) {
        global $wpdb;

        $table = $this->pipelines_table;
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom plugin table
        $pipeline = $wpdb->get_row(
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name is internally defined
            $wpdb->prepare( "SELECT * FROM {$table} WHERE id = %d", $pipeline_id )
        );
        
        if ($pipeline && !empty($pipeline->steps_config)) {
            $pipeline->steps_config = json_decode($pipeline->steps_config, true);
        }
        
        return $pipeline;
    }
    
    /**
     * Get all pipelines
     *
     * @param bool $templates_only Only return templates
     * @param bool $active_only Only return active pipelines
     * @return array
     */
    public function get_all_pipelines($templates_only = false, $active_only = true) {
        global $wpdb;

        $table = $this->pipelines_table;

        $where = array();
        if ($templates_only) {
            $where[] = 'is_template = 1';
        }
        if ($active_only) {
            $where[] = 'is_active = 1';
        }

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

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom plugin table
        $pipelines = $wpdb->get_results(
            "SELECT * FROM {$table} {$where_clause} ORDER BY created_at DESC" // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name and WHERE clause are internally defined
        );
        
        foreach ($pipelines as $pipeline) {
            if (!empty($pipeline->steps_config)) {
                $pipeline->steps_config = json_decode($pipeline->steps_config, true);
            }
        }
        
        return $pipelines;
    }
    
    /**
     * Delete a pipeline
     *
     * @param int $pipeline_id
     * @return bool
     */
    public function delete_pipeline($pipeline_id) {
        global $wpdb;

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom plugin table
        return $wpdb->delete(
            $this->pipelines_table,
            array('id' => $pipeline_id),
            array('%d')
        ) !== false;
    }
    
    /**
     * Get execution history for a pipeline
     *
     * @param int $pipeline_id
     * @param int $limit
     * @return array
     */
    public function get_pipeline_executions($pipeline_id, $limit = 10) {
        global $wpdb;

        $table = $this->executions_table;
        // 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 pipeline_id = %d ORDER BY started_at DESC LIMIT %d", $pipeline_id, $limit )
        );
    }

    /**
     * Get execution by ID
     *
     * @param int $execution_id
     * @return object|null
     */
    public function get_execution($execution_id) {
        global $wpdb;

        $table = $this->executions_table;
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom plugin table
        $execution = $wpdb->get_row(
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name is internally defined
            $wpdb->prepare( "SELECT * FROM {$table} WHERE id = %d", $execution_id )
        );
        
        if ($execution && !empty($execution->context_data)) {
            $execution->context_data = json_decode($execution->context_data, true);
        }
        
        return $execution;
    }
    
    /**
     * Get steps for an execution
     *
     * @param int $execution_id
     * @return array
     */
    public function get_execution_steps($execution_id) {
        global $wpdb;

        $table = $this->execution_steps_table;
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom plugin table
        $steps = $wpdb->get_results(
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name is internally defined
            $wpdb->prepare( "SELECT * FROM {$table} WHERE execution_id = %d ORDER BY step_number ASC", $execution_id )
        );
        
        foreach ($steps as $step) {
            if (!empty($step->step_config)) {
                $step->step_config = json_decode($step->step_config, true);
            }
            if (!empty($step->output_data)) {
                $step->output_data = json_decode($step->output_data, true);
            }
        }
        
        return $steps;
    }
    
    /**
     * Get execution statistics
     *
     * @return object
     */
    public function get_execution_stats() {
        global $wpdb;

        $table = $this->executions_table;

        $stats = new stdClass();

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom plugin table
        $stats->total_executions = $wpdb->get_var( "SELECT COUNT(*) FROM {$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->successful_executions = $wpdb->get_var( "SELECT COUNT(*) FROM {$table} WHERE status = 'completed'" ); // 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->failed_executions = $wpdb->get_var( "SELECT COUNT(*) FROM {$table} WHERE status = 'failed'" ); // 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->running_executions = $wpdb->get_var( "SELECT COUNT(*) FROM {$table} WHERE status = 'running'" ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name is internally defined

        // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name is internally defined
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom plugin table
        $stats->avg_duration = $wpdb->get_var(
            "SELECT AVG(TIMESTAMPDIFF(SECOND, started_at, completed_at))
            FROM {$table}
            WHERE status = 'completed' AND completed_at IS NOT NULL"
        );
        // phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared

        return $stats;
    }
    
    /**
     * Clean up old executions
     *
     * @param int $days_old Delete executions older than this many days
     * @return int Number of executions deleted
     */
    public function cleanup_old_executions($days_old = 30) {
        global $wpdb;

        $executions_table = $this->executions_table;
        $date_threshold   = gmdate( 'Y-m-d H:i:s', strtotime( "-{$days_old} days" ) );

        // Get execution IDs to delete
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom plugin table
        $execution_ids = $wpdb->get_col(
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name is internally defined
            $wpdb->prepare( "SELECT id FROM {$executions_table} WHERE created_at < %s AND status IN ('completed', 'failed')", $date_threshold )
        );

        if ( empty( $execution_ids ) ) {
            return 0;
        }

        $steps_table     = $this->execution_steps_table;
        $ids_placeholder = implode( ',', array_fill( 0, count( $execution_ids ), '%d' ) );

        // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare -- Table names are internally defined, dynamic IN clause
        // Delete execution steps
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom plugin table
        $wpdb->query( $wpdb->prepare( "DELETE FROM {$steps_table} WHERE execution_id IN ($ids_placeholder)", ...$execution_ids ) );

        // Delete executions
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom plugin table
        $deleted = $wpdb->query( $wpdb->prepare( "DELETE FROM {$executions_table} WHERE id IN ($ids_placeholder)", ...$execution_ids ) );
        // phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare

        return $deleted;
    }
}