<?php
/**
 * Database Updater Class
 * 
 * Safely handles database updates without affecting existing data
 *
 * @link       https://mediahubster.in
 * @since      1.0.4
 *
 * @package    Subs_Sys
 * @subpackage Subs_Sys/includes
 */

// If this file is called directly, abort.
if ( ! defined( 'ABSPATH' ) ) {
	exit;
}

if ( ! class_exists( 'Subs_Sys_DB_Updater' ) ) {
	/**
	 * Handles safe database updates for the plugin.
	 *
	 * @since      1.0.4
	 * @package    Subs_Sys
	 * @subpackage Subs_Sys/includes
	 * @author     Joe Youngblood <support@subscriptionplugin.com>
	 */
	class Subs_Sys_DB_Updater {

	/**
	 * Current database version
	 */
	const DB_VERSION = '1.1.1';

	/**
	 * Flag to prevent duplicate checks in same request
	 */
	private static $check_done = false;

	/**
	 * Initialize the updater
	 */
	public function __construct() {
		// Check and update database on every load
		add_action( 'init', array( $this, 'check_and_update_database' ), 5 );
		
		// Also check on plugins_loaded (runs earlier)
		add_action( 'plugins_loaded', array( $this, 'check_and_update_database' ), 5 );
		
		// Show admin notices if tables were created
		add_action( 'admin_notices', array( $this, 'show_update_notices' ) );
	}

	/**
	 * Check if database needs updating and update if necessary
	 * 
	 * This runs on every plugin load to ensure all tables exist
	 * Safe for existing installations - only creates missing tables
	 */
	public function check_and_update_database() {
		// Prevent duplicate checks in same request
		if ( self::$check_done ) {
			return;
		}
		self::$check_done = true;

		$current_version = get_option( 'subs_sys_db_version', '1.0.0' );
		
		// Check if version-based update is needed
		$version_update_needed = version_compare( $current_version, self::DB_VERSION, '<' );
		
		// Check if email logs table exists (critical table check)
		$email_table_missing = ! self::email_logs_table_exists();
		
		// Check if content tables exist
		$content_tables_missing = ! self::content_tables_exist();

		$interest_table_missing = ! self::interest_table_exists();
		
		// Perform update if needed
		if ( $version_update_needed || $email_table_missing || $content_tables_missing || $interest_table_missing ) {
			
			// Log what's triggering the update
			if ( $version_update_needed ) {
				error_log( "Subs_Sys: Version update needed from {$current_version} to " . self::DB_VERSION );
			}
			if ( $email_table_missing ) {
				error_log( "Subs_Sys: Email logs table is missing, will create it now" );
			}
			if ( $content_tables_missing ) {
				error_log( "Subs_Sys: Content subscription tables are missing, will create them now" );
			}
			if ( $interest_table_missing ) {
				error_log( 'Subs_Sys: Interest subscription table is missing, will create it now' );
			}
			
			// Run the update
			$this->update_database( $current_version );
			
			// Update version only if it was a version-based update
			if ( $version_update_needed ) {
				update_option( 'subs_sys_db_version', self::DB_VERSION );
			}
		}
	}

	/**
	 * Show admin notices after database updates
	 */
	public function show_update_notices() {
		// Check if email logs table was just created
		if ( get_transient( 'subs_sys_email_logs_table_created' ) ) {
			?>
			<div class="notice notice-success is-dismissible">
				<p><strong>Subscription System:</strong> Email logging system has been successfully installed. You can now track all sent emails in the Email Logs page!</p>
			</div>
			<?php
			delete_transient( 'subs_sys_email_logs_table_created' );
		}

		// Check if content tables were just created
		if ( get_transient( 'subs_sys_content_tables_created' ) ) {
			?>
			<div class="notice notice-success is-dismissible">
				<p><strong>Subscription System:</strong> Content subscription tables have been successfully installed!</p>
			</div>
			<?php
			delete_transient( 'subs_sys_content_tables_created' );
		}
	}

	/**
	 * Update database from old version to new version
	 * 
	 * Safe for existing installations - uses CREATE TABLE IF NOT EXISTS
	 * Will not affect existing data or tables
	 *
	 * @param string $old_version The old database version
	 */
	private function update_database( $old_version ) {
		global $wpdb;

		// Log the update process
		error_log( "Subs_Sys: Starting database update from {$old_version} to " . self::DB_VERSION );

		// Always check and create content subscription tables if missing
		// Safe because we use "CREATE TABLE IF NOT EXISTS"
		if ( version_compare( $old_version, '1.0.4', '<' ) || ! self::content_tables_exist() ) {
			error_log( "Subs_Sys: Ensuring content subscription tables exist..." );
			$this->add_content_subscription_tables();
		}

		// Always check and create email logs table if missing
		// Safe because we use "CREATE TABLE IF NOT EXISTS"
		if ( version_compare( $old_version, '1.0.6', '<' ) || ! self::email_logs_table_exists() ) {
			error_log( "Subs_Sys: Ensuring email logs table exists..." );
			$this->add_email_logs_table();
		}

		if ( version_compare( $old_version, '1.1.0', '<' ) || ! self::interest_table_exists() ) {
			error_log( 'Subs_Sys: Ensuring interest subscription table exists...' );
			$this->add_interest_subscription_table();
		}

		if ( version_compare( $old_version, '1.1.1', '<' ) ) {
			$this->update_interest_subscription_table();
		}

		error_log( "Subs_Sys: Database update completed successfully" );
	}

		/**
		 * Add content subscription tables (version 1.0.4)
		 */
		public function add_content_subscription_tables() {
			global $wpdb;
			$charset_collate = $wpdb->get_charset_collate();

			// Create content subscription plans table
			$content_plans_table = $wpdb->prefix . 'subscription_content_plans';
			$sql_content_plans = "CREATE TABLE IF NOT EXISTS $content_plans_table (
				id mediumint(9) NOT NULL AUTO_INCREMENT,
				plan_name varchar(255) NOT NULL,
				description text,
				price decimal(10,2) NOT NULL DEFAULT 0.00,
				duration int(11) NOT NULL DEFAULT 30,
				duration_unit varchar(20) NOT NULL DEFAULT 'days',
				content_type varchar(50) NOT NULL DEFAULT 'category',
				content_ids text NOT NULL,
				notification_frequency varchar(50) NOT NULL DEFAULT 'immediate',
				status varchar(20) NOT NULL DEFAULT 'active',
				stripe_product_id varchar(100) DEFAULT NULL,
				stripe_price_id varchar(100) DEFAULT NULL,
				created_at datetime DEFAULT CURRENT_TIMESTAMP NOT NULL,
				updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL,
				PRIMARY KEY  (id),
				KEY content_type (content_type),
				KEY status (status)
			) $charset_collate;";

			// Create content subscriptions table
			$content_subscriptions_table = $wpdb->prefix . 'subscription_content_subscriptions';
			$sql_content_subscriptions = "CREATE TABLE IF NOT EXISTS $content_subscriptions_table (
				id mediumint(9) NOT NULL AUTO_INCREMENT,
				user_id bigint(20) NOT NULL,
				content_plan_id mediumint(9) NOT NULL,
				subscription_id mediumint(9) DEFAULT NULL,
				status varchar(20) NOT NULL DEFAULT 'active',
				start_date datetime NOT NULL,
				end_date datetime NOT NULL,
				last_notification datetime DEFAULT NULL,
				created_at datetime DEFAULT CURRENT_TIMESTAMP NOT NULL,
				updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL,
				PRIMARY KEY  (id),
				KEY user_id (user_id),
				KEY content_plan_id (content_plan_id),
				KEY subscription_id (subscription_id),
				KEY status (status),
				UNIQUE KEY user_content_plan (user_id, content_plan_id)
			) $charset_collate;";

			// Create content notifications table
			$content_notifications_table = $wpdb->prefix . 'subscription_content_notifications';
			$sql_content_notifications = "CREATE TABLE IF NOT EXISTS $content_notifications_table (
				id mediumint(9) NOT NULL AUTO_INCREMENT,
				user_id bigint(20) NOT NULL,
				content_plan_id mediumint(9) NOT NULL,
				post_id bigint(20) NOT NULL,
				notification_type varchar(50) NOT NULL DEFAULT 'new_content',
				sent_at datetime DEFAULT NULL,
				read_at datetime DEFAULT NULL,
				status varchar(20) NOT NULL DEFAULT 'pending',
				created_at datetime DEFAULT CURRENT_TIMESTAMP NOT NULL,
				PRIMARY KEY  (id),
				KEY user_id (user_id),
				KEY content_plan_id (content_plan_id),
				KEY post_id (post_id),
				KEY status (status)
			) $charset_collate;";

			require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );

			// Create tables safely
			dbDelta( $sql_content_plans );
			dbDelta( $sql_content_subscriptions );
			dbDelta( $sql_content_notifications );

			// Log successful creation
			error_log( "Subs_Sys: Content subscription tables created successfully" );
			
			// Set a transient to show admin notice
			set_transient( 'subs_sys_content_tables_created', true, 60 );
		}

	/**
	 * Add email logs table (version 1.0.6)
	 */
	public function add_email_logs_table() {
		global $wpdb;
		$charset_collate = $wpdb->get_charset_collate();

		// Create email logs table
		$email_logs_table = $wpdb->prefix . 'subscription_email_logs';
		$sql_email_logs = "CREATE TABLE IF NOT EXISTS $email_logs_table (
			id bigint(20) NOT NULL AUTO_INCREMENT,
			user_id bigint(20) DEFAULT NULL,
			recipient_email varchar(255) NOT NULL,
			recipient_name varchar(255) DEFAULT NULL,
			post_id bigint(20) DEFAULT NULL,
			post_title varchar(255) DEFAULT NULL,
			post_url text DEFAULT NULL,
			content_plan_id mediumint(9) DEFAULT NULL,
			subject varchar(500) NOT NULL,
			email_type varchar(50) NOT NULL DEFAULT 'content_notification',
			status varchar(20) NOT NULL DEFAULT 'sent',
			error_message text DEFAULT NULL,
			sent_at datetime DEFAULT CURRENT_TIMESTAMP NOT NULL,
			PRIMARY KEY  (id),
			KEY user_id (user_id),
			KEY post_id (post_id),
			KEY recipient_email (recipient_email),
			KEY content_plan_id (content_plan_id),
			KEY email_type (email_type),
			KEY status (status),
			KEY sent_at (sent_at)
		) $charset_collate;";

		require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );

		// Create table safely
		dbDelta( $sql_email_logs );

		// Verify table was created
		if ( $wpdb->get_var( "SHOW TABLES LIKE '$email_logs_table'" ) == $email_logs_table ) {
			error_log( "Subs_Sys: Email logs table created successfully" );
			set_transient( 'subs_sys_email_logs_table_created', true, 60 );
		} else {
			error_log( "Subs_Sys: Failed to create email logs table" );
		}
	}

	/**
	 * Check if content subscription tables exist
	 *
	 * @return bool True if tables exist, false otherwise
	 */
	public static function content_tables_exist() {
		global $wpdb;

		$tables = array(
			$wpdb->prefix . 'subscription_content_plans',
			$wpdb->prefix . 'subscription_content_subscriptions',
			$wpdb->prefix . 'subscription_content_notifications'
		);

		foreach ( $tables as $table ) {
			if ( $wpdb->get_var( "SHOW TABLES LIKE '$table'" ) != $table ) {
				return false;
			}
		}

		return true;
	}

	/**
	 * Add interest subscription table (version 1.1.0)
	 */
	public function add_interest_subscription_table() {
		global $wpdb;
		$charset_collate = $wpdb->get_charset_collate();

		$table = $wpdb->prefix . 'subscription_interest_subscriptions';
		$sql   = "CREATE TABLE IF NOT EXISTS $table (
			id mediumint(9) NOT NULL AUTO_INCREMENT,
			user_id bigint(20) DEFAULT NULL,
			plan_id mediumint(9) DEFAULT NULL,
			subscriber_name varchar(100) NOT NULL,
			subscriber_email varchar(100) NOT NULL,
			category_ids text NOT NULL,
			tag_ids text NOT NULL,
			plan_type varchar(20) NOT NULL DEFAULT 'free',
			status varchar(20) NOT NULL DEFAULT 'active',
			payment_id varchar(100) DEFAULT NULL,
			created_at datetime DEFAULT CURRENT_TIMESTAMP NOT NULL,
			updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL,
			PRIMARY KEY  (id),
			KEY user_id (user_id),
			KEY plan_id (plan_id),
			KEY subscriber_email (subscriber_email),
			KEY status (status),
			KEY plan_type (plan_type)
		) $charset_collate;";

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

		error_log( 'Subs_Sys: Interest subscription table created successfully' );
		set_transient( 'subs_sys_interest_table_created', true, 60 );
	}

	/**
	 * Add plan_id column to interest subscriptions (version 1.1.1).
	 */
	public function update_interest_subscription_table() {
		global $wpdb;

		$table = $wpdb->prefix . 'subscription_interest_subscriptions';
		if ( $wpdb->get_var( "SHOW TABLES LIKE '$table'" ) !== $table ) {
			return;
		}

		$column = $wpdb->get_results( "SHOW COLUMNS FROM $table LIKE 'plan_id'" );
		if ( empty( $column ) ) {
			$wpdb->query( "ALTER TABLE $table ADD COLUMN plan_id mediumint(9) DEFAULT NULL AFTER user_id" );
			$wpdb->query( "ALTER TABLE $table ADD KEY plan_id (plan_id)" );
		}
	}

	/**
	 * Check if interest subscription table exists.
	 *
	 * @return bool
	 */
	public static function interest_table_exists() {
		global $wpdb;
		$table = $wpdb->prefix . 'subscription_interest_subscriptions';
		return $wpdb->get_var( "SHOW TABLES LIKE '$table'" ) === $table;
	}

	/**
	 * Check if email logs table exists
	 *
	 * @return bool True if table exists, false otherwise
	 */
	public static function email_logs_table_exists() {
		global $wpdb;
		$table = $wpdb->prefix . 'subscription_email_logs';
		return $wpdb->get_var( "SHOW TABLES LIKE '$table'" ) == $table;
	}

		/**
		 * Update content subscription table structure if needed
		 */
		public static function update_content_table_structure() {
			global $wpdb;
			
			$content_plans_table = $wpdb->prefix . 'subscription_content_plans';
			
			// Check if table exists first
			if ( $wpdb->get_var( "SHOW TABLES LIKE '$content_plans_table'" ) !== $content_plans_table ) {
				return;
			}
			
			// Get current table structure
			$columns = $wpdb->get_results( "SHOW COLUMNS FROM $content_plans_table" );
			$column_names = array_column( $columns, 'Field' );
			
			// Log table structure for debugging
			error_log( "Subs_Sys: Content plans table columns: " . implode( ', ', $column_names ) );
			
			// The table uses 'duration' and 'duration_unit' instead of 'billing_period'
			// This is the correct structure, so no changes needed
		}

		/**
		 * Get current database version
		 *
		 * @return string Current database version
		 */
		public static function get_current_version() {
			return get_option( 'subs_sys_db_version', '1.0.0' );
		}

		/**
		 * Force database update (for manual triggering)
		 */
		public static function force_update() {
			$updater = new self();
			$current_version = self::get_current_version();
			$updater->update_database( $current_version );
			update_option( 'subs_sys_db_version', self::DB_VERSION );
		}
	}
}