<?php
declare(strict_types=1);

namespace WebCalendar\WP\Database;

defined( 'ABSPATH' ) || exit;

/**
 * Defines the database schema for WebCalendar.
 */
class Schema {

	/**
	 * Get the schema for the MVP tables.
	 *
	 * An index on a VARCHAR wider than 191 characters indexes only its first
	 * 191, as WordPress core does: in utf8mb4 a full VARCHAR(255) key is 1020
	 * bytes, and InnoDB refuses anything over 767 under COMPACT/REDUNDANT row
	 * formats and on MySQL 5.6 / older MariaDB. dbDelta hides that refusal,
	 * so the table is silently missing (SchemaTest guards every index).
	 *
	 * @param string $prefix Table prefix.
	 * @return array<string> List of CREATE TABLE statements.
	 */
	public static function get_mvp_tables( string $prefix ): array {
		$charset_collate = '';
		/** @var \stdClass $wpdb */
		$wpdb = $GLOBALS['wpdb'];
		if ( isset( $wpdb->charset ) && is_string( $wpdb->charset ) && ! empty( $wpdb->charset ) ) {
			$charset_collate = "DEFAULT CHARACTER SET {$wpdb->charset}";
		}
		if ( isset( $wpdb->collate ) && is_string( $wpdb->collate ) && ! empty( $wpdb->collate ) ) {
			$charset_collate .= " COLLATE {$wpdb->collate}";
		}

		return [
			"CREATE TABLE {$prefix}webcal_entry (
				cal_id INT NOT NULL AUTO_INCREMENT,
				cal_group_id INT NULL,
				cal_ext_for_id INT NULL,
				cal_create_by VARCHAR(60) NOT NULL,
				cal_date INT NOT NULL,
				cal_time INT NULL,
				cal_mod_date INT,
				cal_mod_time INT,
				cal_duration INT NOT NULL,
				cal_due_date INT DEFAULT NULL,
				cal_due_time INT DEFAULT NULL,
				cal_location VARCHAR(255) DEFAULT NULL,
				cal_url VARCHAR(255) DEFAULT NULL,
				cal_completed INT DEFAULT NULL,
				cal_priority INT DEFAULT 5,
				cal_type CHAR(1) DEFAULT 'E',
				cal_access CHAR(1) DEFAULT 'P',
				cal_name VARCHAR(255) NOT NULL,
				cal_description TEXT,
				cal_uid VARCHAR(255) DEFAULT NULL,
				cal_sequence INT DEFAULT 0,
				cal_transp VARCHAR(11) DEFAULT 'OPAQUE',
				cal_status VARCHAR(20) DEFAULT NULL,
				cal_geo_lat DECIMAL(10,7) DEFAULT NULL,
				cal_geo_lon DECIMAL(10,7) DEFAULT NULL,
				cal_color VARCHAR(16) DEFAULT NULL,
				cal_conference VARCHAR(255) DEFAULT NULL,
				cal_organizer VARCHAR(255) DEFAULT NULL,
				cal_created INT DEFAULT NULL,
				cal_created_time INT DEFAULT NULL,
				cal_image VARCHAR(2048) DEFAULT NULL,
				cal_conference_label VARCHAR(60) DEFAULT NULL,
				cal_venue_id INT DEFAULT NULL,
				cal_organizer_id INT DEFAULT NULL,
				PRIMARY KEY (cal_id),
				KEY cal_date (cal_date),
				KEY cal_create_by (cal_create_by),
				KEY cal_access (cal_access),
				UNIQUE KEY cal_uid (cal_uid(191))
			) $charset_collate;",

			"CREATE TABLE {$prefix}webcal_entry_repeats (
				cal_id INT NOT NULL,
				cal_type VARCHAR(20),
				cal_end INT,
				cal_endtime INT DEFAULT NULL,
				cal_frequency INT DEFAULT 1,
				cal_days CHAR(7),
				cal_bymonth VARCHAR(50) DEFAULT NULL,
				cal_bymonthday VARCHAR(100) DEFAULT NULL,
				cal_byday VARCHAR(100) DEFAULT NULL,
				cal_bysetpos VARCHAR(50) DEFAULT NULL,
				cal_byweekno VARCHAR(50) DEFAULT NULL,
				cal_byyearday VARCHAR(50) DEFAULT NULL,
				cal_wkst CHAR(2) DEFAULT 'MO',
				cal_count INT DEFAULT NULL,
				cal_byhour VARCHAR(50) DEFAULT NULL,
				cal_byminute VARCHAR(50) DEFAULT NULL,
				cal_bysecond VARCHAR(50) DEFAULT NULL,
				PRIMARY KEY (cal_id)
			) $charset_collate;",

