<php

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

class Diario_Publicidade_DB {
	public static function clients_table() {
		global $wpdb;
		return $wpdb->prefix . 'diario_ads_clients';
	}

	public static function links_table() {
		global $wpdb;
		return $wpdb->prefix . 'diario_ads_client_ads';
	}

	public static function view_events_table() {
		global $wpdb;
		return $wpdb->prefix . 'diario_ads_view_events';
	}

	public static function activate() {
		global $wpdb;
		require_once ABSPATH . 'wp-admin/includes/upgrade.php';

		$charset = $wpdb->get_charset_collate();
		$clients = self::clients_table();
		$links   = self::links_table();
		$views   = self::view_events_table();

		dbDelta( "CREATE TABLE {$clients} (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			company_name varchar(190) NOT NULL,
			contact_name varchar(190) NOT NULL DEFAULT '',
			email varchar(190) NOT NULL DEFAULT '',
			phone varchar(50) NOT NULL DEFAULT '',
			whatsapp varchar(50) NOT NULL DEFAULT '',
			city varchar(120) NOT NULL DEFAULT '',
			notes text NULL,
			created_at datetime NOT NULL,
			updated_at datetime NOT NULL,
			PRIMARY KEY  (id),
			KEY company_name (company_name)
		) {$charset};" );

		dbDelta( "CREATE TABLE {$links} (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			client_id bigint(20) unsigned NOT NULL,
			adrotate_ad_id bigint(20) unsigned NOT NULL,
			custom_label varchar(190) NOT NULL DEFAULT '',
			placement_label varchar(190) NOT NULL DEFAULT '',
			contract_start date NULL,
			contract_end date NULL,
			contract_value decimal(15,2) NULL,
			notes text NULL,
			created_at datetime NOT NULL,
			updated_at datetime NOT NULL,
			PRIMARY KEY  (id),
			KEY client_id (client_id),
			KEY adrotate_ad_id (adrotate_ad_id),
			KEY contract_end (contract_end)
		) {$charset};" );

		dbDelta( "CREATE TABLE {$views} (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			ad_id bigint(20) unsigned NOT NULL,
			page_url text NULL,
			page_path varchar(255) NOT NULL DEFAULT '',
			event_date date NOT NULL,
			event_datetime datetime NOT NULL,
			session_hash varchar(64) NOT NULL DEFAULT '',
			viewport_percent decimal(5,2) NOT NULL DEFAULT 0,
			visible_ms int(10) unsigned NOT NULL DEFAULT 0,
			device_type varchar(30) NOT NULL DEFAULT '',
			created_at datetime NOT NULL,
			PRIMARY KEY  (id),
			KEY ad_id (ad_id),
			KEY event_date (event_date),
			KEY session_hash (session_hash),
			KEY page_path (page_path(191)),
			KEY ad_date (ad_id,event_date)
		) {$charset};" );

		update_option( 'diario_publicidade_db_version', DIARIO_PUBLICIDADE_VERSION );
	}
}
<php

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

/** Read-only, schema-aware gateway to AdRotate tables. */
class Diario_Publicidade_AdRotate_Adapter {
	private $wpdb;
	private $columns = array();
	private $table_exists = array();
	private $date_formats = array();
	private $metric_schemes = array();
	private $suffixes = array( 'adrotate', 'adrotate_groups', 'adrotate_linkmeta', 'adrotate_schedule', 'adrotate_stats', 'adrotate_stats_archive', 'adrotate_tracker' );

	public function __construct() {
		global $wpdb;
		$this->wpdb = $wpdb;
	}

	public function table( $suffix ) {
		return $this->wpdb->prefix . $suffix;
	}

	public function tables() {
		return $this->suffixes;
	}

	public function table_exists( $suffix ) {
		if ( ! in_array( $suffix, $this->suffixes, true ) ) return false;
		if ( ! array_key_exists( $suffix, $this->table_exists ) ) {
			$table = $this->table( $suffix );
			$this->table_exists[ $suffix ] = $table === $this->wpdb->get_var( $this->wpdb->prepare( 'SHOW TABLES LIKE %s', $table ) );
		}
		return $this->table_exists[ $suffix ];
	}

	public function is_installed() {
		return $this->table_exists( 'adrotate' );
	}

