<?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;
		}

		$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();
	}
}