			"CREATE TABLE {$prefix}webcal_entry_repeats_not (
				cal_id INT NOT NULL,
				cal_date INT NOT NULL,
				cal_exdate INT NOT NULL DEFAULT 1,
				PRIMARY KEY (cal_id, cal_date)
			) $charset_collate;",

			"CREATE TABLE {$prefix}webcal_entry_user (
				cal_id INT NOT NULL,
				cal_login VARCHAR(60) NOT NULL,
				cal_status CHAR(1) DEFAULT 'A',
				cal_category INT DEFAULT NULL,
				cal_percent INT DEFAULT 0 NOT NULL,
				PRIMARY KEY (cal_id, cal_login),
				KEY cal_login_id (cal_login, cal_id)
			) $charset_collate;",

			"CREATE TABLE {$prefix}webcal_categories (
				cat_id INT NOT NULL,
				cat_owner VARCHAR(60) DEFAULT '' NOT NULL,
				cat_name VARCHAR(80) NOT NULL,
				cat_color VARCHAR(16) NULL,
				cat_status CHAR(1) DEFAULT 'A',
				cat_is_tag CHAR(1) DEFAULT 'N',
				cat_icon_mime VARCHAR(32) DEFAULT NULL,
				cat_icon_blob LONGBLOB DEFAULT NULL,
				PRIMARY KEY (cat_id, cat_owner)
			) $charset_collate;",

			"CREATE TABLE {$prefix}webcal_entry_categories (
				cal_id INT NOT NULL,
				cat_id INT NOT NULL,
				cat_order INT DEFAULT 0 NOT NULL,
				cat_owner VARCHAR(60) DEFAULT '' NOT NULL,
				PRIMARY KEY (cal_id, cat_id, cat_order, cat_owner),
				KEY cat_id (cat_id)
			) $charset_collate;",

			"CREATE TABLE {$prefix}webcal_user_pref (
				cal_login VARCHAR(60) NOT NULL,
				cal_setting VARCHAR(25) NOT NULL,
				cal_value VARCHAR(100) NULL,
				PRIMARY KEY (cal_login, cal_setting)
			) $charset_collate;",

			"CREATE TABLE {$prefix}webcal_config (
				cal_setting VARCHAR(50) NOT NULL,
				cal_value VARCHAR(100) NULL,
				PRIMARY KEY (cal_setting)
			) $charset_collate;",

			"CREATE TABLE {$prefix}webcal_entry_log (
				cal_log_id INT NOT NULL AUTO_INCREMENT,
				cal_entry_id INT NOT NULL,
				cal_login VARCHAR(60) NOT NULL,
				cal_user_cal VARCHAR(60) NULL,
				cal_type CHAR(1) NOT NULL,
				cal_date INT NOT NULL,
				cal_time INT NULL,
				cal_text TEXT,
				PRIMARY KEY (cal_log_id),
				KEY cal_entry_id (cal_entry_id)
			) $charset_collate;",

			"CREATE TABLE {$prefix}webcal_reminders (
				cal_id INT NOT NULL DEFAULT 0,
				cal_date INT NOT NULL DEFAULT 0,
				cal_offset INT NOT NULL DEFAULT 0,
				cal_related CHAR(1) NOT NULL DEFAULT 'S',
				cal_before CHAR(1) NOT NULL DEFAULT 'Y',
				cal_last_sent INT NOT NULL DEFAULT 0,
				cal_repeats INT NOT NULL DEFAULT 0,
				cal_duration INT NOT NULL DEFAULT 0,
				cal_times_sent INT NOT NULL DEFAULT 0,
				cal_action VARCHAR(12) NOT NULL DEFAULT 'EMAIL',
				cal_description TEXT DEFAULT NULL,
				cal_summary VARCHAR(255) DEFAULT NULL,
				cal_attendee VARCHAR(255) DEFAULT NULL,
				cal_attach VARCHAR(255) DEFAULT NULL,
				cal_time INT DEFAULT NULL,
				PRIMARY KEY (cal_id)
			) $charset_collate;",

			// cal_name is 60 in core's three schemas and in the PRD; this said
			// 25, so a WordPress install got a column less than half the
			// specified width. Harmless while nothing writes extras, and
			// exactly wrong once field labels — which routinely run past 25
			// characters — start being mapped into it.
			"CREATE TABLE {$prefix}webcal_site_extras (
				cal_id INT NOT NULL,
				cal_name VARCHAR(60) NOT NULL,
				cal_type INT NOT NULL,
				cal_date INT DEFAULT 0,
				cal_remind INT DEFAULT 0,
				cal_data TEXT,
				PRIMARY KEY (cal_id, cal_name)
			) $charset_collate;",

			"CREATE TABLE {$prefix}webcal_import (
				cal_import_id INT NOT NULL,
				cal_name VARCHAR(50) NULL,
				cal_date INT NOT NULL,
				cal_check_date INT NULL,
				cal_type VARCHAR(10) NOT NULL,
				cal_login VARCHAR(60) NULL,
				cal_md5 VARCHAR(32) NULL DEFAULT NULL,
				PRIMARY KEY (cal_import_id)
			) $charset_collate;",

			"CREATE TABLE {$prefix}webcal_import_data (
				cal_import_id INT NOT NULL,
				cal_id INT NOT NULL,
				cal_login VARCHAR(60) NOT NULL,
				cal_import_type VARCHAR(15) NOT NULL,
				cal_external_id VARCHAR(200) NULL,
				PRIMARY KEY (cal_id, cal_login),
				KEY cal_import_type (cal_import_type),
				KEY cal_external_id (cal_external_id(191))
			) $charset_collate;",

			"CREATE TABLE {$prefix}webcal_nonuser_cals (
				cal_login VARCHAR(60) NOT NULL,
				cal_lastname VARCHAR(60) DEFAULT NULL,
				cal_firstname VARCHAR(60) DEFAULT NULL,
				cal_admin VARCHAR(60) NOT NULL,
				cal_is_public CHAR(1) DEFAULT 'N' NOT NULL,
				cal_url VARCHAR(255) DEFAULT NULL,
				PRIMARY KEY  (cal_login)
			) $charset_collate;",

			"CREATE TABLE {$prefix}webcal_user_layers (
				cal_layerid INT NOT NULL AUTO_INCREMENT,
				cal_login VARCHAR(60) NOT NULL,
				cal_layeruser VARCHAR(60) NOT NULL,
				cal_color VARCHAR(60) DEFAULT NULL,
				cal_dups CHAR(1) DEFAULT 'N',
				PRIMARY KEY (cal_login, cal_layeruser),
				KEY cal_layerid (cal_layerid)
			) $charset_collate;",

			// Saved venues and organizers (core 4.4+, TEC parity Epic 22).
			"CREATE TABLE {$prefix}webcal_venue (
				venue_id INT NOT NULL,
				venue_name VARCHAR(100) NOT NULL,
				venue_address VARCHAR(100) DEFAULT NULL,
				venue_city VARCHAR(60) DEFAULT NULL,
				venue_state VARCHAR(60) DEFAULT NULL,
				venue_zip VARCHAR(20) DEFAULT NULL,
				venue_country VARCHAR(60) DEFAULT NULL,
				venue_lat DECIMAL(10,7) DEFAULT NULL,
				venue_lon DECIMAL(10,7) DEFAULT NULL,
				venue_url VARCHAR(255) DEFAULT NULL,
				venue_phone VARCHAR(40) DEFAULT NULL,
				PRIMARY KEY (venue_id),
				KEY venue_name (venue_name)
			) $charset_collate;",

			"CREATE TABLE {$prefix}webcal_organizer (
				organizer_id INT NOT NULL,
				organizer_name VARCHAR(100) NOT NULL,
				organizer_email VARCHAR(100) DEFAULT NULL,
				organizer_phone VARCHAR(40) DEFAULT NULL,
				organizer_url VARCHAR(255) DEFAULT NULL,
				PRIMARY KEY (organizer_id),
				KEY organizer_name (organizer_name)
			) $charset_collate;",

			// Ticketing (core 4.5+, TEC parity Epic 28).
			"CREATE TABLE {$prefix}webcal_ticket_type (
				ticket_type_id INT NOT NULL,
				cal_id INT NOT NULL,
				ticket_name VARCHAR(80) NOT NULL,
				ticket_price INT NOT NULL DEFAULT 0,
				ticket_currency CHAR(3) NOT NULL DEFAULT 'USD',
				ticket_capacity INT DEFAULT NULL,
				ticket_sale_start INT DEFAULT NULL,
				ticket_sale_end INT DEFAULT NULL,
				ticket_status CHAR(1) NOT NULL DEFAULT 'A',
				PRIMARY KEY (ticket_type_id),
				KEY cal_id (cal_id)
			) $charset_collate;",

			"CREATE TABLE {$prefix}webcal_ticket_order (
				order_id INT NOT NULL,
				ticket_type_id INT NOT NULL,
				cal_id INT NOT NULL,
				order_email VARCHAR(75) NOT NULL,
				order_name VARCHAR(80) NOT NULL,
				order_qty INT NOT NULL,
				order_amount INT NOT NULL,
				order_currency CHAR(3) NOT NULL,
				order_status CHAR(1) NOT NULL DEFAULT 'P',
				order_external_ref VARCHAR(120) DEFAULT NULL,
				order_created INT NOT NULL DEFAULT 0,
				PRIMARY KEY (order_id),
				KEY ticket_type_id (ticket_type_id),
				KEY cal_id (cal_id)
			) $charset_collate;",

			// Unconfirmed RSVPs live here, outside core's ticketing tables,
			// and deliberately hold no capacity. Core has no state for
			// "registered but unconfirmed": OrderStatus::capacityHolding()
			// is [PENDING, PAID] and a free order is created straight to
			// PAID, so any order that exists already owns a seat. Staging
			// the request here until the emailed link is clicked is what
			// stops an unauthenticated caller exhausting capacity in one
			// pass -- the seat is only claimed at confirm time, by
			// TicketService::purchase().
			//
			// rsvp_token_hash stores SHA-256 of the token, never the token
			// itself, so a database read yields no working confirm links.
			// Lookup is by hash, so there is no PHP-side secret comparison
			// to time.
			"CREATE TABLE {$prefix}webcal_rsvp_pending (
				rsvp_token_hash VARCHAR(64) NOT NULL,
				ticket_type_id INT NOT NULL,
				cal_id INT NOT NULL,
				rsvp_email VARCHAR(75) NOT NULL,
				rsvp_name VARCHAR(80) NOT NULL,
				rsvp_qty INT NOT NULL DEFAULT 1,
				rsvp_created INT NOT NULL DEFAULT 0,
				PRIMARY KEY (rsvp_token_hash),
				KEY cal_id (cal_id),
				KEY rsvp_created (rsvp_created),
				KEY cal_email (cal_id, rsvp_email)
			) $charset_collate;",

			"CREATE TABLE {$prefix}webcal_attendee (
				attendee_id INT NOT NULL,
				order_id INT NOT NULL,
				cal_id INT NOT NULL,
				attendee_name VARCHAR(80) NOT NULL,
				attendee_email VARCHAR(75) DEFAULT NULL,
				attendee_token VARCHAR(64) NOT NULL,
				attendee_checked_in INT DEFAULT NULL,
				PRIMARY KEY (attendee_id),
				UNIQUE KEY attendee_token (attendee_token),
				KEY cal_id (cal_id)
			) $charset_collate;",
		];
	}
}