<?php
/**
 * MindWebConcierge Database Table Definitions
 *
 * @package MindWebConcierge
 */

if ( ! defined( 'ABSPATH' ) ) {
	exit;
}


function mindweco_items_table() {
	global $wpdb;

	$table_name      = $wpdb->prefix . 'mindweco_indexed_items';
	$charset_collate = $wpdb->get_charset_collate();

	$sql = "CREATE TABLE {$table_name} (
		id BIGINT NOT NULL AUTO_INCREMENT,
		post_id BIGINT NOT NULL,
		item_id VARCHAR(255) NOT NULL DEFAULT '',
		category_id BIGINT NOT NULL,
		status VARCHAR(20) NOT NULL DEFAULT 'indexed',
		created_at DATETIME NOT NULL,
		updated_at DATETIME NULL,
		PRIMARY KEY  (id),
		UNIQUE KEY unique_post (post_id)
	) {$charset_collate};";

	require_once ABSPATH . 'wp-admin/includes/upgrade.php';
	dbDelta( $sql );
}

function mindweco_create_conversations() {
	global $wpdb;

	$table_name      = $wpdb->prefix . 'mindweco_conversations';
	$charset_collate = $wpdb->get_charset_collate();

	$sql = "CREATE TABLE {$table_name} (
		id VARCHAR(36) NOT NULL,
		cookie_id VARCHAR(255) NOT NULL,
		ip_address VARCHAR(45) NOT NULL,
		screen_resolution VARCHAR(50) NULL,
		user_agent TEXT NULL,
		language_accepted VARCHAR(50) NULL,
		referrer TEXT NULL,
		timezone VARCHAR(64) NULL,
		platform VARCHAR(100) NULL,
		is_cleared TINYINT(1) NOT NULL DEFAULT 0,
		created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
		PRIMARY KEY  (id)
	) {$charset_collate};";

	require_once ABSPATH . 'wp-admin/includes/upgrade.php';
	dbDelta( $sql );
}

function mindweco_create_questions() {
	global $wpdb;

	$table_name      = $wpdb->prefix . 'mindweco_questions';
	$charset_collate = $wpdb->get_charset_collate();

	$sql = "CREATE TABLE {$table_name} (
		id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
		conversation_id VARCHAR(36) NOT NULL,
		question_text TEXT NOT NULL,
		page_id VARCHAR(255) NULL,
		question_page_based TEXT NULL,
		created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
		PRIMARY KEY  (id),
		KEY conversation_id (conversation_id)
	) {$charset_collate};";

	require_once ABSPATH . 'wp-admin/includes/upgrade.php';
	dbDelta( $sql );
}

function mindweco_create_responses() {
	global $wpdb;

	$table_name      = $wpdb->prefix . 'mindweco_responses';
	$charset_collate = $wpdb->get_charset_collate();

	$sql = "CREATE TABLE {$table_name} (
		id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
		question_id BIGINT UNSIGNED NOT NULL,
		response_text TEXT NOT NULL,
		answer_status TINYINT(1) NOT NULL DEFAULT 0,
		created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
		PRIMARY KEY  (id),
		KEY question_id (question_id)
	) {$charset_collate};";

	require_once ABSPATH . 'wp-admin/includes/upgrade.php';
	dbDelta( $sql );
}

function mindweco_ensure_responses_answer_status_column() {
	global $wpdb;

	$table_name = $wpdb->prefix . 'mindweco_responses';

	$table_exists = $wpdb->get_var(
		$wpdb->prepare( 'SHOW TABLES LIKE %s', $table_name )
	);

	if ( $table_exists !== $table_name ) {
		mindweco_create_responses();
		return;
	}

	$column_exists = $wpdb->get_var(
		$wpdb->prepare( "SHOW COLUMNS FROM `{$table_name}` LIKE %s", 'answer_status' ) // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name is derived from $wpdb->prefix, not user input.
	);

	if ( ! $column_exists ) {
		$wpdb->query( "ALTER TABLE `{$table_name}` ADD `answer_status` TINYINT(1) NOT NULL DEFAULT 0 AFTER `response_text`" ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name is derived from $wpdb->prefix, not user input.
	}
}

function mindweco_products_indexed_table() {
	global $wpdb;

	$table_name      = $wpdb->prefix . 'mindweco_indexed_products';
	$charset_collate = $wpdb->get_charset_collate();

	$sql = "CREATE TABLE {$table_name} (
		id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
		item_id VARCHAR(191) DEFAULT NULL,
		product_id BIGINT(20) NOT NULL,
		post_id BIGINT(20) NOT NULL,
		category_id BIGINT(20) NOT NULL,
		created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
		PRIMARY KEY  (id),
		KEY post_id (post_id),
		UNIQUE KEY unique_product (product_id)
	) {$charset_collate};";

	require_once ABSPATH . 'wp-admin/includes/upgrade.php';
	dbDelta( $sql );
}

function mindweco_media_files_indexed_table() {
	global $wpdb;
	
	$table_name = $wpdb->prefix . 'mindweco_indexed_media_files';
	$charset_collate = $wpdb->get_charset_collate();

	$sql = "CREATE TABLE {$table_name} (
		id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
		item_id VARCHAR(191) DEFAULT NULL,
		file_id BIGINT(20) NOT NULL,
		category_id BIGINT(20) NOT NULL,
		created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
		PRIMARY KEY (id),
		KEY file_id (file_id)
	) {$charset_collate};";

	require_once ABSPATH . 'wp-admin/includes/upgrade.php';
	dbDelta($sql);
}
