<?php
/**
 * Database layer for the Quote Inbox.
 *
 * Table: {prefix}tilvora_quotes
 * Managed entirely via $wpdb; bypasses post meta because quote records
 * are not WordPress posts and require efficient status filtering + pagination.
 */

if ( ! defined( 'ABSPATH' ) ) {
	exit;
}

define( 'TILVORA_QUOTES_DB_VERSION', '1.2' );

// Auto-upgrade the table schema when the DB version changes (e.g. new column added).
add_action( 'admin_init', 'tilvora_maybe_upgrade_quotes_table' );

function tilvora_maybe_upgrade_quotes_table() {
	if ( get_option( 'tilvora_quotes_db_version' ) !== TILVORA_QUOTES_DB_VERSION ) {
		tilvora_create_quotes_table();
	}
}

/**
 * Create (or upgrade) the quotes table. Called on plugin activation and on schema changes.
 * Uses dbDelta() so it is safe to call repeatedly — it only adds missing columns.
 */
function tilvora_create_quotes_table() {
	global $wpdb;

	$table          = $wpdb->prefix . 'tilvora_quotes';
	$charset_collate = $wpdb->get_charset_collate();

	// dbDelta() requires: two spaces before PRIMARY KEY, no trailing comma on last field.
	$sql = "CREATE TABLE $table (
  id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  project_id bigint(20) unsigned NOT NULL DEFAULT '0',
  status varchar(20) NOT NULL DEFAULT 'new',
  customer_name varchar(200) NOT NULL DEFAULT '',
  customer_email varchar(200) NOT NULL DEFAULT '',
  customer_phone varchar(100) NOT NULL DEFAULT '',
  customer_postcode varchar(50) NOT NULL DEFAULT '',
  tile_name varchar(200) NOT NULL DEFAULT '',
  finish_name varchar(200) NOT NULL DEFAULT '',
  selection_summary text NOT NULL,
  price_total varchar(30) NOT NULL DEFAULT '',
  currency varchar(10) NOT NULL DEFAULT '',
  room_width varchar(20) NOT NULL DEFAULT '',
  room_height varchar(20) NOT NULL DEFAULT '',
  room_area varchar(30) NOT NULL DEFAULT '',
  room_area_waste varchar(30) NOT NULL DEFAULT '',
  notes text NOT NULL,
  design_image_url varchar(500) NOT NULL DEFAULT '',
  created_at datetime NOT NULL,
  updated_at datetime NOT NULL,
  PRIMARY KEY  (id),
  KEY project_id (project_id),
  KEY status (status),
  KEY created_at (created_at)
) $charset_collate;";

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

	update_option( 'tilvora_quotes_db_version', TILVORA_QUOTES_DB_VERSION );
}

/**
 * Insert a new quote row. All sanitization happens here so callers can pass raw POST values.
 *
 * @param array $data Raw quote data (name, email, phone, postcode, tile_name, finish_name,
 *                    room_width, room_height, area, area_waste, notes, project_id).
 * @return int|false Inserted row ID, or false on failure.
 */
