<?php

namespace App\Http\Controllers\Data;

use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\File;
use Illuminate\Support\Facades\Response;
use Illuminate\Http\Request;
use App\Http\Controllers\Controller;
use Illuminate\Support\Facades\Storage;
ini_set('memory_limit', '512M');
class GoogleDataApi  extends Controller
{


    public function  index(Request $request){

        $page = $request->query('page', 1);
        $limit = $request->query('limit', 1000);
        $offset = ($page - 1) * $limit;

        $sqlQuery = "SELECT s.network_transaction_id as 'transaction_id'   ,
 s.click_id ,
 usl.user_id,

CASE
    WHEN ISNULL(TRIM(CONCAT(u.last_name ,' ', u.first_name)))   THEN u.email
    ELSE TRIM(CONCAT(u.last_name ,' ', u.first_name))
 END AS user_name  ,
 CASE
    WHEN  ( u.is_merchant=1 AND u.valid_merchant=1) THEN  'YES'
    ELSE 'NO'
 END AS is_merchant ,
 n.name as network,
 CASE
        WHEN JSON_VALID(stores.name) THEN JSON_UNQUOTE(JSON_EXTRACT(stores.name, '$.en'))
        ELSE stores.name
         END AS store ,
         usl.order_id,
         usl.order_amount,
         s.cashback_percent,
         usl.cashback ,
         s.local_order_amount,
         usl.local_cashback,
         usl.currency,
         usl.currency_rate,
         u.country_code,
         u.platform,
         usl.status ,
         c.utm,
       CONCAT( MONTH(usl.transaction_time), '/',  DAY(usl.transaction_time), '/', YEAR(usl.transaction_time) ) AS   transaction_time,
       CONCAT( MONTH(usl.created_at), '/', DAY(usl.created_at), '/', YEAR(usl.created_at) ) AS  created_at,
'1' as orders

FROM `yajny`.user_sales usl
INNER JOIN `yajny`.sales as s ON (s.id= usl.sales_id)
INNER JOIN `yajny`.clicks c  ON (c.id= usl.click_id)
LEFT JOIN `yajny`.users as u ON (u.user_id= usl.user_id)
LEFT JOIN `yajny`.networks as n ON (n.id= usl.network_id)
LEFT JOIN `yajny`.stores  ON (stores.id= usl.store_id)

where usl.created_at BETWEEN  '2023-06-01' AND DATE(now()) LIMIT $offset  ,$limit;";


        $results = DB::select(DB::raw($sqlQuery) );

        // Check if data is empty
        if (!$results ) {
            return response()->json(['message' => 'No data found'], http_response_code(401));
        }

        return response()->json(['data' => $results],http_response_code(200),[]);


    }

}