<?php
/**
 * Local link store — the free, no-account tier.
 *
 * Links live in a table in this WordPress install and redirect from the site's OWN domain
 * (site.com/go/slug → 302), counting clicks locally. Nothing here needs a panel account or the
 * network: cloaking + click counting work standalone. When the user later connects an account,
 * these rows are synced up (each gets a `remote_id`) and the panel adds monitoring on top — but the
 * redirect always stays local so links already placed in posts never break.
 *
 * @package AffKeeper
 */

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

// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery, PluginCheck.Security.DirectDB -- this class manages the plugin's own custom tables. Table names come from self::table()/self::daily_table() ($wpdb->prefix + literal, never user input), every value goes through $wpdb->prepare(), and WordPress has no higher-level API for custom tables.

class AffKeeper_Local {

	const QUERY_VAR = 'affkeeper_go';
	const DEFAULT_PREFIX = 'go';

	private static $instance = null;

	public static function instance() {
		if ( null === self::$instance ) {
			self::$instance = new self();
		}
		return self::$instance;
	}

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

	public static function daily_table() {
		global $wpdb;
		return $wpdb->prefix . 'affkeeper_clicks_daily';
	}

	/**
	 * The path prefix for local short links (site.com/<prefix>/slug). Filterable.
	 *
	 * @return string
	 */
	public static function prefix() {
		$p = (string) get_option( 'affkeeper_local_prefix', self::DEFAULT_PREFIX );
		$p = trim( sanitize_title( $p ) );
		return '' !== $p ? $p : self::DEFAULT_PREFIX;
	}

	/**
	 * Why the given prefix would clash with something already on this site, or '' if it's safe.
	 *
	 * Our rewrite rule is registered 'top', so it shadows anything else under /<prefix>/… — a prefix
	 * like "shop" would hijack WooCommerce product URLs, "blog" would swallow posts. We check the
	 * common collisions before letting the user commit to a prefix.
	 *
	 * @param string $prefix Candidate prefix (raw; sanitised here).
	 * @return string Human-readable reason, or '' when there's no conflict.
	 */
	public static function prefix_conflict( $prefix ) {
		$prefix = trim( sanitize_title( $prefix ) );
		if ( '' === $prefix ) {
			return '';
		}

		// 1. Slugs WordPress core keeps for itself.
		$reserved = array( 'wp-admin', 'wp-content', 'wp-includes', 'wp-json', 'feed', 'rss', 'rss2', 'atom', 'author', 'category', 'tag', 'page', 'comments', 'search', 'embed', 'trackback' );
		if ( in_array( $prefix, $reserved, true ) ) {
			/* translators: %s: the prefix the user tried to use. */
			return sprintf( __( '“%s” is reserved by WordPress. Pick another prefix.', 'affkeeper' ), $prefix );
		}

		// 2. A page/post/product that already lives at /<prefix>/.
		$existing = get_posts(
			array(
				'name'             => $prefix,
				'post_type'        => 'any',
				'post_status'      => array( 'publish', 'future', 'draft', 'pending', 'private' ),
				'numberposts'      => 1,
				'fields'           => 'ids',
			)
		);
		if ( ! empty( $existing ) ) {
			/* translators: %s: the prefix the user tried to use. */
			return sprintf( __( 'A page or post already uses “/%s/”. Pick another prefix.', 'affkeeper' ), $prefix );
		}

		// 3. A registered content type or taxonomy whose URL base is that prefix (e.g. WooCommerce
		//    "product", "shop", "product-category").
		foreach ( get_post_types( array( 'public' => true ), 'objects' ) as $pt ) {
			$slug = is_array( $pt->rewrite ) && ! empty( $pt->rewrite['slug'] ) ? $pt->rewrite['slug'] : $pt->name;
			if ( $slug && $prefix === trim( explode( '/', $slug )[0] ) ) {
				/* translators: 1: the prefix, 2: the conflicting content type. */
				return sprintf( __( 'The “%2$s” content type already uses “/%1$s/”. Pick another prefix.', 'affkeeper' ), $prefix, $pt->name );
			}
		}
		foreach ( get_taxonomies( array( 'public' => true ), 'objects' ) as $tx ) {
			$slug = is_array( $tx->rewrite ) && ! empty( $tx->rewrite['slug'] ) ? $tx->rewrite['slug'] : '';
			if ( $slug && $prefix === trim( explode( '/', $slug )[0] ) ) {
				/* translators: 1: the prefix, 2: the conflicting taxonomy. */
				return sprintf( __( 'The “%2$s” taxonomy already uses “/%1$s/”. Pick another prefix.', 'affkeeper' ), $prefix, $tx->name );
			}
		}

		// 4. Another plugin's rewrite rule already anchored on that base (best-effort — ours excluded).
		$rules = get_option( 'rewrite_rules' );
		if ( is_array( $rules ) ) {
			foreach ( $rules as $regex => $target ) {
				if ( 0 === strpos( (string) $regex, '^' . $prefix . '/' ) && false === strpos( (string) $target, self::QUERY_VAR ) ) {
					/* translators: %s: the prefix the user tried to use. */
					return sprintf( __( 'Another plugin already handles “/%s/”. Pick another prefix.', 'affkeeper' ), $prefix );
				}
			}
		}

		return '';
	}

