<?php
/**
 * Traffic analysis table schema.
 *
 * @package Niwa_AI
 */

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

class Niwa_AI_Traffic_DB {

	const SCHEMA_OPTION = 'niwa_ai_traffic_schema_stamp';
	const SCHEMA_STAMP  = 'traffic-v1';
	const EVENTS        = 'niwa_ai_traffic_events';
	const SESSIONS      = 'niwa_ai_traffic_sessions';
	const TOUCHPOINTS   = 'niwa_ai_traffic_touchpoints';
	const CONVERSIONS   = 'niwa_ai_traffic_conversions';
	const DAILY         = 'niwa_ai_traffic_daily_rollups';

	public static function table( $slug ) {
		global $wpdb;
		return $wpdb->prefix . $slug;
	}

	public static function ensure_schema( $force = false ) {
		if ( ! $force && self::SCHEMA_STAMP === get_option( self::SCHEMA_OPTION, '' ) ) {
			return;
		}

		global $wpdb;
		$charset = $wpdb->get_charset_collate();

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

		dbDelta(
			"CREATE TABLE " . self::table( self::EVENTS ) . " (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				event_id varchar(96) NOT NULL DEFAULT '',
				event_type varchar(60) NOT NULL DEFAULT '',
				session_id varchar(96) NOT NULL DEFAULT '',
				visitor_id varchar(96) NOT NULL DEFAULT '',
				occurred_at datetime NOT NULL,
				received_at datetime NOT NULL,
				page_url text NULL,
				page_path varchar(255) NOT NULL DEFAULT '',
				page_title varchar(255) NOT NULL DEFAULT '',
				referrer_url text NULL,
				referrer_host varchar(191) NOT NULL DEFAULT '',
				source varchar(80) NOT NULL DEFAULT '',
				medium varchar(80) NOT NULL DEFAULT '',
				campaign varchar(191) NOT NULL DEFAULT '',
				term varchar(191) NOT NULL DEFAULT '',
				content varchar(191) NOT NULL DEFAULT '',
				click_id_name varchar(40) NOT NULL DEFAULT '',
				click_id_value varchar(191) NOT NULL DEFAULT '',
				channel varchar(60) NOT NULL DEFAULT '',
				device_type varchar(30) NOT NULL DEFAULT '',
				user_agent_hash char(64) NOT NULL DEFAULT '',
				ip_hash char(64) NOT NULL DEFAULT '',
				value decimal(14,2) DEFAULT NULL,
				order_id bigint(20) unsigned DEFAULT NULL,
				meta longtext NULL,
				PRIMARY KEY (id),
				UNIQUE KEY event_id (event_id),
				KEY occurred_at (occurred_at),
				KEY event_type_time (event_type, occurred_at),
				KEY session_id (session_id),
				KEY visitor_id (visitor_id),
				KEY channel_time (channel, occurred_at),
				KEY source_medium (source, medium),
				KEY campaign (campaign),
				KEY page_path (page_path(191)),
				KEY order_id (order_id)
			) {$charset};"
		);

