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

use Evavel\Database\DB;

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

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

	public function __construct() {
		parent::__construct();
		$this->table_name = $this->prefix.'_whatsapp_messages';
		$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,
			provider varchar(20) NOT NULL,
			conversation_id bigint(20) DEFAULT NULL,
			booking_id bigint(20) DEFAULT NULL,
			customer_id bigint(20) DEFAULT NULL,
			reply_to_message_id bigint(20) DEFAULT NULL,

			direction varchar(10) NOT NULL,
			type varchar(50) NOT NULL,

			phone varchar(50) NOT NULL,
			phone_normalized varchar(20) NOT NULL,
			profile_name varchar(255) DEFAULT NULL,

			message_sid varchar(50) DEFAULT NULL,
			account_sid varchar(50) DEFAULT NULL,

			content_sid varchar(50) DEFAULT NULL,
			template_name varchar(255) DEFAULT NULL,
			template_variables longtext DEFAULT NULL,

			body text DEFAULT NULL,

			media_count int unsigned DEFAULT 0,
			media_urls longtext DEFAULT NULL,

			button_payload varchar(255) DEFAULT NULL,
			button_text varchar(255) DEFAULT NULL,
			original_message_sid varchar(50) DEFAULT NULL,

			latitude decimal(10,8) DEFAULT NULL,
			longitude decimal(11,8) DEFAULT NULL,
			location_address varchar(500) DEFAULT NULL,

			status varchar(20) DEFAULT 'pending',
			error_code varchar(20) DEFAULT NULL,
			error_message text DEFAULT NULL,

			sent_at datetime DEFAULT NULL,
			delivered_at datetime DEFAULT NULL,
			read_at datetime DEFAULT NULL,
			failed_at datetime DEFAULT NULL,

			bsuid varchar(150) DEFAULT NULL,
			external_user_id varchar(150) DEFAULT NULL,

			raw_payload longtext DEFAULT NULL,

			date_created datetime NOT NULL,
			date_modified datetime NOT NULL,
			PRIMARY KEY (id),
			KEY `wa_msg_restaurant_id_foreign` (`restaurant_id`),
			CONSTRAINT `wa_msg_restaurant_id_foreign` FOREIGN KEY (`restaurant_id`) REFERENCES `{$this->prefix}_restaurants` (`id`) ON DELETE CASCADE,
			KEY `wa_msg_conversation_id_foreign` (`conversation_id`),
			CONSTRAINT `wa_msg_conversation_id_foreign` FOREIGN KEY (`conversation_id`) REFERENCES `{$this->prefix}_whatsapp_conversations` (`id`) ON DELETE CASCADE,
			KEY `wa_msg_booking_id_foreign` (`booking_id`),
			CONSTRAINT `wa_msg_booking_id_foreign` FOREIGN KEY (`booking_id`) REFERENCES `{$this->prefix}_bookings` (`id`) ON DELETE SET NULL,
			KEY `wa_msg_reply_to_foreign` (`reply_to_message_id`),
			CONSTRAINT `wa_msg_reply_to_foreign` FOREIGN KEY (`reply_to_message_id`) REFERENCES `{$this->prefix}_whatsapp_messages` (`id`) ON DELETE SET NULL,
			UNIQUE KEY `wa_msg_message_sid_unique` (`message_sid`),
			KEY `wa_msg_direction` (`direction`),
			KEY `wa_msg_type` (`type`),
			KEY `wa_msg_status` (`status`),
			KEY `wa_msg_phone_normalized` (`phone_normalized`),
			KEY `wa_msg_date_created` (`date_created`),
			KEY `wa_msg_restaurant_direction` (`restaurant_id`, `direction`),
			KEY `wa_msg_conversation_date` (`conversation_id`, `date_created`)
			)";
	}

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

		// Sender columns (these use standard ADD syntax without IF NOT EXISTS, so they're fine
		// but let's guard them too for idempotency)
		if (!$this->column_exists('sender_type')) {
			$queries['134'][] = "ALTER TABLE {$this->table_name} ADD sender_type varchar(20) DEFAULT 'bot' AFTER status";
		}
		if (!$this->column_exists('sender_user_id')) {
			$queries['134'][] = "ALTER TABLE {$this->table_name} ADD sender_user_id bigint(20) DEFAULT NULL AFTER sender_type";
		}
		if (!$this->column_exists('sender_user_name')) {
			$queries['134'][] = "ALTER TABLE {$this->table_name} ADD sender_user_name varchar(255) DEFAULT NULL AFTER sender_user_id";
		}

		// 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: BSUID support
		if (!$this->column_exists('bsuid')) {
			$queries['134'][] = "ALTER TABLE {$this->table_name} ADD COLUMN `bsuid` varchar(150) DEFAULT NULL AFTER `failed_at`";
		}
		if (!$this->column_exists('external_user_id')) {
			$queries['134'][] = "ALTER TABLE {$this->table_name} ADD COLUMN `external_user_id` varchar(150) DEFAULT NULL AFTER `bsuid`";
		}

		$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_Messages::init();