<?php
namespace AdFuz\Models;

use AdFuz\Database\DB;
use AdFuz\Helpers\TableName;

if ( ! defined( 'ABSPATH' ) ) {
	exit;
}

class AdSpaces extends AbstractModel {

	public const TABLE = 'adfuz_ad_spaces';

	/**
	 * Declarative schema for adfuz_ad_spaces
	 */
	public static function schema(): array {
		return array(
			'id'             => array(
				'type' => 'bigint',
			),
			'user_id'        => array(
				'type'     => 'bigint',
				'nullable' => true,
			),
			'fallback_ad_id' => array(
				'type'     => 'bigint',
				'nullable' => true,
			),
			'ad_format'      => array(
				'type'     => 'string',
				'length'   => 20,
				'nullable' => true,
			),
			'ad_sequence'    => array(
				'type'     => 'string',
				'length'   => 10,
				'nullable' => true,
			),
			'title'          => array(
				'type'     => 'string',
				'length'   => 100,
				'required' => true,
			),
			'slug'           => array(
				'type'   => 'string',
				'length' => 100,
			),
			'width'          => array(
				'type'     => 'int',
				'required' => true,
			),
			'height'         => array(
				'type'     => 'int',
				'required' => true,
			),
			'availability'   => array(
				'type'    => 'int',
				'default' => 1,
			),
			'status'         => array(
				'type'    => 'enum',
				'enum'    => array( 'active', 'inactive', 'draft' ),
				'default' => 'active',
			),
			'created_at'     => array(
				'type'     => 'datetime',
				'nullable' => true,
			),
			'updated_at'     => array(
				'type'     => 'datetime',
				'nullable' => true,
			),
		);
	}

	public static function table() {
		return static::TABLE;
	}

	// src/Models/Campaign.php

	public static function withAdsBySpaceIds( array $space_ids ): array {
		if ( empty( $space_ids ) ) {
			return array();
		}

		// Join campaigns through the pivot table
		$rows = DB::get( TableName::ADFUZ_CAMPAIGN_AD_SPACES() )
					->select(
						'cas.ad_space_id',
						'c.id',
						'c.name',
						'c.status',
						'c.start_date',
						'c.end_date',
						'c.pricing_model',
						'c.price',
						'c.currency',
						'cas.display_order',
						'cas.status as link_status'
					)
					->alias( 'cas' )
					->join( TableName::ADFUZ_CAMPAIGNS(), 'cas.campaign_id', 'c.id', 'c' )
					->whereIn( 'cas.ad_space_id', $space_ids )
					->orderBy( 'cas.display_order', 'ASC' )
					->get();

		// Group by ad_space_id
		$grouped = array();
		foreach ( $rows as $row ) {
			$grouped[ $row['ad_space_id'] ][] = $row;
		}

		return $grouped;
	}

	public static function withAdsBySpace( int $ad_space_id ): array {
		$grouped = self::withAdsBySpaceIds( array( $ad_space_id ) );

		return $grouped[ $ad_space_id ] ?? array();
	}