		dbDelta(
			"CREATE TABLE " . self::table( self::SESSIONS ) . " (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				session_id varchar(96) NOT NULL DEFAULT '',
				visitor_id varchar(96) NOT NULL DEFAULT '',
				first_seen_at datetime NOT NULL,
				last_seen_at datetime NOT NULL,
				landing_page_url text NULL,
				landing_page_path varchar(255) NOT NULL DEFAULT '',
				referrer_host varchar(191) NOT NULL DEFAULT '',
				first_source varchar(80) NOT NULL DEFAULT '',
				first_medium varchar(80) NOT NULL DEFAULT '',
				first_campaign varchar(191) NOT NULL DEFAULT '',
				first_channel varchar(60) NOT NULL DEFAULT '',
				last_source varchar(80) NOT NULL DEFAULT '',
				last_medium varchar(80) NOT NULL DEFAULT '',
				last_campaign varchar(191) NOT NULL DEFAULT '',
				last_channel varchar(60) NOT NULL DEFAULT '',
				device_type varchar(30) NOT NULL DEFAULT '',
				pageviews int(11) NOT NULL DEFAULT 0,
				engaged_events int(11) NOT NULL DEFAULT 0,
				converted tinyint(1) NOT NULL DEFAULT 0,
				revenue decimal(14,2) NOT NULL DEFAULT 0,
				updated_at datetime NOT NULL,
				PRIMARY KEY (id),
				UNIQUE KEY session_id (session_id),
				KEY visitor_id (visitor_id),
				KEY first_seen_at (first_seen_at),
				KEY last_seen_at (last_seen_at),
				KEY first_channel (first_channel),
				KEY last_channel (last_channel),
				KEY landing_page_path (landing_page_path(191))
			) {$charset};"
		);

		dbDelta(
			"CREATE TABLE " . self::table( self::TOUCHPOINTS ) . " (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				session_id varchar(96) NOT NULL DEFAULT '',
				visitor_id varchar(96) NOT NULL DEFAULT '',
				event_id varchar(96) NOT NULL DEFAULT '',
				occurred_at datetime NOT NULL,
				event_type varchar(60) NOT NULL DEFAULT '',
				page_path varchar(255) NOT NULL DEFAULT '',
				source varchar(80) NOT NULL DEFAULT '',
				medium varchar(80) NOT NULL DEFAULT '',
				campaign varchar(191) NOT NULL DEFAULT '',
				channel varchar(60) NOT NULL DEFAULT '',
				PRIMARY KEY (id),
				UNIQUE KEY event_id (event_id),
				KEY session_time (session_id, occurred_at),
				KEY channel_time (channel, occurred_at),
				KEY campaign (campaign)
			) {$charset};"
		);

		dbDelta(
			"CREATE TABLE " . self::table( self::CONVERSIONS ) . " (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				conversion_id varchar(96) NOT NULL DEFAULT '',
				conversion_type varchar(60) NOT NULL DEFAULT '',
				session_id varchar(96) NOT NULL DEFAULT '',
				visitor_id varchar(96) NOT NULL DEFAULT '',
				order_id bigint(20) unsigned DEFAULT NULL,
				occurred_at datetime NOT NULL,
				source varchar(80) NOT NULL DEFAULT '',
				medium varchar(80) NOT NULL DEFAULT '',
				campaign varchar(191) NOT NULL DEFAULT '',
				channel varchar(60) NOT NULL DEFAULT '',
				landing_page_path varchar(255) NOT NULL DEFAULT '',
				revenue decimal(14,2) NOT NULL DEFAULT 0,
				currency varchar(10) NOT NULL DEFAULT '',
				status varchar(40) NOT NULL DEFAULT '',
				meta longtext NULL,
				PRIMARY KEY (id),
				UNIQUE KEY conversion_id (conversion_id),
				KEY order_id (order_id),
				KEY session_id (session_id),
				KEY occurred_at (occurred_at),
				KEY channel_time (channel, occurred_at),
				KEY campaign (campaign),
				KEY landing_page_path (landing_page_path(191))
			) {$charset};"
		);

		dbDelta(
			"CREATE TABLE " . self::table( self::DAILY ) . " (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				rollup_date date NOT NULL,
				dimension_type varchar(40) NOT NULL DEFAULT '',
				dimension_value varchar(191) NOT NULL DEFAULT '',
				channel varchar(60) NOT NULL DEFAULT '',
				source varchar(80) NOT NULL DEFAULT '',
				medium varchar(80) NOT NULL DEFAULT '',
				campaign varchar(191) NOT NULL DEFAULT '',
				page_path varchar(255) NOT NULL DEFAULT '',
				sessions int(11) NOT NULL DEFAULT 0,
				visitors int(11) NOT NULL DEFAULT 0,
				pageviews int(11) NOT NULL DEFAULT 0,
				events int(11) NOT NULL DEFAULT 0,
				engaged_events int(11) NOT NULL DEFAULT 0,
				conversions int(11) NOT NULL DEFAULT 0,
				orders int(11) NOT NULL DEFAULT 0,
				revenue decimal(14,2) NOT NULL DEFAULT 0,
				created_at datetime NOT NULL,
				updated_at datetime NOT NULL,
				PRIMARY KEY (id),
				UNIQUE KEY rollup_dimension (rollup_date, dimension_type, dimension_value),
				KEY dimension_type (dimension_type),
				KEY channel (channel),
				KEY source_medium (source, medium),
				KEY campaign (campaign),
				KEY page_path (page_path(191))
			) {$charset};"
		);

		update_option( self::SCHEMA_OPTION, self::SCHEMA_STAMP, false );
	}
}
