<?php
/**
 * Database schema (custom tables) and upgrades.
 *
 * Money columns hold integer minor units (cents) and every booking stores its ISO 4217 currency.
 * Datetime columns are UTC. The schema already covers the "Lanjutan" features so later updates
 * only add rows, not data rewrites.
 *
 * @package Rentbaik
 */

namespace Rentbaik\Database;

defined( 'ABSPATH' ) || exit;

/**
 * Creates and upgrades the rentbaik_* tables.
 */
final class Schema {

	/**
	 * Current schema version. Bump when the SQL below changes.
	 */
	public const VERSION = '1.0.4';

	/**
	 * Option that stores the installed schema version.
	 */
	public const OPTION = 'rentbaik_db_version';

	/**
	 * Table names without the "{$wpdb->prefix}rentbaik_" prefix.
	 */
	public const TABLES = array(
		'locations',
		'delivery_zones',
		'units',
		'unit_events',
		'unit_blocks',
		'seasons',
		'rates',
		'extras',
		'coupons',
		'customers',
		'documents',
		'bookings',
		'booking_items',
		'payments',
		'activity_log',
	);

	/**
	 * Full table name for a short name.
	 *
	 * @param string $name Short table name, e.g. "bookings".
	 * @return string
	 */
	public static function table( string $name ): string {
		global $wpdb;
		return $wpdb->prefix . 'rentbaik_' . $name;
	}

	/**
	 * Create or update every table, then run data migrations.
	 *
	 * @return void
	 */
	public static function install(): void {
		$from = (string) get_option( self::OPTION, '' );

		require_once ABSPATH . 'wp-admin/includes/upgrade.php';
		dbDelta( self::sql() );

		Migrations::run( $from );
		update_option( self::OPTION, self::VERSION, true );
		wp_cache_set_last_changed( 'rentbaik_schema' );
	}

	/**
	 * Install or upgrade when the stored version differs from the code.
	 *
	 * @return void
	 */
	public static function maybe_upgrade(): void {
		if ( self::VERSION !== get_option( self::OPTION ) ) {
			self::install();
		}
	}

	/**
	 * Tables that are expected but missing in the database.
	 *
	 * @return string[] Short table names.
	 */
	public static function missing_tables(): array {
		global $wpdb;

		$cache_key = 'missing_tables:' . wp_cache_get_last_changed( 'rentbaik_schema' );
		$existing  = wp_cache_get( $cache_key, 'rentbaik' );

		if ( false === $existing ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- Schema check, result cached below.
			$existing = $wpdb->get_col( $wpdb->prepare( 'SHOW TABLES LIKE %s', $wpdb->esc_like( $wpdb->prefix . 'rentbaik_' ) . '%' ) );
			wp_cache_set( $cache_key, $existing, 'rentbaik' );
		}

		$missing = array();
		foreach ( self::TABLES as $name ) {
			if ( ! in_array( self::table( $name ), $existing, true ) ) {
				$missing[] = $name;
			}
		}
		return $missing;
	}

	/**
	 * Drop every plugin table (uninstall only).
	 *
	 * @return void
	 */
	public static function drop(): void {
		global $wpdb;
		foreach ( self::TABLES as $name ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange -- Uninstall removes the plugin's own tables.
			$wpdb->query( $wpdb->prepare( 'DROP TABLE IF EXISTS %i', self::table( $name ) ) );
		}
		delete_option( self::OPTION );
	}

