<?php
/**
 * BitForgeNet Guest Tickets - Venue model.
 *
 * @package BitForgeNet_Guest_Tickets
 * @since   1.0.0
 */

defined( 'ABSPATH' ) || exit;

// This file talks to the plugin's own tables. WordPress has no API for them --
// they are not posts and not meta -- so $wpdb is the only way in. The rows
// change on every booking and every scan, which makes an object cache wrong
// more often than it is right. Table names are built from $wpdb->prefix and
// never from a request, so interpolating one carries nothing to inject; every
// value goes through $wpdb->prepare(), and the few identifiers that cannot
// (ORDER BY columns) are matched against a whitelist first.
// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery
// phpcs:disable WordPress.DB.DirectDatabaseQuery.NoCaching
// phpcs:disable WordPress.DB.DirectDatabaseQuery.SchemaChange
// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
// phpcs:disable PluginCheck.Security.DirectDB.UnescapedDBParameter


/**
 * Data access for venues.
 */
class BFNET_Venue {

	/**
	 * Table name.
	 *
	 * @return string
	 */
	public static function table() {
		return BFNET_Install::table( 'venues' );
	}

	/**
	 * Fetch one venue.
	 *
	 * @param int $id Venue id.
	 * @return object|null
	 */
	public static function get( $id ) {
		global $wpdb;
		$table = self::table();
		return $wpdb->get_row( $wpdb->prepare( "SELECT * FROM {$table} WHERE id = %d", (int) $id ) ); // phpcs:ignore WordPress.DB.PreparedSQL
	}

	/**
	 * Fetch all venues.
	 *
	 * @param string $status Optional status filter.
	 * @return array
	 */
	public static function all( $status = 'active' ) {
		global $wpdb;
		$table = self::table();
		if ( $status ) {
			return $wpdb->get_results( $wpdb->prepare( "SELECT * FROM {$table} WHERE status = %s ORDER BY name ASC", $status ) ); // phpcs:ignore WordPress.DB.PreparedSQL
		}
		return $wpdb->get_results( "SELECT * FROM {$table} ORDER BY name ASC" ); // phpcs:ignore WordPress.DB.PreparedSQL
	}

	/**
	 * Insert or update a venue.
	 *
	 * @param array $data Field values.
	 * @return int|WP_Error
	 */
	public static function save( array $data ) {
		global $wpdb;

		$id   = isset( $data['id'] ) ? (int) $data['id'] : 0;
		$name = isset( $data['name'] ) ? sanitize_text_field( $data['name'] ) : '';

		if ( '' === trim( $name ) ) {
			return new WP_Error( 'bfnet_venue_name', __( 'The venue needs a name.', 'bitforgenet-guest-tickets' ) );
		}

		$lat = isset( $data['latitude'] ) && '' !== trim( (string) $data['latitude'] ) ? (float) $data['latitude'] : null;
		$lng = isset( $data['longitude'] ) && '' !== trim( (string) $data['longitude'] ) ? (float) $data['longitude'] : null;

		$fields = array(
			'name'            => $name,
			'address'         => isset( $data['address'] ) ? sanitize_text_field( $data['address'] ) : '',
			'city'            => isset( $data['city'] ) ? sanitize_text_field( $data['city'] ) : '',
			'state'           => isset( $data['state'] ) ? strtoupper( sanitize_text_field( $data['state'] ) ) : '',
			'postcode'        => isset( $data['postcode'] ) ? sanitize_text_field( $data['postcode'] ) : '',
			'country'         => isset( $data['country'] ) ? sanitize_text_field( $data['country'] ) : '',
			'google_maps_url' => isset( $data['google_maps_url'] ) ? BFNET_Security::clean_map_url( $data['google_maps_url'] ) : '',
			'latitude'        => ( null !== $lat && $lat >= -90 && $lat <= 90 ) ? $lat : null,
			'longitude'       => ( null !== $lng && $lng >= -180 && $lng <= 180 ) ? $lng : null,
			'capacity'        => isset( $data['capacity'] ) ? max( 0, (int) $data['capacity'] ) : 0,
			'phone'           => isset( $data['phone'] ) ? BFNET_Security::clean_phone( $data['phone'] ) : '',
			'notes'           => isset( $data['notes'] ) ? sanitize_textarea_field( $data['notes'] ) : '',
			'status'          => ( isset( $data['status'] ) && 'inactive' === $data['status'] ) ? 'inactive' : 'active',
			'updated_at'      => current_time( 'mysql' ),
		);

		if ( $id ) {
			$wpdb->update( self::table(), BFNET_Install::writable( self::table(), $fields ), array( 'id' => $id ), null, array( '%d' ) );
			BFNET_Log::audit( 'venue', $id, 'venue_updated', array( 'name' => $name ) );
		} else {
			$fields['created_at'] = current_time( 'mysql' );
			$wpdb->insert( self::table(), BFNET_Install::writable( self::table(), $fields ) );
			$id = (int) $wpdb->insert_id;
			BFNET_Log::audit( 'venue', $id, 'venue_created', array( 'name' => $name ) );
		}

		return $id ? $id : new WP_Error( 'bfnet_venue_save', __( 'The venue could not be saved.', 'bitforgenet-guest-tickets' ) );
	}

