<?php
/**
 * Database schema and lifecycle.
 *
 * @package Dealroot
 */

namespace Dealroot;

defined( 'ABSPATH' ) || exit;

/**
 * Creates and upgrades the plugin tables.
 */
class Installer {

	const DB_VERSION        = '20';
	const DB_VERSION_OPTION = 'dealroot_db_version';
	const CLEANUP_HOOK      = 'dealroot_daily_cleanup';

	/**
	 * Activation callback.
	 */
	public static function activate() {
		$previous = (string) get_option( self::DB_VERSION_OPTION );

		self::create_tables();

		if ( '' !== $previous && version_compare( $previous, '2', '<' ) ) {
			self::backfill_last_touch();
		}

		if ( '' !== $previous && version_compare( $previous, '6', '<' ) ) {
			self::backfill_devices();
		}

		if ( '' !== $previous && version_compare( $previous, '19', '<' ) ) {
			self::clean_topics();
		}

		if ( '' !== $previous && version_compare( $previous, '20', '<' ) ) {
			Form_Stats::start_counting();
		}

		if ( false === get_option( Settings::OPTION ) ) {
			add_option( Settings::OPTION, Settings::defaults() );
		}

		if ( ! wp_next_scheduled( self::CLEANUP_HOOK ) ) {
			wp_schedule_event( time() + HOUR_IN_SECONDS, 'daily', self::CLEANUP_HOOK );
		}

		Digest::reschedule();
		Morning::reschedule();
		Postcodes::maybe_update();
		Admin\Setup_Page::activated();
	}

	/**
	 * Takes the ways of delivery and payment, e.g. "Abholung", out of the topics read so far.
	 */
	private static function clean_topics() {
		global $wpdb;

		$last = 0;

		do {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$rows = (array) $wpdb->get_results( $wpdb->prepare( "SELECT id, ai_topics FROM %i WHERE id > %d AND ai_topics <> '' ORDER BY id LIMIT 2000", self::table(), $last ) );
			$more = 2000 === count( $rows );

			foreach ( $rows as $row ) {
				$last   = (int) $row->id;
				$topics = Ai::clean_topics( explode( '|', (string) $row->ai_topics ) );
				$clean  = implode( '|', $topics );

				if ( $clean !== (string) $row->ai_topics ) {
					// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
					$wpdb->update( self::table(), array( 'ai_topics' => $clean ), array( 'id' => $last ) );
				}
			}
		} while ( $more );
	}

	/**
	 * Deactivation callback.
	 */
	public static function deactivate() {
		wp_clear_scheduled_hook( self::CLEANUP_HOOK );
		wp_clear_scheduled_hook( Digest::HOOK );
		wp_clear_scheduled_hook( Morning::HOOK );
		wp_clear_scheduled_hook( Inbox::HOOK );
		wp_clear_scheduled_hook( Geo::UPDATE_HOOK );
		wp_clear_scheduled_hook( Geo_Maxmind::UPDATE_HOOK );
		wp_clear_scheduled_hook( Postcodes::UPDATE_HOOK );
		wp_unschedule_hook( Ai::ANALYZE_HOOK );
	}

	/**
	 * Runs schema changes after an update without reactivation.
	 */
	public static function maybe_upgrade() {
		if ( get_option( self::DB_VERSION_OPTION ) !== self::DB_VERSION ) {
			self::activate();
		}
	}

	/**
	 * Leads table name.
	 *
	 * @return string
	 */
	public static function table() {
		global $wpdb;

		return $wpdb->prefix . 'dealroot_leads';
	}

	/**
	 * Contact clicks table name.
	 *
	 * @return string
	 */
	public static function events_table() {
		global $wpdb;

		return $wpdb->prefix . 'dealroot_events';
	}

	/**
	 * Visits table name.
	 *
	 * @return string
	 */
	public static function visits_table() {
		global $wpdb;

		return $wpdb->prefix . 'dealroot_visits';
	}

