<?php
/**
 * Database operations for the subscription system.
 *
 * @since      1.0.0
 * @package    Subscription_System
 */

class Subs_Sys_DB {

    /**
     * The table name for subscriptions.
     *
     * @since    1.0.0
     * @access   private
     * @var      string    $table_name    The name of the subscriptions table.
     */
    private $table_name;

    /**
     * Initialize the class and set its properties.
     *
     * @since    1.0.0
     */
    public function __construct() {
        global $wpdb;
        $this->table_name = $wpdb->prefix . 'subscriptions';
    }

    /**
     * Create the database tables needed for the plugin.
     *
     * @since    1.0.0
     */
    public function create_tables() {
        $this->create_subscriptions_table();
        $this->create_plan_categories_table();
        $this->create_subscription_plans_table();
    }

    /**
     * Create the subscriptions table with the correct structure
     */
    public function create_subscriptions_table() {
        global $wpdb;
        $table_name = $wpdb->prefix . 'subscriptions';
        
        // phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log -- Debug logging for table creation
        error_log('Creating subscriptions table with correct structure');
        
        // Drop the table if it exists to ensure clean structure
        $wpdb->query("DROP TABLE IF EXISTS $table_name");
        
        $charset_collate = $wpdb->get_charset_collate();
        
        $sql = "CREATE TABLE $table_name (
            id mediumint(9) NOT NULL AUTO_INCREMENT,
            user_id bigint(20) NOT NULL,
            plan_id mediumint(9) NOT NULL,
            customer_email varchar(100) NOT NULL,
            customer_name varchar(100) NOT NULL,
            payment_id varchar(100) NOT NULL,
            amount decimal(10,2) NOT NULL,
            status varchar(20) NOT NULL,
            start_date datetime NOT NULL,
            end_date datetime NOT NULL,
            created_at datetime NOT NULL,
            updated_at datetime NOT NULL,
            PRIMARY KEY  (id),
            KEY user_id (user_id),
            KEY payment_id (payment_id)
        ) $charset_collate;";
        
        require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
        dbDelta($sql);
        
        // Verify table was created
        $table_exists = $wpdb->get_var("SHOW TABLES LIKE '$table_name'");
        if ($table_exists != $table_name) {
            // phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log -- Debug logging for table creation failure
            error_log('Failed to create subscriptions table');
            return false;
        }
        
        // phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log -- Debug logging for successful table creation
        error_log('Subscriptions table created successfully');
        return true;
    }

    /**
     * Create the plan categories table
     */
    public function create_plan_categories_table() {
        global $wpdb;
        $table_name = $wpdb->prefix . 'subscription_plan_categories';
        $charset_collate = $wpdb->get_charset_collate();

        $sql = "CREATE TABLE $table_name (
            id mediumint(9) NOT NULL AUTO_INCREMENT,
            name varchar(100) NOT NULL,
            category_name varchar(100) NOT NULL,
            description text,
            category_description text,
            created_at datetime DEFAULT CURRENT_TIMESTAMP NOT NULL,
            updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL,
            PRIMARY KEY  (id)
        ) $charset_collate;";

        require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
        dbDelta($sql);
    }

    /**
     * Create the subscription plans table
     */
    public function create_subscription_plans_table() {
        global $wpdb;
        $table_name = $wpdb->prefix . 'subscription_plans';
        $charset_collate = $wpdb->get_charset_collate();
        
        $sql = "CREATE TABLE $table_name (
            id mediumint(9) NOT NULL AUTO_INCREMENT,
            category_id mediumint(9) DEFAULT NULL,
            plan_name varchar(100) NOT NULL,
            description text NOT NULL,
            price decimal(10,2) NOT NULL,
            duration int(11) NOT NULL,
            duration_unit varchar(20) NOT NULL,
            status varchar(20) NOT NULL DEFAULT 'active',
            stripe_product_id varchar(100) DEFAULT NULL,
            stripe_price_id varchar(100) DEFAULT NULL,
            features text,
            created_at datetime DEFAULT CURRENT_TIMESTAMP NOT NULL,
            updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL,
            PRIMARY KEY  (id)
        ) $charset_collate;";
        
        require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
        dbDelta($sql);
    }

    /**
     * Add a new subscription.
     *
     * @since    1.0.0
     * @param    array    $subscription_data    The subscription data.
     * @return   int|false                      The ID of the inserted subscription or false on failure.
     */
    public function add_subscription($subscription_data) {
        global $wpdb;
        
        // Sanitize data
        $data = array(
            'user_id'    => absint($subscription_data['user_id']),
            'email'      => sanitize_email($subscription_data['email']),
            'plan_id'    => sanitize_text_field($subscription_data['plan_id']),
            'status'     => sanitize_text_field($subscription_data['status']),
            'start_date' => sanitize_text_field($subscription_data['start_date']),
            'end_date'   => isset($subscription_data['end_date']) ? sanitize_text_field($subscription_data['end_date']) : null,
            'created_at' => current_time('mysql'),
            'updated_at' => current_time('mysql')
        );
        
        $result = $wpdb->insert($this->table_name, $data);
        
        if ($result) {
            return $wpdb->insert_id;
        }
        
        return false;
    }

    /**
     * Update a subscription.
     *
     * @since    1.0.0
     * @param    int      $subscription_id      The subscription ID.
     * @param    array    $subscription_data    The subscription data.
     * @return   bool                           True on success, false on failure.
     */
    public function update_subscription($subscription_id, $subscription_data) {
        global $wpdb;
        
        // Sanitize data
        $data = array(
            'updated_at' => current_time('mysql')
        );
        
        // Handle standard subscription fields
        if (isset($subscription_data['status'])) {
            $data['status'] = sanitize_text_field($subscription_data['status']);
        }
        
        if (isset($subscription_data['end_date'])) {
            $data['end_date'] = sanitize_text_field($subscription_data['end_date']);
        }
        
        if (isset($subscription_data['plan_id'])) {
            $data['plan_id'] = sanitize_text_field($subscription_data['plan_id']);
        }

        // Handle Stripe-specific fields
        if (isset($subscription_data['stripe_customer_id'])) {
            $data['stripe_customer_id'] = sanitize_text_field($subscription_data['stripe_customer_id']);
        }

        if (isset($subscription_data['stripe_subscription_id'])) {
            $data['stripe_subscription_id'] = sanitize_text_field($subscription_data['stripe_subscription_id']);
        }
        
        $result = $wpdb->update(
            $this->table_name,
            $data,
            array('id' => absint($subscription_id))
        );
        
        return $result !== false;
    }

    /**
     * Get a subscription by ID.
     *
     * @since    1.0.0
     * @param    int      $subscription_id    The subscription ID.
     * @return   object|null                  The subscription object or null if not found.
     */
    public function get_subscription($subscription_id) {
        global $wpdb;
        
        $sql = $wpdb->prepare(
            "SELECT * FROM $this->table_name WHERE id = %d",
            absint($subscription_id)
        );
        
        return $wpdb->get_row($sql);
    }

    /**
     * Get subscriptions by user ID.
     *
     * @since    1.0.0
     * @param    int      $user_id    The user ID.
     * @return   array                The subscriptions.
     */
    public function get_subscriptions_by_user($user_id) {
        global $wpdb;
        
        $sql = $wpdb->prepare(
            "SELECT * FROM $this->table_name WHERE user_id = %d ORDER BY created_at DESC",
            absint($user_id)
        );
        
        return $wpdb->get_results($sql);
    }

    /**
     * Get subscriptions by email.
     *
     * @since    1.0.0
     * @param    string   $email    The email address.
     * @return   array              The subscriptions.
     */
    public function get_subscriptions_by_email($email) {
        global $wpdb;
        
        $sql = $wpdb->prepare(
            "SELECT * FROM $this->table_name WHERE email = %s ORDER BY created_at DESC",
            sanitize_email($email)
        );
        
        return $wpdb->get_results($sql);
    }

    /**
     * Get all subscriptions with optional filtering.
     *
     * @since    1.0.0
     * @param    array    $args    The query arguments.
     * @return   array             The subscriptions.
     */
    public function get_all_subscriptions($args = array()) {
        global $wpdb;
        
        $defaults = array(
            'status'    => '',
            'plan_id'   => '',
            'orderby'   => 'created_at',
            'order'     => 'DESC',
            'limit'     => 20,
            'offset'    => 0
        );
        
        $args = wp_parse_args($args, $defaults);
        
        $where = "WHERE 1=1";
        
        if (!empty($args['status'])) {
            $where .= $wpdb->prepare(" AND status = %s", sanitize_text_field($args['status']));
        }
        
        if (!empty($args['plan_id'])) {
            $where .= $wpdb->prepare(" AND plan_id = %s", sanitize_text_field($args['plan_id']));
        }
        
        $orderby = sanitize_sql_orderby($args['orderby'] . ' ' . $args['order']);
        $limit = absint($args['limit']);
        $offset = absint($args['offset']);
        
        $sql = "SELECT * FROM $this->table_name $where ORDER BY $orderby LIMIT $limit OFFSET $offset";
        
        return $wpdb->get_results($sql);
    }

    /**
     * Count subscriptions with optional filtering.
     *
     * @since    1.0.0
     * @param    array    $args    The query arguments.
     * @return   int               The count.
     */
    public function count_subscriptions($args = array()) {
        global $wpdb;
        
        $defaults = array(
            'status'  => '',
            'plan_id' => ''
        );
        
        $args = wp_parse_args($args, $defaults);
        
        $where = "WHERE 1=1";
        
        if (!empty($args['status'])) {
            $where .= $wpdb->prepare(" AND status = %s", sanitize_text_field($args['status']));
        }
        
        if (!empty($args['plan_id'])) {
            $where .= $wpdb->prepare(" AND plan_id = %s", sanitize_text_field($args['plan_id']));
        }
        
        $sql = "SELECT COUNT(*) FROM $this->table_name $where";
        
        return (int) $wpdb->get_var($sql);
    }

    /**
     * Delete a subscription.
     *
     * @since    1.0.0
     * @param    int      $subscription_id    The subscription ID.
     * @return   bool                         True on success, false on failure.
     */
    public function delete_subscription($subscription_id) {
        global $wpdb;
        
        $result = $wpdb->delete(
            $this->table_name,
            array('id' => absint($subscription_id))
        );
        
        return $result !== false;
    }

    /**
     * Get all plan categories
     * 
     * @return array Array of plan categories
     */
    public function get_plan_categories() {
        global $wpdb;
        $table_name = $wpdb->prefix . 'subscription_plan_categories';
        
        return $wpdb->get_results("SELECT * FROM $table_name ORDER BY id DESC");
    }

    /**
     * Get a specific plan category
     * 
     * @param int $id Category ID
     * @return object|null Category object or null if not found
     */
    public function get_plan_category($id) {
        global $wpdb;
        $table_name = $wpdb->prefix . 'subscription_plan_categories';
        
        return $wpdb->get_row(
            $wpdb->prepare("SELECT * FROM $table_name WHERE id = %d", $id)
        );
    }

    /**
     * Add a new plan category
     * 
     * @param string $category_name Category name
     * @param string $category_description Category description
     * @return int|false The ID of the inserted category or false on failure
     */
    public function add_plan_category($category_name, $category_description = '') {
        global $wpdb;
        $table_name = $wpdb->prefix . 'subscription_plan_categories';
        
        // Check if table exists
        $table_exists = $wpdb->get_var("SHOW TABLES LIKE '$table_name'") === $table_name;
        
        if (!$table_exists) {
            // Table doesn't exist, create it
            $this->create_plan_categories_table();
            
            // Check again if table was created
            $table_exists = $wpdb->get_var("SHOW TABLES LIKE '$table_name'") === $table_name;
            
            if (!$table_exists) {
                // Still doesn't exist, log error
                error_log("Failed to create table: $table_name");
                return false;
            }
        }
        
        $result = $wpdb->insert(
            $table_name,
            array(
                'category_name' => $category_name,
                'category_description' => $category_description
            )
        );
        
        if ($result === false) {
            // Log the SQL error
            error_log("Database error: " . $wpdb->last_error);
            return false;
        }
        
        return $wpdb->insert_id;
    }

    /**
     * Update a plan category
     * 
     * @param int $id Category ID
     * @param string $category_name New category name
     * @param string $category_description New category description
     * @return bool True on success, false on failure
     */
    public function update_plan_category($id, $category_name, $category_description = '') {
        global $wpdb;
        $table_name = $wpdb->prefix . 'subscription_plan_categories';
        
        // Check which columns exist in the table
        $columns = $wpdb->get_col("DESCRIBE {$table_name}", 0);
        $has_description = in_array('description', $columns);
        $has_category_description = in_array('category_description', $columns);
        
        $data = array(
            'category_name' => $category_name
        );
        
        // Add the appropriate description column
        if ($has_description) {
            $data['description'] = $category_description;
        } else if ($has_category_description) {
            $data['category_description'] = $category_description;
        }
        
        // Also update the 'name' column if it exists
        if (in_array('name', $columns)) {
            $data['name'] = $category_name;
        }
        
        return $wpdb->update(
            $table_name,
            $data,
            array('id' => $id)
        ) !== false;
    }

    /**
     * Delete a plan category
     * 
     * @param int $id Category ID
     * @return bool True on success, false on failure
     */
    public function delete_plan_category($id) {
        global $wpdb;
        $table_name = $wpdb->prefix . 'subscription_plan_categories';
        
        return $wpdb->delete(
            $table_name,
            array('id' => $id)
        ) !== false;
    }

    /**
     * Save a new subscription to the database
     */
    public function save_subscription($data) {
        global $wpdb;
        
        $table_name = $wpdb->prefix . 'subscriptions';
        
        // Debug the data being saved
        // phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log, WordPress.PHP.DevelopmentFunctions.print_r -- Debug logging for subscription data
        error_log('Saving subscription data: ' . print_r($data, true));
        
        // Ensure all values are of the correct type
        $sanitized_data = [
            'user_id' => intval($data['user_id']),
            'plan_id' => intval($data['plan_id']),
            'email' => sanitize_email($data['email']),
            'status' => sanitize_text_field($data['status']),
            'stripe_customer_id' => sanitize_text_field($data['stripe_customer_id']),
            'stripe_subscription_id' => sanitize_text_field($data['stripe_subscription_id']),
            'start_date' => $data['start_date'],
            'end_date' => $data['end_date'],
            'created_at' => $data['created_at'],
            'updated_at' => $data['updated_at']
        ];
        
        // Make sure stripe fields are included in the format array
        $result = $wpdb->insert(
            $table_name,
            $sanitized_data,
            array(
                '%d', // user_id
                '%d', // plan_id
                '%s', // email
                '%s', // status
                '%s', // stripe_customer_id
                '%s', // stripe_subscription_id
                '%s', // start_date
                '%s', // end_date
                '%s', // created_at
                '%s'  // updated_at
            )
        );
        
        if ($result) {
            return $wpdb->insert_id;
        }
        
        // phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log -- Debug logging for subscription save failure
        error_log('Failed to save subscription: ' . $wpdb->last_error);
        return false;
    }

    /**
     * Get subscription by Stripe subscription ID
     */
    public function get_subscription_by_stripe_id($stripe_subscription_id) {
        global $wpdb;
        
        $table_name = $wpdb->prefix . 'subscriptions';
        
        return $wpdb->get_row(
            $wpdb->prepare(
                "SELECT * FROM $table_name WHERE stripe_subscription_id = %s",
                $stripe_subscription_id
            )
        );
    }

    /**
     * Get user ID by Stripe customer ID
     */
    public function get_user_id_by_customer_id($customer_id) {
        global $wpdb;
        
        $table_name = $wpdb->prefix . 'subscriptions';
        
        return $wpdb->get_var(
            $wpdb->prepare(
                "SELECT user_id FROM $table_name WHERE stripe_customer_id = %s LIMIT 1",
                $customer_id
            )
        );
    }

    /**
     * Get a subscription plan by ID
     *
     * @param int $plan_id Plan ID
     * @return object|null Plan object or null if not found
     */
    public function get_plan($plan_id) {
        global $wpdb;
        $table_name = $wpdb->prefix . 'subscription_plans';
        
        // phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log -- Debug logging for plan retrieval
        error_log('Getting plan with ID: ' . $plan_id);
        
        return $wpdb->get_row($wpdb->prepare(
            "SELECT * FROM $table_name WHERE id = %d",
            $plan_id
        ));
    }

    /**
     * Direct insert subscription with detailed error logging
     * 
     * @param array $data Subscription data
     * @return int|false Subscription ID or false on failure
     */
    public function direct_insert_subscription($data) {
        global $wpdb;
        $table_name = $wpdb->prefix . 'subscriptions';
        
        // Enable error display
        $wpdb->show_errors();
        
        // Log the data we're trying to insert
        // phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log -- Debug logging for subscription insertion
        error_log('Attempting to insert subscription with data: ' . wp_json_encode($data));
        
        // Check if table exists
        $table_exists = $wpdb->get_var("SHOW TABLES LIKE '$table_name'");
        if ($table_exists != $table_name) {
            // phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log -- Debug logging for missing table
            error_log("Table $table_name does not exist");
            
            // Try to create the table
            if (!$this->create_subscriptions_table()) {
                // phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log -- Debug logging for table creation failure
                error_log("Failed to create table $table_name");
                return false;
            }
        }
        
        // Get table structure
        $columns = $wpdb->get_results("DESCRIBE $table_name", ARRAY_A);
        $column_names = array_column($columns, 'Field');
        // phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log -- Debug logging for table structure
        error_log("Table columns: " . implode(', ', $column_names));
        
        // Filter data to only include existing columns
        $filtered_data = array();
        foreach ($data as $key => $value) {
            if (in_array($key, $column_names)) {
                $filtered_data[$key] = $value;
            } else {
                // phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log -- Debug logging for invalid column
                error_log("Column $key does not exist in table $table_name");
            }
        }
        
        // Check for required columns
        $required_columns = ['user_id', 'plan_id', 'customer_email', 'status', 'start_date'];
        foreach ($required_columns as $column) {
            if (!isset($filtered_data[$column]) || empty($filtered_data[$column])) {
                // phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log -- Debug logging for missing required column
                error_log("Missing required column: $column");
                return false;
            }
        }
        
        // Insert the data
        $result = $wpdb->insert($table_name, $filtered_data);
        
        if ($result === false) {
            // phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log -- Debug logging for insert failure
            error_log("Insert failed: " . $wpdb->last_error);
            // phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log -- Debug logging for last query
            error_log("Last query: " . $wpdb->last_query);
            return false;
        }
        
        $subscription_id = $wpdb->insert_id;
        // phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log -- Debug logging for successful insertion
        error_log("Subscription inserted successfully with ID: $subscription_id");
        
        return $subscription_id;
    }
} 