<?php

declare( strict_types=1 );

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

// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching

/**
 * Handles plugin activation, deactivation, DB table creation, and upgrades.
 */
class Playard_Install {

	const DB_VERSION        = '1.5.0';
	const DB_VERSION_OPTION = 'playard_db_version';

	// -------------------------------------------------------------------------
	// Activation / Deactivation
	// -------------------------------------------------------------------------

	public static function activate(): void {
		self::create_tables();
		self::set_default_options();
		self::schedule_events();

		// Flag for post-activation redirect to the Playard dashboard.
		set_transient( 'playard_activation_redirect', true, 30 );

		do_action( 'playard_activated' );
	}

	public static function deactivate(): void {
		self::unschedule_events();

		do_action( 'playard_deactivated' );
	}

	/**
	 * Run on every plugins_loaded to apply any pending schema changes after an update.
	 */
	public static function maybe_upgrade(): void {
		$installed = get_option( self::DB_VERSION_OPTION, '0' );

		if ( version_compare( (string) $installed, self::DB_VERSION, '<' ) ) {
			self::create_tables();
		}

		// Safety net: dbDelta does not reliably add columns to existing tables.
		self::ensure_booked_slots_column();
		self::ensure_avatar_url_column();
		// Migration: remove legacy slot_locks table (replaced by pending-booking-as-lock).
		self::drop_slot_locks_table();
		// Migration: unschedule legacy lock-cleanup cron and register new stale-booking cron.
		$old_ts = wp_next_scheduled( 'playard_cleanup_slot_locks' );
		if ( $old_ts ) {
			wp_unschedule_event( $old_ts, 'playard_cleanup_slot_locks' );
		}
		self::schedule_events();
	}

	private static function drop_slot_locks_table(): void {
		global $wpdb;
		$table = $wpdb->prefix . 'playard_slot_locks';
		// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.DirectDatabaseQuery.SchemaChange,PluginCheck.Security.DirectDB.UnescapedDBParameter
		$exists = $wpdb->get_var( "SHOW TABLES LIKE '{$table}'" );
		if ( $exists ) {
			$wpdb->query( "DROP TABLE IF EXISTS `{$table}`" );
		}
		// phpcs:enable WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.DirectDatabaseQuery.SchemaChange,PluginCheck.Security.DirectDB.UnescapedDBParameter
	}

	private static function ensure_booked_slots_column(): void {
		global $wpdb;
		$table = $wpdb->prefix . 'playard_bookings';
		// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.DirectDatabaseQuery.SchemaChange,PluginCheck.Security.DirectDB.UnescapedDBParameter
		$col   = $wpdb->get_row( "SHOW COLUMNS FROM `{$table}` LIKE 'booked_slots'" );
		if ( ! $col ) {
			$wpdb->query( "ALTER TABLE `{$table}` ADD COLUMN `booked_slots` TEXT NULL AFTER `slots_count`" );
		}
		// phpcs:enable WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.DirectDatabaseQuery.SchemaChange,PluginCheck.Security.DirectDB.UnescapedDBParameter
	}

	private static function ensure_avatar_url_column(): void {
		global $wpdb;
		$table = $wpdb->prefix . 'playard_customers';
		// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.DirectDatabaseQuery.SchemaChange,PluginCheck.Security.DirectDB.UnescapedDBParameter
		$col   = $wpdb->get_row( "SHOW COLUMNS FROM `{$table}` LIKE 'avatar_url'" );
		if ( ! $col ) {
			$wpdb->query( "ALTER TABLE `{$table}` ADD COLUMN `avatar_url` VARCHAR(500) NOT NULL DEFAULT '' AFTER `email`" );
		}
		// phpcs:enable WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.DirectDatabaseQuery.SchemaChange,PluginCheck.Security.DirectDB.UnescapedDBParameter
	}

	// -------------------------------------------------------------------------
	// DB Tables
	// -------------------------------------------------------------------------

	public static function create_tables(): void {
		global $wpdb;

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

		$charset_collate = $wpdb->get_charset_collate();

		dbDelta( self::get_table_schemas( $charset_collate ) );

		update_option( self::DB_VERSION_OPTION, self::DB_VERSION );

		do_action( 'playard_tables_created' );
	}

