<?php

namespace AdFuz\Models;

use AdFuz\Database\DB;
use AdFuz\Helpers\Common;
use AdFuz\Helpers\TableName;

if ( ! defined( 'ABSPATH' ) ) {
	exit;
}

class Campaigns extends AbstractModel {


	public const TABLE = 'adfuz_campaigns';


	public static function schema(): array {
		return array(
			'id'                 => array(
				'type' => 'bigint',
			),
			'slug'               => array(
				'type'   => 'string',
				'length' => 255,
			),
			'user_id'            => array(
				'type'     => 'bigint',
				'nullable' => true,
			),
			'name'               => array(
				'type'     => 'string',
				'length'   => 255,
				'required' => true,
			),
			'expiry_date'        => array(
				'type'     => 'datetime',
				'nullable' => true,
			),
			'expiry_impressions' => array(
				'type'     => 'int',
				'nullable' => true,
			),
			'expiry_clicks'      => array(
				'type'     => 'int',
				'nullable' => true,
			),
			'targeting'          => array(
				'type'     => 'json',
				'nullable' => true,
			),
			'start_date'         => array(
				'type'     => 'datetime',
				'nullable' => true,
			),
			'end_date'           => array(
				'type'     => 'datetime',
				'nullable' => true,
			),
			'pricing_model'      => array(
				'type'    => 'enum',
				'enum'    => array( 'flat', 'per_impression', 'per_click', 'mixed' ),
				'default' => 'flat',
			),
			'price'              => array(
				'type'    => 'decimal',
				'default' => '0.00',
			),
			'currency'           => array(
				'type'    => 'string',
				'length'  => 5,
				'default' => 'USD',
			),
			'fallback_ad_id'     => array(
				'type'     => 'bigint',
				'nullable' => true,
			),
			'status'             => array(
				'type'    => 'enum',
				'enum'    => array( 'pending', 'active', 'paused', 'expired', 'deleted' ),
				'default' => 'pending',
			),
			'created_at'         => array(
				'type'      => 'datetime',
				'nullable'  => true,
				'immutable' => true,
			),
			'updated_at'         => array(
				'type'      => 'datetime',
				'nullable'  => true,
				'immutable' => true,
			),
		);
	}

	public static function table() {
		return static::TABLE;
	}

	public static function withAds( int $campaignId ): ?array {
		$at = TableName::ADFUZ_ADS();

		$campaign = DB::get( TableName::ADFUZ_CAMPAIGNS() )->find( $campaignId );

		if ( ! $campaign ) {
			return null;
		}

		$campaign['ads'] = array_values(
			DB::get( TableName::ADFUZ_CAMPAIGN_ADS() )
				->alias( 'ca' )
				->select( 'a.id', 'a.title', 'a.status', 'ca.display_order' )
				->join( $at, 'ca.ad_id', 'a.id', 'a' )
				->where( 'ca.campaign_id', $campaignId )
				->orderBy( 'ca.display_order', 'ASC' )
				->get()
		);

		return $campaign;
	}

	/**
	 * Get all campaigns linked to a single Ad Space via the pivot table.
	 *
	 * @param  int $adSpaceId
	 *
	 * @return array  Flat array of campaigns with link_status and display_order
	 */
	public static function withAdsBySpace( int $adSpaceId ): array {
		if ( ! $adSpaceId ) {
			return array();
		}

		$grouped = self::withAdsBySpaceIds( array( $adSpaceId ) );

		return $grouped[ $adSpaceId ] ?? array();
	}


	public static function withAdsMany( array $campaignIds = array() ): array {
		$ct  = TableName::ADFUZ_CAMPAIGNS( true );
		$cat = TableName::ADFUZ_CAMPAIGN_ADS( true );
		$at  = TableName::ADFUZ_ADS( true );

		$campaignIds = array_map( 'intval', $campaignIds );

		$qb = DB::get( TableName::ADFUZ_CAMPAIGNS() )
				->alias( 'c' )
				->select(
					'c.id as campaign_id',
					'c.name',
					'c.status',
					'c.start_date',
					'c.end_date',
					'c.currency',
					'c.price',
					'c.fallback_ad_id',
					'c.created_at as campaign_created_at',
					'a.id as ad_id',
					'a.title as ad_title',
					'a.status as ad_status',
					'ca.display_order'
				)
				->leftJoin( $cat, 'c.id', 'ca.campaign_id', 'ca' )
				->leftJoin( $at, 'ca.ad_id', 'a.id', 'a' )
				->orderBy( 'c.id', 'ASC' );

		if ( ! empty( $campaignIds ) ) {
			$qb->whereIn( 'c.id', $campaignIds );
		}

		return self::groupCampaignRows( $qb->get() );
	}

