<?php

namespace SerpIQ\Database;

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

final class Schema {
	public static function install(): void {
		global $wpdb;

		require_once ABSPATH . 'wp-admin/includes/upgrade.php';

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

		$queries   = "{$prefix}queries";
		$pages     = "{$prefix}pages";
		$totals    = "{$prefix}totals";
		$sync_log  = "{$prefix}sync_log";
		$alerts    = "{$prefix}alerts";
		$links     = "{$prefix}links";

		$sql_queries = "CREATE TABLE {$queries} (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			row_key CHAR(32) NOT NULL,
			query VARCHAR(255) NOT NULL,
			page_url TEXT NOT NULL,
			date DATE NOT NULL,
			country CHAR(3) NULL,
			device VARCHAR(20) NULL,
			clicks INT UNSIGNED NOT NULL DEFAULT 0,
			impressions INT UNSIGNED NOT NULL DEFAULT 0,
			ctr DECIMAL(6,5) NOT NULL DEFAULT 0.00000,
			position DECIMAL(6,2) NOT NULL DEFAULT 0.00,
			PRIMARY KEY  (id),
			UNIQUE KEY serpiq_q_row_key (row_key),
			KEY serpiq_q_date (date),
			KEY serpiq_q_query_date (query(191), date),
			KEY serpiq_q_page_date (page_url(191), date)
		) {$charset_collate};";

		$sql_pages = "CREATE TABLE {$pages} (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			row_key CHAR(32) NOT NULL,
			page_url TEXT NOT NULL,
			date DATE NOT NULL,
			country CHAR(3) NULL,
			device VARCHAR(20) NULL,
			clicks INT UNSIGNED NOT NULL DEFAULT 0,
			impressions INT UNSIGNED NOT NULL DEFAULT 0,
			ctr DECIMAL(6,5) NOT NULL DEFAULT 0.00000,
			position DECIMAL(6,2) NOT NULL DEFAULT 0.00,
			PRIMARY KEY  (id),
			UNIQUE KEY serpiq_p_row_key (row_key),
			KEY serpiq_p_date (date),
			KEY serpiq_p_page_date (page_url(191), date)
		) {$charset_collate};";

		$sql_totals = "CREATE TABLE {$totals} (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			row_key CHAR(32) NOT NULL,
			date DATE NOT NULL,
			country CHAR(3) NULL,
			device VARCHAR(20) NULL,
			clicks INT UNSIGNED NOT NULL DEFAULT 0,
			impressions INT UNSIGNED NOT NULL DEFAULT 0,
			ctr DECIMAL(6,5) NOT NULL DEFAULT 0.00000,
			position DECIMAL(6,2) NOT NULL DEFAULT 0.00,
			PRIMARY KEY  (id),
			UNIQUE KEY serpiq_t_row_key (row_key),
			KEY serpiq_t_date (date)
		) {$charset_collate};";

		$sql_sync_log = "CREATE TABLE {$sync_log} (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			created_at DATETIME NOT NULL,
			job_type VARCHAR(30) NOT NULL DEFAULT 'sync',
			sync_start_date DATE NOT NULL,
			sync_end_date DATE NOT NULL,
			rows_imported INT UNSIGNED NOT NULL DEFAULT 0,
			status VARCHAR(20) NOT NULL,
			error_message LONGTEXT NULL,
			duration_ms INT UNSIGNED NULL,
			PRIMARY KEY  (id),
			KEY serpiq_sync_created_at (created_at),
			KEY serpiq_sync_status (status),
			KEY serpiq_sync_job_type (job_type)
		) {$charset_collate};";

		$sql_alerts = "CREATE TABLE {$alerts} (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			alert_type VARCHAR(30) NOT NULL,
			dimension_type VARCHAR(20) NOT NULL,
			dimension_value VARCHAR(500) NOT NULL,
			metric VARCHAR(20) NOT NULL,
			previous_value DECIMAL(12,4) NOT NULL,
			current_value DECIMAL(12,4) NOT NULL,
			period_previous_start DATE NOT NULL,
			period_previous_end DATE NOT NULL,
			period_current_start DATE NOT NULL,
			period_current_end DATE NOT NULL,
			created_at DATETIME NOT NULL,
			PRIMARY KEY  (id),
			KEY serpiq_a_created (created_at),
			KEY serpiq_a_type (alert_type)
		) {$charset_collate};";

		$sql_links = "CREATE TABLE {$links} (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			from_post_id BIGINT UNSIGNED NOT NULL,
			to_post_id BIGINT UNSIGNED NOT NULL,
			from_url TEXT NOT NULL,
			to_url TEXT NOT NULL,
			anchor_text TEXT NULL,
			PRIMARY KEY  (id),
			KEY serpiq_l_from (from_post_id),
			KEY serpiq_l_to (to_post_id),
			KEY serpiq_l_to_url (to_url(191))
		) {$charset_collate};";

		dbDelta( $sql_queries );
		dbDelta( $sql_pages );
		dbDelta( $sql_totals );
		dbDelta( $sql_sync_log );
		dbDelta( $sql_alerts );
		dbDelta( $sql_links );
	}
}