	/**
	 * Returns an array of CREATE TABLE SQL strings, one per table.
	 * dbDelta rules: PRIMARY KEY  (two spaces), KEY not INDEX, each column on its own line.
	 *
	 * @param string $charset_collate Output of $wpdb->get_charset_collate().
	 * @return array<string>
	 */
	private static function get_table_schemas( string $charset_collate ): array {
		global $wpdb;

		return [

			// ------------------------------------------------------------------
			// Facilities — one per installation (Free), unlimited (Pro)
			// ------------------------------------------------------------------
			"CREATE TABLE {$wpdb->prefix}playard_facilities (
  id               BIGINT UNSIGNED  NOT NULL AUTO_INCREMENT,
  name             VARCHAR(255)     NOT NULL DEFAULT '',
  description      TEXT             NULL,
  address          TEXT             NULL,
  lat              DECIMAL(10,8)    NOT NULL DEFAULT 0.00000000,
  lng              DECIMAL(11,8)    NOT NULL DEFAULT 0.00000000,
  logo_url         VARCHAR(500)     NOT NULL DEFAULT '',
  cover_photo_url  VARCHAR(500)     NOT NULL DEFAULT '',
  phone            VARCHAR(20)      NOT NULL DEFAULT '',
  email            VARCHAR(255)     NOT NULL DEFAULT '',
  timezone         VARCHAR(100)     NOT NULL DEFAULT 'America/New_York',
  currency         VARCHAR(10)      NOT NULL DEFAULT 'USD',
  open_time        TIME             NOT NULL DEFAULT '06:00:00',
  close_time       TIME             NOT NULL DEFAULT '22:00:00',
  open_days        LONGTEXT         NOT NULL,
  status           VARCHAR(20)      NOT NULL DEFAULT 'active',
  created_at       DATETIME         NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at       DATETIME         NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY  (id),
  KEY status (status)
) $charset_collate;",

			// ------------------------------------------------------------------
			// Fields / Courts
			// ------------------------------------------------------------------
			"CREATE TABLE {$wpdb->prefix}playard_fields (
  id               BIGINT UNSIGNED   NOT NULL AUTO_INCREMENT,
  facility_id      BIGINT UNSIGNED   NOT NULL DEFAULT 1,
  name             VARCHAR(255)      NOT NULL DEFAULT '',
  description      TEXT              NOT NULL,
  photos           LONGTEXT          NOT NULL,
  sports           LONGTEXT          NOT NULL,
  capacity         INT UNSIGNED      NOT NULL DEFAULT 1,
  slot_duration    INT UNSIGNED      NOT NULL DEFAULT 60,
  min_advance      INT UNSIGNED      NOT NULL DEFAULT 0,
  max_advance      INT UNSIGNED      NOT NULL DEFAULT 30,
  buffer_time      INT UNSIGNED      NOT NULL DEFAULT 0,
  base_price       DECIMAL(10,2)     NOT NULL DEFAULT 0.00,
  peak_price       DECIMAL(10,2)     NOT NULL DEFAULT 0.00,
  peak_hours       LONGTEXT          NOT NULL,
  weekend_price    DECIMAL(10,2)     NOT NULL DEFAULT 0.00,
  payment_mode     VARCHAR(20)       NOT NULL DEFAULT 'cash',
  deposit_percent  INT UNSIGNED      NOT NULL DEFAULT 0,
  status           VARCHAR(20)       NOT NULL DEFAULT 'active',
  sort_order       INT UNSIGNED      NOT NULL DEFAULT 0,
  created_at       DATETIME          NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at       DATETIME          NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY  (id),
  KEY facility_id (facility_id),
  KEY status_sort (status, sort_order)
) $charset_collate;",