	/**
	 * Delete a venue if no event references it.
	 *
	 * @param int $id Venue id.
	 * @return true|WP_Error
	 */
	public static function delete( $id ) {
		global $wpdb;

		$events = BFNET_Install::table( 'events' );
		$used   = (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM {$events} WHERE venue_id = %d", (int) $id ) ); // phpcs:ignore WordPress.DB.PreparedSQL

		if ( $used > 0 ) {
			return new WP_Error( 'bfnet_venue_in_use', __( 'This venue is attached to existing events. Set it to inactive instead of deleting it.', 'bitforgenet-guest-tickets' ) );
		}

		$wpdb->delete( self::table(), array( 'id' => (int) $id ), array( '%d' ) );
		BFNET_Log::audit( 'venue', $id, 'venue_deleted', array(), 'warning' );

		return true;
	}

	/**
	 * One-line address string.
	 *
	 * @param object|null $venue Venue row.
	 * @return string
	 */
	/**
	 * Provinces and states, grouped by country.
	 *
	 * Offered as suggestions on a free text field rather than as the only answers,
	 * because most of the world does not divide itself this way. Nothing is
	 * preselected: the plugin does not know which country it has been installed in
	 * and should not guess.
	 *
	 * @return array
	 */
	public static function regions() {
		return array(
			'United States' => array(
				'AL' => 'Alabama', 'AK' => 'Alaska', 'AZ' => 'Arizona', 'AR' => 'Arkansas',
				'CA' => 'California', 'CO' => 'Colorado', 'CT' => 'Connecticut', 'DE' => 'Delaware',
				'DC' => 'District of Columbia', 'FL' => 'Florida', 'GA' => 'Georgia', 'HI' => 'Hawaii',
				'ID' => 'Idaho', 'IL' => 'Illinois', 'IN' => 'Indiana', 'IA' => 'Iowa',
				'KS' => 'Kansas', 'KY' => 'Kentucky', 'LA' => 'Louisiana', 'ME' => 'Maine',
				'MD' => 'Maryland', 'MA' => 'Massachusetts', 'MI' => 'Michigan', 'MN' => 'Minnesota',
				'MS' => 'Mississippi', 'MO' => 'Missouri', 'MT' => 'Montana', 'NE' => 'Nebraska',
				'NV' => 'Nevada', 'NH' => 'New Hampshire', 'NJ' => 'New Jersey', 'NM' => 'New Mexico',
				'NY' => 'New York', 'NC' => 'North Carolina', 'ND' => 'North Dakota', 'OH' => 'Ohio',
				'OK' => 'Oklahoma', 'OR' => 'Oregon', 'PA' => 'Pennsylvania', 'RI' => 'Rhode Island',
				'SC' => 'South Carolina', 'SD' => 'South Dakota', 'TN' => 'Tennessee', 'TX' => 'Texas',
				'UT' => 'Utah', 'VT' => 'Vermont', 'VA' => 'Virginia', 'WA' => 'Washington',
				'WV' => 'West Virginia', 'WI' => 'Wisconsin', 'WY' => 'Wyoming',
			),
			'Canada' => array(
				'AB' => 'Alberta',
				'BC' => 'British Columbia',
				'MB' => 'Manitoba',
				'NB' => 'New Brunswick',
				'NL' => 'Newfoundland and Labrador',
				'NS' => 'Nova Scotia',
				'NT' => 'Northwest Territories',
				'NU' => 'Nunavut',
				'ON' => 'Ontario',
				'PE' => 'Prince Edward Island',
				'QC' => 'Quebec',
				'SK' => 'Saskatchewan',
				'YT' => 'Yukon',
			),
		);
	}

	/**
	 * The country a province or state belongs to.
	 *
	 * @param string $state Two-letter code.
	 * @return string
	 */
	public static function country_for( $state ) {
		foreach ( self::regions() as $country => $list ) {
			if ( isset( $list[ $state ] ) ) {
				return $country;
			}
		}
		return '';
	}

	public static function address_line( $venue ) {
		if ( ! $venue ) {
			return '';
		}
		// Written the way a North American address is written: street, then
		// city, province and postal code on one line, then the country.
		$locality = trim(
			implode(
				' ',
				array_filter(
					array(
						$venue->city ? $venue->city . ',' : '',
						isset( $venue->state ) ? $venue->state : '',
						$venue->postcode,
					)
				)
			)
		);
		$locality = rtrim( $locality, ',' );

		return implode( ', ', array_filter( array( $venue->address, $locality, $venue->country ) ) );
	}

	/**
	 * Best available map link for a venue.
	 *
	 * @param object|null $venue Venue row.
	 * @return string
	 */
	public static function map_url( $venue ) {
		if ( ! $venue ) {
			return '';
		}
		if ( ! empty( $venue->google_maps_url ) ) {
			return $venue->google_maps_url;
		}
		if ( null !== $venue->latitude && null !== $venue->longitude ) {
			return 'https://www.google.com/maps/search/?api=1&query=' . rawurlencode( $venue->latitude . ',' . $venue->longitude );
		}
		$address = self::address_line( $venue );
		if ( $address ) {
			return 'https://www.google.com/maps/search/?api=1&query=' . rawurlencode( $venue->name . ' ' . $address );
		}
		return '';
	}
}
