<?php

declare(strict_types=1);

namespace FlexatechTableReservations\Install;

defined( 'ABSPATH' ) || exit;

/**
 * Owns the schema. A fresh install creates the final shape at once.
 *
 * DB changelog:
 *   v1 to v7: development versions before the first release, which also
 *       carried the service-booking tables this plugin started from.
 *   v8: the restaurant schema. fxtr_areas, fxtr_tables, fxtr_shifts and
 *       fxtr_closures describe the floor and the opening times;
 *       fxtr_reservations and fxtr_reservation_tables hold the bookings,
 *       one row per table a party sits at. Editors and Administrators get
 *       the fxtr_manage_reservations capability (Roles). The old
 *       fxtr_bookings and fxtr_booking_waitlist tables are no longer made;
 *       a site that has them keeps them, untouched.
 *   v9: fxtr_reservations.gcal_event_id and gcal_calendar, the Google
 *       Calendar event a reservation was copied to and its calendar.
 *   v10: fxtr_reservations.fields, the guest's answers to the owner's own
 *       booking form fields as JSON (Form\FormAnswers).
 *   v11: fxtr_shifts.about and image_id, the text and photo a guest sees
 *       when picking a time in the shift.
 */
final class Migrator {

	public const DB_VERSION     = 11;
	public const VERSION_OPTION = 'fxtr_db_version';

	public static function migrate(): void {
		require_once ABSPATH . 'wp-admin/includes/upgrade.php';

		global $wpdb;
		self::restaurant( $wpdb->get_charset_collate() );
		Roles::grant();

		update_option( self::VERSION_OPTION, self::DB_VERSION );
	}

	/**
	 * Times are the restaurant's local time.
	 * Shift days are a bitmask, bit 0 for Sunday; turn_times is JSON.
	 */
	private static function restaurant( string $charset_collate ): void {
		global $wpdb;
		$prefix = $wpdb->prefix . 'fxtr_';

		dbDelta(
			"CREATE TABLE {$prefix}areas (
  id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  name varchar(100) NOT NULL DEFAULT '',
  online tinyint(1) NOT NULL DEFAULT 1,
  sort int(11) NOT NULL DEFAULT 0,
  created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY  (id)
) {$charset_collate};"
		);

		dbDelta(
			"CREATE TABLE {$prefix}tables (
  id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  area_id bigint(20) unsigned NOT NULL DEFAULT 0,
  name varchar(50) NOT NULL DEFAULT '',
  min_party smallint(5) unsigned NOT NULL DEFAULT 1,
  max_party smallint(5) unsigned NOT NULL DEFAULT 2,
  online tinyint(1) NOT NULL DEFAULT 1,
  combine_group varchar(40) NOT NULL DEFAULT '',
  sort int(11) NOT NULL DEFAULT 0,
  created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY  (id),
  KEY area_id (area_id)
) {$charset_collate};"
		);

		dbDelta(
			"CREATE TABLE {$prefix}shifts (
  id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  name varchar(100) NOT NULL DEFAULT '',
  days tinyint(3) unsigned NOT NULL DEFAULT 0,
  opens time NOT NULL DEFAULT '00:00:00',
  last_seating time NOT NULL DEFAULT '00:00:00',
  slot_interval smallint(5) unsigned NOT NULL DEFAULT 15,
  max_covers smallint(5) unsigned NOT NULL DEFAULT 0,
  turn_default smallint(5) unsigned NOT NULL DEFAULT 90,
  turn_times text NULL,
  active tinyint(1) NOT NULL DEFAULT 1,
  about text NULL,
  image_id bigint(20) unsigned NOT NULL DEFAULT 0,
  created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY  (id)
) {$charset_collate};"
		);

		dbDelta(
			"CREATE TABLE {$prefix}closures (
  id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  starts_on date NOT NULL,
  ends_on date NOT NULL,
  shift_id bigint(20) unsigned NOT NULL DEFAULT 0,
  note varchar(191) NOT NULL DEFAULT '',
  created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY  (id),
  KEY dates (starts_on,ends_on)
) {$charset_collate};"
		);

		dbDelta(
			"CREATE TABLE {$prefix}reservations (
  id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  reservation_date date NOT NULL,
  reservation_time time NOT NULL,
  party_size smallint(5) unsigned NOT NULL DEFAULT 2,
  duration smallint(5) unsigned NOT NULL DEFAULT 90,
  shift_id bigint(20) unsigned NOT NULL DEFAULT 0,
  customer_name varchar(191) NOT NULL DEFAULT '',
  phone varchar(40) NOT NULL DEFAULT '',
  email varchar(191) NOT NULL DEFAULT '',
  notes text NULL,
  fields longtext NULL,
  status varchar(20) NOT NULL DEFAULT 'pending',
  source varchar(20) NOT NULL DEFAULT 'online',
  reminded tinyint(1) NOT NULL DEFAULT 0,
  gcal_event_id varchar(255) NOT NULL DEFAULT '',
  gcal_calendar varchar(255) NOT NULL DEFAULT '',
  created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY  (id),
  KEY day (reservation_date,status),
  KEY status (status),
  KEY shift_id (shift_id)
) {$charset_collate};"
		);

		// reservation_date is copied in so a table's day is one index lookup.
		dbDelta(
			"CREATE TABLE {$prefix}reservation_tables (
  reservation_id bigint(20) unsigned NOT NULL,
  table_id bigint(20) unsigned NOT NULL,
  reservation_date date NOT NULL,
  PRIMARY KEY  (reservation_id,table_id),
  KEY table_day (table_id,reservation_date)
) {$charset_collate};"
		);
	}

	public static function maybe_upgrade(): void {
		if ( (int) get_option( self::VERSION_OPTION, 0 ) !== self::DB_VERSION ) {
			self::migrate();
		}
	}
}