<?php
/**
 * Canonical schema definition for all WrenchPress custom tables.
 *
 * Holds every table-create statement in one place and runs them through
 * dbDelta(). These columns are the canonical schema; later phases extend them
 * additively through the migration runner and never reshape them in place.
 *
 * dbDelta formatting rules observed here (all silently fail if violated):
 * - column types are lowercase
 * - two spaces between the primary key keyword and the opening paren
 * - one field per line, no blank lines inside the SQL
 * - every key is named
 * - no conditional-existence clause, no relational constraints, no field comments
 * - get_charset_collate() appended to each statement
 *
 * @package WrenchPress
 */

defined( 'ABSPATH' ) || exit;

/**
 * Class WrenchPress_Schema
 */
class WrenchPress_Schema {

	/**
	 * Create or update all plugin tables via dbDelta.
	 *
	 * Idempotent: safe to re-run on an existing install. dbDelta applies only
	 * additive differences, so calling this repeatedly does not lose data.
	 *
	 * @return void
	 */
	public static function create_tables() {
		global $wpdb;

		require_once ABSPATH . 'wp-admin/includes/upgrade.php';

		$charset_collate = $wpdb->get_charset_collate();

		foreach ( self::table_statements( $charset_collate ) as $sql ) {
			dbDelta( $sql );
		}
	}

	/**
	 * Version-7 migration: create every table, then seed the document counters.
	 *
	 * Runs the idempotent create_tables() first (so dbDelta adds the new
	 * documents and counters tables to an existing install) and then seeds one
	 * counter row per document type. The seed is deliberately NOT part of the
	 * generic create_tables path because inserting a starting row is not
	 * idempotent the way dbDelta is: a plain re-insert on upgrade would rewind a
	 * live sequence back to 1. The insert-if-absent guard (INSERT IGNORE against
	 * the doc_type primary key) makes the seed safe to re-run, so an existing
	 * counter that has already advanced is left untouched.
	 *
	 * @return void
	 */
	public static function create_documents() {
		global $wpdb;

		self::create_tables();

		$counters = $wpdb->prefix . 'wrenchpress_document_counters';

		foreach ( array( 'invoice', 'estimate' ) as $doc_type ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom plugin table, one-time idempotent counter seed, not object-cached.
			$wpdb->query(
				$wpdb->prepare(
					'INSERT IGNORE INTO %i (doc_type, next_number) VALUES (%s, 1)',
					$counters,
					$doc_type
				)
			);
		}
	}

	/**
	 * Return the table name => create-statement map.
	 *
	 * @param string $charset_collate Result of $wpdb->get_charset_collate().
	 * @return array<string, string> Map of full table name to CREATE statement.
	 */
	public static function table_statements( $charset_collate ) {
		global $wpdb;

		$customers         = $wpdb->prefix . 'wrenchpress_customers';
		$vehicles          = $wpdb->prefix . 'wrenchpress_vehicles';
		$work_orders       = $wpdb->prefix . 'wrenchpress_work_orders';
		$work_order_items  = $wpdb->prefix . 'wrenchpress_work_order_items';
		$events            = $wpdb->prefix . 'wrenchpress_events';
		$appointments      = $wpdb->prefix . 'wrenchpress_appointments';
		$reminders         = $wpdb->prefix . 'wrenchpress_reminders';
		$leads             = $wpdb->prefix . 'wrenchpress_leads';
		$documents         = $wpdb->prefix . 'wrenchpress_documents';
		$counters          = $wpdb->prefix . 'wrenchpress_document_counters';
		$customer_sessions = $wpdb->prefix . 'wrenchpress_customer_sessions';

		$statements = array();

		$statements[ $customers ] = "CREATE TABLE $customers (
  id bigint(20) unsigned NOT NULL auto_increment,
  first_name varchar(100) NOT NULL default '',
  last_name varchar(100) NOT NULL default '',
  phone varchar(40) NOT NULL default '',
  phone_digits varchar(40) NOT NULL default '',
  email varchar(190) NOT NULL default '',
  address varchar(255) NOT NULL default '',
  notes text NOT NULL,
  status varchar(20) NOT NULL default 'active',
  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 last_name (last_name),
  KEY phone_digits (phone_digits),
  KEY email (email),
  KEY status (status)
) $charset_collate;";

