<?php
/**
 * Database layer — the culprit_incidents table, schema install/upgrade, and a
 * safe query method.
 *
 * phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter
 * Justification (documented, scoped to this file only): this class IS the
 * query layer for Culprit's own custom table. The table name is always built
 * from $wpdb->prefix plus a literal (never user input), every value is bound
 * through $wpdb->prepare(), and orderby/order are whitelisted before
 * interpolation. Incident rows are crash forensics that must be read fresh —
 * caching them in the object cache would risk showing stale conclusions right
 * after a crash, which is the one moment this plugin must be correct.
 *
 * @package Culprit
 */

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

/**
 * Class Culprit_Database
 */
class Culprit_Database {

	const SCHEMA_VERSION  = '1';
	const SCHEMA_OPTION   = 'culprit_schema_version';

	/**
	 * Fully-qualified incidents table name.
	 *
	 * @return string
	 */
	public static function table() {
		global $wpdb;
		return $wpdb->prefix . 'culprit_incidents';
	}

	/**
	 * Create the table (activation).
	 */
	public static function install() {
		global $wpdb;

		$table           = self::table();
		$charset_collate = $wpdb->get_charset_collate();

		$sql = "CREATE TABLE {$table} (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			incident_uid VARCHAR(40) NOT NULL DEFAULT '',
			error_type VARCHAR(40) NOT NULL DEFAULT '',
			error_message TEXT NULL,
			failing_file VARCHAR(255) NOT NULL DEFAULT '',
			failing_line INT NOT NULL DEFAULT 0,
			blamed_slug VARCHAR(190) NOT NULL DEFAULT '',
			blamed_name VARCHAR(190) NOT NULL DEFAULT '',
			blamed_type VARCHAR(20) NOT NULL DEFAULT 'unknown',
			confidence TINYINT UNSIGNED NOT NULL DEFAULT 0,
			context LONGTEXT NULL,
			first_seen DATETIME NOT NULL DEFAULT '1970-01-01 00:00:00',
			last_seen DATETIME NOT NULL DEFAULT '1970-01-01 00:00:00',
			occurrences INT UNSIGNED NOT NULL DEFAULT 1,
			status VARCHAR(20) NOT NULL DEFAULT 'new',
			PRIMARY KEY  (id),
			KEY incident_uid (incident_uid),
			KEY blamed_slug (blamed_slug),
			KEY first_seen (first_seen),
			KEY status (status)
		) {$charset_collate};";

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