	/**
	 * Get campaigns with their ads for multiple Ad Spaces
	 *
	 * @param array $space_ids
	 * @return array Grouped by ad_space_id with campaigns and their ads
	 */
	public static function withCampaignAdsBySpaceIds(array $space_ids): array
	{
		if (empty($space_ids)) {
			return array();
		}

		// Join all necessary tables to get campaigns with their ads
		$rows = DB::get(TableName::ADFUZ_CAMPAIGN_AD_SPACES())
		          ->alias('cas')
		          ->select(
			          'cas.id as link_id',
			          'cas.ad_space_id',
			          'cas.campaign_id',
			          'cas.status as link_status',
			          'cas.display_order',
			          'c.name',
			          'c.status as campaign_status',
			          'c.start_date',
			          'c.end_date',
			          'a.id as ad_id',
			          'a.title as ad_title',
			          'a.status as ad_status',
			          'a.ad_type',
			          'ca.display_order as ad_display_order'
		          )
		          ->join(TableName::ADFUZ_CAMPAIGNS(), 'cas.campaign_id', 'c.id', 'c')
		          ->leftJoin(TableName::ADFUZ_CAMPAIGN_ADS(), 'c.id', 'ca.campaign_id', 'ca')
		          ->leftJoin(TableName::ADFUZ_ADS(), 'ca.ad_id', 'a.id', 'a')
		          ->whereIn('cas.ad_space_id', $space_ids)
		          ->where('c.status', '!=', 'deleted')
		          ->orderBy('cas.display_order', 'ASC')
		          ->orderBy('ca.display_order', 'ASC')
		          ->get();

		return self::buildCampaignMapBySpace($rows, $space_ids);
	}

	/**
	 * Build campaign map grouped by ad_space_id with ads
	 */
	private static function buildCampaignMapBySpace(array $rows, array $space_ids): array
	{
		$grouped = array_fill_keys($space_ids, []);

		foreach ($rows as $row) {
			$space_id = $row['ad_space_id'];
			$campaign_id = $row['campaign_id'];

			if (!isset($grouped[$space_id])) {
				$grouped[$space_id] = [];
			}

			// Find or create campaign in this space
			$campaign_index = null;
			foreach ($grouped[$space_id] as $index => $campaign) {
				if ($campaign['campaign_id'] === $campaign_id) {
					$campaign_index = $index;
					break;
				}
			}

			if ($campaign_index === null) {
				$campaign_index = count($grouped[$space_id]);
				$grouped[$space_id][$campaign_index] = [
					'link_id' => $row['link_id'],
					'campaign_id' => $campaign_id,
					'ad_space_id' => $space_id,
					'name' => $row['name'],
					'status' => $row['campaign_status'],
					'link_status' => $row['link_status'],
					'display_order' => $row['display_order'],
					'start_date' => $row['start_date'],
					'end_date' => $row['end_date'],
					'ads' => [],
				];
			}

			// Add ad if exists
			if (!empty($row['ad_id']) && $row['ad_status'] === 'active') {
				$grouped[$space_id][$campaign_index]['ads'][] = [
					'id' => (int) $row['ad_id'],
					'title' => $row['ad_title'],
					'status' => $row['ad_status'],
					'ad_type' => $row['ad_type'] ?? '—',
					'display_order' => (int) $row['ad_display_order'],
				];
			}
		}

		// Sort ads by display_order within each campaign
		foreach ($grouped as $space_id => &$campaigns) {
			foreach ($campaigns as &$campaign) {
				usort($campaign['ads'], fn($a, $b) => $a['display_order'] <=> $b['display_order']);
			}
		}
		unset($campaigns);

		return $grouped;
	}


	/**
	 * Get all campaigns for a given Ad Space, each with their associated ads.
	 *
	 * @param  int $adSpaceId
	 *
	 * @return array
	 */
	public static function withAdsBySpaceIds( array $space_ids ): array {
		if ( empty( $space_ids ) ) {
			return array();
		}

		$rows = DB::get( TableName::ADFUZ_CAMPAIGN_AD_SPACES() )
					->alias( 'cas' )
					->join( TableName::ADFUZ_CAMPAIGNS(), 'cas.campaign_id', 'c.id', 'c' )
					->select(
						'cas.id as link_id',       // ← use cas.id as the unique key, not c.id
						'cas.ad_space_id',
						'cas.campaign_id',
						'cas.status as link_status',
						'cas.display_order',
						// 'c.id as campaign_id_val', // ← alias c.id to avoid key conflict
						'c.name',
						'c.status',
						'c.start_date',
						'c.end_date'
					)
					->whereIn( 'cas.ad_space_id', $space_ids )
					->orderBy( 'cas.display_order', 'ASC' )
					->get();

		$grouped = array();

		foreach ( $rows as $row ) {
			$grouped[ $row['ad_space_id'] ][] = $row;
		}


		return $grouped;
	}


