<?php
/**
 * Database Installation
 *
 * Handles database table creation and upgrades for Warranty Cart.
 *
 * @package WarrantyCart
 */

namespace WarrantyCart\Core;

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

/**
 * Database Installation Class
 */
class Schema {

	/**
	 * The schema version.
	 *
	 * Bumped whenever a table is added or altered. `needs_upgrade()` compares
	 * against it, so an existing install picks the change up on the next load
	 * rather than only on a fresh activation — which is how the claims table
	 * reaches sites that already had the plugin.
	 *
	 * 1.2.0 adds `covered_product_id`: which product a warranty was bought to
	 * cover, when it was bought alongside one. `dbDelta()` adds the column in
	 * place and existing rows keep NULL, which is the truthful value for them —
	 * every warranty sold before this was chosen from the selector page without
	 * reference to any particular item.
	 *
	 * 1.3.0 adds the renewals table. `renewal_count` and `last_renewal_date`
	 * record that a warranty was renewed and when the most recent one happened,
	 * which is a tally rather than a history — it cannot say what a renewal cost,
	 * which order paid for it, or how the term moved. Warranties renewed before
	 * this have no rows, and the count is the only thing that can be said about
	 * them.
	 *
	 * 1.4.0 adds the claim attachments table. A claim could carry a subject and a
	 * description and nothing else, so the photograph of the crack and the copy
	 * of the receipt arrived by email, outside the plugin, attached to nothing.
	 * Existing claims have no rows, which is truthful: nobody was ever able to
	 * attach anything to them.
	 *
	 * 1.5.0 types the outcome of a claim. `resolution` is a note in somebody's
	 * own words — useful to a customer, useless to a query — so a shop could say
	 * how much warranty revenue it made and not whether that was a good idea.
	 * `outcome`, `outcome_cost` and `replacement_order_id` make the question
	 * answerable. Claims decided before this have NULL, which is truthful:
	 * nobody was ever asked what the decision cost.
	 *
	 * 1.6.0 adds `guest_email` to claims. Filing required an account, so a
	 * customer who checked out as a guest — which WooCommerce allows by default —
	 * had cover recorded against `customer_id = 0` and no way to claim against
	 * it. The column holds the address the claim was filed and answered under.
	 * Claims filed before this have NULL, which is truthful: every one of them
	 * came from a logged-in account.
	 *
	 * 1.7.0 adds the claim messages table. A claim could be answered exactly once
	 * — the `resolution` note written when its status moved — so a shop needing
	 * to ask a question, or to say anything at all without deciding the claim,
	 * had to leave the plugin and use email, where the answer is then invisible
	 * to everyone but the two people in that thread. Claims answered before this
	 * have no rows, and their `resolution` is still where their one answer is.
	 *
	 * 1.8.0 adds `serial_number` to warranties, with an index. Electronics and
	 * appliances — the two things most likely to carry extended cover — are
	 * identified by the plate on the back rather than by an order number, and a
	 * customer holding a broken washing machine has the serial in front of them
	 * and the order number in an inbox somewhere. Warranties sold before this
	 * have NULL, which is truthful: nobody was ever able to record one.
	 */
	public const DB_VERSION = '1.8.0';

	/**
	 * Create database tables
	 *
	 * @return bool True on success, false on failure.
	 */
	public static function create_tables(): bool {
		global $wpdb;

		$charset_collate = $wpdb->get_charset_collate();

		// Warranty records table
		$table_name = $wpdb->prefix . 'warranty_cart_warranties';

		$sql = "CREATE TABLE $table_name (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			order_id bigint(20) unsigned NOT NULL,
			customer_id bigint(20) unsigned DEFAULT NULL,
			serial_number varchar(100) DEFAULT NULL,
			product_id bigint(20) unsigned NOT NULL,
			covered_product_id bigint(20) unsigned DEFAULT NULL,
			warranty_type varchar(50) NOT NULL,
			duration int(11) NOT NULL DEFAULT 1,
			duration_unit varchar(20) NOT NULL DEFAULT 'years',
			purchase_date datetime NOT NULL,
			expiration_date date NOT NULL,
			purchase_price decimal(10,2) NOT NULL,
			currency varchar(3) NOT NULL DEFAULT 'EUR',
			payment_method varchar(100) DEFAULT NULL,
			status varchar(20) NOT NULL DEFAULT 'active',
			source varchar(50) NOT NULL DEFAULT 'woocommerce',
			coverage_details longtext,
			reminder_sent tinyint(1) DEFAULT 0,
			expired_notified tinyint(1) DEFAULT 0,
			renewal_count int(11) DEFAULT 0,
			last_renewal_date datetime DEFAULT NULL,
			created_at datetime NOT NULL,
			updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
			PRIMARY KEY (id),
			KEY order_id (order_id),
			KEY customer_id (customer_id),
			KEY serial_number (serial_number),
			KEY product_id (product_id),
			KEY covered_product_id (covered_product_id),
			KEY expiration_date (expiration_date),
			KEY status (status),
			KEY warranty_type (warranty_type)
		) $charset_collate;";

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