	/**
	 * CREATE TABLE statements in dbDelta format.
	 *
	 * @return string
	 */
	private static function sql(): string {
		global $wpdb;
		$c = $wpdb->get_charset_collate();
		$t = static function ( string $name ): string {
			return self::table( $name );
		};

		return "
CREATE TABLE {$t( 'locations' )} (
	id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
	name varchar(191) NOT NULL DEFAULT '',
	slug varchar(191) NOT NULL DEFAULT '',
	address text NULL,
	city varchar(191) NOT NULL DEFAULT '',
	region varchar(191) NOT NULL DEFAULT '',
	postcode varchar(32) NOT NULL DEFAULT '',
	country char(2) NOT NULL DEFAULT '',
	latitude decimal(10,7) NULL,
	longitude decimal(10,7) NULL,
	tax_rate decimal(5,2) NULL,
	phone varchar(64) NOT NULL DEFAULT '',
	email varchar(191) NOT NULL DEFAULT '',
	opening_hours longtext NULL,
	closed_dates longtext NULL,
	is_primary tinyint(1) unsigned NOT NULL DEFAULT 0,
	status varchar(20) NOT NULL DEFAULT 'active',
	sort_order int(11) NOT NULL DEFAULT 0,
	created_at datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
	updated_at datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
	PRIMARY KEY  (id),
	KEY slug (slug),
	KEY status (status),
	KEY is_primary (is_primary)
) $c;
CREATE TABLE {$t( 'delivery_zones' )} (
	id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
	location_id bigint(20) unsigned NOT NULL DEFAULT 0,
	name varchar(191) NOT NULL DEFAULT '',
	fee bigint(20) NOT NULL DEFAULT 0,
	fee_per_distance bigint(20) NOT NULL DEFAULT 0,
	max_distance decimal(10,2) NULL,
	status varchar(20) NOT NULL DEFAULT 'active',
	sort_order int(11) NOT NULL DEFAULT 0,
	PRIMARY KEY  (id),
	KEY location_id (location_id)
) $c;
CREATE TABLE {$t( 'units' )} (
	id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
	vehicle_id bigint(20) unsigned NOT NULL DEFAULT 0,
	location_id bigint(20) unsigned NOT NULL DEFAULT 0,
	plate_number varchar(64) NOT NULL DEFAULT '',
	color varchar(64) NOT NULL DEFAULT '',
	vin varchar(64) NOT NULL DEFAULT '',
	odometer int(10) unsigned NOT NULL DEFAULT 0,
	status varchar(20) NOT NULL DEFAULT 'available',
	photo_id bigint(20) unsigned NOT NULL DEFAULT 0,
	owner_name varchar(191) NOT NULL DEFAULT '',
	owner_share decimal(5,2) NULL,
	notes text NULL,
	created_at datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
	updated_at datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
	PRIMARY KEY  (id),
	KEY vehicle_id (vehicle_id),
	KEY location_id (location_id),
	KEY status (status),
	KEY plate_number (plate_number)
) $c;
CREATE TABLE {$t( 'unit_events' )} (
	id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
	unit_id bigint(20) unsigned NOT NULL DEFAULT 0,
	event_type varchar(20) NOT NULL DEFAULT '',
	title varchar(191) NOT NULL DEFAULT '',
	details text NULL,
	odometer int(10) unsigned NULL,
	due_on date NULL,
	due_odometer int(10) unsigned NULL,
	cost bigint(20) NOT NULL DEFAULT 0,
	attachment_ids text NULL,
	status varchar(20) NOT NULL DEFAULT 'open',
	created_by bigint(20) unsigned NOT NULL DEFAULT 0,
	created_at datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
	PRIMARY KEY  (id),
	KEY unit_id (unit_id),
	KEY event_type (event_type),
	KEY due_on (due_on)
) $c;
CREATE TABLE {$t( 'unit_blocks' )} (
	id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
	unit_id bigint(20) unsigned NOT NULL DEFAULT 0,
	booking_id bigint(20) unsigned NOT NULL DEFAULT 0,
	block_type varchar(20) NOT NULL DEFAULT 'booking',
	start_at datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
	end_at datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
	expires_at datetime NULL,
	note varchar(191) NOT NULL DEFAULT '',
	created_by bigint(20) unsigned NOT NULL DEFAULT 0,
	created_at datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
	PRIMARY KEY  (id),
	KEY unit_range (unit_id,start_at,end_at),
	KEY booking_id (booking_id),
	KEY expires_at (expires_at)
) $c;
CREATE TABLE {$t( 'seasons' )} (
	id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
	name varchar(191) NOT NULL DEFAULT '',
	season_type varchar(20) NOT NULL DEFAULT 'high',
	date_from date NOT NULL DEFAULT '0000-00-00',
	date_to date NOT NULL DEFAULT '0000-00-00',
	repeat_yearly tinyint(1) unsigned NOT NULL DEFAULT 0,
	adjustment_type varchar(20) NOT NULL DEFAULT 'percent',
	adjustment decimal(10,2) NOT NULL DEFAULT 0,
	min_duration int(10) unsigned NULL,
	status varchar(20) NOT NULL DEFAULT 'active',
	PRIMARY KEY  (id),
	KEY dates (date_from,date_to)
) $c;
CREATE TABLE {$t( 'rates' )} (
	id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
	vehicle_id bigint(20) unsigned NOT NULL DEFAULT 0,
	location_id bigint(20) unsigned NOT NULL DEFAULT 0,
	season_id bigint(20) unsigned NOT NULL DEFAULT 0,
	rate_unit varchar(10) NOT NULL DEFAULT 'day',
	min_duration int(10) unsigned NOT NULL DEFAULT 1,
	max_duration int(10) unsigned NULL,
	weekdays varchar(20) NOT NULL DEFAULT '',
	price bigint(20) NOT NULL DEFAULT 0,
	priority int(11) NOT NULL DEFAULT 0,
	PRIMARY KEY  (id),
	KEY vehicle_rate (vehicle_id,rate_unit),
	KEY season_id (season_id)
) $c;
CREATE TABLE {$t( 'extras' )} (
	id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
	extra_type varchar(20) NOT NULL DEFAULT 'extra',
	name varchar(191) NOT NULL DEFAULT '',
	description text NULL,
	price bigint(20) NOT NULL DEFAULT 0,
	charge_type varchar(20) NOT NULL DEFAULT 'per_rental',
	max_quantity int(10) unsigned NOT NULL DEFAULT 1,
	stock int(10) unsigned NULL,
	is_required tinyint(1) unsigned NOT NULL DEFAULT 0,
	vehicle_types varchar(191) NOT NULL DEFAULT '',
	deductible bigint(20) NULL,
	status varchar(20) NOT NULL DEFAULT 'active',
	sort_order int(11) NOT NULL DEFAULT 0,
	PRIMARY KEY  (id),
	KEY extra_type (extra_type),
	KEY status (status)
) $c;
CREATE TABLE {$t( 'coupons' )} (
	id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
	code varchar(64) NOT NULL DEFAULT '',
	discount_type varchar(20) NOT NULL DEFAULT 'percent',
	amount decimal(12,2) NOT NULL DEFAULT 0,
	valid_from datetime NULL,
	valid_to datetime NULL,
	usage_limit int(10) unsigned NULL,
	usage_count int(10) unsigned NOT NULL DEFAULT 0,
	min_duration int(10) unsigned NULL,
	status varchar(20) NOT NULL DEFAULT 'active',
	created_at datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
	PRIMARY KEY  (id),
	UNIQUE KEY code (code)
) $c;
CREATE TABLE {$t( 'customers' )} (
	id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
	user_id bigint(20) unsigned NOT NULL DEFAULT 0,
	first_name varchar(191) NOT NULL DEFAULT '',
	last_name varchar(191) NOT NULL DEFAULT '',
	email varchar(191) NOT NULL DEFAULT '',
	phone varchar(64) NOT NULL DEFAULT '',
	country char(2) NOT NULL DEFAULT '',
	address text NULL,
	stay_address text NULL,
	birth_date date NULL,
	license_number varchar(64) NOT NULL DEFAULT '',
	document_status varchar(20) NOT NULL DEFAULT 'none',
	is_blocked tinyint(1) unsigned NOT NULL DEFAULT 0,
	notes text NULL,
	created_at datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
	updated_at datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
	PRIMARY KEY  (id),
	KEY user_id (user_id),
	KEY email (email),
	KEY phone (phone)
) $c;
CREATE TABLE {$t( 'documents' )} (
	id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
	customer_id bigint(20) unsigned NOT NULL DEFAULT 0,
	booking_id bigint(20) unsigned NOT NULL DEFAULT 0,
	doc_type varchar(32) NOT NULL DEFAULT '',
	file_name varchar(191) NOT NULL DEFAULT '',
	mime_type varchar(100) NOT NULL DEFAULT '',
	document_number varchar(64) NOT NULL DEFAULT '',
	expires_on date NULL,
	status varchar(20) NOT NULL DEFAULT 'pending',
	verified_by bigint(20) unsigned NOT NULL DEFAULT 0,
	verified_at datetime NULL,
	delete_after date NULL,
	created_at datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
	PRIMARY KEY  (id),
	KEY customer_id (customer_id),
	KEY booking_id (booking_id),
	KEY delete_after (delete_after)
) $c;
CREATE TABLE {$t( 'bookings' )} (
	id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
	booking_number varchar(32) NOT NULL DEFAULT '',
	status varchar(20) NOT NULL DEFAULT 'pending',
	source varchar(20) NOT NULL DEFAULT 'web',
	customer_id bigint(20) unsigned NOT NULL DEFAULT 0,
	pickup_location_id bigint(20) unsigned NOT NULL DEFAULT 0,
	return_location_id bigint(20) unsigned NOT NULL DEFAULT 0,
	delivery_zone_id bigint(20) unsigned NOT NULL DEFAULT 0,
	delivery_address text NULL,
	pickup_at datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
	return_at datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
	actual_pickup_at datetime NULL,
	actual_return_at datetime NULL,
	currency char(3) NOT NULL DEFAULT '',
	subtotal bigint(20) NOT NULL DEFAULT 0,
	extras_total bigint(20) NOT NULL DEFAULT 0,
	fees_total bigint(20) NOT NULL DEFAULT 0,
	discount_total bigint(20) NOT NULL DEFAULT 0,
	tax_total bigint(20) NOT NULL DEFAULT 0,
	total bigint(20) NOT NULL DEFAULT 0,
	deposit_amount bigint(20) NOT NULL DEFAULT 0,
	deposit_status varchar(20) NOT NULL DEFAULT 'none',
	paid_total bigint(20) NOT NULL DEFAULT 0,
	payment_method varchar(32) NOT NULL DEFAULT '',
	coupon_id bigint(20) unsigned NOT NULL DEFAULT 0,
	wc_order_id bigint(20) unsigned NOT NULL DEFAULT 0,
	hold_expires_at datetime NULL,
	pickup_reminder_sent tinyint(1) unsigned NOT NULL DEFAULT 0,
	return_reminder_sent tinyint(1) unsigned NOT NULL DEFAULT 0,
	customer_note text NULL,
	internal_note text NULL,
	created_by bigint(20) unsigned NOT NULL DEFAULT 0,
	created_at datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
	updated_at datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
	PRIMARY KEY  (id),
	UNIQUE KEY booking_number (booking_number),
	KEY status (status),
	KEY customer_id (customer_id),
	KEY pickup_at (pickup_at),
	KEY return_at (return_at),
	KEY hold_expires_at (hold_expires_at)
) $c;
CREATE TABLE {$t( 'booking_items' )} (
	id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
	booking_id bigint(20) unsigned NOT NULL DEFAULT 0,
	item_type varchar(20) NOT NULL DEFAULT 'vehicle',
	vehicle_id bigint(20) unsigned NOT NULL DEFAULT 0,
	unit_id bigint(20) unsigned NOT NULL DEFAULT 0,
	extra_id bigint(20) unsigned NOT NULL DEFAULT 0,
	label varchar(191) NOT NULL DEFAULT '',
	quantity int(10) unsigned NOT NULL DEFAULT 1,
	duration decimal(10,2) NOT NULL DEFAULT 0,
	duration_unit varchar(10) NOT NULL DEFAULT '',
	unit_price bigint(20) NOT NULL DEFAULT 0,
	total bigint(20) NOT NULL DEFAULT 0,
	meta longtext NULL,
	PRIMARY KEY  (id),
	KEY booking_id (booking_id),
	KEY unit_id (unit_id)
) $c;
CREATE TABLE {$t( 'payments' )} (
	id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
	booking_id bigint(20) unsigned NOT NULL DEFAULT 0,
	payment_type varchar(20) NOT NULL DEFAULT 'payment',
	method varchar(32) NOT NULL DEFAULT '',
	amount bigint(20) NOT NULL DEFAULT 0,
	currency char(3) NOT NULL DEFAULT '',
	status varchar(20) NOT NULL DEFAULT 'pending',
	reference varchar(191) NOT NULL DEFAULT '',
	proof_attachment_id bigint(20) unsigned NOT NULL DEFAULT 0,
	note text NULL,
	paid_at datetime NULL,
	created_by bigint(20) unsigned NOT NULL DEFAULT 0,
	created_at datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
	PRIMARY KEY  (id),
	KEY booking_id (booking_id),
	KEY status (status)
) $c;
CREATE TABLE {$t( 'activity_log' )} (
	id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
	object_type varchar(20) NOT NULL DEFAULT '',
	object_id bigint(20) unsigned NOT NULL DEFAULT 0,
	action varchar(32) NOT NULL DEFAULT '',
	message text NULL,
	user_id bigint(20) unsigned NOT NULL DEFAULT 0,
	created_at datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
	PRIMARY KEY  (id),
	KEY object (object_type,object_id),
	KEY created_at (created_at)
) $c;";
	}
}