function tilvora_insert_quote( $data ) {
	global $wpdb;

	$now = current_time( 'mysql' );

	// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Direct insert to a custom plugin table; no meta API equivalent exists.
	$result = $wpdb->insert(
		$wpdb->prefix . 'tilvora_quotes',
		array(
			'project_id'       => absint( $data['project_id'] ?? 0 ),
			'status'           => 'new',
			'customer_name'    => sanitize_text_field( $data['name'] ?? '' ),
			'customer_email'   => sanitize_email( $data['email'] ?? '' ),
			'customer_phone'   => sanitize_text_field( $data['phone'] ?? '' ),
			'customer_postcode' => sanitize_text_field( $data['postcode'] ?? '' ),
			'tile_name'        => sanitize_text_field( $data['tile_name'] ?? '' ),
			'finish_name'      => sanitize_text_field( $data['finish_name'] ?? '' ),
			'selection_summary' => sanitize_textarea_field( $data['selection_summary'] ?? '' ),
			'price_total'      => sanitize_text_field( $data['price_total'] ?? '' ),
			'currency'         => sanitize_text_field( $data['currency'] ?? '' ),
			'room_width'       => sanitize_text_field( $data['room_width'] ?? '' ),
			'room_height'      => sanitize_text_field( $data['room_height'] ?? '' ),
			'room_area'        => sanitize_text_field( $data['area'] ?? '' ),
			'room_area_waste'  => sanitize_text_field( $data['area_waste'] ?? '' ),
			'notes'            => sanitize_textarea_field( $data['notes'] ?? '' ),
			'design_image_url' => esc_url_raw( $data['design_image_url'] ?? '' ),
			'created_at'       => $now,
			'updated_at'       => $now,
		),
		array( '%d', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s' )
	);
	// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, PluginCheck.Security.DirectDB.UnescapedDBParameter

	return $result ? $wpdb->insert_id : false;
}

/**
 * Return a single quote row by ID.
 *
 * @param int $id Quote ID.
 * @return object|null Quote row object or null.
 */
function tilvora_get_quote( $id ) {
	global $wpdb;
	$table = $wpdb->prefix . 'tilvora_quotes';
	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Custom table, no caching layer; data must be live for admin review.
	return $wpdb->get_row( $wpdb->prepare( "SELECT * FROM `$table` WHERE `id` = %d LIMIT 1", absint( $id ) ) );
}

/**
 * Count all quotes, or only those with a specific status.
 *
 * @param string $status Optional status filter ('new'|'replied'|'completed').
 * @return int Count.
 */
function tilvora_count_quotes( $status = '' ) {
	global $wpdb;
	$table = $wpdb->prefix . 'tilvora_quotes';

	if ( $status ) {
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Custom table, live count required for admin menu badge.
		return (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM `$table` WHERE `status` = %s", $status ) );
	}

	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Custom table, live count required for admin menu badge.
	return (int) $wpdb->get_var( "SELECT COUNT(*) FROM `$table`" );
}

/**
 * Retrieve quotes with optional status filter, search, sorting and pagination.
 *
 * @param array $args {
 *   @type string $status   Status filter (empty = all).
 *   @type string $search   Search string matched against name / email.
 *   @type string $orderby  Column name (whitelisted internally).
 *   @type string $order    'ASC' or 'DESC'.
 *   @type int    $limit    Rows per page.
 *   @type int    $offset   Row offset.
 * }
 * @return array Array of stdClass rows.
 */
function tilvora_get_quotes( $args = array() ) {
	global $wpdb;
	$table = $wpdb->prefix . 'tilvora_quotes';

	$defaults = array(
		'status'  => '',
		'search'  => '',
		'orderby' => 'created_at',
		'order'   => 'DESC',
		'limit'   => 20,
		'offset'  => 0,
	);
	$args = wp_parse_args( $args, $defaults );

	// Whitelist orderby to prevent SQL injection.
	$allowed_orderby = array( 'id', 'customer_name', 'customer_email', 'status', 'created_at', 'tile_name' );
	$orderby = in_array( $args['orderby'], $allowed_orderby, true ) ? $args['orderby'] : 'created_at';
	$order   = 'ASC' === strtoupper( $args['order'] ) ? 'ASC' : 'DESC';
	$limit   = absint( $args['limit'] );
	$offset  = absint( $args['offset'] );

	$where  = array();
	$values = array();

	if ( $args['status'] ) {
		$where[]  = '`status` = %s';
		$values[] = $args['status'];
	}
	if ( $args['search'] ) {
		$like     = '%' . $wpdb->esc_like( $args['search'] ) . '%';
		$where[]  = '(`customer_name` LIKE %s OR `customer_email` LIKE %s)';
		$values[] = $like;
		$values[] = $like;
	}

	$where_sql = $where ? 'WHERE ' . implode( ' AND ', $where ) : '';

	// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- Custom table; $table is a trusted constant, $orderby/$order are whitelisted, $where_sql is built from placeholders only, array $values accounts for all dynamic placeholders, $sql is the output of $wpdb->prepare().
	if ( $values ) {
		$values[] = $limit;
		$values[] = $offset;
		$sql = $wpdb->prepare(
			"SELECT * FROM `$table` $where_sql ORDER BY `$orderby` $order LIMIT %d OFFSET %d",
			$values
		);
	} else {
		$sql = $wpdb->prepare(
			"SELECT * FROM `$table` ORDER BY `$orderby` $order LIMIT %d OFFSET %d",
			$limit,
			$offset
		);
	}
	return (array) $wpdb->get_results( $sql );
	// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber
}

/**
 * Count quotes matching the same filters used by tilvora_get_quotes() — for pagination.
 *
 * @param array $args Same args as tilvora_get_quotes() (only status/search used).
 * @return int Total matching row count.
 */
function tilvora_count_quotes_filtered( $args = array() ) {
	global $wpdb;
	$table = $wpdb->prefix . 'tilvora_quotes';

	$where  = array();
	$values = array();

	if ( ! empty( $args['status'] ) ) {
		$where[]  = '`status` = %s';
		$values[] = $args['status'];
	}
	if ( ! empty( $args['search'] ) ) {
		$like     = '%' . $wpdb->esc_like( $args['search'] ) . '%';
		$where[]  = '(`customer_name` LIKE %s OR `customer_email` LIKE %s)';
		$values[] = $like;
		$values[] = $like;
	}

	$where_sql = $where ? 'WHERE ' . implode( ' AND ', $where ) : '';

	// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare -- Custom table, live count for pagination; $table is trusted, $where_sql contains only %s placeholders.
	if ( $values ) {
		return (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM `$table` $where_sql", $values ) );
	}
	return (int) $wpdb->get_var( "SELECT COUNT(*) FROM `$table`" );
	// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare
}

/**
 * Update a quote's status.
 *
 * @param int    $id     Quote ID.
 * @param string $status New status ('new'|'replied'|'completed').
 * @return int|false Rows updated or false on failure / invalid status.
 */
function tilvora_update_quote_status( $id, $status ) {
	global $wpdb;

	$allowed = array( 'new', 'replied', 'completed' );
	if ( ! in_array( $status, $allowed, true ) ) {
		return false;
	}

	// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Direct update to custom table; no caching needed for a status toggle.
	return $wpdb->update(
		$wpdb->prefix . 'tilvora_quotes',
		array(
			'status'     => $status,
			'updated_at' => current_time( 'mysql' ),
		),
		array( 'id' => absint( $id ) ),
		array( '%s', '%s' ),
		array( '%d' )
	);
	// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter
}

/**
 * Permanently delete one or more quotes.
 *
 * @param int|int[] $ids Single ID or array of IDs.
 * @return int|false Number of rows deleted, or false on failure.
 */
function tilvora_delete_quotes( $ids ) {
	global $wpdb;

	$ids = array_filter( array_map( 'absint', (array) $ids ) );
	if ( ! $ids ) {
		return 0;
	}

	$placeholders = implode( ',', array_fill( 0, count( $ids ), '%d' ) );

	// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare -- Custom table delete; $placeholders is only %d tokens, $ids are all absint-filtered.
	return $wpdb->query(
		$wpdb->prepare(
			"DELETE FROM `{$wpdb->prefix}tilvora_quotes` WHERE `id` IN ($placeholders)",
			$ids
		)
	);
	// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
}
