<?php

/**
 * Content Library Database Schema
 *
 * @package ProRank\SEO\Database\Schema
 */

declare(strict_types=1);

namespace ProRank\SEO\Database\Schema;

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


/**
 * Content Library Schema class
 */


class ContentLibrarySchema {
	/**
	 * Get the content library table name
	 *
	 * @return string
	 */
	public static function get_table_name(): string {
		global $wpdb;
		return $wpdb->prefix . 'prorank_ai_content_library';
	}

	/**
	 * Get the templates table name
	 *
	 * @return string
	 */
	public static function get_templates_table_name(): string {
		global $wpdb;
		return $wpdb->prefix . 'prorank_ai_templates';
	}

	/**
	 * Get the generation history table name
	 *
	 * @return string
	 */
	public static function get_history_table_name(): string {
		global $wpdb;
		return $wpdb->prefix . 'prorank_ai_generation_history';
	}

	/**
	 * Create tables for content library
	 *
	 * @return void
	 */
	public static function create_tables(): void {
		global $wpdb;
		$charset_collate = $wpdb->get_charset_collate();

		// Content Library Table
		$content_library_table = self::get_table_name();
		$sql_library = "CREATE TABLE IF NOT EXISTS $content_library_table (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			user_id bigint(20) unsigned NOT NULL,
			title varchar(255) NOT NULL,
			content longtext NOT NULL,
			content_type varchar(50) NOT NULL,
			status varchar(20) DEFAULT 'draft',
			meta_data longtext,
			tags text,
			category varchar(100),
			word_count int(11) DEFAULT 0,
			seo_score int(11) DEFAULT 0,
			readability_score int(11) DEFAULT 0,
			ai_provider varchar(50),
			ai_model varchar(100),
			prompt_used text,
			generation_params text,
			created_at datetime DEFAULT CURRENT_TIMESTAMP,
			updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
			PRIMARY KEY (id),
			KEY user_id (user_id),
			KEY content_type (content_type),
			KEY status (status),
			KEY created_at (created_at),
			FULLTEXT KEY content_search (title, content, tags)
		) $charset_collate;";

		// Templates Table
		$templates_table = self::get_templates_table_name();
		$sql_templates = "CREATE TABLE IF NOT EXISTS $templates_table (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			user_id bigint(20) unsigned NOT NULL,
			name varchar(255) NOT NULL,
			description text,
			content_type varchar(50) NOT NULL,
			prompt_template longtext NOT NULL,
			variables text,
			default_params text,
			category varchar(100),
			is_public tinyint(1) DEFAULT 0,
			usage_count int(11) DEFAULT 0,
			rating float DEFAULT 0,
			created_at datetime DEFAULT CURRENT_TIMESTAMP,
			updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
			PRIMARY KEY (id),
			KEY user_id (user_id),
			KEY content_type (content_type),
			KEY is_public (is_public),
			KEY category (category),
			FULLTEXT KEY template_search (name, description)
		) $charset_collate;";

		// Generation History Table
		$history_table = self::get_history_table_name();
		$sql_history = "CREATE TABLE IF NOT EXISTS $history_table (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			user_id bigint(20) unsigned NOT NULL,
			content_id bigint(20) unsigned,
			template_id bigint(20) unsigned,
			action varchar(50) NOT NULL,
			content_type varchar(50),
			prompt text,
			response longtext,
			tokens_used int(11) DEFAULT 0,
			cost_estimate decimal(10, 6) DEFAULT 0,
			ai_provider varchar(50),
			ai_model varchar(100),
			status varchar(20) DEFAULT 'success',
			error_message text,
			execution_time float DEFAULT 0,
			ip_address varchar(45),
			user_agent text,
			created_at datetime DEFAULT CURRENT_TIMESTAMP,
			PRIMARY KEY (id),
			KEY user_id (user_id),
			KEY content_id (content_id),
			KEY template_id (template_id),
			KEY action (action),
			KEY status (status),
			KEY created_at (created_at),
			KEY ai_provider (ai_provider)
		) $charset_collate;";

		require_once ABSPATH . 'wp-admin/includes/upgrade.php';
		dbDelta( $sql_library );
		dbDelta( $sql_templates );
		dbDelta( $sql_history );

		// Store version for future migrations
		update_option( 'prorank_ai_content_db_version', '1.0.0' );
	}

	/**
	 * Drop tables
	 *
	 * @return void
	 */
	public static function drop_tables(): void {
		global $wpdb;
		
		$tables = [
			self::get_table_name(),
			self::get_templates_table_name(),
			self::get_history_table_name(),
		];

		foreach ( $tables as $table ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange
			$wpdb->query( "DROP TABLE IF EXISTS $table" );
		}

		delete_option( 'prorank_ai_content_db_version' );
	}

	/**
	 * Check if tables exist
	 *
	 * @return bool
	 */
	public static function tables_exist(): bool {
		global $wpdb;
		
		$content_table = self::get_table_name();
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$result = $wpdb->get_var( "SHOW TABLES LIKE '$content_table'" );
		
		return $result === $content_table;
	}
}