<?php
namespace factory;

class FacebookAds
{
  static private function clients_has_deleted_column()
  {
    global $mdb;

    static $has_deleted = null;
    if ( $has_deleted !== null )
    {
      return (bool) $has_deleted;
    }

    try
    {
      $stmt = $mdb -> pdo -> prepare( 'SHOW COLUMNS FROM clients LIKE :column_name' );
      if ( $stmt )
      {
        $stmt -> bindValue( ':column_name', 'deleted', \PDO::PARAM_STR );
        $stmt -> execute();
        $has_deleted = $stmt -> fetch( \PDO::FETCH_ASSOC ) ? 1 : 0;
      }
      else
      {
        $has_deleted = 0;
      }
    }
    catch ( \Throwable $e )
    {
      $has_deleted = 0;
    }

    return (bool) $has_deleted;
  }

  static private function sql_clients_not_deleted( $alias = '' )
  {
    $alias = trim( (string) $alias );
    $prefix = $alias !== '' ? $alias . '.' : '';

    if ( self::clients_has_deleted_column() )
    {
      return 'COALESCE(' . $prefix . 'deleted, 0) = 0';
    }

    return '1=1';
  }

  static public function get_clients_for_sync( $client_id = 0 )
  {
    global $mdb;

    $client_id = (int) $client_id;
    $clients_not_deleted_sql = self::sql_clients_not_deleted();

    $params = [];
    $where_client = '';
    if ( $client_id > 0 )
    {
      $where_client = ' AND id = :client_id';
      $params[':client_id'] = $client_id;
    }

    return $mdb -> query(
      'SELECT *
       FROM clients
       WHERE ' . $clients_not_deleted_sql . '
         AND COALESCE(active, 0) = 1
         AND TRIM(COALESCE(facebook_ads_account_id, \'\')) <> \'\'' . $where_client . '
       ORDER BY id ASC',
      $params
    ) -> fetchAll( \PDO::FETCH_ASSOC );
  }

  static public function sync_active_last_days_for_client( $client, $api, $days = 30, $active_only = true )
  {
    $client = is_array( $client ) ? $client : [];
    $client_id = (int) ( $client['id'] ?? 0 );
    $account_id = \services\FacebookAdsApi::normalize_ad_account_id( $client['facebook_ads_account_id'] ?? '' );

    if ( $client_id <= 0 || !$account_id )
    {
      return [
        'success' => false,
        'client_id' => $client_id,
        'errors' => [ 'Brak poprawnego client_id lub facebook_ads_account_id.' ]
      ];
    }

    $days = max( 1, min( 90, (int) $days ) );
    $payload = $api -> fetch_active_insights_last_days( $account_id, $days, $active_only );
    if ( $payload === false )
    {
      $last_error = trim( (string) \services\FacebookAdsApi::get_setting( 'facebook_ads_last_error' ) );

      return [
        'success' => false,
        'client_id' => $client_id,
        'account_id' => $account_id,
        'errors' => [ $last_error !== '' ? $last_error : 'Nie udalo sie pobrac danych z Meta API.' ]
      ];
    }

    $save_result = self::save_snapshot( $client_id, $payload );
    $save_result['client_id'] = $client_id;
    $save_result['account_id'] = $account_id;
    $save_result['days'] = $days;
    $save_result['meta'] = $payload['meta'] ?? [];

    return $save_result;
  }

  static public function sync_active_date_for_client( $client, $api, $sync_date, $active_only = true )
  {
    $client = is_array( $client ) ? $client : [];
    $client_id = (int) ( $client['id'] ?? 0 );
    $account_id = \services\FacebookAdsApi::normalize_ad_account_id( $client['facebook_ads_account_id'] ?? '' );

    if ( $client_id <= 0 || !$account_id )
    {
      return [
        'success' => false,
        'client_id' => $client_id,
        'errors' => [ 'Brak poprawnego client_id lub facebook_ads_account_id.' ]
      ];
    }

    $sync_timestamp = strtotime( (string) $sync_date );
    if ( !$sync_timestamp )
    {
      return [
        'success' => false,
        'client_id' => $client_id,
        'account_id' => $account_id,
        'errors' => [ 'Niepoprawna data synchronizacji Facebook Ads.' ]
      ];
    }

    $sync_date = date( 'Y-m-d', $sync_timestamp );
    $payload = $api -> fetch_active_insights_for_date( $account_id, $sync_date, $active_only );
    if ( $payload === false )
    {
      $last_error = trim( (string) \services\FacebookAdsApi::get_setting( 'facebook_ads_last_error' ) );

      return [
        'success' => false,
        'client_id' => $client_id,
        'account_id' => $account_id,
        'sync_date' => $sync_date,
        'errors' => [ $last_error !== '' ? $last_error : 'Nie udalo sie pobrac danych z Meta API.' ]
      ];
    }

    $save_result = self::save_snapshot( $client_id, $payload );
    $save_result['client_id'] = $client_id;
    $save_result['account_id'] = $account_id;
    $save_result['days'] = 1;
    $save_result['sync_date'] = $sync_date;
    $save_result['meta'] = $payload['meta'] ?? [];

    return $save_result;
  }

  static public function sync_date_range_for_client( $client, $api, $since, $until, $active_only = true )
  {
    $client = is_array( $client ) ? $client : [];
    $client_id = (int) ( $client['id'] ?? 0 );
    $account_id = \services\FacebookAdsApi::normalize_ad_account_id( $client['facebook_ads_account_id'] ?? '' );

    if ( $client_id <= 0 || !$account_id )
    {
      return [
        'success' => false,
        'client_id' => $client_id,
        'errors' => [ 'Brak poprawnego client_id lub facebook_ads_account_id.' ]
      ];
    }

    $since_ts = strtotime( (string) $since );
    $until_ts = strtotime( (string) $until );
    if ( !$since_ts || !$until_ts || $since_ts > $until_ts )
    {
      return [
        'success' => false,
        'client_id' => $client_id,
        'account_id' => $account_id,
        'errors' => [ 'Niepoprawny zakres dat synchronizacji Facebook Ads.' ]
      ];
    }

    $since = date( 'Y-m-d', $since_ts );
    $until = date( 'Y-m-d', $until_ts );
    $days = (int) floor( ( $until_ts - $since_ts ) / 86400 ) + 1;

    $payload = $api -> fetch_active_insights_for_range( $account_id, $since, $until, $active_only );
    if ( $payload === false )
    {
      $last_error = trim( (string) \services\FacebookAdsApi::get_setting( 'facebook_ads_last_error' ) );

      return [
        'success' => false,
        'client_id' => $client_id,
        'account_id' => $account_id,
        'since' => $since,
        'until' => $until,
        'errors' => [ $last_error !== '' ? $last_error : 'Nie udalo sie pobrac danych z Meta API.' ]
      ];
    }

    foreach ( [ 'campaign', 'adset', 'ad' ] as $level )
    {
      if ( is_array( $payload[ $level ] ?? null ) )
      {
        foreach ( $payload[ $level ] as &$row )
        {
          $row['date_start'] = $until;
        }
        unset( $row );
      }
    }

    $all_time_raw = $api -> fetch_campaigns_all_time( $account_id, $active_only );
    $all_time_error = '';
    if ( $all_time_raw === false )
    {
      $all_time_error = (string) \services\FacebookAdsApi::get_setting( 'facebook_ads_last_error' );
    }
    $all_time_map = is_array( $all_time_raw ) ? $all_time_raw : [];

    $save_result = self::save_snapshot( $client_id, $payload, $all_time_map );
    $save_result['client_id'] = $client_id;
    $save_result['account_id'] = $account_id;
    $save_result['days'] = $days;
    $save_result['since'] = $since;
    $save_result['until'] = $until;
    $save_result['meta'] = $payload['meta'] ?? [];
    $save_result['all_time_debug'] = [
      'raw_type' => is_array( $all_time_raw ) ? 'array' : ( $all_time_raw === false ? 'false' : gettype( $all_time_raw ) ),
      'campaigns_count' => count( $all_time_map ),
      'error' => $all_time_error,
      'data' => array_slice( $all_time_map, 0, 5, true )
    ];

    return $save_result;
  }

  static private function save_snapshot( $client_id, $payload, $all_time_map = [] )
  {
    global $mdb;

    $stats = [
      'success' => false,
      'campaigns_synced' => 0,
      'campaign_history_synced' => 0,
      'ad_sets_synced' => 0,
      'ad_set_history_synced' => 0,
      'ads_synced' => 0,
      'ad_history_synced' => 0,
      'errors' => []
    ];

    $campaign_map = [];
    $ad_set_map = [];
    $ad_map = [];

    $campaign_rows = is_array( $payload['campaign'] ?? null ) ? $payload['campaign'] : [];
    $ad_set_rows = is_array( $payload['adset'] ?? null ) ? $payload['adset'] : [];
    $ad_rows = is_array( $payload['ad'] ?? null ) ? $payload['ad'] : [];

    try
    {
      $started_transaction = false;
      if ( method_exists( $mdb -> pdo, 'beginTransaction' ) && !( $mdb -> pdo -> inTransaction() ) )
      {
        $mdb -> pdo -> beginTransaction();
        $started_transaction = true;
      }

      foreach ( $campaign_rows as $row )
      {
        $campaign_db_id = self::upsert_campaign( $client_id, $row );
        if ( $campaign_db_id <= 0 )
        {
          continue;
        }

        $campaign_external_id = self::normalize_external_id( $row['campaign_id'] ?? '' );
        if ( $campaign_external_id !== null )
        {
          $campaign_map[ $campaign_external_id ] = $campaign_db_id;
        }

        $roas_at = (float) ( $all_time_map[ $campaign_external_id ]['roas_all_time'] ?? 0 );
        if ( self::upsert_campaign_history( $campaign_db_id, $row, $roas_at ) )
        {
          $stats['campaign_history_synced']++;
        }
      }

      foreach ( $ad_set_rows as $row )
      {
        $campaign_db_id = self::resolve_campaign_db_id( $client_id, $row, $campaign_map );
        $ad_set_db_id = self::upsert_ad_set( $client_id, $campaign_db_id, $row );
        if ( $ad_set_db_id <= 0 )
        {
          continue;
        }

        $ad_set_external_id = self::normalize_external_id( $row['adset_id'] ?? '' );
        if ( $ad_set_external_id !== null )
        {
          $ad_set_map[ $ad_set_external_id ] = $ad_set_db_id;
        }

        if ( self::upsert_ad_set_history( $ad_set_db_id, $row ) )
        {
          $stats['ad_set_history_synced']++;
        }
      }

      foreach ( $ad_rows as $row )
      {
        $campaign_db_id = self::resolve_campaign_db_id( $client_id, $row, $campaign_map );
        $ad_set_db_id = self::resolve_ad_set_db_id( $client_id, $campaign_db_id, $row, $ad_set_map );
        $ad_db_id = self::upsert_ad( $client_id, $campaign_db_id, $ad_set_db_id, $row );
        if ( $ad_db_id <= 0 )
        {
          continue;
        }

        $ad_external_id = self::normalize_external_id( $row['ad_id'] ?? '' );
        if ( $ad_external_id !== null )
        {
          $ad_map[ $ad_external_id ] = $ad_db_id;
        }

        if ( self::upsert_ad_history( $ad_db_id, $row ) )
        {
          $stats['ad_history_synced']++;
        }
      }

      $stats['campaigns_synced'] = count( $campaign_map );
      $stats['ad_sets_synced'] = count( $ad_set_map );
      $stats['ads_synced'] = count( $ad_map );

      if ( !empty( $started_transaction ) && method_exists( $mdb -> pdo, 'commit' ) && $mdb -> pdo -> inTransaction() )
      {
        $mdb -> pdo -> commit();
      }

      $stats['success'] = true;
    }
    catch ( \Throwable $e )
    {
      if ( !empty( $started_transaction ) && method_exists( $mdb -> pdo, 'rollBack' ) && $mdb -> pdo -> inTransaction() )
      {
        $mdb -> pdo -> rollBack();
      }

      $stats['errors'][] = $e -> getMessage();
      $stats['success'] = false;
    }

    return $stats;
  }

  static private function upsert_campaign( $client_id, $row )
  {
    global $mdb;

    $campaign_external_id = self::normalize_external_id( $row['campaign_id'] ?? '' );
    if ( $campaign_external_id === null )
    {
      return 0;
    }

    $account_id = self::normalize_external_id( $row['account_id'] ?? '' ) ?: '0';
    $campaign_name = trim( (string) ( $row['campaign_name'] ?? '' ) );
    $date_sync = date( 'Y-m-d H:i:s' );

    $mdb -> query(
      'INSERT INTO facebook_campaigns
         (client_id, account_id, campaign_id, campaign_name, status, effective_status, date_sync)
       VALUES
         (:client_id, :account_id, :campaign_id, :campaign_name, NULL, \'ACTIVE\', :date_sync)
       ON DUPLICATE KEY UPDATE
         id = LAST_INSERT_ID(id),
         account_id = VALUES(account_id),
         campaign_name = VALUES(campaign_name),
         effective_status = VALUES(effective_status),
         date_sync = VALUES(date_sync)',
      [
        ':client_id' => (int) $client_id,
        ':account_id' => $account_id,
        ':campaign_id' => $campaign_external_id,
        ':campaign_name' => $campaign_name,
        ':date_sync' => $date_sync
      ]
    );

    $db_id = (int) $mdb -> id();
    if ( $db_id > 0 )
    {
      return $db_id;
    }

    $existing = $mdb -> query(
      'SELECT id
       FROM facebook_campaigns
       WHERE client_id = :client_id
         AND campaign_id = :campaign_id
       LIMIT 1',
      [ ':client_id' => (int) $client_id, ':campaign_id' => $campaign_external_id ]
    ) -> fetchColumn();

    return (int) $existing;
  }

  static private function upsert_campaign_history( $campaign_db_id, $row, $roas_all_time = 0.0 )
  {
    global $mdb;

    $date_add = self::normalize_date( $row['date_start'] ?? null );
    if ( !$date_add )
    {
      return false;
    }

    $spend = self::to_decimal( $row['spend'] ?? 0 );
    $conversion_value = self::extract_conversion_value( $row );
    $roas = self::extract_roas( $row, $conversion_value, $spend );

    $mdb -> query(
      'INSERT INTO facebook_campaigns_history
         (facebook_campaign_id, spend, impressions, clicks, ctr, cpc, conversion_value, roas, roas_all_time, date_add)
       VALUES
         (:campaign_id, :spend, :impressions, :clicks, :ctr, :cpc, :conversion_value, :roas, :roas_all_time, :date_add)
       ON DUPLICATE KEY UPDATE
         spend = VALUES(spend),
         impressions = VALUES(impressions),
         clicks = VALUES(clicks),
         ctr = VALUES(ctr),
         cpc = VALUES(cpc),
         conversion_value = VALUES(conversion_value),
         roas = VALUES(roas),
         roas_all_time = VALUES(roas_all_time)',
      [
        ':campaign_id' => (int) $campaign_db_id,
        ':spend' => $spend,
        ':impressions' => self::to_int( $row['impressions'] ?? 0 ),
        ':clicks' => self::to_int( $row['clicks'] ?? 0 ),
        ':ctr' => self::to_decimal( $row['ctr'] ?? 0 ),
        ':cpc' => self::to_decimal( $row['cpc'] ?? 0 ),
        ':conversion_value' => $conversion_value,
        ':roas' => $roas,
        ':roas_all_time' => (float) $roas_all_time,
        ':date_add' => $date_add
      ]
    );

    return true;
  }

  static private function upsert_ad_set( $client_id, $campaign_db_id, $row )
  {
    global $mdb;

    $ad_set_external_id = self::normalize_external_id( $row['adset_id'] ?? '' );
    if ( $ad_set_external_id === null )
    {
      return 0;
    }

    $campaign_external_id = self::normalize_external_id( $row['campaign_id'] ?? '' ) ?: '0';
    $ad_set_name = trim( (string) ( $row['adset_name'] ?? '' ) );
    $date_sync = date( 'Y-m-d H:i:s' );

    $mdb -> query(
      'INSERT INTO facebook_ad_sets
         (client_id, facebook_campaign_id, campaign_id, ad_set_id, ad_set_name, status, effective_status, date_sync)
       VALUES
         (:client_id, :facebook_campaign_id, :campaign_id, :ad_set_id, :ad_set_name, NULL, \'ACTIVE\', :date_sync)
       ON DUPLICATE KEY UPDATE
         id = LAST_INSERT_ID(id),
         facebook_campaign_id = VALUES(facebook_campaign_id),
         campaign_id = VALUES(campaign_id),
         ad_set_name = VALUES(ad_set_name),
         effective_status = VALUES(effective_status),
         date_sync = VALUES(date_sync)',
      [
        ':client_id' => (int) $client_id,
        ':facebook_campaign_id' => $campaign_db_id > 0 ? (int) $campaign_db_id : null,
        ':campaign_id' => $campaign_external_id,
        ':ad_set_id' => $ad_set_external_id,
        ':ad_set_name' => $ad_set_name,
        ':date_sync' => $date_sync
      ]
    );

    $db_id = (int) $mdb -> id();
    if ( $db_id > 0 )
    {
      return $db_id;
    }

    $existing = $mdb -> query(
      'SELECT id
       FROM facebook_ad_sets
       WHERE client_id = :client_id
         AND ad_set_id = :ad_set_id
       LIMIT 1',
      [ ':client_id' => (int) $client_id, ':ad_set_id' => $ad_set_external_id ]
    ) -> fetchColumn();

    return (int) $existing;
  }

  static private function upsert_ad_set_history( $ad_set_db_id, $row )
  {
    global $mdb;

    $date_add = self::normalize_date( $row['date_start'] ?? null );
    if ( !$date_add )
    {
      return false;
    }

    $spend = self::to_decimal( $row['spend'] ?? 0 );
    $conversion_value = self::extract_conversion_value( $row );
    $roas = self::extract_roas( $row, $conversion_value, $spend );

    $mdb -> query(
      'INSERT INTO facebook_ad_sets_history
         (facebook_ad_set_id, spend, impressions, clicks, ctr, cpc, conversion_value, roas, date_add)
       VALUES
         (:ad_set_id, :spend, :impressions, :clicks, :ctr, :cpc, :conversion_value, :roas, :date_add)
       ON DUPLICATE KEY UPDATE
         spend = VALUES(spend),
         impressions = VALUES(impressions),
         clicks = VALUES(clicks),
         ctr = VALUES(ctr),
         cpc = VALUES(cpc),
         conversion_value = VALUES(conversion_value),
         roas = VALUES(roas)',
      [
        ':ad_set_id' => (int) $ad_set_db_id,
        ':spend' => $spend,
        ':impressions' => self::to_int( $row['impressions'] ?? 0 ),
        ':clicks' => self::to_int( $row['clicks'] ?? 0 ),
        ':ctr' => self::to_decimal( $row['ctr'] ?? 0 ),
        ':cpc' => self::to_decimal( $row['cpc'] ?? 0 ),
        ':conversion_value' => $conversion_value,
        ':roas' => $roas,
        ':date_add' => $date_add
      ]
    );

    return true;
  }

  static private function upsert_ad( $client_id, $campaign_db_id, $ad_set_db_id, $row )
  {
    global $mdb;

    $ad_external_id = self::normalize_external_id( $row['ad_id'] ?? '' );
    if ( $ad_external_id === null )
    {
      return 0;
    }

    $campaign_external_id = self::normalize_external_id( $row['campaign_id'] ?? '' ) ?: '0';
    $ad_set_external_id = self::normalize_external_id( $row['adset_id'] ?? '' ) ?: '0';
    $ad_name = trim( (string) ( $row['ad_name'] ?? '' ) );
    $date_sync = date( 'Y-m-d H:i:s' );

    $mdb -> query(
      'INSERT INTO facebook_ads
         (client_id, facebook_campaign_id, facebook_ad_set_id, campaign_id, ad_set_id, ad_id, ad_name, status, effective_status, date_sync)
       VALUES
         (:client_id, :facebook_campaign_id, :facebook_ad_set_id, :campaign_id, :ad_set_id, :ad_id, :ad_name, NULL, \'ACTIVE\', :date_sync)
       ON DUPLICATE KEY UPDATE
         id = LAST_INSERT_ID(id),
         facebook_campaign_id = VALUES(facebook_campaign_id),
         facebook_ad_set_id = VALUES(facebook_ad_set_id),
         campaign_id = VALUES(campaign_id),
         ad_set_id = VALUES(ad_set_id),
         ad_name = VALUES(ad_name),
         effective_status = VALUES(effective_status),
         date_sync = VALUES(date_sync)',
      [
        ':client_id' => (int) $client_id,
        ':facebook_campaign_id' => $campaign_db_id > 0 ? (int) $campaign_db_id : null,
        ':facebook_ad_set_id' => $ad_set_db_id > 0 ? (int) $ad_set_db_id : null,
        ':campaign_id' => $campaign_external_id,
        ':ad_set_id' => $ad_set_external_id,
        ':ad_id' => $ad_external_id,
        ':ad_name' => $ad_name,
        ':date_sync' => $date_sync
      ]
    );

    $db_id = (int) $mdb -> id();
    if ( $db_id > 0 )
    {
      return $db_id;
    }

    $existing = $mdb -> query(
      'SELECT id
       FROM facebook_ads
       WHERE client_id = :client_id
         AND ad_id = :ad_id
       LIMIT 1',
      [ ':client_id' => (int) $client_id, ':ad_id' => $ad_external_id ]
    ) -> fetchColumn();

    return (int) $existing;
  }

  static private function upsert_ad_history( $ad_db_id, $row )
  {
    global $mdb;

    $date_add = self::normalize_date( $row['date_start'] ?? null );
    if ( !$date_add )
    {
      return false;
    }

    $spend = self::to_decimal( $row['spend'] ?? 0 );
    $conversion_value = self::extract_conversion_value( $row );
    $roas = self::extract_roas( $row, $conversion_value, $spend );

    $mdb -> query(
      'INSERT INTO facebook_ads_history
         (facebook_ad_id, spend, impressions, clicks, ctr, cpc, conversion_value, roas, date_add)
       VALUES
         (:ad_id, :spend, :impressions, :clicks, :ctr, :cpc, :conversion_value, :roas, :date_add)
       ON DUPLICATE KEY UPDATE
         spend = VALUES(spend),
         impressions = VALUES(impressions),
         clicks = VALUES(clicks),
         ctr = VALUES(ctr),
         cpc = VALUES(cpc),
         conversion_value = VALUES(conversion_value),
         roas = VALUES(roas)',
      [
        ':ad_id' => (int) $ad_db_id,
        ':spend' => $spend,
        ':impressions' => self::to_int( $row['impressions'] ?? 0 ),
        ':clicks' => self::to_int( $row['clicks'] ?? 0 ),
        ':ctr' => self::to_decimal( $row['ctr'] ?? 0 ),
        ':cpc' => self::to_decimal( $row['cpc'] ?? 0 ),
        ':conversion_value' => $conversion_value,
        ':roas' => $roas,
        ':date_add' => $date_add
      ]
    );

    return true;
  }

  static private function resolve_campaign_db_id( $client_id, $row, &$campaign_map )
  {
    $campaign_external_id = self::normalize_external_id( $row['campaign_id'] ?? '' );
    if ( $campaign_external_id === null )
    {
      return 0;
    }

    if ( isset( $campaign_map[ $campaign_external_id ] ) )
    {
      return (int) $campaign_map[ $campaign_external_id ];
    }

    $campaign_db_id = self::upsert_campaign( $client_id, $row );
    if ( $campaign_db_id > 0 )
    {
      $campaign_map[ $campaign_external_id ] = $campaign_db_id;
    }

    return $campaign_db_id;
  }

  static private function resolve_ad_set_db_id( $client_id, $campaign_db_id, $row, &$ad_set_map )
  {
    $ad_set_external_id = self::normalize_external_id( $row['adset_id'] ?? '' );
    if ( $ad_set_external_id === null )
    {
      return 0;
    }

    if ( isset( $ad_set_map[ $ad_set_external_id ] ) )
    {
      return (int) $ad_set_map[ $ad_set_external_id ];
    }

    $ad_set_db_id = self::upsert_ad_set( $client_id, $campaign_db_id, $row );
    if ( $ad_set_db_id > 0 )
    {
      $ad_set_map[ $ad_set_external_id ] = $ad_set_db_id;
    }

    return $ad_set_db_id;
  }

  static private function normalize_external_id( $value )
  {
    $digits = preg_replace( '/\D+/', '', (string) $value );
    if ( $digits === '' )
    {
      return null;
    }

    return $digits;
  }

  static private function normalize_date( $value )
  {
    $value = trim( (string) $value );
    if ( $value === '' )
    {
      return null;
    }

    $timestamp = strtotime( $value );
    if ( !$timestamp )
    {
      return null;
    }

    return date( 'Y-m-d', $timestamp );
  }

  static private function to_decimal( $value )
  {
    if ( is_string( $value ) )
    {
      $value = str_replace( ',', '.', $value );
    }

    return (float) $value;
  }

  static private function to_int( $value )
  {
    return (int) round( (float) $value );
  }

  static private function get_purchase_action_types_priority()
  {
    return [
      'purchase',
      'omni_purchase',
      'offsite_conversion.fb_pixel_purchase',
      'web_in_store_purchase',
      'onsite_conversion.purchase',
      'app_custom_event.fb_mobile_purchase'
    ];
  }

  static private function get_metric_map( $raw )
  {
    $map = [];
    if ( !is_array( $raw ) )
    {
      return $map;
    }

    foreach ( $raw as $row )
    {
      if ( !is_array( $row ) )
      {
        continue;
      }

      $action_type = trim( (string) ( $row['action_type'] ?? '' ) );
      if ( $action_type === '' )
      {
        continue;
      }

      $map[ $action_type ] = self::to_decimal( $row['value'] ?? 0 );
    }

    return $map;
  }

  static private function extract_conversion_value( $row )
  {
    if ( !is_array( $row ) )
    {
      return 0.0;
    }

    if ( isset( $row['conversion_value'] ) )
    {
      return self::to_decimal( $row['conversion_value'] );
    }

    $action_values_map = self::get_metric_map( $row['action_values'] ?? [] );
    if ( empty( $action_values_map ) )
    {
      return 0.0;
    }

    foreach ( self::get_purchase_action_types_priority() as $action_type )
    {
      if ( array_key_exists( $action_type, $action_values_map ) )
      {
        return self::to_decimal( $action_values_map[ $action_type ] );
      }
    }

    return 0.0;
  }

  static private function extract_roas( $row, $conversion_value, $spend )
  {
    if ( !is_array( $row ) )
    {
      return 0.0;
    }

    $purchase_roas_map = self::get_metric_map( $row['purchase_roas'] ?? [] );
    foreach ( self::get_purchase_action_types_priority() as $action_type )
    {
      if ( array_key_exists( $action_type, $purchase_roas_map ) )
      {
        return self::to_decimal( $purchase_roas_map[ $action_type ] ) * 100;
      }
    }

    $conversion_value = self::to_decimal( $conversion_value );
    $spend = self::to_decimal( $spend );
    if ( $conversion_value > 0 && $spend > 0 )
    {
      return round( ( $conversion_value / $spend ) * 100, 6 );
    }

    return 0.0;
  }

  static private function get_level_config( $level )
  {
    $level = strtolower( trim( (string) $level ) );

    $map = [
      'campaign' => [
        'base_table' => 'facebook_campaigns',
        'history_table' => 'facebook_campaigns_history',
        'fk_column' => 'facebook_campaign_id',
        'name_column' => 'campaign_name',
        'external_id_column' => 'campaign_id'
      ],
      'adset' => [
        'base_table' => 'facebook_ad_sets',
        'history_table' => 'facebook_ad_sets_history',
        'fk_column' => 'facebook_ad_set_id',
        'name_column' => 'ad_set_name',
        'external_id_column' => 'ad_set_id'
      ],
      'ad' => [
        'base_table' => 'facebook_ads',
        'history_table' => 'facebook_ads_history',
        'fk_column' => 'facebook_ad_id',
        'name_column' => 'ad_name',
        'external_id_column' => 'ad_id'
      ]
    ];

    return $map[ $level ] ?? null;
  }

  static public function get_clients_for_reports()
  {
    return self::get_clients_for_sync( 0 );
  }

  static public function get_entities_with_latest_metrics( $client_id, $level, $filters = [] )
  {
    global $mdb;

    $cfg = self::get_level_config( $level );
    if ( !$cfg )
    {
      return [];
    }

    $client_id = (int) $client_id;
    if ( $client_id <= 0 )
    {
      return [];
    }

    $filters = is_array( $filters ) ? $filters : [];
    $campaign_id = (int) ( $filters['campaign_id'] ?? 0 );
    $ad_set_id = (int) ( $filters['ad_set_id'] ?? 0 );

    $extra_where = '';
    $params = [ ':client_id' => $client_id ];

    if ( $level === 'adset' && $campaign_id > 0 )
    {
      $extra_where .= ' AND b.facebook_campaign_id = :campaign_id';
      $params[':campaign_id'] = $campaign_id;
    }

    if ( $level === 'ad' )
    {
      if ( $campaign_id > 0 )
      {
        $extra_where .= ' AND b.facebook_campaign_id = :campaign_id';
        $params[':campaign_id'] = $campaign_id;
      }

      if ( $ad_set_id > 0 )
      {
        $extra_where .= ' AND b.facebook_ad_set_id = :ad_set_id';
        $params[':ad_set_id'] = $ad_set_id;
      }
    }

    $sql = 'SELECT
              b.id,
              b.' . $cfg['external_id_column'] . ' AS external_id,
              b.' . $cfg['name_column'] . ' AS entity_name,
              h.date_add,
              h.spend,
              h.impressions,
              h.clicks,
              h.ctr,
              h.cpc,
              h.conversion_value,
              h.roas
            FROM ' . $cfg['base_table'] . ' b
            LEFT JOIN ' . $cfg['history_table'] . ' h
              ON h.' . $cfg['fk_column'] . ' = b.id
             AND h.date_add = (
               SELECT MAX(h2.date_add)
               FROM ' . $cfg['history_table'] . ' h2
               WHERE h2.' . $cfg['fk_column'] . ' = b.id
             )
            WHERE b.client_id = :client_id
                ' . $extra_where . '
            ORDER BY b.' . $cfg['name_column'] . ' ASC';

              return $mdb -> query( $sql, $params ) -> fetchAll( \PDO::FETCH_ASSOC );
  }

  static public function get_entity_history( $level, $entity_id, $start, $length, $revert = false )
  {
    global $mdb;

    $cfg = self::get_level_config( $level );
    if ( !$cfg )
    {
      return [];
    }

    $entity_id = (int) $entity_id;
    if ( $entity_id <= 0 )
    {
      return [];
    }

    $start = max( 0, (int) $start );
    $length = max( 1, min( 2000, (int) $length ) );
    $order = $revert ? 'ASC' : 'DESC';

    $columns = 'date_add, spend, impressions, clicks, ctr, cpc, conversion_value, roas';
    if ( $level === 'campaign' )
    {
      $columns .= ', roas_all_time';
    }

    $sql = 'SELECT ' . $columns . '
            FROM ' . $cfg['history_table'] . '
            WHERE ' . $cfg['fk_column'] . ' = :entity_id
            ORDER BY date_add ' . $order . '
            LIMIT ' . $start . ', ' . $length;

    return $mdb -> query( $sql, [ ':entity_id' => $entity_id ] ) -> fetchAll( \PDO::FETCH_ASSOC );
  }

  static public function get_entity_history_total( $level, $entity_id )
  {
    global $mdb;

    $cfg = self::get_level_config( $level );
    if ( !$cfg )
    {
      return 0;
    }

    $entity_id = (int) $entity_id;
    if ( $entity_id <= 0 )
    {
      return 0;
    }

    $count = $mdb -> query(
      'SELECT COUNT(*)
       FROM ' . $cfg['history_table'] . '
       WHERE ' . $cfg['fk_column'] . ' = :entity_id',
      [ ':entity_id' => $entity_id ]
    ) -> fetchColumn();

    return (int) $count;
  }
}