	/**
	 * Form analytics table name.
	 *
	 * @return string
	 */
	public static function forms_table() {
		global $wpdb;

		return $wpdb->prefix . 'dealroot_forms';
	}

	/**
	 * Creates or updates the schema.
	 */
	private static function create_tables() {
		global $wpdb;

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

		$leads   = self::table();
		$events  = self::events_table();
		$visits  = self::visits_table();
		$forms   = self::forms_table();
		$charset = $wpdb->get_charset_collate();

		dbDelta(
			"CREATE TABLE {$leads} (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			created_at datetime NOT NULL,
			updated_at datetime NOT NULL,
			form_plugin varchar(32) NOT NULL DEFAULT '',
			form_id varchar(64) NOT NULL DEFAULT '',
			form_name varchar(191) NOT NULL DEFAULT '',
			name varchar(191) NOT NULL DEFAULT '',
			email varchar(191) NOT NULL DEFAULT '',
			phone varchar(64) NOT NULL DEFAULT '',
			fields longtext NOT NULL,
			status varchar(20) NOT NULL DEFAULT 'new',
			value decimal(14,2) DEFAULT NULL,
			margin decimal(7,4) DEFAULT NULL,
			currency char(3) NOT NULL DEFAULT '',
			won_at datetime DEFAULT NULL,
			contacted_at datetime DEFAULT NULL,
			note text NOT NULL,
			channel varchar(32) NOT NULL DEFAULT '',
			source varchar(191) NOT NULL DEFAULT '',
			medium varchar(191) NOT NULL DEFAULT '',
			campaign varchar(191) NOT NULL DEFAULT '',
			last_channel varchar(32) NOT NULL DEFAULT '',
			last_source varchar(191) NOT NULL DEFAULT '',
			last_medium varchar(191) NOT NULL DEFAULT '',
			last_campaign varchar(191) NOT NULL DEFAULT '',
			gclid varchar(191) NOT NULL DEFAULT '',
			gbraid varchar(191) NOT NULL DEFAULT '',
			wbraid varchar(191) NOT NULL DEFAULT '',
			fbclid varchar(191) NOT NULL DEFAULT '',
			msclkid varchar(191) NOT NULL DEFAULT '',
			landing_page text NOT NULL,
			attribution longtext NOT NULL,
			clarity varchar(120) NOT NULL DEFAULT '',
			device varchar(10) NOT NULL DEFAULT '',
			browser varchar(40) NOT NULL DEFAULT '',
			country char(2) NOT NULL DEFAULT '',
			city varchar(100) NOT NULL DEFAULT '',
			lat decimal(5,2) NOT NULL DEFAULT 0,
			lon decimal(6,2) NOT NULL DEFAULT 0,
			geo varchar(8) NOT NULL DEFAULT '',
			ai_summary varchar(255) NOT NULL DEFAULT '',
			ai_topics varchar(255) NOT NULL DEFAULT '',
			ai_urgent tinyint(1) NOT NULL DEFAULT 0,
			ai_kind varchar(10) NOT NULL DEFAULT '',
			ai_state varchar(10) NOT NULL DEFAULT '',
			ai_budget decimal(14,2) DEFAULT NULL,
			ai_currency char(3) NOT NULL DEFAULT '',
			ai_details varchar(255) NOT NULL DEFAULT '',
			ai_confirmed_at datetime DEFAULT NULL,
			ai_confirmation text,
			answered_via varchar(12) NOT NULL DEFAULT '',
			ai_lang char(2) NOT NULL DEFAULT '',
			ai_abusive tinyint(1) NOT NULL DEFAULT 0,
			ai_translation text,
			privacy_request varchar(10) NOT NULL DEFAULT '',
			privacy_request_id bigint(20) unsigned NOT NULL DEFAULT 0,
			lost_reason varchar(12) NOT NULL DEFAULT '',
			ai_draft text,
			ai_draft_at datetime DEFAULT NULL,
			unread tinyint(1) NOT NULL DEFAULT 0,
			last_message_at datetime DEFAULT NULL,
			PRIMARY KEY  (id),
			KEY created_at (created_at),
			KEY status (status),
			KEY channel (channel),
			KEY email (email),
			KEY phone (phone),
			KEY status_value (status,value),
			KEY unread_created (unread,created_at),
			KEY unread_message (unread,last_message_at)
		) {$charset};"
		);

