<?php

namespace Limb_Chatbot\Includes\Migrations;

use Limb_Chatbot\Includes\Data_Objects\Lead_Field;
use Limb_Chatbot\Includes\Services\Seeder_Service;

/**
 * Migration for version 1.0.13
 *
 * Creates the leads, lead_fields, and lead_values tables for managing lead captures
 * from both actions and widgets in a structured, queryable format.
 *
 * @since 1.0.13
 */
class Migration_1_0_13 extends Abstract_Migration {

	/**
	 * Get the target version this migration upgrades to.
	 *
	 * @return string
	 * @since 1.0.13
	 */
	public function get_version(): string {
		return '1.0.13';
	}

	/**
	 * Get migration description.
	 *
	 * @return string
	 * @since 1.0.13
	 */
	public function get_description(): string {
		return 'Create leads, lead_fields, and lead_values tables for structured lead capture management';
	}

	/**
	 * Execute the migration.
	 *
	 * Creates three normalized tables for managing lead captures:
	 * - leads: Main lead records with source tracking and qualification status
	 * - lead_fields: Field definitions and metadata
	 * - lead_values: Field values with typed storage (string, number, date)
	 *
	 * @return bool True on success, false on failure.
	 * @since 1.0.13
	 */
	public function up(): bool {
		$this->log( 'Starting migration 1.0.13 - Creating leads tables' );

		if ( ! $this->create_leads_table() ) {
			return false;
		}

		if ( ! $this->create_lead_fields_table() ) {
			return false;
		}

		if ( ! $this->create_lead_values_table() ) {
			return false;
		}

		if ( ! $this->create_default_lead_fields() ) {
			return false;
		}

		if ( ! $this->migrate_live_agent_settings() ) {
			return false;
		}

		$this->log( 'Migration 1.0.13 completed successfully - leads tables created' );

		return true;
	}

	/**
	 * Create the lbaic_leads table.
	 *
	 * Stores lead records with metadata about their source, capture time, and qualification status.
	 *
	 * @return bool True on success, false on failure.
	 * @since 1.0.13
	 */
	private function create_leads_table(): bool {
		$table_name = $this->wpdb->prefix . 'lbaic_leads';

		// Check if table already exists
		if ( $this->table_exists( $table_name ) ) {
			$this->log( "Table {$table_name} already exists, skipping creation" );

			return true;
		}

		$collate = $this->wpdb->get_charset_collate();

		$sql = "CREATE TABLE `{$table_name}` (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			chatbot_user_uuid varchar(36) NOT NULL,
			chatbot_uuid varchar(36) NULL,
			chat_uuid varchar(36) NULL,
			source_type varchar(50) NOT NULL,
			source varchar(255) NOT NULL,
			source_title varchar(255) NOT NULL,
			metadata LONGTEXT NULL,
			created_at datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
			status varchar(50) DEFAULT 'new' NOT NULL,
			is_qualified tinyint(1) DEFAULT 0 NOT NULL,
			PRIMARY KEY (id),
			KEY chatbot_user_uuid (chatbot_user_uuid),
			KEY chatbot_uuid (chatbot_uuid),
			KEY source_type (source_type),
			KEY source (source),
			KEY status (status),
			KEY is_qualified (is_qualified),
			KEY created_at (created_at)
		) {$collate};";

		$results = $this->db_delta( $sql );

		// Verify table was created
		if ( ! $this->table_exists( $table_name ) ) {
			$this->log( "Failed to create table {$table_name}", 'error' );

			return false;
		}

		$this->log( "Successfully created table {$table_name}" );

