<?php

namespace Limb_Chatbot\Includes\Services;

use Limb_Chatbot\Includes\Traits\SingletonTrait;
use wpdb;

/**
 * Handles plugin installation tasks such as creating and dropping database tables and user roles.
 *
 * @since 1.0.0
 */
class Install {

	use SingletonTrait;

	/**
	 * WordPress database global instance.
	 *
	 * @var wpdb
	 * @since 1.0.0
	 */
	protected $wpdb;

	/**
	 * Constructor is protected to enforce singleton pattern.
	 *
	 * @since 1.0.0
	 */
	protected function __construct() {
		global $wpdb;
		$this->wpdb = $wpdb;
	}

	/**
	 * Runs on plugin activation.
	 *
	 * @return void
	 * @since 1.0.0
	 */
	public static function activate(): void {
		self::deactivate_pro_version();
		$instance = self::instance();
		$instance->create_tables();
		$instance->create_roles();
		( new Seeder_Service() )->seed();
	}

	/**
	 * Runs on plugin deactivation.
	 *
	 * @return void
	 * @since 1.0.0
	 */
	public static function deactivate() {
	}

	/**
	 * Creates custom user roles if needed.
	 *
	 * @return void
	 * @since 1.0.0
	 */
	public function create_roles() {
		// TODO Add custom user roles if needed
	}

	/**
	 * Creates database tables.
	 *
	 * @return void
	 * @since 1.0.0
	 */
	public function create_tables() {
		$wpdb = $this->wpdb;
		// Include necessary WordPress upgrade functions and execute the query.
		require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
		dbDelta( self::get_schemas( $wpdb ) );
	}