	// REPLACE the existing groupCampaignRows() with this:
	private static function groupCampaignRows( array $rows ): array {
		return array_values( self::buildCampaignMap( $rows ) );
	}


	private static function groupCampaignRowsBySpace( array $rows ): array {
		$grouped = array();
		foreach ( self::buildCampaignMap( $rows ) as $campaign ) {
			$sid = $campaign['ad_space_id'];
			unset( $campaign['ad_space_id'] );
			$grouped[ $sid ][] = $campaign;
		}

		return $grouped;
	}

	private static function buildCampaignMap( array $rows ): array {
		if ( ! empty( $rows ) ) {
			Common::log_file( array_keys( $rows[0] ) );
		}
		$campaigns = array();
		foreach ( $rows as $row ) {
			$cid = $row['campaign_id'];
			if ( ! isset( $campaigns[ $cid ] ) ) {
				$campaigns[ $cid ] = array(
					'id'          => $cid,
					'ad_space_id' => $row['ad_space_id'] ?? null,
					'name'        => $row['name'],
					'status'      => $row['status'],
					'start_date'  => $row['start_date'],
					'end_date'    => $row['end_date'],
					'price'       => $row['price'] ?? null,
					'currency'    => $row['currency'] ?? null,
					'created_at'  => $row['campaign_created_at'] ?? null,
					'ads'         => array(),
				);
			}
			if ( ! empty( $row['ad_id'] ) ) {
				$campaigns[ $cid ]['ads'][] = array(
					'id'            => $row['ad_id'],
					'title'         => $row['ad_title'],
					'ad_type'       => $row['ad_type'] ?? '—',
					'status'        => $row['ad_status'],
					'display_order' => (int) $row['display_order'],
				);
			}
		}
		foreach ( $campaigns as &$campaign ) {
			usort( $campaign['ads'], fn( $a, $b ) => $a['display_order'] <=> $b['display_order'] );
		}

		unset( $campaign );

		return $campaigns;
	}

	/**
	 * Get only ACTIVE, date-valid campaigns for a space with FULL ad data.
	 * Specifically designed for frontend ad serving — not for admin views.
	 *
	 * Differences from withAdsBySpace():
	 *  - Filters status = 'active' only
	 *  - Filters start_date / end_date window
	 *  - Returns full ad fields needed by AdRendererController
	 *  - Includes expiry_impressions, expiry_clicks, display_order for rotation
	 *
	 * @param  int $adSpaceId
	 *
	 * @return array  [ [ 'campaign' => [...], 'ads' => [...] ], ... ]
	 */
	public static function activeWithAdsBySpace( int $adSpaceId ): array {
		$cast = TableName::ADFUZ_CAMPAIGN_AD_SPACES( true );
		$cat  = TableName::ADFUZ_CAMPAIGN_ADS();
		$at   = TableName::ADFUZ_ADS();
		$now  = current_time( 'mysql' );

		$rows = DB::get( TableName::ADFUZ_CAMPAIGN_AD_SPACES() )
					->alias( 'cas' )
					->select(
					// Campaign-Ad Space pivot fields
						'cas.display_order as campaign_order',
						'cas.status as link_status',
						// Campaign fields
						'c.id as campaign_id',
						'c.name',
						'c.expiry_impressions',
						'c.expiry_clicks',
						'c.fallback_ad_id',
						'c.start_date',
						'c.end_date',
						'c.status as campaign_status',
						// Full ad fields needed by AdRendererController
						'a.id as ad_id',
						'a.ad_type',
						'a.title as ad_title',
						'a.hide_title',
						'a.description',
						'a.target_url',
						'a.media_url',
						'a.media_id',
						'a.script_html',
						'a.cta',
						'a.cta_type',
						'a.cta_position',
						'a.link_to',
						'a.status as ad_status',
						// Ad display order within campaign
						'ca.display_order'
					)
					->join( TableName::ADFUZ_CAMPAIGNS(), 'cas.campaign_id', 'c.id', 'c' )
					->leftJoin( $cat, 'c.id', 'ca.campaign_id', 'ca' )
					->leftJoin( $at, 'ca.ad_id', 'a.id', 'a' )
					->where( 'cas.ad_space_id', $adSpaceId )
					->where( 'cas.status', 'active' )
					->where( 'c.status', 'active' )
					->get();

		// Filter date window in PHP — QueryBuilder doesn't support
		// NULL-aware date comparisons (start_date IS NULL OR start_date <= now) cleanly
		$rows = array_filter(
			$rows,
			function ( $row ) use ( $now ) {
				$start_ok = empty( $row['start_date'] ) || $row['start_date'] <= $now;
				$end_ok   = empty( $row['end_date'] ) || $row['end_date'] >= $now;

				return $start_ok && $end_ok;
			}
		);

		return self::buildServingMap( array_values( $rows ) );
	}

