<?php
if ( ! defined( 'ABSPATH' ) ) {
	exit;
}

function chcpl_get_submissions_table_name() {
	global $wpdb;
	return $wpdb->prefix . 'chcpl_submissions';
}

function chcpl_submissions_table_exists() {
	if ( get_transient( 'chcpl_submissions_table_ok' ) ) {
		return true;
	}
	global $wpdb;
	$table = chcpl_get_submissions_table_name();
	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
	$found  = $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $table ) );
	$exists = ( $found === $table );
	if ( $exists ) {
		set_transient( 'chcpl_submissions_table_ok', 1, DAY_IN_SECONDS );
	}
	return $exists;
}

function chcpl_create_submissions_table() {
	global $wpdb;
	$table_name = chcpl_get_submissions_table_name();
	$charset_collate = $wpdb->get_charset_collate();
	require_once ABSPATH . 'wp-admin/includes/upgrade.php';
	$sql = "CREATE TABLE $table_name (
		id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
		list_id BIGINT(20) UNSIGNED NOT NULL DEFAULT 0,
		list_name VARCHAR(255) NOT NULL DEFAULT '',
		name VARCHAR(190) NOT NULL DEFAULT '',
		company VARCHAR(190) NOT NULL DEFAULT '',
		siren VARCHAR(60) NOT NULL DEFAULT '',
		email VARCHAR(190) NOT NULL DEFAULT '',
		status VARCHAR(20) NOT NULL DEFAULT 'unread',
		payload LONGTEXT NULL,
		submitted_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
		PRIMARY KEY  (id),
		KEY status (status),
		KEY submitted_at (submitted_at),
		KEY list_id (list_id)
	) $charset_collate;";
	dbDelta( $sql );
}

function chcpl_maybe_install_submissions_table() {
	if ( ! chcpl_submissions_table_exists() ) {
		chcpl_create_submissions_table();
	}
}
add_action( 'plugins_loaded', 'chcpl_maybe_install_submissions_table' );

function chcpl_record_access_request_submission( $data ) {
	global $wpdb;
	$table = chcpl_get_submissions_table_name();
	if ( empty( $data ) ) {
		return;
	}
	if ( ! chcpl_submissions_table_exists() ) {
		chcpl_create_submissions_table();
	}
	$defaults = array(
		'list_id'   => 0,
		'list_name' => '',
		'name'      => '',
		'company'   => '',
		'siren'     => '',
		'email'     => '',
		'status'    => 'unread',
		'payload'   => array(),
	);
	$data = wp_parse_args( $data, $defaults );
	$payload = ! empty( $data['payload'] ) ? wp_json_encode( $data['payload'] ) : '';
	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
	$wpdb->insert(
		$table,
		array(
			'list_id'      => (int) $data['list_id'],
			'list_name'    => sanitize_text_field( $data['list_name'] ),
			'name'         => sanitize_text_field( $data['name'] ),
			'company'      => sanitize_text_field( $data['company'] ),
			'siren'        => sanitize_text_field( $data['siren'] ),
			'email'        => sanitize_email( $data['email'] ),
			'status'       => in_array( $data['status'], array( 'read', 'unread' ), true ) ? $data['status'] : 'unread',
			'payload'      => $payload,
			'submitted_at' => current_time( 'mysql' ),
		),
		array( '%d', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s' )
	);
}

function chcpl_get_unread_submission_count() {
	global $wpdb;
	$table = chcpl_get_submissions_table_name();
	if ( ! chcpl_submissions_table_exists() ) {
		return 0;
	}
	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter, PluginCheck.Security.DirectDB.UnescapedDBParameter
	$count = $wpdb->get_var( "SELECT COUNT(*) FROM $table WHERE status = 'unread'" );
	return (int) $count;
}

function chcpl_get_submissions( $args = array() ) {
	global $wpdb;
	$table = chcpl_get_submissions_table_name();
	if ( ! chcpl_submissions_table_exists() ) {
		return array();
	}
	$defaults = array(
		'per_page' => 20,
		'offset'   => 0,
		'status'   => '',
	);
	$args     = wp_parse_args( $args, $defaults );
	$per_page = max( 1, (int) $args['per_page'] );
	$offset   = max( 0, (int) $args['offset'] );
	$status   = in_array( $args['status'], array( 'read', 'unread' ), true ) ? $args['status'] : '';

	if ( $status ) {
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$res = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT * FROM $table WHERE status = %s ORDER BY submitted_at DESC LIMIT %d OFFSET %d",
				$status,
				$per_page,
				$offset
			)
		);
		// phpcs:enable
		return $res;
	}
	// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
	$res = $wpdb->get_results(
		$wpdb->prepare(
			"SELECT * FROM $table ORDER BY submitted_at DESC LIMIT %d OFFSET %d",
			$per_page,
			$offset
		)
	);
	// phpcs:enable
	return $res;
}

