<?php
/**
 * Survey Response Store for BW AI Schema Pro
 *
 * CRUD over the bw_schema_survey_responses table plus dbDelta-driven
 * install/upgrade. See docs/SPEC-team-survey.md § Data model.
 *
 * @package BW_AI_Schema_Pro
 * @since 2.2.0
 */

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

class BW_Schema_Survey_Store {

	const DB_VERSION       = '1.0';
	const DB_VERSION_OPT   = 'bw_schema_survey_db_version';
	const TABLE_SUFFIX     = 'bw_schema_survey_responses';

	const STATUS_NEW        = 'new';
	const STATUS_TRIAGED    = 'triaged';
	const STATUS_STRUCTURED = 'structured';
	const STATUS_READY      = 'ready';
	const STATUS_PUBLISHED  = 'published';
	const STATUS_REJECTED   = 'rejected';

	/**
	 * Fully qualified table name (with wpdb prefix).
	 */
	public static function table_name() {
		global $wpdb;
		return $wpdb->prefix . self::TABLE_SUFFIX;
	}

	/**
	 * Install or upgrade the table via dbDelta. Idempotent.
	 */
	public static function maybe_install() {
		$installed = get_option( self::DB_VERSION_OPT, '' );
		if ( $installed === self::DB_VERSION ) {
			return;
		}

		global $wpdb;
		$table   = self::table_name();
		$charset = $wpdb->get_charset_collate();

		$sql = "CREATE TABLE {$table} (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			target_post_id BIGINT UNSIGNED NULL DEFAULT NULL,
			submitter_name VARCHAR(191) NOT NULL DEFAULT '',
			submitter_email VARCHAR(191) NULL DEFAULT NULL,
			submitter_ip VARCHAR(45) NOT NULL DEFAULT '',
			status VARCHAR(32) NOT NULL DEFAULT 'new',
			raw_payload LONGTEXT NOT NULL,
			structured_payload LONGTEXT NULL,
			moderator_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',
			published_at DATETIME NULL DEFAULT NULL,
			PRIMARY KEY  (id),
			KEY status (status),
			KEY target_post_id (target_post_id),
			KEY created_at (created_at)
		) {$charset};";

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

