<?php

/**
 * Database Management Class
 */

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

class Affloc_DB
{
	public function __construct()
	{
	}

	/**
	 * Create the custom table for mapping.
	 */
	public static function create_table()
	{
		global $wpdb;
		$charset_collate = $wpdb->get_charset_collate();
		$table_name      = $wpdb->prefix . 'aff_links';

		$sql = "CREATE TABLE {$table_name} (
			id bigint(20) NOT NULL AUTO_INCREMENT,
			slug varchar(100) NOT NULL,
			country_code varchar(10) NOT NULL DEFAULT 'default',
			url text NOT NULL,
			aff_text varchar(255) NOT NULL DEFAULT '',
			created_at datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
			PRIMARY KEY  (id),
			KEY slug (slug),
			KEY country_code (country_code)
		) {$charset_collate};";

		require_once ABSPATH . 'wp-admin/includes/upgrade.php';
		dbDelta($sql); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
	}

	/**
	 * Get mapping for a specific slug.
	 */
	public static function get_mapping($slug)
	{
		global $wpdb;

		$results = $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$wpdb->prepare(
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
				'SELECT country_code, url FROM ' . $wpdb->prefix . 'aff_links WHERE slug = %s',
				$slug
			),
			ARRAY_A
		);

		$mapping = [];
		foreach ($results as $row)
		{
			$mapping[$row['country_code']] = $row['url'];
		}

		return $mapping;
	}

	/**
	 * Get total count of links.
	 */
	public static function get_total_count()
	{
		global $wpdb;

		return (int) $wpdb->get_var( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$wpdb->prepare(
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
				'SELECT COUNT(id) FROM ' . $wpdb->prefix . 'aff_links WHERE 1=%d',
				1
			)
		);
	}

	/**
	 * Save a mapping entry.
	 */
	public static function save_link($slug, $country, $url, $aff_text = '')
	{
		global $wpdb;

		$slug     = sanitize_title($slug);
		$country  = strtoupper(sanitize_text_field($country));
		$url      = esc_url_raw($url);
		$aff_text = sanitize_text_field($aff_text);

		$id = $wpdb->get_var( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$wpdb->prepare(
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
				'SELECT id FROM ' . $wpdb->prefix . 'aff_links WHERE slug = %s AND country_code = %s',
				$slug,
				$country
			)
		);

		if ($id)
		{
			return $wpdb->update( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
				$wpdb->prefix . 'aff_links',
				array(
					'url'      => $url,
					'aff_text' => $aff_text,
				),
				array('id' => $id)
			);
		}
		else
		{
			return $wpdb->insert( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
				$wpdb->prefix . 'aff_links',
				array(
					'slug'         => $slug,
					'country_code' => $country,
					'url'          => $url,
					'aff_text'     => $aff_text,
					'created_at'   => current_time('mysql'),
				)
			);
		}
	}

	/**
	 * Delete a mapping entry by ID.
	 */
	public static function delete_link($id)
	{
		global $wpdb;

		return $wpdb->delete( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$wpdb->prefix . 'aff_links',
			array('id' => intval($id)),
			array('%d')
		);
	}
}