<?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',
			sender_type varchar(20) DEFAULT 'bot',
			sender_user_id bigint(20) DEFAULT NULL,
			sender_user_name varchar(255) DEFAULT NULL,
			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`)
			)"; // ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
	}

	public function sql_update_columns_releases()
	{
		//ray('Actualizando table de messages');
		global $wpdb;

		$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 = 'bsuid'
		");

		if ( ! $col_exists ) {
			$wpdb->query("SET FOREIGN_KEY_CHECKS=0");
			$wpdb->query("DROP TABLE IF EXISTS {$this->table_name}");
			$wpdb->query("SET FOREIGN_KEY_CHECKS=1");
		}

		// No incremental migrations needed — table is always recreated from scratch via sql_create().
	}
}

SRR_DB_Whatsapp_Messages::init();