		return true;
	}

	/**
	 * Create the lbaic_lead_fields table.
	 *
	 * Stores field definitions and metadata for each lead field.
	 * Allows tracking of field labels and data types for type-safe value storage.
	 *
	 * @return bool True on success, false on failure.
	 * @since 1.0.13
	 */
	private function create_lead_fields_table(): bool {
		$table_name = $this->wpdb->prefix . 'lbaic_lead_fields';

		// Check if table already exists
		if ( $this->table_exists( $table_name ) ) {
			$this->log( "Table {$table_name} already exists, skipping creation" );

			return true;
		}

		$collate = $this->wpdb->get_charset_collate();

		$sql = "CREATE TABLE `{$table_name}` (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			field_key varchar(255) NOT NULL,
			label varchar(255) NOT NULL,
			status tinyint(1) NOT NULL DEFAULT 1,
			data_type varchar(50) NOT NULL DEFAULT 'text',
			PRIMARY KEY (id),
			UNIQUE KEY field_key (field_key),
			KEY label (label),
			KEY data_type (data_type)
		) {$collate};";

		$results = $this->db_delta( $sql );

		// Verify table was created
		if ( ! $this->table_exists( $table_name ) ) {
			$this->log( "Failed to create table {$table_name}", 'error' );

			return false;
		}

		$this->log( "Successfully created table {$table_name}" );

		return true;
	}

	/**
	 * Create the lbaic_lead_values table.
	 *
	 * Stores the actual lead field values with support for multiple data types.
	 * Each field value is stored with type-specific columns for efficient querying.
	 *
	 * @return bool True on success, false on failure.
	 * @since 1.0.13
	 */
	private function create_lead_values_table(): bool {
		$table_name = $this->wpdb->prefix . 'lbaic_lead_values';

		// Check if table already exists
		if ( $this->table_exists( $table_name ) ) {
			$this->log( "Table {$table_name} already exists, skipping creation" );

			return true;
		}

		$collate = $this->wpdb->get_charset_collate();

		$sql = "CREATE TABLE `{$table_name}` (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			lead_id bigint(20) unsigned NOT NULL,
			field_id bigint(20) unsigned NOT NULL,
			value_string longtext NULL,
			value_number decimal(19,4) NULL,
			value_date datetime NULL,
			PRIMARY KEY (id),
			KEY lead_id (lead_id),
			KEY field_id (field_id),
			KEY lead_field (lead_id, field_id)
		) {$collate};";

		$results = $this->db_delta( $sql );

		// Verify table was created
		if ( ! $this->table_exists( $table_name ) ) {
			$this->log( "Failed to create table {$table_name}", 'error' );

			return false;
		}

		$this->log( "Successfully created table {$table_name}" );

		return true;
	}

	/**
	 * Create default lead fields for name and email.
	 *
	 * These are the two standard fields used by the lead capture widget.
	 * They are always created to ensure consistency.
	 *
	 * @return bool True on success, false on failure.
	 * @since 1.0.13
	 */
	public function create_default_lead_fields(): bool {
		return (new Seeder_Service())->seed_lead_fields();
	}

	/**
	 * Migrate live agent settings from legacy names to unified names.
	 *
	 * For Telegram: Copies agent_telegram_ids to agent_ids and telegram_fetch_method to agent_fetch_method.
	 * For Slack: Copies slack_fetch_method to agent_fetch_method (agent_ids already exists).
	 *
	 * @return bool True on success, false on failure.
	 * @since 1.0.13
	 */
	private function migrate_live_agent_settings(): bool {
		try {
			$this->log( 'Migrating live agent settings to unified names' );

			$prefix         = 'lbaic.utilities.chatbot.';
			$migrated_count = 0;

			// Get all settings that need migration
			$legacy_settings = [
				// Telegram legacy settings
				'agent_telegram_ids'    => 'agent_ids',
				'telegram_fetch_method' => 'agent_fetch_method',
				// Slack legacy setting (agent_ids already exists for Slack, only migrate fetch method)
				'slack_fetch_method'    => 'agent_fetch_method',
			];

			foreach ( $legacy_settings as $legacy_key => $new_key ) {
				$legacy_full_key = $prefix . $legacy_key;
				$new_full_key    = $prefix . $new_key;

				// Get legacy value
				$legacy_value = get_option( $legacy_full_key, null );

				if ( $legacy_value === null || $legacy_value === '' || $legacy_value === [] ) {
					$this->log( sprintf( 'Legacy setting %s not found or empty, skipping', $legacy_key ) );
					continue;
				}

				// Check if new setting already exists with data
				$new_value = get_option( $new_full_key, null );

				if ( $new_value !== null && $new_value !== '' && $new_value !== [] ) {
					$this->log( sprintf( 'New setting %s already has data, skipping migration from %s', $new_key, $legacy_key ) );
					continue;
				}

				// Migrate the value
				update_option( $new_full_key, $legacy_value );

				$this->log( sprintf( 'Migrated %s to %s', $legacy_key, $new_key ) );
				$migrated_count++;
			}

			if ( $migrated_count > 0 ) {
				$this->log( sprintf( 'Successfully migrated %d live agent settings', $migrated_count ) );
			} else {
				$this->log( 'No live agent settings needed migration' );
			}

			return true;
		} catch ( \Exception $e ) {
			$this->log( sprintf( 'Error migrating live agent settings: %s', $e->getMessage() ), 'error' );

			return false;
		}
	}
}