		/*
		 * dbDelta() returns what it *changed*, not what exists.
		 *
		 * An empty return means "nothing needed doing" — the overwhelmingly
		 * common case, since this runs against a table that is usually already
		 * correct. Treating that as failure meant create_tables() returned
		 * false on any site whose schema was already up to date, so upgrade()
		 * reported failure and, once a second table was added below, never got
		 * far enough to create it.
		 *
		 * The existence check immediately below is the real test.
		 */
		\dbDelta( $sql );

		// Verify table exists
		$table_exists = $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $table_name ) );
		if ( ! $table_exists ) {
			\wc_get_logger()->error( 'Warranty table creation failed - table does not exist', array( 'source' => 'warranty-cart-db' ) );

			return false;
		}

		if ( ! self::create_claims_table( $charset_collate ) ) {
			return false;
		}

		if ( ! self::create_renewals_table( $charset_collate ) ) {
			return false;
		}

		if ( ! self::create_claim_attachments_table( $charset_collate ) ) {
			return false;
		}

		if ( ! self::create_claim_messages_table( $charset_collate ) ) {
			return false;
		}

		// Store database version
		\update_option( 'warranty_cart_db_version', self::DB_VERSION );

		// Log successful table creation
		\wc_get_logger()->info( 'Warranty database tables created successfully', array( 'source' => 'warranty-cart-db' ) );

		return true;
	}

	/**
	 * Create the warranty claims table.
	 *
	 * Claims are their own records rather than meta on a warranty: a warranty
	 * can be claimed against more than once, each claim carries its own status
	 * and correspondence, and the admin needs to list and filter them without
	 * walking every warranty.
	 *
	 * @param string $charset_collate The collation clause.
	 *
	 * @return bool
	 */
	private static function create_claims_table( string $charset_collate ): bool {
		global $wpdb;

		$table_name = $wpdb->prefix . 'warranty_cart_claims';

		$sql = "CREATE TABLE $table_name (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			warranty_id bigint(20) unsigned NOT NULL,
			order_id bigint(20) unsigned NOT NULL,
			customer_id bigint(20) unsigned DEFAULT NULL,
			guest_email varchar(100) DEFAULT NULL,
			product_id bigint(20) unsigned NOT NULL,
			subject varchar(255) NOT NULL,
			description longtext NOT NULL,
			status varchar(20) NOT NULL DEFAULT 'pending',
			resolution longtext DEFAULT NULL,
			outcome varchar(20) DEFAULT NULL,
			outcome_cost decimal(10,2) DEFAULT NULL,
			outcome_currency varchar(3) DEFAULT NULL,
			replacement_order_id bigint(20) unsigned DEFAULT NULL,
			created_at datetime NOT NULL,
			updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
			PRIMARY KEY (id),
			KEY warranty_id (warranty_id),
			KEY order_id (order_id),
			KEY customer_id (customer_id),
			KEY status (status),
			KEY outcome (outcome),
			KEY created_at (created_at)
		) $charset_collate;";

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

		\dbDelta( $sql );

		$exists = $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $table_name ) );

		if ( ! $exists ) {
			\wc_get_logger()->error(
				'Warranty claims table creation failed',
				array( 'source' => 'warranty-cart-db' )
			);

			return false;
		}

		return true;
	}

	/**
	 * What was said about a claim, and to whom.
	 *
	 * `visibility` is the whole point of the table rather than a refinement of
	 * it: the shop needs somewhere to write "customer says it was dropped" that
	 * the customer does not read, and somewhere to write "we have ordered the
	 * part" that they do. One table with a column keeps both in the order they
	 * happened, which is how anybody picking the claim up later can follow it.
	 *
	 * `author_name` is a snapshot. Staff leave, accounts are deleted, and a
	 * three-year-old claim thread that has forgotten who answered it is evidence
	 * of nothing.
	 *
	 * @param string $charset_collate Charset and collation clause.
	 *
	 * @return bool
	 */
	private static function create_claim_messages_table( string $charset_collate ): bool {
		global $wpdb;

		$table_name = $wpdb->prefix . 'warranty_cart_claim_messages';

		$sql = "CREATE TABLE $table_name (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			claim_id bigint(20) unsigned NOT NULL,
			author_id bigint(20) unsigned DEFAULT NULL,
			author_name varchar(255) DEFAULT NULL,
			visibility varchar(20) NOT NULL DEFAULT 'customer',
			body longtext NOT NULL,
			created_at datetime NOT NULL,
			PRIMARY KEY (id),
			KEY claim_id (claim_id),
			KEY visibility (visibility)
		) $charset_collate;";

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

		\dbDelta( $sql );

		$exists = $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $table_name ) );

		if ( ! $exists ) {
			\wc_get_logger()->error(
				'Warranty claim messages table creation failed',
				array( 'source' => 'warranty-cart-db' )
			);

			return false;
		}

		return true;
	}

	/**
	 * Files attached to claims.
	 *
	 * Metadata only — the files themselves live in a closed directory under
	 * uploads and are served by the REST layer after it has checked who is
	 * asking. See `Core\ClaimAttachments` for why they are not media library
	 * attachments.
	 *
	 * @param string $charset_collate Charset and collation clause.
	 *
	 * @return bool
	 */
	private static function create_claim_attachments_table( string $charset_collate ): bool {
		global $wpdb;

		$table_name = $wpdb->prefix . 'warranty_cart_claim_attachments';

		$sql = "CREATE TABLE $table_name (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			claim_id bigint(20) unsigned NOT NULL,
			stored_name varchar(255) NOT NULL,
			original_name varchar(255) NOT NULL,
			mime_type varchar(100) NOT NULL,
			file_size bigint(20) unsigned NOT NULL DEFAULT 0,
			uploaded_by bigint(20) unsigned DEFAULT NULL,
			created_at datetime NOT NULL,
			PRIMARY KEY (id),
			KEY claim_id (claim_id)
		) $charset_collate;";

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

		\dbDelta( $sql );

		$exists = $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $table_name ) );

		if ( ! $exists ) {
			\wc_get_logger()->error(
				'Warranty claim attachments table creation failed',
				array( 'source' => 'warranty-cart-db' )
			);

			return false;
		}

		return true;
	}

	/**
	 * Create the warranty renewals table.
	 *
	 * One row per renewal, rather than the running tally on the warranty.
	 * `renewal_count` says a warranty has been renewed three times and nothing
	 * else: not what each renewal cost, which order paid for it, or how far the
	 * term moved. A shop owner looking at a policy that has been renewed for
	 * five years wants the shape of that relationship, and a customer disputing
	 * a charge wants the order it came from.
	 *
	 * `order_id` is nullable because an administrator can renew from the record
	 * screen without taking payment — that is a real renewal with no order
	 * behind it, and recording zero there would invent one.
	 *
	 * @param string $charset_collate The collation clause.
	 *
	 * @return bool
	 */
	private static function create_renewals_table( string $charset_collate ): bool {
		global $wpdb;

		$table_name = $wpdb->prefix . 'warranty_cart_renewals';

		$sql = "CREATE TABLE $table_name (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			warranty_id bigint(20) unsigned NOT NULL,
			order_id bigint(20) unsigned DEFAULT NULL,
			previous_expiration date DEFAULT NULL,
			new_expiration date NOT NULL,
			amount decimal(10,2) DEFAULT NULL,
			currency varchar(3) DEFAULT NULL,
			source varchar(20) NOT NULL DEFAULT 'order',
			created_at datetime NOT NULL,
			PRIMARY KEY (id),
			KEY warranty_id (warranty_id),
			KEY order_id (order_id),
			KEY created_at (created_at)
		) $charset_collate;";

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

		\dbDelta( $sql );

		$exists = $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $table_name ) );

		if ( ! $exists ) {
			\wc_get_logger()->error(
				'Warranty renewals table creation failed',
				array( 'source' => 'warranty-cart-db' )
			);

			return false;
		}

		return true;
	}

	/**
	 * Check if database needs upgrade
	 *
	 * @return bool
	 */
	public static function needs_upgrade(): bool {
		$current_version = \get_option( 'warranty_cart_db_version', '0' );

		return version_compare( $current_version, self::DB_VERSION, '<' );
	}

	/**
	 * Run database upgrades
	 *
	 * @return bool True on success, false on failure.
	 */
	public static function upgrade(): bool {
		$current_version = \get_option( 'warranty_cart_db_version', '0' );

		// If no version set, assume fresh install
		if ( '0' === $current_version ) {
			$result = self::create_tables();
			if ( $result ) {
				\wc_get_logger()->info( 'Fresh warranty database installation completed', array( 'source' => 'warranty-cart-db' ) );
			}

			return $result;
		}

		// Check if upgrade is needed
		if ( ! self::needs_upgrade() ) {
			return true; // Already up to date
		}

		\wc_get_logger()->info(
			\sprintf( 'Upgrading warranty database from version %s to %s', $current_version, self::DB_VERSION ),
			array( 'source' => 'warranty-cart-db' )
		);

		// For now, just recreate tables (future versions may need migration logic)
		$result = self::create_tables();

		if ( $result ) {
			\wc_get_logger()->info( 'Warranty database upgrade completed successfully', array( 'source' => 'warranty-cart-db' ) );
		} else {
			\wc_get_logger()->error( 'Warranty database upgrade failed', array( 'source' => 'warranty-cart-db' ) );
		}

		return $result;
	}

	/**
	 * Get current database version
	 *
	 * @return string
	 */
	public static function get_version(): string {
		return \get_option( 'warranty_cart_db_version', '0' );
	}

	/**
	 * Check if database tables exist
	 *
	 * @return bool
	 */
	public static function tables_exist(): bool {
		global $wpdb;

		$table_name   = $wpdb->prefix . 'warranty_cart_warranties';
		$table_exists = $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $table_name ) );

		return ! empty( $table_exists );
	}
}