	public function hooks() {
		add_action( 'init', array( $this, 'add_rewrite' ) );
		add_filter( 'query_vars', array( $this, 'query_vars' ) );
		add_action( 'template_redirect', array( $this, 'handle_redirect' ), 0 );
	}

	// --- schema (created on activation) ---

	public static function install() {
		global $wpdb;
		require_once ABSPATH . 'wp-admin/includes/upgrade.php';
		$table   = self::table();
		$charset = $wpdb->get_charset_collate();
		// dbDelta is picky: two spaces after PRIMARY KEY, KEY names lowercased.
		$sql = "CREATE TABLE $table (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			slug varchar(190) NOT NULL,
			destination text NOT NULL,
			title varchar(255) NOT NULL DEFAULT '',
			is_affiliate tinyint(1) NOT NULL DEFAULT 0,
			monitored tinyint(1) NOT NULL DEFAULT 1,
			clicks bigint(20) unsigned NOT NULL DEFAULT 0,
			remote_id varchar(64) NOT NULL DEFAULT '',
			health varchar(20) NOT NULL DEFAULT '',
			source_url text NULL,
			created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
			PRIMARY KEY  (id),
			UNIQUE KEY slug (slug),
			KEY remote_id (remote_id)
		) $charset;";
		dbDelta( $sql );
		// Per-day click counts — lets every screen show a windowed number (last 30 days) while the
		// cumulative `clicks` column stays the total that syncs up to the panel.
		$daily = self::daily_table();
		$sql2  = "CREATE TABLE $daily (
			link_id bigint(20) unsigned NOT NULL,
			day date NOT NULL,
			clicks bigint(20) unsigned NOT NULL DEFAULT 0,
			PRIMARY KEY  (link_id,day)
		) $charset;";
		dbDelta( $sql2 );
		self::instance()->add_rewrite();
		flush_rewrite_rules();
	}

	// --- routing ---

	public function add_rewrite() {
		add_rewrite_rule( '^' . self::prefix() . '/([^/]+)/?$', 'index.php?' . self::QUERY_VAR . '=$matches[1]', 'top' );
		// The prefix was just changed in Settings: now that the rule for the NEW prefix is registered
		// (this runs on init), persist it. Flag-gated so we flush once, not on every request.
		if ( get_option( 'affkeeper_flush_needed' ) ) {
			delete_option( 'affkeeper_flush_needed' );
			flush_rewrite_rules();
		}
	}

	public function query_vars( $vars ) {
		$vars[] = self::QUERY_VAR;
		return $vars;
	}

	/**
	 * Serve a local short link: look up the slug, count the click, 302 to its destination.
	 */
	public function handle_redirect() {
		$slug = get_query_var( self::QUERY_VAR );
		if ( '' === $slug || null === $slug ) {
			return;
		}
		$link = self::get_by_slug( sanitize_title( $slug ) );
		if ( ! $link || empty( $link->destination ) ) {
			status_header( 404 );
			return;
		}
		self::increment_clicks( (int) $link->id );
		// Push the fresh count to the account right away (non-blocking) — panel stats shouldn't
		// lag an hour behind a click. The hourly cron remains the reliable reconciler.
		$fresh = self::get( (int) $link->id );
		if ( $fresh ) {
			// Event first, count second: the panel dedupes the count report against events it has
			// already received, so this order keeps the breakdowns exact even for a single click.
			AffKeeper_Sync::push_click_event( $fresh );
			AffKeeper_Sync::push_click_fast( $fresh );
		}
		// Same rel intent as the shortcode; a bare redirect carries no referrer to the destination.
		// phpcs:ignore WordPress.Security.SafeRedirect.wp_redirect_wp_redirect -- redirecting to the user's own (external) destination URL is this plugin's core feature; wp_safe_redirect() refuses external hosts.
		wp_redirect( $link->destination, 302 );
		exit;
	}

	// --- CRUD (all local; no network) ---

	public static function all( $orderby = 'created_at', $order = 'DESC' ) {
		global $wpdb;
		$orderby = in_array( $orderby, array( 'created_at', 'clicks', 'title', 'slug' ), true ) ? $orderby : 'created_at';
		$order   = 'ASC' === strtoupper( $order ) ? 'ASC' : 'DESC';
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- $orderby/$order whitelisted above.
		return $wpdb->get_results( "SELECT * FROM " . self::table() . " ORDER BY $orderby $order" );
	}

	public static function count() {
		global $wpdb;
		return (int) $wpdb->get_var( 'SELECT COUNT(*) FROM ' . self::table() ); // phpcs:ignore WordPress.DB
	}

	public static function get( $id ) {
		global $wpdb;
		return $wpdb->get_row( $wpdb->prepare( 'SELECT * FROM ' . self::table() . ' WHERE id = %d', $id ) ); // phpcs:ignore WordPress.DB
	}

	public static function get_by_slug( $slug ) {
		global $wpdb;
		return $wpdb->get_row( $wpdb->prepare( 'SELECT * FROM ' . self::table() . ' WHERE slug = %s', $slug ) ); // phpcs:ignore WordPress.DB
	}

	public static function slug_exists( $slug, $exclude_id = 0 ) {
		global $wpdb;
		return (int) $wpdb->get_var( $wpdb->prepare( 'SELECT COUNT(*) FROM ' . self::table() . ' WHERE slug = %s AND id <> %d', $slug, $exclude_id ) ) > 0; // phpcs:ignore WordPress.DB
	}

	/**
	 * A link with this destination created moments ago, or null. Double-click insurance: two
	 * near-simultaneous identical creates collapse into one link instead of minting twins.
	 *
	 * @param string $dest    Destination URL.
	 * @param int    $seconds Window size.
	 * @return object|null
	 */
	public static function recent_duplicate( $dest, $seconds = 10 ) {
		global $wpdb;
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery
		return $wpdb->get_row( $wpdb->prepare(
			'SELECT * FROM ' . self::table() . ' WHERE destination = %s AND created_at >= (NOW() - INTERVAL %d SECOND) ORDER BY id DESC LIMIT 1',
			$dest,
			$seconds
		) );
	}

	public static function gen_slug() {
		$alphabet = 'abcdefghijklmnopqrstuvwxyz0123456789';
		do {
			$slug = '';
			for ( $i = 0; $i < 8; $i++ ) {
				$slug .= $alphabet[ wp_rand( 0, strlen( $alphabet ) - 1 ) ];
			}
		} while ( self::slug_exists( $slug ) );
		return $slug;
	}

	/**
	 * @param array $data destination (required), slug, title, is_affiliate, source_url.
	 * @return int|WP_Error New id.
	 */
	public static function create( $data ) {
		global $wpdb;
		$dest = isset( $data['destination'] ) ? esc_url_raw( $data['destination'] ) : '';
		if ( '' === $dest ) {
			return new WP_Error( 'affkeeper_bad_url', __( 'A destination URL is required.', 'affkeeper' ) );
		}
		$slug = isset( $data['slug'] ) ? sanitize_title( $data['slug'] ) : '';
		if ( '' === $slug ) {
			$slug = self::gen_slug();
		} elseif ( self::slug_exists( $slug ) ) {
			return new WP_Error( 'affkeeper_slug_taken', __( 'That slug is already used.', 'affkeeper' ) );
		}
		$ok = $wpdb->insert( // phpcs:ignore WordPress.DB
			self::table(),
			array(
				'slug'         => $slug,
				'destination'  => $dest,
				'title'        => isset( $data['title'] ) ? sanitize_text_field( $data['title'] ) : '',
				'is_affiliate' => ! empty( $data['is_affiliate'] ) ? 1 : 0,
				// Monitoring is opt-in per link (it occupies one of the plan's slots). Defaults:
				// affiliate = on (that's the product), plain redirect = off.
				'monitored'    => isset( $data['monitored'] )
					? ( ! empty( $data['monitored'] ) ? 1 : 0 )
					: ( ! empty( $data['is_affiliate'] ) ? 1 : 0 ),
				'source_url'   => isset( $data['source_url'] ) ? esc_url_raw( $data['source_url'] ) : '',
			),
			array( '%s', '%s', '%s', '%d', '%d', '%s' )
		);
		if ( ! $ok ) {
			return new WP_Error( 'affkeeper_db', __( 'Could not save the link.', 'affkeeper' ) );
		}
		return (int) $wpdb->insert_id;
	}

	public static function update( $id, $data ) {
		global $wpdb;
		$fields = array();
		$fmt    = array();
		if ( isset( $data['destination'] ) ) {
			$fields['destination'] = esc_url_raw( $data['destination'] );
			$fmt[]                 = '%s';
		}
		if ( isset( $data['title'] ) ) {
			$fields['title'] = sanitize_text_field( $data['title'] );
			$fmt[]           = '%s';
		}
		if ( isset( $data['is_affiliate'] ) ) {
			$fields['is_affiliate'] = ! empty( $data['is_affiliate'] ) ? 1 : 0;
			$fmt[]                  = '%d';
		}
		if ( isset( $data['monitored'] ) ) {
			$fields['monitored'] = ! empty( $data['monitored'] ) ? 1 : 0;
			$fmt[]               = '%d';
		}
		if ( isset( $data['source_url'] ) ) {
			$fields['source_url'] = esc_url_raw( $data['source_url'] );
			$fmt[]                = '%s';
		}
		if ( isset( $data['remote_id'] ) ) {
			$fields['remote_id'] = sanitize_text_field( $data['remote_id'] );
			$fmt[]               = '%s';
		}
		if ( isset( $data['slug'] ) ) {
			$slug = sanitize_title( $data['slug'] );
			if ( '' !== $slug && ! self::slug_exists( $slug, (int) $id ) ) {
				$fields['slug'] = $slug;
				$fmt[]          = '%s';
			}
		}
		if ( empty( $fields ) ) {
			return true;
		}
		return false !== $wpdb->update( self::table(), $fields, array( 'id' => (int) $id ), $fmt, array( '%d' ) ); // phpcs:ignore WordPress.DB
	}

	public static function delete( $id ) {
		global $wpdb;
		return false !== $wpdb->delete( self::table(), array( 'id' => (int) $id ), array( '%d' ) ); // phpcs:ignore WordPress.DB
	}

	/**
	 * Drop every stored panel pointer — used when the site is re-linked to a DIFFERENT account,
	 * so the next sync pushes fresh records there instead of chasing ids from the old tenant.
	 */
	public static function clear_remote_ids() {
		global $wpdb;
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery
		$wpdb->query( 'UPDATE ' . self::table() . " SET remote_id = ''" );
	}

	public static function increment_clicks( $id ) {
		global $wpdb;
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery
		$wpdb->query( $wpdb->prepare( 'UPDATE ' . self::table() . ' SET clicks = clicks + 1 WHERE id = %d', $id ) );
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery
		$wpdb->query( $wpdb->prepare(
			'INSERT INTO ' . self::daily_table() . ' (link_id, day, clicks) VALUES (%d, CURDATE(), 1)'
			. ' ON DUPLICATE KEY UPDATE clicks = clicks + 1',
			$id
		) );
	}

	/**
	 * "YYYY-MM-DD" => clicks for ONE link over the last $days days — the panel charts WP-served
	 * links from these buckets (it never sees the individual clicks).
	 *
	 * @param int $link_id Local row id.
	 * @param int $days    Window size.
	 * @return array<string,int>
	 */
	public static function daily_map( $link_id, $days = 35 ) {
		global $wpdb;
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery
		$rows = $wpdb->get_results( $wpdb->prepare(
			'SELECT day, clicks FROM ' . self::daily_table()
			. ' WHERE link_id = %d AND day >= (CURDATE() - INTERVAL %d DAY)',
			$link_id,
			max( 1, (int) $days ) - 1
		) );
		$map = array();
		foreach ( (array) $rows as $r ) {
			$map[ (string) $r->day ] = (int) $r->clicks;
		}
		return $map;
	}

	/**
	 * link_id => clicks over the last 30 days (today included), for every local link at once.
	 *
	 * @return array<int,int>
	 */
	public static function clicks_30d_map() {
		global $wpdb;
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery
		$rows = $wpdb->get_results( 'SELECT link_id, SUM(clicks) AS c FROM ' . self::daily_table()
			. ' WHERE day >= (CURDATE() - INTERVAL 29 DAY) GROUP BY link_id' );
		$map = array();
		foreach ( (array) $rows as $r ) {
			$map[ (int) $r->link_id ] = (int) $r->c;
		}
		return $map;
	}

	/**
	 * Full short URL for a local link on this site.
	 *
	 * @param object $link Row from the table.
	 * @return string
	 */
	public static function short_url( $link ) {
		return home_url( '/' . self::prefix() . '/' . $link->slug );
	}

	/**
	 * Normalise a local row into the same shape the panel's link_map() returns, so the Links page
	 * and shortcode can render local links with the exact same code as connected ones. Local links
	 * carry no availability/health — that monitoring is the account tier — so those come back null.
	 *
	 * @param object $link Row from the table.
	 * @return array
	 */
	public static function to_row( $link ) {
		return array(
			'id'              => (string) $link->id,
			'slug'            => $link->slug,
			// Local slugs are always the user's/auto alias, so they double as the "custom" slug.
			'custom_slug'     => $link->slug,
			'short_display'   => self::prefix() . '/' . $link->slug,
			'short_url'       => self::short_url( $link ),
			'destination_url' => $link->destination,
			'source_url'      => isset( $link->source_url ) ? (string) $link->source_url : '',
			'is_affiliate'    => (bool) (int) $link->is_affiliate,
			'monitored'       => ! isset( $link->monitored ) || (int) $link->monitored ? true : false,
			'is_active'       => true,
			'clicks'          => (int) $link->clicks,
			'created_at'      => $link->created_at,
			'product_title'   => (string) $link->title,
			'availability'    => null,
			'health'          => null,
		);
	}

	/**
	 * slug => row-shaped array for every local link (newest first). Mirror of AffKeeper_API::link_map()
	 * for the free, no-account tier.
	 *
	 * @return array
	 */
	public static function link_map() {
		$map   = array();
		$map30 = self::clicks_30d_map();
		foreach ( self::all( 'created_at', 'DESC' ) as $link ) {
			$row                = self::to_row( $link );
			$row['clicks_30d']  = isset( $map30[ (int) $link->id ] ) ? $map30[ (int) $link->id ] : 0;
			$map[ $link->slug ] = $row;
		}
		return $map;
	}
}