	public function columns( $suffix ) {
		if ( isset( $this->columns[ $suffix ] ) ) return $this->columns[ $suffix ];
		if ( ! in_array( $suffix, $this->suffixes, true ) || ! $this->table_exists( $suffix ) ) return array();
		$rows = $this->wpdb->get_results( 'SHOW COLUMNS FROM `' . esc_sql( $this->table( $suffix ) ) . '`', ARRAY_A );
		$this->columns[ $suffix ] = is_array( $rows ) ?$rows : array();
		return $this->columns[ $suffix ];
	}

	public function column_names( $suffix ) {
		return wp_list_pluck( $this->columns( $suffix ), 'Field' );
	}

	private function first_column( $suffix, $candidates ) {
		$names = $this->column_names( $suffix );
		foreach ( $candidates as $candidate ) if ( in_array( $candidate, $names, true ) ) return $candidate;
		return null;
	}

	private function column_type( $suffix, $column ) {
		foreach ( $this->columns( $suffix ) as $meta ) if ( $meta['Field'] === $column ) return strtolower( $meta['Type'] );
		return '';
	}

	public function diagnostic() {
		$result = array();
		foreach ( $this->suffixes as $suffix ) {
			$exists = $this->table_exists( $suffix );
			$table = $this->table( $suffix );
			$fields = $exists ? $this->detected_fields( $suffix ) : array();
			$result[ $suffix ] = array(
				'table' => $table,
				'exists' => $exists,
				'columns' => $exists ? $this->columns( $suffix ) : array(),
				'count' => null,
				'samples' => $exists ? $this->wpdb->get_results( 'SELECT * FROM `' . esc_sql( $table ) . '` LIMIT 5', ARRAY_A ) : array(),
				'banner_samples' => ( $exists && ! empty( $fields['ad_id'] ) ) ? $this->sample_rows_for_ad( $suffix, 4, 10 ) : array(),
				'date_min' => ( $exists && ! empty( $fields['date'] ) ) ? $this->date_boundary( $suffix, $fields['date'], 'MIN' ) : null,
				'date_max' => ( $exists && ! empty( $fields['date'] ) ) ? $this->date_boundary( $suffix, $fields['date'], 'MAX' ) : null,
				'date_format' => $exists ? $this->detected_date_format( $suffix ) : 'unavailable',
				'fields' => $fields,
				'metric_scheme' => $exists ? $this->metric_scheme( $suffix ) : array( 'mode' => 'unavailable', 'reason' => 'Tabela inexistente.' ),
				'raw_totals_id4' => $exists ? $this->raw_totals_for_ad( $suffix, 4 ) : array( 'impressions' => 0, 'clicks' => 0, 'reason' => 'Tabela inexistente.' ),
			);
		}
		return $result;
	}

	public function detected_fields( $suffix = 'adrotate_stats' ) {
		return array(
			'ad_id' => $this->first_column( $suffix, array( 'ad', 'advert', 'banner', 'bannerid', 'ad_id', 'advert_id', 'banner_id', 'id' ) ),
			'impressions' => $this->first_column( $suffix, array( 'impressions', 'impression', 'views', 'shown' ) ),
			'clicks' => $this->first_column( $suffix, array( 'clicks', 'click', 'clicked' ) ),
			'date' => $this->first_column( $suffix, array( 'thetime', 'date', 'day', 'stat_date', 'created_at', 'timestamp', 'timer', 'time', 'created' ) ),
			'stat_type' => $this->first_column( $suffix, array( 'stat', 'type', 'event', 'action', 'tracker', 'request' ) ),
			'value' => $this->first_column( $suffix, array( 'amount', 'value', 'count', 'counter', 'thecount' ) ),
			'group_id' => $this->first_column( $suffix, array( 'group', 'group_id', 'groupid' ) ),
		);
	}

	private function sample_rows_for_ad( $suffix, $ad_id, $limit = 10 ) {
		$f = $this->detected_fields( $suffix );
		if ( empty( $f['ad_id'] ) ) return array();
		return $this->wpdb->get_results(
			$this->wpdb->prepare(
				'SELECT * FROM `' . esc_sql( $this->table( $suffix ) ) . '` WHERE `' . esc_sql( $f['ad_id'] ) . '` = %d LIMIT %d',
				absint( $ad_id ),
				absint( $limit )
			),
			ARRAY_A
		);
	}

	private function date_boundary( $suffix, $column, $function ) {
		$function = 'MIN' === $function ? 'MIN' : 'MAX';
		return $this->wpdb->get_var( 'SELECT ' . $function . '(`' . esc_sql( $column ) . '`) FROM `' . esc_sql( $this->table( $suffix ) ) . '`' );
	}