		update_option( self::DB_VERSION_OPT, self::DB_VERSION, false );
	}

	/**
	 * Insert a new response. Returns inserted row ID or 0 on failure.
	 *
	 * @param array $data Keys: target_post_id (int|null), submitter_name,
	 *                    submitter_email, submitter_ip, raw_payload (array, will be JSON-encoded).
	 */
	public static function insert( array $data ) {
		global $wpdb;

		$now = gmdate( 'Y-m-d H:i:s' );
		$row = array(
			'target_post_id'  => isset( $data['target_post_id'] ) && $data['target_post_id'] ? absint( $data['target_post_id'] ) : null,
			'submitter_name'  => isset( $data['submitter_name'] ) ? mb_substr( (string) $data['submitter_name'], 0, 191 ) : '',
			'submitter_email' => ! empty( $data['submitter_email'] ) ? mb_substr( (string) $data['submitter_email'], 0, 191 ) : null,
			'submitter_ip'    => isset( $data['submitter_ip'] ) ? (string) $data['submitter_ip'] : '',
			'status'          => self::STATUS_NEW,
			'raw_payload'     => wp_json_encode( isset( $data['raw_payload'] ) ? $data['raw_payload'] : array() ),
			'structured_payload' => null,
			'moderator_notes' => null,
			'created_at'      => $now,
			'updated_at'      => $now,
			'published_at'    => null,
		);

		$format = array( '%d', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s' );

		$ok = $wpdb->insert( self::table_name(), $row, $format );
		if ( false === $ok ) {
			return 0;
		}
		return (int) $wpdb->insert_id;
	}

	/**
	 * Update an existing response.
	 *
	 * @param int   $id     Response ID.
	 * @param array $fields Allowed keys: target_post_id, status, raw_payload (array),
	 *                      structured_payload (array), moderator_notes, published_at.
	 * @return bool
	 */
	public static function update( $id, array $fields ) {
		global $wpdb;

		$id = absint( $id );
		if ( ! $id ) {
			return false;
		}

		$row    = array();
		$format = array();

		if ( array_key_exists( 'target_post_id', $fields ) ) {
			$row['target_post_id'] = $fields['target_post_id'] ? absint( $fields['target_post_id'] ) : null;
			$format[]              = '%d';
		}
		if ( array_key_exists( 'status', $fields ) ) {
			$row['status'] = self::sanitize_status( (string) $fields['status'] );
			$format[]      = '%s';
		}
		if ( array_key_exists( 'raw_payload', $fields ) ) {
			$row['raw_payload'] = wp_json_encode( (array) $fields['raw_payload'] );
			$format[]           = '%s';
		}
		if ( array_key_exists( 'structured_payload', $fields ) ) {
			$row['structured_payload'] = null === $fields['structured_payload']
				? null
				: wp_json_encode( (array) $fields['structured_payload'] );
			$format[] = '%s';
		}
		if ( array_key_exists( 'moderator_notes', $fields ) ) {
			$row['moderator_notes'] = null === $fields['moderator_notes']
				? null
				: sanitize_textarea_field( (string) $fields['moderator_notes'] );
			$format[] = '%s';
		}
		if ( array_key_exists( 'published_at', $fields ) ) {
			$row['published_at'] = $fields['published_at']
				? (string) $fields['published_at']
				: null;
			$format[] = '%s';
		}

		if ( empty( $row ) ) {
			return false;
		}

		$row['updated_at'] = gmdate( 'Y-m-d H:i:s' );
		$format[]          = '%s';

		$ok = $wpdb->update(
			self::table_name(),
			$row,
			array( 'id' => $id ),
			$format,
			array( '%d' )
		);

		return false !== $ok;
	}

	/**
	 * Fetch a single response. Returns associative array (raw_payload +
	 * structured_payload decoded), or null if not found.
	 */
	public static function get( $id ) {
		global $wpdb;

		$id = absint( $id );
		if ( ! $id ) {
			return null;
		}

		$table = self::table_name();
		// Table name is built from $wpdb->prefix + a constant; safe to interpolate.
		$row = $wpdb->get_row(
			$wpdb->prepare( "SELECT * FROM {$table} WHERE id = %d", $id ),
			ARRAY_A
		);

		return $row ? self::hydrate( $row ) : null;
	}

	/**
	 * List responses with simple filters. Returns hydrated rows.
	 *
	 * @param array $args Keys:
	 *   - status (string|string[]|'')
	 *   - target_post_id (int|null|'__holding__' for unlinked only)
	 *   - search (string)
	 *   - orderby (string column)
	 *   - order ('ASC'|'DESC')
	 *   - per_page (int)
	 *   - page (int)
	 */
	public static function query( array $args = array() ) {
		global $wpdb;

		$defaults = array(
			'status'         => '',
			'target_post_id' => null,
			'search'         => '',
			'orderby'        => 'created_at',
			'order'          => 'DESC',
			'per_page'       => 50,
			'page'           => 1,
		);
		$args = array_merge( $defaults, $args );

		$where  = array( '1=1' );
		$params = array();

		if ( ! empty( $args['status'] ) ) {
			$statuses = is_array( $args['status'] ) ? $args['status'] : array( $args['status'] );
			$statuses = array_map( array( __CLASS__, 'sanitize_status' ), $statuses );
			$statuses = array_filter( $statuses );
			if ( ! empty( $statuses ) ) {
				$placeholders = implode( ',', array_fill( 0, count( $statuses ), '%s' ) );
				$where[]      = "status IN ({$placeholders})";
				$params       = array_merge( $params, $statuses );
			}
		}

		if ( '__holding__' === $args['target_post_id'] ) {
			$where[] = 'target_post_id IS NULL';
		} elseif ( null !== $args['target_post_id'] && '' !== $args['target_post_id'] ) {
			$where[]  = 'target_post_id = %d';
			$params[] = absint( $args['target_post_id'] );
		}

		if ( ! empty( $args['search'] ) ) {
			$like     = '%' . $wpdb->esc_like( (string) $args['search'] ) . '%';
			$where[]  = '(submitter_name LIKE %s OR submitter_email LIKE %s)';
			$params[] = $like;
			$params[] = $like;
		}

		$allowed_orderby = array( 'id', 'created_at', 'updated_at', 'status', 'published_at' );
		$orderby         = in_array( $args['orderby'], $allowed_orderby, true ) ? $args['orderby'] : 'created_at';
		$order           = 'ASC' === strtoupper( (string) $args['order'] ) ? 'ASC' : 'DESC';

		$per_page = max( 1, min( 200, absint( $args['per_page'] ) ) );
		$page     = max( 1, absint( $args['page'] ) );
		$offset   = ( $page - 1 ) * $per_page;

		$table      = self::table_name();
		$where_sql  = implode( ' AND ', $where );
		$sql        = "SELECT * FROM {$table} WHERE {$where_sql} ORDER BY {$orderby} {$order} LIMIT %d OFFSET %d";
		$params[]   = $per_page;
		$params[]   = $offset;

		$rows = $wpdb->get_results( $wpdb->prepare( $sql, $params ), ARRAY_A );

		return array_map( array( __CLASS__, 'hydrate' ), $rows ?: array() );
	}

	/**
	 * Count responses matching a status (or 'all').
	 */
	public static function count_by_status( $status = 'all' ) {
		global $wpdb;

		$table = self::table_name();

		if ( 'all' === $status || '' === $status ) {
			return (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$table}" );
		}

		return (int) $wpdb->get_var(
			$wpdb->prepare( "SELECT COUNT(*) FROM {$table} WHERE status = %s", self::sanitize_status( $status ) )
		);
	}

	/**
	 * Delete every response row. Used by the settings-page "Reset" action so
	 * the admin can clear test data between runs. Caller must have already
	 * verified capability + nonce. Returns the number of rows deleted.
	 *
	 * @since 2.2.1
	 * @return int
	 */
	public static function truncate_responses() {
		global $wpdb;
		$table = self::table_name();
		// $wpdb->query returns rows affected (or false on error).
		$deleted = $wpdb->query( "DELETE FROM {$table}" );
		return false === $deleted ? 0 : (int) $deleted;
	}

	/**
	 * Delete a response. Used for true purge; status=rejected is preferred for soft delete.
	 */
	public static function delete( $id ) {
		global $wpdb;
		$id = absint( $id );
		if ( ! $id ) {
			return false;
		}
		return false !== $wpdb->delete( self::table_name(), array( 'id' => $id ), array( '%d' ) );
	}

	/**
	 * Allow-listed status sanitizer.
	 */
	public static function sanitize_status( $status ) {
		$valid = self::valid_statuses();
		$status = is_string( $status ) ? $status : '';
		return in_array( $status, $valid, true ) ? $status : '';
	}

	/**
	 * All recognized status values.
	 */
	public static function valid_statuses() {
		return array(
			self::STATUS_NEW,
			self::STATUS_TRIAGED,
			self::STATUS_STRUCTURED,
			self::STATUS_READY,
			self::STATUS_PUBLISHED,
			self::STATUS_REJECTED,
		);
	}

	/**
	 * Decode JSON columns and cast numeric fields.
	 */
	private static function hydrate( array $row ) {
		$row['id']             = isset( $row['id'] ) ? (int) $row['id'] : 0;
		$row['target_post_id'] = isset( $row['target_post_id'] ) && null !== $row['target_post_id']
			? (int) $row['target_post_id']
			: null;

		$row['raw_payload'] = isset( $row['raw_payload'] ) ? json_decode( $row['raw_payload'], true ) : array();
		if ( ! is_array( $row['raw_payload'] ) ) {
			$row['raw_payload'] = array();
		}

		if ( ! empty( $row['structured_payload'] ) ) {
			$decoded = json_decode( $row['structured_payload'], true );
			$row['structured_payload'] = is_array( $decoded ) ? $decoded : array();
		} else {
			$row['structured_payload'] = array();
		}

		return $row;
	}
}