	/**
	 * Builds the structured [ 'campaign' => [...], 'ads' => [...] ] format
	 * expected by AdCacheController / AdRotatorController.
	 */
	private static function buildServingMap( array $rows ): array {
		$campaigns = array();

		foreach ( $rows as $row ) {
			$cid = $row['campaign_id'];

			if ( ! isset( $campaigns[ $cid ] ) ) {
				$campaigns[ $cid ] = array(
					'campaign' => array(
						'id'                 => $cid,
						'name'               => $row['name'],
						'display_order'      => (int) $row['campaign_order'],  // ← from cas.display_order
						'expiry_impressions' => $row['expiry_impressions'],
						'expiry_clicks'      => $row['expiry_clicks'],
						'fallback_ad_id'     => $row['fallback_ad_id'],
						'start_date'         => $row['start_date'],
						'end_date'           => $row['end_date'],
						'status'             => $row['campaign_status'],
					),
					'ads'      => array(),
				);
			}

			// Skip left-join rows where no ad is linked
			if ( empty( $row['ad_id'] ) || $row['ad_status'] !== 'active' ) {
				continue;
			}

			$campaigns[ $cid ]['ads'][] = array(
				'id'            => (int) $row['ad_id'],
				'ad_type'       => $row['ad_type'],
				'title'         => $row['ad_title'],
				'hide_title'    => (bool) $row['hide_title'],
				'description'   => $row['description'],
				'target_url'    => $row['target_url'],
				'media_url'     => $row['media_url'],
				'media_id'      => $row['media_id'],
				'script_html'   => $row['script_html'],
				'cta'           => $row['cta'],
				'cta_type'      => $row['cta_type'],
				'cta_position'  => $row['cta_position'],
				'link_to'       => $row['link_to'],
				'display_order' => (int) $row['display_order'],
				'campaign_id'   => $cid,
			);
		}

		foreach ( $campaigns as &$entry ) {
			usort( $entry['ads'], fn( $a, $b ) => $a['display_order'] <=> $b['display_order'] );
		}
		unset( $entry );

		$result = array_values( $campaigns );
		usort( $result, fn( $a, $b ) => $a['campaign']['display_order'] <=> $b['campaign']['display_order'] );

		return $result;
	}


	/**
	 * Paginate campaigns and return each with their ads.
	 *
	 * @param  int   $per_page
	 * @param  int   $current_page
	 * @param  array $filters  ['status' => '', 'search' => '']
	 *
	 * @return array { data, total, per_page, current_page, last_page }
	 */
	public static function paginate( int $per_page = 20, int $current_page = 1, array $filters = array() ): array {

		// ── Step 1: Paginate campaign IDs only (no joins) ────────────────
		$qb = DB::get( TableName::ADFUZ_CAMPAIGNS() )
				->select( 'id' )
				->where( 'status', '!=', 'deleted' )
				->orderBy( 'id', 'DESC' );

		if ( ! empty( $filters['status'] ) ) {
			$qb->where( 'status', $filters['status'] );
		}

		// paginate() on simple table — works perfectly
		$paginated = $qb->paginate( $per_page, $current_page );

		// ── Step 2: Search filter (raw, after pagination meta is built) ──
		// If search is active, redo with LIKE for total + IDs
		if ( ! empty( $filters['search'] ) ) {
			global $wpdb;
			$search = '%' . $wpdb->esc_like( $filters['search'] ) . '%';
			$table  = $wpdb->prefix . TableName::ADFUZ_CAMPAIGNS();
			$offset = ( $current_page - 1 ) * $per_page;

			$total = (int) $wpdb->get_var(
				$wpdb->prepare(
					"SELECT COUNT(*) FROM {$table} WHERE status != 'deleted' AND name LIKE %s",
					$search
				)
			);

			$ids = $wpdb->get_col(
				$wpdb->prepare(
					"SELECT id FROM {$table} WHERE status != 'deleted' AND name LIKE %s ORDER BY id DESC LIMIT %d OFFSET %d",
					$search,
					$per_page,
					$offset
				)
			);

			$paginated = array(
				'data'         => array_map( fn( $id ) => array( 'id' => $id ), $ids ),
				'total'        => $total,
				'per_page'     => $per_page,
				'current_page' => $current_page,
				'last_page'    => (int) ceil( $total / $per_page ),
			);
		}

		// ── Step 3: Extract IDs from this page ──────────────────────────
		$ids = array_column( $paginated['data'], 'id' );

		// ── Step 4: Fetch full campaigns + ads for only this page ────────
		$paginated['data'] = ! empty( $ids )
			? self::withAdsMany( array_map( 'intval', $ids ) )
			: array();

		return $paginated;
	}
}