	public function detected_date_format( $suffix ) {
		if ( isset( $this->date_formats[ $suffix ] ) ) return $this->date_formats[ $suffix ];
		$column = $this->detected_fields( $suffix )['date'];
		if ( ! $column ) return $this->date_formats[ $suffix ] = 'unavailable';
		$type = $this->column_type( $suffix, $column );
		if ( preg_match( '/^(date)(\(|$)/', $type ) ) return $this->date_formats[ $suffix ] = 'date';
		if ( preg_match( '/^(datetime|timestamp)(\(|$)/', $type ) ) return $this->date_formats[ $suffix ] = 'datetime';
		if ( ! preg_match( '/(tinyint|smallint|mediumint|int|bigint|decimal|numeric)/', $type ) ) return $this->date_formats[ $suffix ] = 'unavailable';

		$table = $this->table( $suffix );
		$sample = $this->wpdb->get_var( 'SELECT MAX(`' . esc_sql( $column ) . '`) FROM `' . esc_sql( $table ) . '` WHERE `' . esc_sql( $column ) . '` > 0' );
		if ( null === $sample ) return $this->date_formats[ $suffix ] = 'unavailable';
		$digits = preg_replace( '/\D/', '', (string) $sample );
		if ( 8 === strlen( $digits ) ) {
			$year = (int) substr( $digits, 0, 4 ); $month = (int) substr( $digits, 4, 2 ); $day = (int) substr( $digits, 6, 2 );
			if ( $year >= 1970 && $year <= 2200 && checkdate( $month, $day, $year ) ) return $this->date_formats[ $suffix ] = 'yyyymmdd';
		}
		$number = (float) $sample;
		if ( $number >= 100000000000.0 && $number < 100000000000000.0 ) return $this->date_formats[ $suffix ] = 'unix_milliseconds';
		if ( $number >= 315532800 && $number <= 7258118400 ) return $this->date_formats[ $suffix ] = 'unix_seconds';
		return $this->date_formats[ $suffix ] = 'unavailable';
	}

	public function get_ads() {
		if ( ! $this->table_exists( 'adrotate' ) ) return array();
		$id = $this->first_column( 'adrotate', array( 'id', 'ad', 'ad_id' ) );
		$name = $this->first_column( 'adrotate', array( 'title', 'name', 'description' ) );
		if ( ! $id ) return array();
		$name_sql = $name ? '`' . esc_sql( $name ) . '`' : "''";
		return $this->wpdb->get_results( 'SELECT `' . esc_sql( $id ) . '` AS id, ' . $name_sql . ' AS name FROM `' . esc_sql( $this->table( 'adrotate' ) ) . '` ORDER BY `' . esc_sql( $id ) . '` DESC', ARRAY_A );
	}

	public function get_ad( $ad_id ) {
		foreach ( $this->get_ads() as $ad ) if ( (int) $ad['id'] === (int) $ad_id ) return $ad;
		return null;
	}

	public function get_ad_status( $ad_id ) {
		if ( ! $this->table_exists( 'adrotate' ) ) return 'Desconhecido';
		$id = $this->first_column( 'adrotate', array( 'id', 'ad', 'ad_id' ) );
		$status = $this->first_column( 'adrotate', array( 'status', 'type', 'active', 'enabled' ) );
		if ( ! $id || ! $status ) return 'Não informado';
		$value = $this->wpdb->get_var( $this->wpdb->prepare( 'SELECT `' . esc_sql( $status ) . '` FROM `' . esc_sql( $this->table( 'adrotate' ) ) . '` WHERE `' . esc_sql( $id ) . '` = %d', $ad_id ) );
		if ( in_array( strtolower( (string) $value ), array( '1', 'yes', 'active', 'enabled' ), true ) ) return 'Ativo';
		if ( in_array( strtolower( (string) $value ), array( '0', 'no', 'inactive', 'disabled' ), true ) ) return 'Inativo';
		return (string) $value ?: 'Não informado';
	}

	public function get_groups() {
		if ( ! $this->table_exists( 'adrotate_groups' ) ) return array();
		$id = $this->first_column( 'adrotate_groups', array( 'id', 'group', 'group_id' ) );
		$name = $this->first_column( 'adrotate_groups', array( 'name', 'title' ) );
		if ( ! $id ) return array();
		$name_sql = $name ? '`' . esc_sql( $name ) . '`' : "''";
		return $this->wpdb->get_results( 'SELECT `' . esc_sql( $id ) . '` AS id, ' . $name_sql . ' AS name FROM `' . esc_sql( $this->table( 'adrotate_groups' ) ) . '` ORDER BY `' . esc_sql( $id ) . '`', ARRAY_A );
	}