		dbDelta(
			"CREATE TABLE {$events} (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			created_at datetime NOT NULL,
			type varchar(20) NOT NULL DEFAULT '',
			target varchar(191) NOT NULL DEFAULT '',
			page varchar(255) NOT NULL DEFAULT '',
			channel varchar(32) NOT NULL DEFAULT '',
			source varchar(191) NOT NULL DEFAULT '',
			medium varchar(191) NOT NULL DEFAULT '',
			campaign varchar(191) NOT NULL DEFAULT '',
			last_channel varchar(32) NOT NULL DEFAULT '',
			last_source varchar(191) NOT NULL DEFAULT '',
			last_medium varchar(191) NOT NULL DEFAULT '',
			last_campaign varchar(191) NOT NULL DEFAULT '',
			landing_page varchar(255) NOT NULL DEFAULT '',
			gclid varchar(191) NOT NULL DEFAULT '',
			gbraid varchar(191) NOT NULL DEFAULT '',
			wbraid varchar(191) NOT NULL DEFAULT '',
			fbclid varchar(191) NOT NULL DEFAULT '',
			msclkid varchar(191) NOT NULL DEFAULT '',
			lead_id bigint(20) unsigned NOT NULL DEFAULT 0,
			PRIMARY KEY  (id),
			KEY created_at (created_at),
			KEY type (type),
			KEY channel (channel)
		) {$charset};"
		);

		dbDelta(
			"CREATE TABLE {$visits} (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			day date NOT NULL,
			hash char(32) NOT NULL,
			channel varchar(32) NOT NULL DEFAULT '',
			source varchar(191) NOT NULL DEFAULT '',
			medium varchar(191) NOT NULL DEFAULT '',
			campaign varchar(191) NOT NULL DEFAULT '',
			landing_page varchar(191) NOT NULL DEFAULT '',
			device varchar(10) NOT NULL DEFAULT '',
			browser varchar(40) NOT NULL DEFAULT '',
			country char(2) NOT NULL DEFAULT '',
			city varchar(100) NOT NULL DEFAULT '',
			lat decimal(5,2) NOT NULL DEFAULT 0,
			lon decimal(6,2) NOT NULL DEFAULT 0,
			visits int(10) unsigned NOT NULL DEFAULT 0,
			PRIMARY KEY  (id),
			UNIQUE KEY hash (hash),
			KEY day (day)
		) {$charset};"
		);

		dbDelta(
			"CREATE TABLE {$forms} (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			day date NOT NULL,
			hash char(32) NOT NULL,
			form varchar(191) NOT NULL DEFAULT '',
			field varchar(191) NOT NULL DEFAULT '',
			starts int(10) unsigned NOT NULL DEFAULT 0,
			sent int(10) unsigned NOT NULL DEFAULT 0,
			left_at int(10) unsigned NOT NULL DEFAULT 0,
			PRIMARY KEY  (id),
			UNIQUE KEY hash (hash),
			KEY day (day)
		) {$charset};"
		);

		$messages = Messages::table();

		dbDelta(
			"CREATE TABLE {$messages} (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			lead_id bigint(20) unsigned NOT NULL,
			created_at datetime NOT NULL,
			direction char(3) NOT NULL DEFAULT 'out',
			channel varchar(12) NOT NULL DEFAULT '',
			user_id bigint(20) unsigned NOT NULL DEFAULT 0,
			subject varchar(191) NOT NULL DEFAULT '',
			body text,
			files text,
			PRIMARY KEY  (id),
			KEY lead_id (lead_id),
			KEY lead_out (lead_id,direction,created_at)
		) {$charset};"
		);

		$live = Live::table();

		dbDelta(
			"CREATE TABLE {$live} (
			id char(32) NOT NULL,
			ref char(12) NOT NULL DEFAULT '',
			started datetime NOT NULL,
			updated datetime NOT NULL,
			page varchar(191) NOT NULL DEFAULT '',
			title varchar(191) NOT NULL DEFAULT '',
			form varchar(191) NOT NULL DEFAULT '',
			state varchar(10) NOT NULL DEFAULT '',
			pages smallint(5) unsigned NOT NULL DEFAULT 1,
			channel varchar(32) NOT NULL DEFAULT '',
			source varchar(191) NOT NULL DEFAULT '',
			device varchar(10) NOT NULL DEFAULT '',
			browser varchar(40) NOT NULL DEFAULT '',
			country char(2) NOT NULL DEFAULT '',
			city varchar(100) NOT NULL DEFAULT '',
			lat decimal(5,2) NOT NULL DEFAULT 0,
			lon decimal(6,2) NOT NULL DEFAULT 0,
			nudge varchar(100) NOT NULL DEFAULT '',
			nudge_state varchar(8) NOT NULL DEFAULT '',
			push varchar(20) NOT NULL DEFAULT '',
			PRIMARY KEY  (id),
			KEY updated (updated),
			KEY ref (ref)
		) {$charset};"
		);

		update_option( self::DB_VERSION_OPTION, self::DB_VERSION, true );
		self::preload_options();
	}

	/**
	 * Options read on every page of the site are loaded with the others in one query. One that
	 * does not exist yet would cost a query of its own each time, so it is added empty.
	 */
	private static function preload_options() {
		if ( false === get_option( Form_Selection::OPTION ) ) {
			add_option( Form_Selection::OPTION, array(), '', true );
		}

		wp_set_option_autoload_values(
			array(
				self::DB_VERSION_OPTION => true,
				Form_Selection::OPTION  => true,
			)
		);
	}

	/**
	 * Fills the device and browser columns of leads stored before version 6 from the device
	 * kept with their attribution.
	 */
	private static function backfill_devices() {
		global $wpdb;

		$table = self::table();

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$rows = $wpdb->get_results( $wpdb->prepare( "SELECT id, attribution FROM %i WHERE device = '' AND attribution LIKE %s LIMIT 5000", $table, '%' . $wpdb->esc_like( '"device"' ) . '%' ) );

		foreach ( (array) $rows as $row ) {
			$attribution = json_decode( (string) $row->attribution, true );
			$device      = Devices::sanitize( is_array( $attribution ) ? ( $attribution['device'] ?? null ) : null );

			if ( $device ) {
				$wpdb->update(
					$table,
					array(
						'device'  => $device['type'],
						'browser' => $device['browser'],
					),
					array( 'id' => (int) $row->id )
				);
			}
		}
		// phpcs:enable
	}

	/**
	 * Fills the last-touch columns of leads stored before version 2.
	 */
	private static function backfill_last_touch() {
		global $wpdb;

		$table = self::table();

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$rows = $wpdb->get_results( $wpdb->prepare( "SELECT id, attribution FROM %i WHERE attribution <> '' AND last_source = ''", $table ) );

		foreach ( (array) $rows as $row ) {
			$attribution = json_decode( (string) $row->attribution, true );
			if ( empty( $attribution['last'] ) || ! is_array( $attribution['last'] ) ) {
				continue;
			}

			$wpdb->update(
				$table,
				array(
					'last_source'   => Attribution::source( $attribution['last'] ),
					'last_medium'   => Attribution::medium( $attribution['last'] ),
					'last_campaign' => $attribution['last']['utm_campaign'] ?? '',
				),
				array( 'id' => (int) $row->id )
			);
		}
		// phpcs:enable
	}
}