	/**
	 * Get paginated Ad Spaces with their Campaigns and Ads nested.
	 *
	 * @param  int $page
	 * @param  int $per_page
	 *
	 * @return array { items[], total, per_page, page, total_pages }
	 */
	public static function withCampaignAdsPaginated( int $page = 1, int $per_page = 10 ): array {

		// 1. Total count — no alias needed, no joins
		$total = DB::get( TableName::ADFUZ_AD_SPACES() )
					->where( 'status', '<>', 'deleted' )
					->count();

		// 2. Paginated ad spaces — remove ->alias('sp'), use plain column names
		$ad_spaces = DB::get( TableName::ADFUZ_AD_SPACES() )
						->select( 'id', 'title', 'slug', 'status', 'width', 'height', 'ad_format', 'created_at' )
						->where( 'status', '<>', 'deleted' )
						->orderBy( 'created_at', 'DESC' )
						->paginate( $per_page, $page );

		if ( empty( $ad_spaces['data'] ) ) {
			return array(
				'items'       => array(),
				'total'       => $total,
				'per_page'    => $per_page,
				'page'        => $page,
				'total_pages' => (int) ceil( $total / $per_page ),
			);
		}

		$space_ids = array_column( $ad_spaces['data'], 'id' );

		// 3. Delegate to Campaign model
		$campaigns_by_space = Campaigns::withCampaignAdsBySpaceIds( $space_ids );

		// 4. Attach campaigns to each ad space
		$items = array();
		foreach ( $ad_spaces['data'] as $space ) {
			$space['campaigns'] = $campaigns_by_space[ $space['id'] ] ?? array();
			$items[]            = $space;
		}

		$status_rows = DB::get( TableName::ADFUZ_AD_SPACES() )
						->select( 'status', 'COUNT(*) as count' )
						->where( 'status', '!=', 'deleted' )
						->groupBy( 'status' )
						->get();

		$stats = array(
			'total'    => $total ?? 0,
			'active'   => 0,
			'inactive' => 0,
			'draft'    => 0,
		);

		foreach ( $status_rows as $row ) {
			$key = strtolower( $row['status'] );
			if ( isset( $stats[ $key ] ) ) {
				$stats[ $key ] = (int) $row['count'];
			}
		}

		return array(
			'items'       => $items,
			'total'       => $total,
			'per_page'    => $per_page,
			'page'        => $page,
			'stats'       => $stats,
			'total_pages' => (int) ceil( $total / $per_page ),
		);
	}

	/**
	 * Fetch a single active Ad Space by its ID.
	 * Used by AdCacheController for shortcode resolution.
	 *
	 * Returns an array (matching QueryBuilder's ARRAY_A convention)
	 * or null if not found / deleted.
	 *
	 * @param  int $id
	 *
	 * @return array|null
	 */
	public static function findActiveById( int $id ): ?array {
		$row = DB::get( TableName::ADFUZ_AD_SPACES() )
				->where( 'status', '=', 'active' )
				->find( $id );

		return $row ?: null;
	}

	/**
	 * Check if an ad space has reached its availability limit.
	 * Logic: count ads assigned to active campaigns of this ad space.
	 *
	 * @param  int $ad_space_id
	 *
	 * @return array { availability: int, assigned_count: int, is_full: bool, not_found?: bool }
	 */
	public static function checkAvailability( int $ad_space_id ): array {

		// 1. Get the ad space and its availability limit
		$ad_space = DB::get( TableName::ADFUZ_AD_SPACES() )
						->select( 'id', 'availability' )
						->find( $ad_space_id );

		if ( ! $ad_space ) {
			return array(
				'availability'   => 0,
				'assigned_count' => 0,
				'is_full'        => true,
				'not_found'      => true,
			);
		}

		$availability = (int) $ad_space['availability'];

		// 2. Get active campaign IDs for this ad space
		$campaigns = DB::get( TableName::ADFUZ_CAMPAIGNS() )
						->select( 'id' )
						->where( 'ad_space_id', $ad_space_id )
						->where( 'status', 'active' )
						->get();

		if ( empty( $campaigns ) ) {
			return array(
				'availability'   => $availability,
				'assigned_count' => 0,
				'is_full'        => false,
			);
		}

		$campaign_ids = array_column( $campaigns, 'id' );

		// 3. Count ads in campaign_ads belonging to those active campaigns
		$assigned_count = DB::get( TableName::ADFUZ_CAMPAIGN_ADS() )
							->whereIn( 'campaign_id', $campaign_ids )
							->count();

		return array(
			'availability'   => $availability,
			'assigned_count' => $assigned_count,
			'is_full'        => $assigned_count > $availability,
		);
	}


	/**
	 * Get all active ad spaces by ad_format.
	 * Respects an optional max_spaces limit.
	 *
	 * @param  string   $format
	 * @param  int|null $maxSpaces
	 *
	 * @return array
	 */
	public static function getActiveByFormat( string $format, ?int $maxSpaces = null ): array {
		$query = DB::get( TableName::ADFUZ_AD_SPACES() )
					->select( 'id', 'title', 'status', 'width', 'height', 'ad_format', 'availability' )
					->where( 'ad_format', $format )
					->where( 'status', '!=', 'deleted' )
					->orderBy( 'title', 'ASC' );

		if ( $maxSpaces !== null ) {
			$query->limit( $maxSpaces );
		}

		return $query->get();
	}
}