		$statements[ $vehicles ] = "CREATE TABLE $vehicles (
  id bigint(20) unsigned NOT NULL auto_increment,
  customer_id bigint(20) unsigned NOT NULL default 0,
  make varchar(100) NOT NULL default '',
  model varchar(100) NOT NULL default '',
  year smallint(5) unsigned NOT NULL default 0,
  vin varchar(40) NOT NULL default '',
  plate varchar(20) NOT NULL default '',
  mileage int(10) unsigned NOT NULL default 0,
  fuel_type varchar(30) NOT NULL default '',
  transmission varchar(30) NOT NULL default '',
  color varchar(40) NOT NULL default '',
  engine varchar(60) NOT NULL default '',
  engine_number varchar(60) NOT NULL default '',
  status varchar(20) NOT NULL default 'active',
  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 customer_id (customer_id),
  KEY plate (plate),
  KEY vin (vin),
  KEY engine (engine),
  KEY engine_number (engine_number),
  KEY status (status)
) $charset_collate;";

		$statements[ $work_orders ] = "CREATE TABLE $work_orders (
  id bigint(20) unsigned NOT NULL auto_increment,
  customer_id bigint(20) unsigned NOT NULL default 0,
  vehicle_id bigint(20) unsigned NOT NULL default 0,
  status varchar(30) NOT NULL default 'new',
  tax_rate decimal(5,4) 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 customer_id (customer_id),
  KEY vehicle_id (vehicle_id),
  KEY status (status)
) $charset_collate;";

		$statements[ $work_order_items ] = "CREATE TABLE $work_order_items (
  id bigint(20) unsigned NOT NULL auto_increment,
  work_order_id bigint(20) unsigned NOT NULL default 0,
  item_type varchar(20) NOT NULL default 'labor',
  description text NOT NULL,
  quantity decimal(10,2) NOT NULL default 0,
  unit_price decimal(10,2) NOT NULL default 0,
  position int(10) 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),
  KEY work_order_id (work_order_id),
  KEY item_type (item_type)
) $charset_collate;";

		$statements[ $events ] = "CREATE TABLE $events (
  id bigint(20) unsigned NOT NULL auto_increment,
  work_order_id bigint(20) unsigned NOT NULL default 0,
  event_type varchar(20) NOT NULL default 'note',
  from_status varchar(30) NOT NULL default '',
  to_status varchar(30) NOT NULL default '',
  note text NOT NULL,
  author_id bigint(20) unsigned NOT NULL default 0,
  author_name varchar(190) NOT NULL default '',
  created_at datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (id),
  KEY work_order_id (work_order_id),
  KEY event_type (event_type),
  KEY created_at (created_at)
) $charset_collate;";

		$statements[ $appointments ] = "CREATE TABLE $appointments (
  id bigint(20) unsigned NOT NULL auto_increment,
  customer_id bigint(20) unsigned NOT NULL default 0,
  vehicle_id bigint(20) unsigned NOT NULL default 0,
  start_at datetime NOT NULL default '0000-00-00 00:00:00',
  end_at datetime NOT NULL default '0000-00-00 00:00:00',
  status varchar(30) NOT NULL default 'scheduled',
  work_order_id bigint(20) unsigned NOT NULL default 0,
  notes text NOT 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 customer_id (customer_id),
  KEY vehicle_id (vehicle_id),
  KEY start_at (start_at),
  KEY status (status),
  KEY work_order_id (work_order_id)
) $charset_collate;";

		$statements[ $reminders ] = "CREATE TABLE $reminders (
  id bigint(20) unsigned NOT NULL auto_increment,
  customer_id bigint(20) unsigned NOT NULL default 0,
  vehicle_id bigint(20) unsigned NOT NULL default 0,
  work_order_id bigint(20) unsigned NOT NULL default 0,
  due_date date NOT NULL default '0000-00-00',
  due_mileage int(10) unsigned NOT NULL default 0,
  status varchar(30) NOT NULL default 'pending',
  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 customer_id (customer_id),
  KEY vehicle_id (vehicle_id),
  KEY work_order_id (work_order_id),
  KEY due_date (due_date),
  KEY status (status)
) $charset_collate;";

		$statements[ $leads ] = "CREATE TABLE $leads (
  id bigint(20) unsigned NOT NULL auto_increment,
  first_name varchar(100) NOT NULL default '',
  last_name varchar(100) NOT NULL default '',
  phone varchar(40) NOT NULL default '',
  phone_digits varchar(40) NOT NULL default '',
  email varchar(190) NOT NULL default '',
  address varchar(255) NOT NULL default '',
  make varchar(100) NOT NULL default '',
  model varchar(100) NOT NULL default '',
  year smallint(5) unsigned NOT NULL default 0,
  vin varchar(40) NOT NULL default '',
  plate varchar(20) NOT NULL default '',
  mileage int(10) unsigned NOT NULL default 0,
  fuel_type varchar(30) NOT NULL default '',
  transmission varchar(30) NOT NULL default '',
  color varchar(40) NOT NULL default '',
  subject varchar(255) NOT NULL default '',
  description text NOT NULL,
  consent tinyint(1) NOT NULL default 0,
  consent_at datetime NOT NULL default '0000-00-00 00:00:00',
  source varchar(40) NOT NULL default '',
  submit_ip varchar(100) NOT NULL default '',
  submit_ua varchar(255) NOT NULL default '',
  status varchar(20) NOT NULL default 'new',
  promoted_customer_id 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),
  KEY status (status),
  KEY phone_digits (phone_digits),
  KEY email (email),
  KEY created_at (created_at)
) $charset_collate;";

		$statements[ $documents ] = "CREATE TABLE $documents (
  id bigint(20) unsigned NOT NULL auto_increment,
  work_order_id bigint(20) unsigned NOT NULL default 0,
  doc_type varchar(20) NOT NULL default 'invoice',
  doc_number bigint(20) unsigned NOT NULL default 0,
  labor decimal(10,2) NOT NULL default 0,
  parts decimal(10,2) NOT NULL default 0,
  subtotal decimal(10,2) NOT NULL default 0,
  tax_rate decimal(5,4) NOT NULL default 0,
  tax_amount decimal(10,2) NOT NULL default 0,
  grand_total decimal(10,2) NOT NULL default 0,
  line_items longtext NOT NULL,
  branding longtext NOT NULL,
  notes text NOT NULL,
  issued_at datetime NOT NULL default '0000-00-00 00:00:00',
  issued_by bigint(20) unsigned NOT NULL default 0,
  PRIMARY KEY  (id),
  UNIQUE KEY work_order_type (work_order_id,doc_type),
  KEY doc_type (doc_type),
  KEY doc_number (doc_number)
) $charset_collate;";

		$statements[ $counters ] = "CREATE TABLE $counters (
  doc_type varchar(20) NOT NULL,
  next_number bigint(20) unsigned NOT NULL default 1,
  PRIMARY KEY  (doc_type)
) $charset_collate;";

		$statements[ $customer_sessions ] = "CREATE TABLE $customer_sessions (
  id bigint(20) unsigned NOT NULL auto_increment,
  customer_id bigint(20) unsigned NOT NULL default 0,
  token_hash char(64) NOT NULL default '',
  status varchar(20) NOT NULL default 'pending',
  ip varchar(100) NOT NULL default '',
  ua varchar(255) NOT NULL default '',
  expires_at datetime NOT NULL default '0000-00-00 00:00:00',
  used_at datetime NOT NULL default '0000-00-00 00:00:00',
  created_at datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (id),
  KEY customer_id (customer_id),
  KEY token_hash (token_hash),
  KEY status (status),
  KEY expires_at (expires_at)
) $charset_collate;";

		return $statements;
	}

	/**
	 * Verify that every expected table exists after creation.
	 *
	 * The dbDelta return values describe intended actions, not actual state, so
	 * confirm independently with SHOW TABLES.
	 *
	 * @return bool True when all 11 tables are present.
	 */
	public static function tables_exist() {
		global $wpdb;

		$names = array_keys( self::table_statements( $wpdb->get_charset_collate() ) );

		foreach ( $names as $table ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom plugin table, intentionally not object-cached.
			$found = $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $table ) );
			if ( $found !== $table ) {
				return false;
			}
		}

		return true;
	}
}