	/**
	 * Returns the SQL CREATE TABLE statements for all required tables.
	 *
	 * @param  wpdb  $wpdb WordPress database instance.
	 * @return string SQL statements for creating tables.
	 * @since 1.0.0
	 */
	protected static function get_schemas( $wpdb ) {
		$collate = $wpdb->get_charset_collate();

		return "
CREATE TABLE {$wpdb->prefix}lbaic_chats (
  id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  name varchar(256) DEFAULT NULL,
  uuid char(36) NOT NULL, 
  chatbot_uuid char(36) NULL, /* TODO maybe only chatbot_id should be kept and uuid id should be only applied based on response logic */
  chatbot_id bigint(20) unsigned NULL, /* TODO allowing null ? Considering that the default chatbot won't have any uuid */
  type tinyint(1) DEFAULT 1 NOT NULL,
  status tinyint(1) DEFAULT 1 NOT NULL, 
  created_at datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
  updated_at datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uuid (uuid),
  KEY chatbot_uuid (chatbot_uuid),
  KEY created_at (created_at),
  KEY `type` (type),
  KEY `status` (status)
) $collate;
CREATE TABLE {$wpdb->prefix}lbaic_chat_participants (
    id bigint unsigned NOT NULL AUTO_INCREMENT,
    chat_id bigint unsigned NOT NULL,
    user_id bigint unsigned NOT NULL,
    role varchar(128) DEFAULT 'user',
    joined_at datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
    PRIMARY KEY (id),
    KEY chat_id (chat_id),
    KEY user_id (user_id)
) $collate;
CREATE TABLE {$wpdb->prefix}lbaic_chat_metas (
  id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  chat_id bigint(20) unsigned NOT NULL,
  meta_key varchar(512) NOT NULL,
  meta_value longtext NULL,
  PRIMARY KEY  (id),
  KEY chat_id (chat_id),
  KEY meta_key (meta_key)
) $collate;
CREATE TABLE {$wpdb->prefix}lbaic_chat_message_metas (
  id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  message_id bigint(20) unsigned NOT NULL,
  meta_key varchar(512) NOT NULL,
  meta_value longtext NULL,
  PRIMARY KEY  (id),
  KEY message_id (message_id),
  KEY meta_key (meta_key)
) $collate;
CREATE TABLE {$wpdb->prefix}lbaic_chat_messages (
  id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  uuid char(36) NOT NULL,
  chat_uuid char(36) NOT NULL,
  user_id bigint(20) unsigned NULL,
  role varchar(32) NULL,
  content longtext NOT NULL,
  created_at datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
  updated_at datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
  PRIMARY KEY  (id),
  UNIQUE KEY uuid (uuid),
  KEY chat_uuid (chat_uuid),
  KEY user_id (user_id),
  KEY created_at (created_at)
) $collate;
CREATE TABLE {$wpdb->prefix}lbaic_configs (
  id int unsigned NOT NULL AUTO_INCREMENT,
  title varchar(256) NULL,
  description varchar(512) NULL,
  params longtext NOT NULL,
  related_to varchar(512) DEFAULT '' NOT NULL,
  `default` tinyint(1) DEFAULT 0 NOT NULL,
  created_at datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
  updated_at datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
  PRIMARY KEY  (id),
  KEY related_to (related_to),
  KEY `default` (`default`),
  KEY created_at (created_at)
) $collate;
CREATE TABLE {$wpdb->prefix}lbaic_datasets (
  id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  name text NULL,
  type tinyint(1) DEFAULT 1 NOT NULL,
  status tinyint(1) DEFAULT 2 NOT NULL,
  instruction text NULL,
  source varchar(512) NULL,
  source_type varchar(256) NULL,
  source_sub_type varchar(256) NULL,
  created_at datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
  updated_at datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
  PRIMARY KEY  (id),
  KEY `source` (source),
  KEY source_type (source_type),
  KEY source_sub_type (source_sub_type),
  KEY created_at (created_at),
  KEY updated_at (updated_at)
) $collate;
CREATE TABLE {$wpdb->prefix}lbaic_conversation_states (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  chat_uuid VARCHAR(64) NOT NULL,
  topic VARCHAR(255) NULL,
  entities TEXT NULL,
  summary TEXT NULL,
  confidence DECIMAL(5,4) NULL,
  sentiment VARCHAR(50) NULL,
  user_preferences TEXT NULL,
  user_intent_history TEXT NULL,
  PRIMARY KEY (id),
  KEY `xchat_uuid` (chat_uuid)
) $collate;
CREATE TABLE {$wpdb->prefix}lbaic_dataset_metas (
  id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  dataset_id bigint(20) unsigned NOT NULL,
  meta_key varchar(512) NOT NULL,
  meta_value longtext NULL,
  PRIMARY KEY  (id),
  KEY dataset_id (dataset_id),
  KEY meta_key (meta_key)
) $collate;
CREATE TABLE {$wpdb->prefix}lbaic_dataset_entries (
  id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  dataset_id bigint(20) unsigned NOT NULL,
  entry longtext NOT NULL,
  created_at datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
  updated_at datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
  PRIMARY KEY  (id),
  KEY dataset_id (dataset_id),
  KEY created_at (created_at),
  KEY updated_at (updated_at)
) $collate;
CREATE TABLE {$wpdb->prefix}lbaic_ai_model_metas (
  id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  ai_model_id bigint(20) unsigned NOT NULL,
  meta_key varchar(512) NOT NULL,
  meta_value longtext NULL,
  PRIMARY KEY  (id),
  KEY ai_model_id (ai_model_id),
  KEY meta_key (meta_key)
) $collate;
CREATE TABLE {$wpdb->prefix}lbaic_ai_models (
  id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  label varchar(128) NOT NULL,
  name varchar(128) NOT NULL,
  description text NULL,
  ai_provider_id varchar(64) NULL,
  `default` tinyint(1) DEFAULT 0 NOT NULL,
  modalities longtext NULL,
  endpoints longtext NULL,
  features longtext NULL,
  input_token_limit bigint(20) NULL,
  output_token_limit bigint(20) NULL,
  fine_tuned tinyint(1) DEFAULT 0 NOT NULL,
  status tinyint(1) DEFAULT 0 NOT NULL,
  PRIMARY KEY  (id),
  KEY `name` (name),
  KEY label (label),
  KEY ai_provider_id (ai_provider_id),
  KEY `default` (ai_provider_id, `default`),
  KEY fine_tuned (`fine_tuned`),
  KEY `status` (`status`)
) $collate;
CREATE TABLE {$wpdb->prefix}lbaic_vector_indexes (
  id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  name varchar(256) NOT NULL,
  description text NULL,
  vector_db_id varchar(64) NULL,
  config_id int unsigned NULL,
  created_at datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
  updated_at datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
  PRIMARY KEY  (id),
  KEY vector_db_id (vector_db_id),
  KEY config_id (config_id)
) $collate;
CREATE TABLE {$wpdb->prefix}lbaic_vector_index_metas (
  id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  vector_index_id bigint(20) unsigned NOT NULL,
  meta_key varchar(512) NOT NULL,
  meta_value longtext NULL,
  PRIMARY KEY  (id),
  KEY vector_index_id (vector_index_id),
  KEY meta_key (meta_key)
) $collate;
CREATE TABLE {$wpdb->prefix}lbaic_vectors (
  id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  uuid char(36) NOT NULL,
  `values` longtext NULL,
  dimension int NULL,
  status tinyint(1) DEFAULT 1 NOT NULL,
  ai_model_id bigint(20) unsigned NULL,
  dataset_entry_id bigint(20) unsigned NULL,
  config_id int unsigned NULL,
  created_at datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
  updated_at datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
  PRIMARY KEY  (id),
  KEY uuid (uuid),
  KEY ai_model_id (ai_model_id),
  KEY dataset_entry_id (dataset_entry_id),
  KEY config_id (config_id),
  KEY created_at (created_at),
  KEY `status` (status)
) $collate;
CREATE TABLE `{$wpdb->prefix}lbaic_users` (
id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  uuid varchar(36) NOT NULL,
  wp_user_id bigint(20) unsigned DEFAULT NULL,
  device_uuid varchar(255) NOT NULL,
  ip varchar(255) DEFAULT NULL,
  type tinyint(1) DEFAULT NULL,
  status tinyint(1) DEFAULT 1 NOT NULL,
  created_at datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
  updated_at datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
  PRIMARY KEY (id),
  KEY uuid (uuid),
  KEY wp_user_id (wp_user_id),
  KEY device_uuid (device_uuid),
  KEY ip (ip)
) $collate;
CREATE TABLE `{$wpdb->prefix}lbaic_user_metas` (
  id bigint(20) NOT NULL AUTO_INCREMENT,
  chatbot_user_id bigint(20) NOT NULL,
  meta_key varchar(256) NOT NULL,
  meta_value longtext NULL,
  PRIMARY KEY (id),
  KEY chatbot_user_id (chatbot_user_id),
  KEY meta_key (meta_key)
) $collate;
CREATE TABLE {$wpdb->prefix}lbaic_vector_indexes_vectors (
  id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  vector_index_id bigint(20) unsigned NOT NULL,
  vector_id bigint(20) unsigned NOT NULL,
  PRIMARY KEY  (id),
  KEY vector_index_id (vector_index_id),
  KEY vector_id (vector_id)
) $collate;
CREATE TABLE {$wpdb->prefix}lbaic_actions (
    id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    title VARCHAR(255) NOT NULL,
    name VARCHAR(255) NOT NULL,
    ai_instructions TEXT NULL,
    status TINYINT(1) DEFAULT 1 NOT NULL,
    created_at datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
    updated_at datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY name (name),
    UNIQUE KEY title (title),
    KEY status (status)
) $collate;
CREATE TABLE {$wpdb->prefix}lbaic_parameters (
    id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    action_id BIGINT NOT NULL,
    name VARCHAR(255) NOT NULL,
    label VARCHAR(255) NOT NULL,
    type VARCHAR(255) NOT NULL DEFAULT 'text',
    `order` INT NOT NULL DEFAULT 1,
    question TEXT NULL,
    required VARCHAR(10) DEFAULT '0',
    default_value TEXT NULL,
    placeholder VARCHAR(255) NULL,
    validation_rules TEXT NULL,
    suggestions TEXT NULL,
	config TEXT NULL,
    PRIMARY KEY (id),
    KEY action_id (action_id),
    KEY `order` (`order`),
    KEY name (name)
) $collate;
CREATE TABLE {$wpdb->prefix}lbaic_action_callbacks (
    id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    action_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(255) NOT NULL,
    title VARCHAR(255) NOT NULL,
    description TEXT NULL,
    type VARCHAR(50) NOT NULL,
    config LONGTEXT NOT NULL,
    `order` INT NOT NULL DEFAULT 1,
    is_required TINYINT(1) DEFAULT 1 NOT NULL,
    status TINYINT(1) DEFAULT 1 NOT NULL,
    created_at datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
    updated_at datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY name_action_unique (name, action_id),
    KEY action_id (action_id),
    KEY type (type),
    KEY `order` (`order`),
    KEY status (status)
) $collate;
CREATE TABLE {$wpdb->prefix}lbaic_action_submissions (
    id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    action_id bigint(20) unsigned NOT NULL,
    action_data longtext NULL,
    chatbot_user_id bigint(20) unsigned NULL,
    chat_uuid char(36) NULL,
    callback_results longtext NULL,
    extracted_vars longtext NULL,
    status varchar(50) DEFAULT 'pending' NOT NULL,
    error_message longtext NULL,
    created_at datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
    PRIMARY KEY (id),
    KEY action_id (action_id),
    KEY chatbot_user_id (chatbot_user_id),
    KEY chat_uuid (chat_uuid),
    KEY status (status),
    KEY created_at (created_at)
) $collate;
CREATE TABLE {$wpdb->prefix}lbaic_jobs (
    id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    type VARCHAR(100) NOT NULL,
    sub_type VARCHAR(100) NOT NULL,
    status VARCHAR(50) NOT NULL DEFAULT 'pending',
    progress_percent DECIMAL(5,2) DEFAULT 0.00 NOT NULL,
    chatbot_uuid char(36) NULL,
    config LONGTEXT NULL,
    stats LONGTEXT NULL,
    errors LONGTEXT NULL,
    created_at datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
    started_at datetime NULL,
    completed_at datetime NULL,
    PRIMARY KEY (id),
    KEY type (type),
    KEY sub_type (sub_type),
    KEY status (status),
    KEY chatbot_uuid (chatbot_uuid),
    KEY created_at (created_at),
    KEY status_type (status, type)
) $collate;
CREATE TABLE {$wpdb->prefix}lbaic_job_tasks (
    id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    job_id bigint(20) unsigned NOT NULL,
    parent_task_id bigint(20) unsigned NULL,
    payload LONGTEXT NOT NULL,
    status VARCHAR(50) NOT NULL DEFAULT 'pending',
    attempt_count INT DEFAULT 0 NOT NULL,
    error TEXT NULL,
    task_started_at datetime NULL,
    task_completed_at datetime NULL,
    PRIMARY KEY (id),
    KEY job_id (job_id),
    KEY parent_task_id (parent_task_id),
    KEY status (status),
    KEY job_status (job_id, status),
    KEY job_parent_status (job_id, parent_task_id, status)
) $collate;
CREATE TABLE {$wpdb->prefix}lbaic_leads (
	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),
	KEY source_type_status (source_type, status)
) $collate;
CREATE TABLE {$wpdb->prefix}lbaic_lead_fields (
	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;
CREATE TABLE {$wpdb->prefix}lbaic_lead_values (
	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;
CREATE TABLE {$wpdb->prefix}lbaic_notification_log (
	id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
	chatbot_user_id bigint(20) unsigned NOT NULL,
	type_key varchar(100) NOT NULL,
	channel varchar(50) NOT NULL,
	status varchar(20) NOT NULL DEFAULT 'pending',
	sent_at datetime NULL DEFAULT NULL,
	fail_reason TEXT NULL DEFAULT NULL,
	content_type varchar(100) NULL DEFAULT NULL,
	content LONGTEXT NULL DEFAULT NULL,
	PRIMARY KEY (id),
	KEY chatbot_user_id (chatbot_user_id),
	KEY type_key (type_key),
	KEY channel (channel),
	KEY status (status),
	KEY sent_at (sent_at)
) $collate;";
	}

	/**
	 * Returns the list of all table names used by the plugin.
	 *
	 * @return array List of table names.
	 * @since 1.0.0
	 */
	public static function get_tables() {
		$wpdb   = self::instance()->wpdb;
		$tables = array(
			"{$wpdb->prefix}lbaic_chats",
			"{$wpdb->prefix}lbaic_chat_metas",
			"{$wpdb->prefix}lbaic_chat_messages",
			"{$wpdb->prefix}lbaic_chat_message_metas",
			"{$wpdb->prefix}lbaic_configs",
			"{$wpdb->prefix}lbaic_datasets",
			"{$wpdb->prefix}lbaic_dataset_entries",
			"{$wpdb->prefix}lbaic_ai_models",
			"{$wpdb->prefix}lbaic_ai_model_metas",
			"{$wpdb->prefix}lbaic_users",
			"{$wpdb->prefix}lbaic_user_metas",
			"{$wpdb->prefix}lbaic_vector_indexes",
			"{$wpdb->prefix}lbaic_vector_index_metas",
			"{$wpdb->prefix}lbaic_vectors",
			"{$wpdb->prefix}lbaic_vector_indexes_vectors",
			"{$wpdb->prefix}lbaic_conversation_states",
			"{$wpdb->prefix}lbaic_chat_participants",
			"{$wpdb->prefix}lbaic_dataset_metas",
			"{$wpdb->prefix}lbaic_actions",
			"{$wpdb->prefix}lbaic_parameters",
			"{$wpdb->prefix}lbaic_action_callbacks",
			"{$wpdb->prefix}lbaic_action_submissions",
			"{$wpdb->prefix}lbaic_jobs",
			"{$wpdb->prefix}lbaic_job_tasks",
			"{$wpdb->prefix}lbaic_leads",
			"{$wpdb->prefix}lbaic_lead_fields",
			"{$wpdb->prefix}lbaic_lead_values",
			"{$wpdb->prefix}lbaic_notification_log",
		);

		return apply_filters( 'lbaic_db_tables', $tables );
	}

	/**
	 * Drops all plugin-related tables from the database.
	 *
	 * @return void
	 * @since 1.0.0
	 */
	public function drop_tables() {
		$wpdb   = $this->wpdb;
		$tables = $this::get_tables();
		foreach ( $tables as $table ) {
			// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			$wpdb->query( "DROP TABLE IF EXISTS {$table}" );
			// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		}
	}

	/**
	 * Deactivate the free version when PRO is activated.
	 *
	 * @return void
	 * @since 1.0.9
	 */
	private static function deactivate_pro_version(): void {
		// Include the plugin.php file to use is_plugin_active() and deactivate_plugins()
		if ( ! function_exists( 'is_plugin_active' ) ) {
			require_once ABSPATH . 'wp-admin/includes/plugin.php';
		}

		// Define possible plugin paths for the free version
		$free_plugin_paths = array(
			'limb-chatbot-pro/limb-chatbot-pro.php',
		);

		// Check if any free version is active and deactivate it
		foreach ( $free_plugin_paths as $plugin_path ) {
			if ( is_plugin_active( $plugin_path ) ) {
				deactivate_plugins( $plugin_path );
				add_option( 'lbaic.plugin.pro_deactivated', true );
				break;
			}
		}
	}
}
