<?php
if ( ! defined( 'ABSPATH' ) ) exit;

use Evavel\Database\DB;

class SRR_DB_Whatsapp_Conversations extends DB
{
	public static $table_db = '';

	public static function init()
	{
		global $wpdb;
		self::$table_db = $wpdb->prefix.DB::$namespace.'_whatsapp_conversations';
	}

	public function __construct() {
		parent::__construct();
		$this->table_name = $this->prefix.'_whatsapp_conversations';
		$this->primary_key = 'id';
		$this->version     = '1.0';
	}

	public function sql_create()
	{
		return "CREATE TABLE {$this->table_name} (
			id bigint(20) NOT NULL AUTO_INCREMENT,
			restaurant_id bigint(20) NOT NULL,
			customer_id bigint(20) DEFAULT NULL,
			active_booking_id bigint(20) DEFAULT NULL,
			phone varchar(50) NOT NULL,
			phone_normalized varchar(20) NOT NULL,
			profile_name varchar(255) DEFAULT NULL,
			wa_id varchar(50) DEFAULT NULL,
			bsuid varchar(150) DEFAULT NULL,
			status varchar(20) DEFAULT 'open',
			customer_status varchar(30) DEFAULT 'IDLE',
			window_expires_at datetime DEFAULT NULL,
			last_message_at datetime DEFAULT NULL,
			last_inbound_at datetime DEFAULT NULL,
			last_outbound_at datetime DEFAULT NULL,
			unread_count int unsigned DEFAULT 0,
			message_count int unsigned DEFAULT 0,
			date_created datetime NOT NULL,
			date_modified datetime NOT NULL,
			PRIMARY KEY (id),
			KEY `wa_conv_restaurant_id_foreign` (`restaurant_id`),
			CONSTRAINT `wa_conv_restaurant_id_foreign` FOREIGN KEY (`restaurant_id`) REFERENCES `{$this->prefix}_restaurants` (`id`) ON DELETE CASCADE,
			KEY `wa_conv_customer_id_foreign` (`customer_id`),
			CONSTRAINT `wa_conv_customer_id_foreign` FOREIGN KEY (`customer_id`) REFERENCES `{$this->prefix}_customers` (`id`) ON DELETE CASCADE,
			KEY `wa_conv_phone_normalized` (`phone_normalized`),
			UNIQUE KEY `wa_conv_unique_phone` (`restaurant_id`, `phone_normalized`),
			KEY `wa_conv_bsuid` (`bsuid`),
			KEY `wa_conv_status` (`status`),
			KEY `wa_conv_customer_status` (`customer_status`),
			KEY `wa_conv_window_expires` (`window_expires_at`)
			)";
	}

	public function sql_update_columns_releases()
	{
		global $wpdb;
		$queries = [];

		// Para emoticonos
		$queries['134'][] = "ALTER TABLE {$this->table_name} ENGINE=InnoDB";
		$queries['134'][] = "ALTER TABLE {$this->table_name} CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci";

		// V2: Customer-centric conversations
		// Drop foreign key wa_conv_booking_id_foreign if it exists
		$fk_exists = $wpdb->get_var("
        SELECT CONSTRAINT_NAME FROM information_schema.TABLE_CONSTRAINTS
        WHERE TABLE_SCHEMA = DATABASE()
          AND TABLE_NAME = '{$this->table_name}'
          AND CONSTRAINT_NAME = 'wa_conv_booking_id_foreign'
          AND CONSTRAINT_TYPE = 'FOREIGN KEY'
    ");
		if ($fk_exists) {
			$queries['134'][] = "ALTER TABLE {$this->table_name} DROP FOREIGN KEY `wa_conv_booking_id_foreign`";
		}

		// Drop index wa_conv_booking_id_foreign if it exists
		$idx_exists = $wpdb->get_var("SHOW INDEX FROM {$this->table_name} WHERE Key_name = 'wa_conv_booking_id_foreign'");
		if ($idx_exists) {
			$queries['134'][] = "ALTER TABLE {$this->table_name} DROP INDEX `wa_conv_booking_id_foreign`";
		}

		// Drop index wa_conv_unique_booking if it exists
		$idx_exists = $wpdb->get_var("SHOW INDEX FROM {$this->table_name} WHERE Key_name = 'wa_conv_unique_booking'");
		if ($idx_exists) {
			$queries['134'][] = "ALTER TABLE {$this->table_name} DROP INDEX `wa_conv_unique_booking`";
		}

		// Drop column booking_id if it exists
		$col_exists = $wpdb->get_var("
        SELECT COLUMN_NAME FROM information_schema.COLUMNS
        WHERE TABLE_SCHEMA = DATABASE()
          AND TABLE_NAME = '{$this->table_name}'
          AND COLUMN_NAME = 'booking_id'
    ");
		if ($col_exists) {
			$queries['134'][] = "ALTER TABLE {$this->table_name} DROP COLUMN `booking_id`";
		}

		// Add column active_booking_id if not exists
		if (!$this->column_exists('active_booking_id')) {
			$queries['134'][] = "ALTER TABLE {$this->table_name} ADD COLUMN `active_booking_id` bigint(20) DEFAULT NULL AFTER `customer_id`";
		}

		// Add column bsuid if not exists
		if (!$this->column_exists('bsuid')) {
			$queries['134'][] = "ALTER TABLE {$this->table_name} ADD COLUMN `bsuid` varchar(150) DEFAULT NULL AFTER `wa_id`";
		}

		// Add column customer_status if not exists
		if (!$this->column_exists('customer_status')) {
			$queries['134'][] = "ALTER TABLE {$this->table_name} ADD COLUMN `customer_status` varchar(30) DEFAULT 'IDLE' AFTER `status`";
		}

		// Add index wa_conv_bsuid if not exists
		if (!$this->index_exists('wa_conv_bsuid')) {
			$queries['134'][] = "ALTER TABLE {$this->table_name} ADD INDEX `wa_conv_bsuid` (`bsuid`)";
		}

		// Add index wa_conv_customer_status if not exists
		if (!$this->index_exists('wa_conv_customer_status')) {
			$queries['134'][] = "ALTER TABLE {$this->table_name} ADD INDEX `wa_conv_customer_status` (`customer_status`)";
		}

		// Add UNIQUE KEY wa_conv_unique_phone if not exists (prevents duplicate conversations per phone)
		if (!$this->index_exists('wa_conv_unique_phone')) {
			$queries['134'][] = "ALTER TABLE {$this->table_name} ADD UNIQUE KEY `wa_conv_unique_phone` (`restaurant_id`, `phone_normalized`)";
		}

		$this->run_queries_updates($queries);
	}

	private function column_exists($column_name)
	{
		global $wpdb;
		return $wpdb->get_var("
        SELECT COLUMN_NAME FROM information_schema.COLUMNS
        WHERE TABLE_SCHEMA = DATABASE()
          AND TABLE_NAME = '{$this->table_name}'
          AND COLUMN_NAME = '{$column_name}'
    ");
	}

	private function index_exists($index_name)
	{
		global $wpdb;
		return $wpdb->get_var("SHOW INDEX FROM {$this->table_name} WHERE Key_name = '{$index_name}'");
	}
}

SRR_DB_Whatsapp_Conversations::init();