<?php
namespace AIOSEO\Plugin\Pro\Db;

// Exit if accessed directly.
if ( ! defined( 'ABSPATH' ) ) {
	exit;
}

use AIOSEO\Plugin\Common\Db\Schema as CommonSchema;

/**
 * Database Schema for AIOSEO Pro tables.
 *
 * Extends the Common schema and adds Pro-specific tables.
 *
 * @since 4.9.7
 */
class Schema extends CommonSchema {
	/**
	 * Get all table schemas for Pro tables.
	 *
	 * Includes all Common tables plus Pro-specific tables.
	 *
	 * @since 4.9.7
	 *
	 * @return array Array of SQL CREATE TABLE statements.
	 */
	public function getSchema() {
		return array_merge(
			parent::getSchema(),
			[
				$this->getTermsTableSchema(),
				$this->getRevisionsTableSchema(),
				$this->getSearchStatisticsObjectsTableSchema(),
				$this->getSearchStatisticsKeywordsTableSchema(),
				$this->getSearchStatisticsKeywordGroupsTableSchema(),
				$this->getSearchStatisticsKeywordRelationshipsTableSchema(),
				$this->getSeoAnalyzerObjectsTableSchema(),
			]
		);
	}

	/**
	 * Get the schema for aioseo_terms table.
	 *
	 * Column history:
	 * - 4.0.0: Initial table creation
	 * - 4.1.5: Removed several unused columns
	 * - 4.1.6: og_image_url, og_image_width, og_image_height, twitter_image_url
	 * - 4.3.9: priority changed to float
	 * - 4.8.3: breadcrumb_settings
	 * - 4.8.6: seo_analyzer_scan_date
	 *
	 * @since 4.9.7
	 *
	 * @return string SQL CREATE TABLE statement.
	 */
	public function getTermsTableSchema() {
		$tableName      = aioseo()->core->db->db->prefix . 'aioseo_terms';
		$charsetCollate = aioseo()->core->db->db->get_charset_collate();

		return "CREATE TABLE {$tableName} (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			term_id bigint(20) unsigned NOT NULL,
			title text DEFAULT NULL,
			description text DEFAULT NULL,
			keywords mediumtext DEFAULT NULL,
			canonical_url text DEFAULT NULL,
			og_title text DEFAULT NULL,
			og_description text DEFAULT NULL,
			og_object_type varchar(64) DEFAULT 'default',
			og_image_type varchar(64) DEFAULT 'default',
			og_image_custom_url text DEFAULT NULL,
			og_image_custom_fields text DEFAULT NULL,
			og_image_url text DEFAULT NULL,
			og_image_width int(11) DEFAULT NULL,
			og_image_height int(11) DEFAULT NULL,
			og_video varchar(255) DEFAULT NULL,
			og_custom_url text DEFAULT NULL,
			og_article_section text DEFAULT NULL,
			og_article_tags text DEFAULT NULL,
			twitter_use_og tinyint(1) DEFAULT 0,
			twitter_card varchar(64) DEFAULT 'default',
			twitter_image_type varchar(64) DEFAULT 'default',
			twitter_image_custom_url text DEFAULT NULL,
			twitter_image_custom_fields text DEFAULT NULL,
			twitter_image_url text DEFAULT NULL,
			twitter_title text DEFAULT NULL,
			twitter_description text DEFAULT NULL,
			pillar_content tinyint(1) DEFAULT NULL,
			robots_default tinyint(1) DEFAULT 1 NOT NULL,
			robots_noindex tinyint(1) DEFAULT 0 NOT NULL,
			robots_noarchive tinyint(1) DEFAULT 0 NOT NULL,
			robots_nosnippet tinyint(1) DEFAULT 0 NOT NULL,
			robots_nofollow tinyint(1) DEFAULT 0 NOT NULL,
			robots_noimageindex tinyint(1) DEFAULT 0 NOT NULL,
			robots_noodp tinyint(1) DEFAULT 0 NOT NULL,
			robots_notranslate tinyint(1) DEFAULT 0 NOT NULL,
			robots_max_snippet int(11) DEFAULT NULL,
			robots_max_videopreview int(11) DEFAULT NULL,
			robots_max_imagepreview varchar(20) DEFAULT 'large',
			priority float DEFAULT NULL,
			frequency tinytext DEFAULT NULL,
			images longtext DEFAULT NULL,
			videos longtext DEFAULT NULL,
			video_scan_date datetime DEFAULT NULL,
			local_seo longtext DEFAULT NULL,
			breadcrumb_settings longtext DEFAULT NULL,
			seo_analyzer_scan_date datetime DEFAULT NULL,
			created datetime NOT NULL,
			updated datetime NOT NULL,
			PRIMARY KEY  (id),
			KEY ndx_aioseo_terms_term_id (term_id)
		) {$charsetCollate};";
	}

	/**
	 * Get the schema for aioseo_revisions table.
	 *
	 * @since 4.9.7
	 *
	 * @return string SQL CREATE TABLE statement.
	 */
	public function getRevisionsTableSchema() {
		$tableName      = aioseo()->core->db->db->prefix . 'aioseo_revisions';
		$charsetCollate = aioseo()->core->db->db->get_charset_collate();

		return "CREATE TABLE {$tableName} (
			id BIGINT(20) NOT NULL AUTO_INCREMENT,
			object_id BIGINT(20) NOT NULL,
			object_type varchar(20) NOT NULL,
			author_id BIGINT(20) NOT NULL,
			note varchar(255) DEFAULT NULL,
			revision_data LONGTEXT NOT NULL,
			created DATETIME NOT NULL,
			updated DATETIME NOT NULL,
			PRIMARY KEY  (id),
			KEY object (object_id, object_type)
		) {$charsetCollate};";
	}

	/**
	 * Get the schema for aioseo_search_statistics_objects table.
	 *
	 * Column history:
	 * - 4.3.0: Initial table creation
	 * - 4.5.0: inspection_result, inspection_result_date, indexed
	 * - 4.8.2: verdict, robots_txt_state, indexing_state, page_fetch_state, coverage_state, crawled_as, last_crawl_time
	 *
	 * @since 4.9.7
	 *
	 * @return string SQL CREATE TABLE statement.
	 */
	public function getSearchStatisticsObjectsTableSchema() {
		$tableName      = aioseo()->core->db->db->prefix . 'aioseo_search_statistics_objects';
		$charsetCollate = aioseo()->core->db->db->get_charset_collate();

		return "CREATE TABLE {$tableName} (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			object_id bigint(20) unsigned DEFAULT NULL,
			object_type varchar(100) DEFAULT NULL,
			object_subtype varchar(100) DEFAULT NULL,
			object_path varchar(500) NOT NULL,
			object_path_hash varchar(40) NOT NULL,
			inspection_result longtext DEFAULT NULL,
			inspection_result_date datetime DEFAULT NULL,
			verdict varchar(64) DEFAULT NULL,
			robots_txt_state varchar(64) DEFAULT NULL,
			indexing_state varchar(64) DEFAULT NULL,
			page_fetch_state varchar(64) DEFAULT NULL,
			coverage_state varchar(64) DEFAULT NULL,
			crawled_as varchar(64) DEFAULT NULL,
			last_crawl_time datetime DEFAULT NULL,
			created datetime NOT NULL,
			updated datetime NOT NULL,
			PRIMARY KEY  (id),
			UNIQUE KEY ndx_aioseo_object_path_hash1 (object_path_hash),
			KEY ndx_aioseo_object_id1 (object_id)
		) {$charsetCollate};";
	}

	/**
	 * Get the schema for aioseo_search_statistics_keywords table.
	 *
	 * @since 4.9.7
	 *
	 * @return string SQL CREATE TABLE statement.
	 */
	public function getSearchStatisticsKeywordsTableSchema() {
		$tableName      = aioseo()->core->db->db->prefix . 'aioseo_search_statistics_keywords';
		$charsetCollate = aioseo()->core->db->db->get_charset_collate();

		return "CREATE TABLE {$tableName} (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			name varchar(150) NOT NULL,
			favorited tinyint(1) NOT NULL DEFAULT 0,
			created DATETIME NOT NULL,
			updated DATETIME NOT NULL,
			PRIMARY KEY  (id),
			UNIQUE KEY ndx_aioseo_search_statistics_keyword_name (name)
		) {$charsetCollate};";
	}

	/**
	 * Get the schema for aioseo_search_statistics_keyword_groups table.
	 *
	 * @since 4.9.7
	 *
	 * @return string SQL CREATE TABLE statement.
	 */
	public function getSearchStatisticsKeywordGroupsTableSchema() {
		$tableName      = aioseo()->core->db->db->prefix . 'aioseo_search_statistics_keyword_groups';
		$charsetCollate = aioseo()->core->db->db->get_charset_collate();

		return "CREATE TABLE {$tableName} (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			name varchar(150) DEFAULT NULL,
			created DATETIME NOT NULL,
			updated DATETIME NOT NULL,
			PRIMARY KEY  (id),
			UNIQUE KEY ndx_aioseo_search_statistics_keyword_groups_name (name)
		) {$charsetCollate};";
	}

	/**
	 * Get the schema for aioseo_search_statistics_keyword_relationships table.
	 *
	 * @since 4.9.7
	 *
	 * @return string SQL CREATE TABLE statement.
	 */
	public function getSearchStatisticsKeywordRelationshipsTableSchema() {
		$tableName      = aioseo()->core->db->db->prefix . 'aioseo_search_statistics_keyword_relationships';
		$charsetCollate = aioseo()->core->db->db->get_charset_collate();

		return "CREATE TABLE {$tableName} (
			keyword_id bigint(20) unsigned NOT NULL,
			keyword_group_id bigint(20) unsigned NOT NULL,
			PRIMARY KEY  (keyword_id, keyword_group_id)
		) {$charsetCollate};";
	}

	/**
	 * Get the schema for aioseo_seo_analyzer_objects table.
	 *
	 * @since 4.9.7
	 *
	 * @return string SQL CREATE TABLE statement.
	 */
	public function getSeoAnalyzerObjectsTableSchema() {
		$tableName      = aioseo()->core->db->db->prefix . 'aioseo_seo_analyzer_objects';
		$charsetCollate = aioseo()->core->db->db->get_charset_collate();

		return "CREATE TABLE {$tableName} (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			object_id bigint(20) unsigned NOT NULL,
			object_type varchar(40) NOT NULL,
			object_subtype varchar(40) NOT NULL,
			code varchar(50) NOT NULL,
			metadata text DEFAULT NULL,
			is_ignored boolean NOT NULL DEFAULT 0,
			created DATETIME NOT NULL,
			updated DATETIME NOT NULL,
			PRIMARY KEY  (id),
			KEY ndx_aioseo_seo_analyzer_objects_object (object_id, object_type),
			KEY ndx_aioseo_seo_analyzer_objects_code (code)
		) {$charsetCollate};";
	}
}