<?php
/**
 * DB schema (dbDelta).
 *
 * @package DatvetaucaotocBooking
 */

declare(strict_types=1);

if ( ! defined( 'ABSPATH' ) ) {
	exit;
}

final class Datvetau_Booking_Database {

	public static function table( string $suffix ): string {
		global $wpdb;
		return $wpdb->prefix . 'datvetau_' . $suffix;
	}

	public static function install(): void {
		global $wpdb;

		require_once ABSPATH . 'wp-admin/includes/upgrade.php';

		$charset = $wpdb->get_charset_collate();
		$routes  = self::table( 'routes' );
		$trips   = self::table( 'trips' );
		$bookings = self::table( 'bookings' );
		$passengers = self::table( 'passengers' );

		$sql_routes = "CREATE TABLE {$routes} (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			slug varchar(191) NOT NULL,
			label varchar(255) NOT NULL,
			reverse_route_id bigint(20) unsigned NULL,
			sort_order int(11) NOT NULL DEFAULT 0,
			is_active tinyint(1) NOT NULL DEFAULT 1,
			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 slug (slug),
			KEY is_active (is_active)
		) {$charset};";

		$sql_trips = "CREATE TABLE {$trips} (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			route_id bigint(20) unsigned NOT NULL,
			trip_date date NOT NULL,
			departure_time time NOT NULL,
			arrival_time time NOT NULL,
			operator_name varchar(255) NOT NULL,
			operator_logo_url varchar(512) NULL,
			seat_class varchar(8) NOT NULL DEFAULT 'eco',
			price_adult_eco_base int(11) NOT NULL DEFAULT 0,
			price_adult int(11) NOT NULL DEFAULT 0,
			price_child int(11) NOT NULL DEFAULT 0,
			price_senior int(11) NOT NULL DEFAULT 0,
			capacity int(11) NOT NULL DEFAULT 0,
			seats_remaining int(11) NOT NULL DEFAULT 0,
			status varchar(16) 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 route_date (route_id,trip_date),
			KEY trip_lookup (route_id,trip_date,seat_class,departure_time)
		) {$charset};";

		$sql_bookings = "CREATE TABLE {$bookings} (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			booking_code varchar(32) NOT NULL,
			status varchar(32) NOT NULL DEFAULT 'pending_payment',
			trip_type varchar(16) NOT NULL,
			route_id bigint(20) unsigned NOT NULL,
			outbound_trip_id bigint(20) unsigned NOT NULL,
			inbound_trip_id bigint(20) unsigned NULL,
			seat_class varchar(8) NOT NULL,
			ticket_count int(11) NOT NULL DEFAULT 0,
			total_vnd int(11) NOT NULL DEFAULT 0,
			booker_name varchar(255) NOT NULL,
			booker_phone varchar(64) NOT NULL,
			booker_email varchar(255) NULL,
			booker_note 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_code (booking_code),
			KEY status (status),
			KEY created_at (created_at)
		) {$charset};";

		$sql_passengers = "CREATE TABLE {$passengers} (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			booking_id bigint(20) unsigned NOT NULL,
			full_name varchar(255) NOT NULL,
			birth_date varchar(16) NOT NULL,
			nationality varchar(128) NOT NULL,
			id_number varchar(64) NOT NULL,
			hometown varchar(255) NOT NULL,
			fare_category varchar(16) NOT NULL,
			unit_price_vnd int(11) NOT NULL DEFAULT 0,
			legs_count int(11) NOT NULL DEFAULT 1,
			PRIMARY KEY  (id),
			KEY booking_id (booking_id)
		) {$charset};";

		dbDelta( $sql_routes );
		dbDelta( $sql_trips );
		dbDelta( $sql_bookings );
		dbDelta( $sql_passengers );

		// Dong bo cot gia sau dbDelta (site da co du lieu cu).
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$wpdb->query(
			"UPDATE {$trips} SET price_adult = price_adult_eco_base WHERE price_adult = 0 AND price_adult_eco_base > 0"
		);

		update_option( 'datvetau_booking_db_version', DATVETAU_BOOKING_VERSION );
	}
}