function chcpl_get_submissions_total( $status = '' ) {
	global $wpdb;
	$table = chcpl_get_submissions_table_name();
	if ( ! chcpl_submissions_table_exists() ) {
		return 0;
	}
	$status = in_array( $status, array( 'read', 'unread' ), true ) ? $status : '';
	if ( $status ) {
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		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
	return (int) $wpdb->get_var( "SELECT COUNT(*) FROM $table" );
}

function chcpl_update_submission_status( $id, $status ) {
	global $wpdb;
	$table = chcpl_get_submissions_table_name();
	if ( ! chcpl_submissions_table_exists() ) {
		return false;
	}
	if ( ! in_array( $status, array( 'read', 'unread' ), true ) ) {
		return false;
	}
	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
	return $wpdb->update(
		$table,
		array( 'status' => $status ),
		array( 'id' => (int) $id ),
		array( '%s' ),
		array( '%d' )
	);
}

function chcpl_delete_submission( $id ) {
	global $wpdb;
	$table = chcpl_get_submissions_table_name();
	if ( ! chcpl_submissions_table_exists() ) {
		return false;
	}
	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
	return $wpdb->delete(
		$table,
		array( 'id' => (int) $id ),
		array( '%d' )
	);
}

// ── Quote Requests (Demande de devis) ──────────────────────────────────────

function chcpl_get_quote_requests_table_name() {
	global $wpdb;
	return $wpdb->prefix . 'chcpl_quote_requests';
}

function chcpl_quote_requests_table_exists() {
	if ( get_transient( 'chcpl_quote_requests_table_ok' ) ) {
		return true;
	}
	global $wpdb;
	$table = chcpl_get_quote_requests_table_name();
	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
	$found  = $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $table ) );
	$exists = ( $found === $table );
	if ( $exists ) {
		set_transient( 'chcpl_quote_requests_table_ok', 1, DAY_IN_SECONDS );
	}
	return $exists;
}

function chcpl_create_quote_requests_table() {
	global $wpdb;
	$table_name = chcpl_get_quote_requests_table_name();
	$charset_collate = $wpdb->get_charset_collate();
	require_once ABSPATH . 'wp-admin/includes/upgrade.php';
	$sql = "CREATE TABLE $table_name (
		id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
		list_id BIGINT(20) UNSIGNED NOT NULL DEFAULT 0,
		list_name VARCHAR(255) NOT NULL DEFAULT '',
		service_id BIGINT(20) UNSIGNED NOT NULL DEFAULT 0,
		service_name VARCHAR(255) NOT NULL DEFAULT '',
		name VARCHAR(190) NOT NULL DEFAULT '',
		company VARCHAR(190) NOT NULL DEFAULT '',
		email VARCHAR(190) NOT NULL DEFAULT '',
		message LONGTEXT NULL,
		status VARCHAR(20) NOT NULL DEFAULT 'unread',
		submitted_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
		PRIMARY KEY  (id),
		KEY status (status),
		KEY submitted_at (submitted_at),
		KEY list_id (list_id)
	) $charset_collate;";
	dbDelta( $sql );
}

function chcpl_maybe_install_quote_requests_table() {
	if ( ! chcpl_quote_requests_table_exists() ) {
		chcpl_create_quote_requests_table();
	}
}
add_action( 'plugins_loaded', 'chcpl_maybe_install_quote_requests_table' );

