<?php
/**
 * Handles creation and upgrading of custom DB tables.
 */

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

class CPECS_Install {

	public static function create_tables() {
		global $wpdb;

		$charset_collate = $wpdb->get_charset_collate();

		$conversations_table = $wpdb->prefix . 'cpecs_conversations';
		$messages_table      = $wpdb->prefix . 'cpecs_messages';

		$sql = "
CREATE TABLE {$conversations_table} (
	id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
	visitor_uuid VARCHAR(64) NOT NULL,
	visitor_name VARCHAR(191) DEFAULT NULL,
	visitor_email VARCHAR(191) DEFAULT NULL,
	visitor_ip VARCHAR(100) DEFAULT NULL,
	page_url TEXT DEFAULT NULL,
	referrer TEXT DEFAULT NULL,
	user_agent TEXT DEFAULT NULL,
	status VARCHAR(20) NOT NULL DEFAULT 'open',
	agent_id BIGINT UNSIGNED DEFAULT NULL,
	created_at DATETIME NOT NULL,
	updated_at DATETIME NOT NULL,
	closed_at DATETIME DEFAULT NULL,
	PRIMARY KEY  (id),
	KEY visitor_uuid (visitor_uuid),
	KEY status (status),
	KEY updated_at (updated_at)
) {$charset_collate};

CREATE TABLE {$messages_table} (
	id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
	conversation_id BIGINT UNSIGNED NOT NULL,
	sender_type VARCHAR(20) NOT NULL DEFAULT 'visitor',
	sender_id BIGINT UNSIGNED DEFAULT NULL,
	message LONGTEXT NOT NULL,
	created_at DATETIME NOT NULL,
	read_at DATETIME DEFAULT NULL,
	PRIMARY KEY  (id),
	KEY conversation_id (conversation_id),
	KEY created_at (created_at)
) {$charset_collate};
";

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