		update_option( self::SCHEMA_OPTION, self::SCHEMA_VERSION );
	}

	/**
	 * Run schema upgrades on admin load if the stored version is behind.
	 */
	public static function maybe_upgrade() {
		$current = get_option( self::SCHEMA_OPTION, '0' );
		if ( version_compare( (string) $current, self::SCHEMA_VERSION, '<' ) ) {
			self::install();
		}
	}

	/**
	 * Insert or update an incident, deduplicating on incident_uid. A repeat of a
	 * known incident bumps occurrences + last_seen rather than inserting a row.
	 *
	 * @param array $data Normalized incident fields.
	 * @return int Row id (0 on failure).
	 */
	public static function upsert_incident( array $data ) {
		global $wpdb;
		$table = self::table();

		$uid = isset( $data['incident_uid'] ) ? (string) $data['incident_uid'] : '';
		if ( '' !== $uid ) {
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name from $wpdb->prefix; value is bound.
			$existing = $wpdb->get_row( $wpdb->prepare( "SELECT id, occurrences FROM {$table} WHERE incident_uid = %s", $uid ) );
			if ( $existing ) {
				$wpdb->update(
					$table,
					array(
						'last_seen'   => isset( $data['last_seen'] ) ? $data['last_seen'] : current_time( 'mysql', true ),
						'occurrences' => (int) $existing->occurrences + 1,
					),
					array( 'id' => (int) $existing->id ),
					array( '%s', '%d' ),
					array( '%d' )
				);
				return (int) $existing->id;
			}
		}

		$now = current_time( 'mysql', true );
		$row = array(
			'incident_uid'  => $uid,
			'error_type'    => isset( $data['error_type'] ) ? (string) $data['error_type'] : '',
			'error_message' => isset( $data['error_message'] ) ? (string) $data['error_message'] : '',
			'failing_file'  => isset( $data['failing_file'] ) ? (string) $data['failing_file'] : '',
			'failing_line'  => isset( $data['failing_line'] ) ? (int) $data['failing_line'] : 0,
			'blamed_slug'   => isset( $data['blamed_slug'] ) ? (string) $data['blamed_slug'] : '',
			'blamed_name'   => isset( $data['blamed_name'] ) ? (string) $data['blamed_name'] : '',
			'blamed_type'   => isset( $data['blamed_type'] ) ? (string) $data['blamed_type'] : 'unknown',
			'confidence'    => isset( $data['confidence'] ) ? (int) $data['confidence'] : 0,
			'context'       => isset( $data['context'] ) ? wp_json_encode( $data['context'] ) : null,
			'first_seen'    => isset( $data['first_seen'] ) ? $data['first_seen'] : $now,
			'last_seen'     => isset( $data['last_seen'] ) ? $data['last_seen'] : $now,
			'occurrences'   => 1,
			'status'        => 'new',
		);

		$wpdb->insert( $table, $row );
		return (int) $wpdb->insert_id;
	}

	/**
	 * Query incidents. Safe: ORDER BY whitelisted, order forced, values bound.
	 *
	 * @param array $args { status, blamed_slug, orderby, order, per_page, page }.
	 * @return array Row objects.
	 */
	public static function query( array $args = array() ) {
		global $wpdb;
		$table = self::table();

		$defaults = array(
			'status'      => '',
			'blamed_slug' => '',
			'orderby'     => 'last_seen',
			'order'       => 'DESC',
			'per_page'    => 50,
			'page'        => 1,
		);
		$args = wp_parse_args( $args, $defaults );

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

		if ( '' !== $args['status'] ) {
			$where   .= ' AND status = %s';
			$params[] = $args['status'];
		}
		if ( '' !== $args['blamed_slug'] ) {
			$where   .= ' AND blamed_slug = %s';
			$params[] = $args['blamed_slug'];
		}

		$orderby_whitelist = array( 'last_seen', 'first_seen', 'occurrences', 'confidence', 'id' );
		$orderby           = in_array( $args['orderby'], $orderby_whitelist, true ) ? $args['orderby'] : 'last_seen';
		$order             = ( 'ASC' === strtoupper( $args['order'] ) ) ? 'ASC' : 'DESC';

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

		$params[] = $per_page;
		$params[] = $offset;

		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table + whitelisted orderby/order; all values bound below.
		$sql = "SELECT * FROM {$table} {$where} ORDER BY {$orderby} {$order} LIMIT %d OFFSET %d";

		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- prepared immediately below with bound params.
		return $wpdb->get_results( $wpdb->prepare( $sql, $params ) );
	}

	/**
	 * Update one incident's status (new / acknowledged / resolved).
	 *
	 * @param int    $id     Incident id.
	 * @param string $status New status.
	 * @return bool
	 */
	public static function set_status( $id, $status ) {
		global $wpdb;

		$allowed = array( 'new', 'acknowledged', 'resolved' );
		if ( ! in_array( $status, $allowed, true ) || (int) $id <= 0 ) {
			return false;
		}

		$updated = $wpdb->update(
			self::table(),
			array( 'status' => $status ),
			array( 'id' => (int) $id ),
			array( '%s' ),
			array( '%d' )
		);

		return false !== $updated;
	}

	/**
	 * Count incidents matching a status (or all).
	 *
	 * @param string $status Optional status filter.
	 * @return int
	 */
	public static function count( $status = '' ) {
		global $wpdb;
		$table = self::table();

		if ( '' !== $status ) {
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table from prefix; value bound.
			return (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM {$table} WHERE status = %s", $status ) );
		}
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table from prefix.
		return (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$table}" );
	}
}