	private function metric_scheme( $suffix ) {
		if ( isset( $this->metric_schemes[ $suffix ] ) ) return $this->metric_schemes[ $suffix ];
		$f = $this->detected_fields( $suffix );
		if ( empty( $f['ad_id'] ) ) return $this->metric_schemes[ $suffix ] = array( 'mode' => 'unavailable', 'reason' => 'Coluna do anúncio não identificada.' );
		if ( ! empty( $f['impressions'] ) || ! empty( $f['clicks'] ) ) {
			return $this->metric_schemes[ $suffix ] = array(
				'mode' => 'aggregate_columns',
				'impressions_column' => $f['impressions'],
				'clicks_column' => $f['clicks'],
				'reason' => 'Métricas em colunas numéricas agregadas.',
			);
		}
		if ( ! empty( $f['stat_type'] ) ) {
			return $this->metric_schemes[ $suffix ] = array(
				'mode' => 'typed_rows',
				'type_column' => $f['stat_type'],
				'value_column' => $f['value'],
				'reason' => 'Métricas em linhas separadas por tipo de evento.',
			);
		}
		if ( 'adrotate_tracker' === $suffix ) {
			return $this->metric_schemes[ $suffix ] = array(
				'mode' => 'tracker_rows',
				'reason' => 'Tabela de tracker interpretada como eventos de clique quando não há coluna de tipo.',
			);
		}
		return $this->metric_schemes[ $suffix ] = array( 'mode' => 'unavailable', 'reason' => 'Colunas de métrica não identificadas.' );
	}

	private function metric_sql_parts( $suffix ) {
		$scheme = $this->metric_scheme( $suffix );
		$mode = $scheme['mode'] ?? 'unavailable';
		if ( 'aggregate_columns' === $mode ) {
			$imp = ! empty( $scheme['impressions_column'] ) ? 'SUM(COALESCE(`' . esc_sql( $scheme['impressions_column'] ) . '`,0))' : '0';
			$clk = ! empty( $scheme['clicks_column'] ) ? 'SUM(COALESCE(`' . esc_sql( $scheme['clicks_column'] ) . '`,0))' : '0';
			return array( $imp, $clk, '' );
		}
		if ( 'typed_rows' === $mode ) {
			$type = esc_sql( $scheme['type_column'] );
			$value = ! empty( $scheme['value_column'] ) && $scheme['value_column'] !== $scheme['type_column'] ? 'COALESCE(`' . esc_sql( $scheme['value_column'] ) . '`,1)' : '1';
			$imp_values = "'i','imp','impression','impressions','view','views','show','shown','display','displayed'";
			$clk_values = "'c','clk','click','clicks','clicked'";
			return array(
				"SUM(CASE WHEN LOWER(CAST(`{$type}` AS CHAR)) IN ({$imp_values}) THEN {$value} ELSE 0 END)",
				"SUM(CASE WHEN LOWER(CAST(`{$type}` AS CHAR)) IN ({$clk_values}) THEN {$value} ELSE 0 END)",
				" AND LOWER(CAST(`{$type}` AS CHAR)) IN ({$imp_values},{$clk_values})",
			);
		}
		if ( 'tracker_rows' === $mode ) return array( '0', 'COUNT(*)', '' );
		return array( null, null, '' );
	}

	public function raw_totals_for_ad( $suffix, $ad_id ) {
		if ( ! $this->table_exists( $suffix ) ) return array( 'impressions' => 0, 'clicks' => 0, 'reason' => 'Tabela inexistente.' );
		$f = $this->detected_fields( $suffix );
		list( $imp, $clk, $extra_where ) = $this->metric_sql_parts( $suffix );
		if ( empty( $f['ad_id'] ) || null === $imp || null === $clk ) {
			$scheme = $this->metric_scheme( $suffix );
			return array( 'impressions' => 0, 'clicks' => 0, 'reason' => $scheme['reason'] ?? 'Esquema indisponível.' );
		}
		$sql = 'SELECT ' . $imp . ' impressions, ' . $clk . ' clicks FROM `' . esc_sql( $this->table( $suffix ) ) . '` WHERE `' . esc_sql( $f['ad_id'] ) . '` = %d' . $extra_where;
		$row = $this->wpdb->get_row( $this->wpdb->prepare( $sql, absint( $ad_id ) ), ARRAY_A );
		return array(
			'impressions' => isset( $row['impressions'] ) ? (int) $row['impressions'] : 0,
			'clicks' => isset( $row['clicks'] ) ? (int) $row['clicks'] : 0,
			'reason' => $this->metric_scheme( $suffix )['reason'] ?? '',
		);
	}