			// ------------------------------------------------------------------
			// Blocked Slots — admin-defined unavailable windows
			// ------------------------------------------------------------------
			"CREATE TABLE {$wpdb->prefix}playard_blocked_slots (
  id           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  field_id     BIGINT UNSIGNED NOT NULL,
  blocked_date DATE            NOT NULL,
  start_time   TIME            NOT NULL,
  end_time     TIME            NOT NULL,
  reason       VARCHAR(255)    NOT NULL DEFAULT '',
  created_at   DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY  (id),
  KEY field_date (field_id, blocked_date)
) $charset_collate;",

			// ------------------------------------------------------------------
			// Customers
			// ------------------------------------------------------------------
			"CREATE TABLE {$wpdb->prefix}playard_customers (
  id             BIGINT UNSIGNED  NOT NULL AUTO_INCREMENT,
  name           VARCHAR(255)     NOT NULL DEFAULT '',
  phone          VARCHAR(20)      NOT NULL DEFAULT '',
  email          VARCHAR(255)     NOT NULL DEFAULT '',
  avatar_url     VARCHAR(500)     NOT NULL DEFAULT '',
  password_hash  VARCHAR(255)     NOT NULL DEFAULT '',
  notes          TEXT             NULL,
  total_bookings INT UNSIGNED     NOT NULL DEFAULT 0,
  total_spent    DECIMAL(12,2)    NOT NULL DEFAULT 0.00,
  created_at     DATETIME         NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at     DATETIME         NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY  (id),
  UNIQUE KEY phone (phone),
  KEY email (email)
) $charset_collate;",

			// ------------------------------------------------------------------
			// Bookings
			// ------------------------------------------------------------------
			"CREATE TABLE {$wpdb->prefix}playard_bookings (
  id                  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  booking_ref         VARCHAR(20)     NOT NULL DEFAULT '',
  field_id            BIGINT UNSIGNED NOT NULL,
  customer_id         BIGINT UNSIGNED NOT NULL,
  booking_date        DATE            NOT NULL,
  start_time          TIME            NOT NULL,
  end_time            TIME            NOT NULL,
  slots_count         TINYINT UNSIGNED NOT NULL DEFAULT 1,
  booked_slots        TEXT            NULL,
  sport               VARCHAR(100)    NOT NULL DEFAULT '',
  amount_total        DECIMAL(10,2)   NOT NULL DEFAULT 0.00,
  amount_paid         DECIMAL(10,2)   NOT NULL DEFAULT 0.00,
  amount_due          DECIMAL(10,2)   NOT NULL DEFAULT 0.00,
  payment_mode        VARCHAR(20)     NOT NULL DEFAULT 'cash',
  payment_status      VARCHAR(20)     NOT NULL DEFAULT 'pending',
  booking_status      VARCHAR(20)     NOT NULL DEFAULT 'pending',
  payment_gateway     VARCHAR(50)     NOT NULL DEFAULT '',
  payment_intent_id   VARCHAR(255)    NOT NULL DEFAULT '',
  notes               TEXT            NULL,
  admin_notes         TEXT            NULL,
  cancelled_at        DATETIME        NULL DEFAULT NULL,
  cancellation_reason TEXT            NULL,
  created_at          DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at          DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY  (id),
  UNIQUE KEY booking_ref (booking_ref),
  KEY field_date (field_id, booking_date),
  KEY field_date_time (field_id, booking_date, start_time),
  KEY customer_id (customer_id),
  KEY booking_status (booking_status),
  KEY payment_status (payment_status)
) $charset_collate;",

			// ------------------------------------------------------------------
			// Payments — one row per transaction (payment or refund)
			// ------------------------------------------------------------------
			"CREATE TABLE {$wpdb->prefix}playard_payments (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  booking_id  BIGINT UNSIGNED NOT NULL,
  amount      DECIMAL(10,2)   NOT NULL DEFAULT 0.00,
  gateway     VARCHAR(50)     NOT NULL DEFAULT '',
  gateway_ref VARCHAR(255)    NOT NULL DEFAULT '',
  type        VARCHAR(20)     NOT NULL DEFAULT 'payment',
  status      VARCHAR(20)     NOT NULL DEFAULT 'pending',
  created_at  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY  (id),
  KEY booking_id (booking_id)
) $charset_collate;",

			// ------------------------------------------------------------------
			// OTP — phone verification codes
			// ------------------------------------------------------------------
			"CREATE TABLE {$wpdb->prefix}playard_otp (
  id         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  phone      VARCHAR(20)     NOT NULL DEFAULT '',
  otp_code   VARCHAR(10)     NOT NULL DEFAULT '',
  expires_at DATETIME        NOT NULL,
  verified   TINYINT(1)      NOT NULL DEFAULT 0,
  created_at DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY  (id),
  KEY phone (phone),
  KEY expires_at (expires_at)
) $charset_collate;",

		];
	}

	// -------------------------------------------------------------------------
	// Default Options
	// -------------------------------------------------------------------------

	private static function set_default_options(): void {
		$defaults = [
			'playard_version'              => PLAYARD_VERSION,
			'playard_onboarding_complete'  => false,
			'playard_settings'             => [
				'currency'           => 'USD',
				'timezone'           => 'America/New_York',
				'slot_lock_minutes'  => 5,
				'otp_expiry_minutes' => 10,
				'booking_ref_prefix' => 'PY',
				'auth'               => [
					'require_pw_otp' => false,
				],
				'notifications'      => [
					'from_name'                   => '',
					'from_email'                  => '',
					'account_created'             => true,
					'set_password_invitation'     => true,
					'booking_confirmed_customer'  => true,
					'booking_confirmed_owner'     => true,
					'booking_cancelled_customer'  => true,
					'booking_status_changed'      => true,
					'reminders_enabled'           => true,
				],
			],
		];

		foreach ( $defaults as $key => $value ) {
			if ( false === get_option( $key ) ) {
				add_option( $key, $value, '', 'no' );
			}
		}

		do_action( 'playard_default_options_set' );
	}

	// -------------------------------------------------------------------------
	// Scheduled Events
	// -------------------------------------------------------------------------

	public static function register_cron_intervals( array $schedules ): array {
		$schedules['playard_five_minutes'] = [
			'interval' => 300,
			'display'  => __( 'Every 5 Minutes (Playard)', 'playard' ),
		];
		return $schedules;
	}

	private static function schedule_events(): void {
		if ( ! wp_next_scheduled( 'playard_cleanup_stale_bookings' ) ) {
			wp_schedule_event( time(), 'playard_five_minutes', 'playard_cleanup_stale_bookings' );
		}

		if ( ! wp_next_scheduled( 'playard_cleanup_otp' ) ) {
			wp_schedule_event( time(), 'hourly', 'playard_cleanup_otp' );
		}

		if ( ! wp_next_scheduled( 'playard_send_reminders' ) ) {
			wp_schedule_event( time(), 'playard_five_minutes', 'playard_send_reminders' );
		}

		if ( ! wp_next_scheduled( 'playard_auto_complete_bookings' ) ) {
			wp_schedule_event( time(), 'playard_five_minutes', 'playard_auto_complete_bookings' );
		}

		do_action( 'playard_events_scheduled' );
	}

	private static function unschedule_events(): void {
		foreach ( [ 'playard_cleanup_stale_bookings', 'playard_cleanup_slot_locks', 'playard_cleanup_otp', 'playard_send_reminders', 'playard_auto_complete_bookings' ] as $hook ) {
			$timestamp = wp_next_scheduled( $hook );
			if ( $timestamp ) {
				wp_unschedule_event( $timestamp, $hook );
			}
		}

		do_action( 'playard_events_unscheduled' );
	}

	// -------------------------------------------------------------------------
	// Roles & Capabilities
	// -------------------------------------------------------------------------

	public static function setup_roles(): void {
		$admin_role = get_role( 'administrator' );
		if ( $admin_role ) {
			$admin_role->add_cap( 'playard_admin' );
			$admin_role->add_cap( 'playard_staff' );
		}

		if ( ! get_role( 'playard_staff' ) ) {
			add_role( 'playard_staff', __( 'Playard Staff', 'playard' ), [
				'read'          => true,
				'playard_staff' => true,
			] );
		}
	}

	public static function remove_roles(): void {
		remove_role( 'playard_staff' );

		$admin_role = get_role( 'administrator' );
		if ( $admin_role ) {
			$admin_role->remove_cap( 'playard_admin' );
			$admin_role->remove_cap( 'playard_staff' );
		}
	}
}