function chcpl_record_quote_request( $data ) {
	global $wpdb;
	$table = chcpl_get_quote_requests_table_name();
	if ( empty( $data ) ) {
		return;
	}
	if ( ! chcpl_quote_requests_table_exists() ) {
		chcpl_create_quote_requests_table();
	}
	$defaults = array(
		'list_id'      => 0,
		'list_name'    => '',
		'service_id'   => 0,
		'service_name' => '',
		'name'         => '',
		'company'      => '',
		'email'        => '',
		'message'      => '',
		'status'       => 'unread',
	);
	$data = wp_parse_args( $data, $defaults );
	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
	$wpdb->insert(
		$table,
		array(
			'list_id'      => (int) $data['list_id'],
			'list_name'    => sanitize_text_field( $data['list_name'] ),
			'service_id'   => (int) $data['service_id'],
			'service_name' => sanitize_text_field( $data['service_name'] ),
			'name'         => sanitize_text_field( $data['name'] ),
			'company'      => sanitize_text_field( $data['company'] ),
			'email'        => sanitize_email( $data['email'] ),
			'message'      => sanitize_textarea_field( $data['message'] ),
			'status'       => 'unread',
			'submitted_at' => current_time( 'mysql' ),
		),
		array( '%d', '%s', '%d', '%s', '%s', '%s', '%s', '%s', '%s', '%s' )
	);

	// Free plan: keep only the 20 most recent quote requests.
	if ( function_exists( 'chcpl_is_free' ) && chcpl_is_free() ) {
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$keep_ids = $wpdb->get_col( "SELECT id FROM $table ORDER BY id DESC LIMIT 20" );
		if ( ! empty( $keep_ids ) ) {
			$placeholders = implode( ',', array_map( 'intval', $keep_ids ) );
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
			$wpdb->query( "DELETE FROM $table WHERE id NOT IN ($placeholders)" );
		}
	}
}

function chcpl_get_unread_quote_request_count() {
	global $wpdb;
	$table = chcpl_get_quote_requests_table_name();
	if ( ! chcpl_quote_requests_table_exists() ) {
		return 0;
	}
	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter, PluginCheck.Security.DirectDB.UnescapedDBParameter
	$count = $wpdb->get_var( "SELECT COUNT(*) FROM $table WHERE status = 'unread'" );
	return (int) $count;
}

function chcpl_get_quote_requests( $args = array() ) {
	global $wpdb;
	$table = chcpl_get_quote_requests_table_name();
	if ( ! chcpl_quote_requests_table_exists() ) {
		return array();
	}
	$defaults = array(
		'per_page' => 20,
		'offset'   => 0,
		'status'   => '',
	);
	$args     = wp_parse_args( $args, $defaults );
	$per_page = max( 1, (int) $args['per_page'] );
	$offset   = max( 0, (int) $args['offset'] );
	$status   = in_array( $args['status'], array( 'read', 'unread' ), true ) ? $args['status'] : '';

	if ( $status ) {
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$res = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT * FROM $table WHERE status = %s ORDER BY submitted_at DESC LIMIT %d OFFSET %d",
				$status,
				$per_page,
				$offset
			)
		);
		// phpcs:enable
		return $res;
	}
	// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
	$res = $wpdb->get_results(
		$wpdb->prepare(
			"SELECT * FROM $table ORDER BY submitted_at DESC LIMIT %d OFFSET %d",
			$per_page,
			$offset
		)
	);
	// phpcs:enable
	return $res;
}

function chcpl_get_quote_requests_total( $status = '' ) {
	global $wpdb;
	$table = chcpl_get_quote_requests_table_name();
	if ( ! chcpl_quote_requests_table_exists() ) {
		return 0;
	}
	$status = in_array( $status, array( 'read', 'unread' ), true ) ? $status : '';
	if ( $status ) {
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		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
	return (int) $wpdb->get_var( "SELECT COUNT(*) FROM $table" );
}

function chcpl_update_quote_request_status( $id, $status ) {
	global $wpdb;
	$table = chcpl_get_quote_requests_table_name();
	if ( ! chcpl_quote_requests_table_exists() ) {
		return false;
	}
	if ( ! in_array( $status, array( 'read', 'unread' ), true ) ) {
		return false;
	}
	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
	return $wpdb->update(
		$table,
		array( 'status' => $status ),
		array( 'id' => (int) $id ),
		array( '%s' ),
		array( '%d' )
	);
}

function chcpl_delete_quote_request( $id ) {
	global $wpdb;
	$table = chcpl_get_quote_requests_table_name();
	if ( ! chcpl_quote_requests_table_exists() ) {
		return false;
	}
	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
	return $wpdb->delete(
		$table,
		array( 'id' => (int) $id ),
		array( '%d' )
	);
}