	private function source_daily_stats( $suffix, $ad_id, $start, $end ) {
		if ( ! $this->table_exists( $suffix ) ) return array();
		$f = $this->detected_fields( $suffix );
		$format = $this->detected_date_format( $suffix );
		list( $imp, $clk, $extra_where ) = $this->metric_sql_parts( $suffix );
		if ( ! $f['ad_id'] || ! $f['date'] || 'unavailable' === $format || null === $imp || null === $clk ) return array();
		$table = esc_sql( $this->table( $suffix ) );
		$ad = esc_sql( $f['ad_id'] ); $date = esc_sql( $f['date'] );

		if (
			in_array( $suffix, array( 'adrotate_stats', 'adrotate_stats_archive' ), true )
			&& 'aggregate_columns' === ( $this->metric_scheme( $suffix )['mode'] ?? '' )
			&& 'thetime' === $f['date']
			&& in_array( $format, array( 'unix_seconds', 'unix_milliseconds' ), true )
		) {
			return $this->adrotate_bucket_daily_stats( $table, $ad, $date, $imp, $clk, $format, $ad_id, $start, $end );
		}

		if ( in_array( $format, array( 'date', 'datetime' ), true ) ) {
			$sql = "SELECT DATE(`{$date}`) stat_day, {$imp} impressions, {$clk} clicks FROM `{$table}` WHERE `{$ad}` = %d AND `{$date}` >= %s AND `{$date}` < %s{$extra_where} GROUP BY DATE(`{$date}`) HAVING (impressions > 0 OR clicks > 0) ORDER BY stat_day";
			$end_exclusive = ( new DateTimeImmutable( $end, wp_timezone() ) )->modify( '+1 day' )->format( 'Y-m-d' );
			return $this->wpdb->get_results( $this->wpdb->prepare( $sql, absint( $ad_id ), $start . ( 'datetime' === $format ? ' 00:00:00' : '' ), $end_exclusive . ( 'datetime' === $format ? ' 00:00:00' : '' ) ), ARRAY_A );
		}

		if ( 'yyyymmdd' === $format ) {
			$sql = "SELECT `{$date}` stat_day_raw, {$imp} impressions, {$clk} clicks FROM `{$table}` WHERE `{$ad}` = %d AND `{$date}` >= %d AND `{$date}` <= %d{$extra_where} GROUP BY `{$date}` HAVING (impressions > 0 OR clicks > 0) ORDER BY `{$date}`";
			$rows = $this->wpdb->get_results( $this->wpdb->prepare( $sql, absint( $ad_id ), (int) str_replace( '-', '', $start ), (int) str_replace( '-', '', $end ) ), ARRAY_A );
			foreach ( $rows as &$row ) { $raw = (string) $row['stat_day_raw']; $row['stat_day'] = substr($raw,0,4).'-'.substr($raw,4,2).'-'.substr($raw,6,2); }
			return $rows;
		}

		return $this->timestamp_daily_stats( $table, $ad, $date, $imp, $clk, $extra_where, $format, $ad_id, $start, $end );
	}

	private function adrotate_bucket_bounds( $start, $end, $format ) {
		$timezone = wp_timezone();
		$start_dt = new DateTimeImmutable( $start . ' 00:00:00', $timezone );
		$end_dt = ( new DateTimeImmutable( $end . ' 00:00:00', $timezone ) )->modify( '+1 day' );
		$start_offset = $timezone->getOffset( $start_dt );
		$end_offset = $timezone->getOffset( $end_dt );
		$from = gmmktime( 0, 0, 0, (int) $start_dt->format( 'm' ), (int) $start_dt->format( 'd' ), (int) $start_dt->format( 'Y' ) ) + $start_offset;
		$to = gmmktime( 0, 0, 0, (int) $end_dt->format( 'm' ), (int) $end_dt->format( 'd' ), (int) $end_dt->format( 'Y' ) ) + $end_offset;
		if ( 'unix_milliseconds' === $format ) {
			$from *= 1000;
			$to *= 1000;
		}
		return array( $from, $to );
	}

	private function adrotate_bucket_daily_stats( $table, $ad_column, $date_column, $imp, $clk, $format, $ad_id, $start, $end ) {
		list( $from, $to ) = $this->adrotate_bucket_bounds( $start, $end, $format );
		$sql = "SELECT `{$date_column}` AS stat_bucket, {$imp} AS impressions, {$clk} AS clicks FROM `{$table}` WHERE `{$ad_column}` = %d AND `{$date_column}` >= %d AND `{$date_column}` < %d GROUP BY `{$date_column}` HAVING (impressions > 0 OR clicks > 0) ORDER BY `{$date_column}`";
		$rows = $this->wpdb->get_results( $this->wpdb->prepare( $sql, absint( $ad_id ), $from, $to ), ARRAY_A );
		foreach ( $rows as &$row ) {
			$row['stat_day'] = $this->adrotate_bucket_stat_day( (int) $row['stat_bucket'], $format, $start );
		}
		return $rows;
	}

	private function adrotate_bucket_stat_day( $raw_bucket, $format, $reference_date ) {
		$seconds = 'unix_milliseconds' === $format ? (int) floor( $raw_bucket / 1000 ) : $raw_bucket;
		list( $reference_bucket ) = $this->adrotate_bucket_bounds( $reference_date, $reference_date, $format );
		$reference_seconds = 'unix_milliseconds' === $format ? (int) floor( $reference_bucket / 1000 ) : $reference_bucket;
		$day_offset = (int) floor( ( $seconds - $reference_seconds ) / DAY_IN_SECONDS );
		return ( new DateTimeImmutable( $reference_date . ' 00:00:00', wp_timezone() ) )->modify( '+' . $day_offset . ' days' )->format( 'Y-m-d' );
	}

	private function source_ignore_reason( $suffix ) {
		if ( ! $this->table_exists( $suffix ) ) return 'Tabela inexistente.';
		$f = $this->detected_fields( $suffix );
		if ( empty( $f['ad_id'] ) ) return 'Coluna do anúncio não identificada.';
		if ( empty( $f['date'] ) ) return 'Coluna de data não identificada.';
		if ( 'unavailable' === $this->detected_date_format( $suffix ) ) return 'Formato de data não identificado.';
		$scheme = $this->metric_scheme( $suffix );
		if ( 'unavailable' === ( $scheme['mode'] ?? 'unavailable' ) ) return $scheme['reason'] ?? 'Esquema de métricas não identificado.';
		return '';
	}

	private function timestamp_daily_stats( $table, $ad_column, $date_column, $imp, $clk, $extra_where, $format, $ad_id, $start, $end ) {
		$timezone = wp_timezone();
		$current = new DateTimeImmutable( $start . ' 00:00:00', $timezone );
		$last = ( new DateTimeImmutable( $end . ' 00:00:00', $timezone ) )->modify( '+1 day' );
		$days = array();
		while ( $current < $last ) {
			$next = $current->modify( '+1 day' );
			$from = $current->getTimestamp(); $to = $next->getTimestamp();
			if ( 'unix_milliseconds' === $format ) { $from *= 1000; $to *= 1000; }
			$days[] = array( 'day'=>$current->format('Y-m-d'), 'from'=>$from, 'to'=>$to );
			$current = $next;
		}
		$rows = array();
		foreach ( array_chunk( $days, 90 ) as $chunk ) {
			$parts = array(); $args = array();
			foreach ( $chunk as $day ) {
				$parts[] = "SELECT %s stat_day, {$imp} impressions, {$clk} clicks FROM `{$table}` WHERE `{$ad_column}` = %d AND `{$date_column}` >= %d AND `{$date_column}` < %d{$extra_where} HAVING (impressions > 0 OR clicks > 0)";
				array_push( $args, $day['day'], absint( $ad_id ), $day['from'], $day['to'] );
			}
			$rows = array_merge( $rows, $this->wpdb->get_results( $this->wpdb->prepare( implode( ' UNION ALL ', $parts ), $args ), ARRAY_A ) );
		}
		return $rows;
	}

	public function get_daily_stats( $ad_id, $start, $end ) {
		$current = $this->normalize_daily_rows( $this->source_daily_stats( 'adrotate_stats', $ad_id, $start, $end ) );
		$archive = $this->normalize_daily_rows( $this->source_daily_stats( 'adrotate_stats_archive', $ad_id, $start, $end ) );
		// Archived days are authoritative. Current stats only fill days absent from archive.
		$days = $archive + $current;
		$clicks = array_sum( wp_list_pluck( $days, 'clicks' ) );
		if ( 0 === (int) $clicks ) {
			$tracker = $this->normalize_daily_rows( $this->source_daily_stats( 'adrotate_tracker', $ad_id, $start, $end ) );
			foreach ( $tracker as $date => $row ) {
				if ( isset( $days[ $date ] ) ) $days[ $date ]['clicks'] += (int) $row['clicks'];
				else $days[ $date ] = $row;
			}
		}
		ksort( $days );
		foreach ( $days as &$day ) $day['ctr'] = $this->ctr( $day['impressions'], $day['clicks'] );
		return array_values( $days );
	}

	public function debug_stats( $ad_id, $start, $end ) {
		$debug = array();
		foreach ( array( 'adrotate_stats', 'adrotate_stats_archive', 'adrotate_tracker' ) as $suffix ) {
			$rows = $this->source_daily_stats( $suffix, $ad_id, $start, $end );
			$normalized = $this->normalize_daily_rows( $rows );
			$debug[ $suffix ] = array(
				'table' => $this->table( $suffix ),
				'fields' => $this->detected_fields( $suffix ),
				'date_format' => $this->detected_date_format( $suffix ),
				'metric_scheme' => $this->metric_scheme( $suffix ),
				'params' => array( 'ad_id' => absint( $ad_id ), 'start' => $start, 'end' => $end ),
				'query' => $this->wpdb->last_query,
				'raw_total' => $this->raw_totals_for_ad( $suffix, $ad_id ),
				'raw_rows_debug' => 'adrotate_stats' === $suffix ? $this->debug_adrotate_stats_rows( $ad_id, $start, $end ) : array(),
				'period_total' => array(
					'impressions' => array_sum( wp_list_pluck( $normalized, 'impressions' ) ),
					'clicks' => array_sum( wp_list_pluck( $normalized, 'clicks' ) ),
				),
				'ignored_reason' => $rows ? '' : $this->source_ignore_reason( $suffix ),
			);
		}
		$consolidated = $this->get_daily_stats( $ad_id, $start, $end );
		$debug['consolidated'] = array(
			'impressions' => array_sum( wp_list_pluck( $consolidated, 'impressions' ) ),
			'clicks' => array_sum( wp_list_pluck( $consolidated, 'clicks' ) ),
			'days' => count( $consolidated ),
			'deduplication' => 'adrotate_stats_archive é autoritativa por dia; adrotate_stats preenche dias ausentes; adrotate_tracker só complementa cliques quando stats/archive retornam zero clique.',
		);
		return $debug;
	}

	public function debug_adrotate_stats_rows( $ad_id, $start, $end ) {
		if ( ! $this->table_exists( 'adrotate_stats' ) ) return array();
		$f = $this->detected_fields( 'adrotate_stats' );
		if ( empty( $f['ad_id'] ) || empty( $f['date'] ) ) return array();
		$format = $this->detected_date_format( 'adrotate_stats' );
		list( $from, $to ) = in_array( $format, array( 'unix_seconds', 'unix_milliseconds' ), true ) ? $this->adrotate_bucket_bounds( $start, $end, $format ) : array( null, null );
		$rows = $this->sample_rows_for_ad( 'adrotate_stats', $ad_id, 10 );
		$debug = array();
		foreach ( $rows as $row ) {
			$raw = isset( $row[ $f['date'] ] ) ? (int) $row[ $f['date'] ] : 0;
			$raw_seconds = 'unix_milliseconds' === $format ? (int) floor( $raw / 1000 ) : $raw;
			$in_period = null !== $from && $raw >= $from && $raw < $to;
			$debug[] = array(
				'thetime_bruto' => $raw,
				'impressions_bruto' => isset( $row['impressions'] ) ? (int) $row['impressions'] : 0,
				'clicks_bruto' => isset( $row['clicks'] ) ? (int) $row['clicks'] : 0,
				'data_interpretada_evento' => $raw_seconds ? wp_date( 'Y-m-d H:i:s', $raw_seconds ) : '',
				'data_estatistica_considerada' => $raw_seconds ? $this->adrotate_bucket_stat_day( $raw, $format, $start ) : '',
				'entrou_no_periodo' => $in_period ? 'sim' : 'nao',
				'motivo' => $in_period ? 'Bucket di?rio dentro do per?odo selecionado.' : 'Bucket di?rio fora do range calculado para o per?odo.',
			);
		}
		return $debug;
	}

	private function normalize_daily_rows( $rows ) {
		$days = array();
		foreach ( $rows as $row ) {
			$day = $row['stat_day'] ?? '';
			if ( ! preg_match( '/^\d{4}-\d{2}-\d{2}$/', $day ) ) continue;
			$days[ $day ] = array( 'date'=>$day, 'impressions'=>(int)$row['impressions'], 'clicks'=>(int)$row['clicks'] );
		}
		return $days;
	}

	public function ctr( $impressions, $clicks ) {
		return $impressions > 0 ? round( ( $clicks / $impressions ) * 100, 2 ) : 0.0;
	}
}
<php

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

class Diario_Publicidade_Public_Tracker {
	const AJAX_ACTION = 'diario_publicidade_track_view';

	public function __construct() {
		add_action( 'wp_enqueue_scripts', array( $this, 'enqueue' ) );
		add_action( 'wp_ajax_' . self::AJAX_ACTION, array( $this, 'record_view' ) );
		add_action( 'wp_ajax_nopriv_' . self::AJAX_ACTION, array( $this, 'record_view' ) );
	}

	public function enqueue() {
		if ( is_admin() ) return;
		wp_enqueue_script(
			'diario-publicidade-tracker',
			plugins_url( 'assets/public-tracker.js', DIARIO_PUBLICIDADE_FILE ),
			array(),
			DIARIO_PUBLICIDADE_VERSION,
			true
		);
		wp_localize_script(
			'diario-publicidade-tracker',
			'DiarioPublicidadeTracker',
			array(
				'ajaxUrl' => admin_url( 'admin-ajax.php' ),
				'action'  => self::AJAX_ACTION,
				'nonce'   => wp_create_nonce( self::AJAX_ACTION ),
				'debug'   => current_user_can( 'manage_options' ),
			)
		);
	}

	public function record_view() {
		global $wpdb;

		check_ajax_referer( self::AJAX_ACTION, 'nonce' );

		$ad_id = isset( $_POST['ad_id'] ) ? absint( $_POST['ad_id'] ) : 0;
		if ( ! $ad_id ) wp_send_json_error( array( 'message' => 'invalid_ad_id' ), 400 );

		$page_url = esc_url_raw( wp_unslash( $_POST['page_url'] ?? '' ) );
		$page_path = sanitize_text_field( wp_unslash( $_POST['page_path'] ?? '' ) );
		$session_raw = sanitize_text_field( wp_unslash( $_POST['session_id'] ?? '' ) );
		$viewport_percent = isset( $_POST['viewport_percent'] ) ? min( 100, max( 0, (float) $_POST['viewport_percent'] ) ) : 0;
		$visible_ms = isset( $_POST['visible_ms'] ) ? absint( $_POST['visible_ms'] ) : 0;

		if ( ! $session_raw ) wp_send_json_error( array( 'message' => 'invalid_session' ), 400 );
		if ( $visible_ms < 1000 || $viewport_percent < 50 ) wp_send_json_error( array( 'message' => 'not_viewable' ), 400 );

		$session_hash = hash_hmac( 'sha256', $session_raw, wp_salt( 'nonce' ) );
		$event_date = current_time( 'Y-m-d' );
		$event_datetime = current_time( 'mysql' );
		$table = Diario_Publicidade_DB::view_events_table();

		$exists = (int) $wpdb->get_var(
			$wpdb->prepare(
				"SELECT id FROM {$table} WHERE ad_id = %d AND session_hash = %s AND page_path = %s AND event_date = %s LIMIT 1",
				$ad_id,
				$session_hash,
				$page_path,
				$event_date
			)
		);

		if ( $exists ) wp_send_json_success( array( 'recorded' => false, 'duplicate' => true ) );

		$wpdb->insert(
			$table,
			array(
				'ad_id'            => $ad_id,
				'page_url'         => $page_url,
				'page_path'        => $page_path,
				'event_date'       => $event_date,
				'event_datetime'   => $event_datetime,
				'session_hash'     => $session_hash,
				'viewport_percent' => $viewport_percent,
				'visible_ms'       => $visible_ms,
				'device_type'      => $this->device_type(),
				'created_at'       => $event_datetime,
			),
			array( '%d', '%s', '%s', '%s', '%s', '%s', '%f', '%d', '%s', '%s' )
		);

		wp_send_json_success( array( 'recorded' => true ) );
	}

	private function device_type() {
		$ua = strtolower( sanitize_text_field( wp_unslash( $_SERVER['HTTP_USER_AGENT'] ?? '' ) ) );
		if ( preg_match( '/ipad|tablet|kindle/', $ua ) ) return 'tablet';
		if ( preg_match( '/mobile|android|iphone|ipod|windows phone/', $ua ) ) return 'mobile';
		return 'desktop';
	}
}