<?php

namespace App\Http\Controllers\Api;

use App\Http\Controllers\Controller;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;

class ClientProjectionsController extends Controller
{

     /**
     * Get all orders with seeds info and order items
     */
    public function projectionseed()
    {
        $orders = DB::table('orders')
            ->join('inputsActivitiesCostsSeeds as seeds', 'orders.seed_id', '=', 'seeds.id')
            ->leftJoin('order_items', 'orders.id', '=', 'order_items.order_id')
            ->select(
                'orders.id as order_id',
                'orders.instructions',
                'orders.total as order_total',
                'orders.createdby',
                'orders.created_at as order_created_at',
                'seeds.seeds_name',
                'seeds.seeds_source',
                'seeds.description as seed_description',
                'order_items.id as order_item_id',
                'order_items.item_name',
                'order_items.unite',
                'order_items.quantity',
                'order_items.unit_price',
                'order_items.total_price'
            )
            ->orderBy('orders.id', 'asc')
            ->get()
            ->groupBy('order_id')
            ->map(function ($group) {
                $first = $group->first();
                return [
                    'order_id' => $first->order_id,
                    'instructions' => $first->instructions,
                    'order_total' => $first->order_total,
                    'createdby' => $first->createdby,
                    'created_at' => $first->order_created_at,
                    'seed' => [
                        'name' => $first->seeds_name,
                        'source' => $first->seeds_source,
                        'description' => $first->seed_description,
                    ],
                    'items' => $group->map(function ($item) {
                        return [
                            'id' => $item->order_item_id,
                            'name' => $item->item_name,
                            'unit' => $item->unite,
                            'quantity' => $item->quantity,
                            'unit_price' => $item->unit_price,
                            'total_price' => $item->total_price,
                        ];
                    })->values(),
                ];
            })
            ->values();

        return response()->json($orders);
    }

    /**
     * Get a single order by ID
     */
public function show($id)
{
    $rows = DB::table('orders')
        ->join('inputsActivitiesCostsSeeds as seeds', 'orders.seed_id', '=', 'seeds.id')
        ->leftJoin('order_items', 'orders.id', '=', 'order_items.order_id')
        ->leftJoin('scheduled_visitsReport as visits', 'orders.id', '!=', 'visits.scheduled_id')
        // Client joins from projections()
        ->leftJoin('clients as c', 'c.id', '=', 'visits.client_id')
        ->leftJoin('persons as p', 'p.client_id', '=', 'c.id')
        ->leftJoin('organizations as org', 'org.client_id', '=', 'c.id')
        ->leftJoin('accounts as a', 'a.client_id', '=', 'c.id')
        ->leftJoin('locations as l', function($join) {
            $join->on('l.id', '=', 'p.location_id')
                 ->orOn('l.id', '=', 'org.location_id');
        })
        ->select(
            'orders.id as order_id',
            'orders.instructions',
            'orders.total as order_total',
            'orders.createdby',
            'orders.created_at as order_created_at',
            'seeds.seeds_name',
            'seeds.seeds_source',
            'seeds.description as seed_description',
            'order_items.id as order_item_id',
            'order_items.item_name',
            'order_items.unite',
            'order_items.quantity',
            'order_items.unit_price',
            'order_items.total_price',
            'visits.id as visit_id',
            'visits.client_id',
            'visits.areaSize',
            'visits.perimeter',
            'visits.PropartieLocation',
            'visits.coordinates',
            'visits.created_at as visit_created_at',
            // Client details
            'c.type as client_type',
            'c.category as client_category',
            'c.created_at as client_created_at',
            'c.updated_at as client_updated_at',
            'p.first_name',
            'p.last_name',
            'p.birth_date',
            'p.birth_place',
            'p.residence',
            'p.marital_status',
            'p.national_id',
            'p.gender',
            'p.phone',
            'p.children_count',
            'org.company_name',
            'org.nif',
            'org.rc',
            'org.creation_date',
            'org.head_office',
            'org.legal_form',
            'org.member_count',
            'org.women_count',
            'org.men_count',
            'org.youth_count',
            'a.account_number',
            'a.branch',
            'l.colline',
            'l.commune',
            'l.province',
            'l.region'
        )
        ->where('orders.id', $id)
        ->get();

    if ($rows->isEmpty()) {
        return response()->json(['message' => 'Order not found'], 404);
    }

    $first = $rows->first();

    $order = [
        'order_id' => $first->order_id,
        'instructions' => $first->instructions,
        'order_total' => $first->order_total,
        'createdby' => $first->createdby,
        'created_at' => $first->order_created_at,
        'seed' => [
            'name' => $first->seeds_name,
            'source' => $first->seeds_source,
            'description' => $first->seed_description,
        ],
        'items' => $rows->filter(fn($r) => $r->order_item_id)
            ->map(fn($item) => [
                'id' => $item->order_item_id,
                'name' => $item->item_name,
                'unit' => $item->unite,
                'quantity' => $item->quantity,
                'unit_price' => $item->unit_price,
                'total_price' => $item->total_price,
            ])
            ->unique('id')
            ->values(),
        'visits' => $rows->filter(fn($r) => $r->visit_id)
            ->map(function ($v) {
                return [
                    'id' => $v->visit_id,
                    'client_id' => $v->client_id,
                    'area_size' => $v->areaSize,
                    'perimeter' => $v->perimeter,
                    'property_location' => $v->PropartieLocation,
                    'coordinates' => $v->coordinates ? json_decode($v->coordinates, true) : null,
                    'created_at' => $v->visit_created_at,
                    'client' => [
                        'type' => $v->client_type,
                        'category' => $v->client_category,
                        'created_at' => $v->client_created_at,
                        'updated_at' => $v->client_updated_at,
                        'person' => [
                            'first_name' => $v->first_name,
                            'last_name' => $v->last_name,
                            'birth_date' => $v->birth_date,
                            'birth_place' => $v->birth_place,
                            'residence' => $v->residence,
                            'marital_status' => $v->marital_status,
                            'national_id' => $v->national_id,
                            'gender' => $v->gender,
                            'phone' => $v->phone,
                            'children_count' => $v->children_count,
                        ],
                        'organization' => [
                            'company_name' => $v->company_name,
                            'nif' => $v->nif,
                            'rc' => $v->rc,
                            'creation_date' => $v->creation_date,
                            'head_office' => $v->head_office,
                            'legal_form' => $v->legal_form,
                            'member_count' => $v->member_count,
                            'women_count' => $v->women_count,
                            'men_count' => $v->men_count,
                            'youth_count' => $v->youth_count,
                        ],
                        'account' => [
                            'account_number' => $v->account_number,
                            'branch' => $v->branch,
                        ],
                        'location' => [
                            'colline' => $v->colline,
                            'commune' => $v->commune,
                            'province' => $v->province,
                            'region' => $v->region,
                        ]
                    ]
                ];
            })
            ->unique('id')
            ->values(),
    ];

    return response()->json($order);
}




    public function projections()
    {
        $data = DB::table('scheduled_visitsReport as svr')
            // Join orders for projection calculation
            ->leftJoin('orders as o', 'o.seed_id', '=', 'svr.scheduled_id')
            // Join clients
            ->leftJoin('clients as c', 'c.id', '=', 'svr.client_id')
            // Join persons (if client is a person)
            ->leftJoin('persons as p', 'p.client_id', '=', 'c.id')
            // Join organizations (if client is an organization)
            ->leftJoin('organizations as org', 'org.client_id', '=', 'c.id')
            // Join accounts
            ->leftJoin('accounts as a', 'a.client_id', '=', 'c.id')
            // Join location (from persons or organizations)
            ->leftJoin('locations as l', function($join){
                $join->on('l.id', '=', 'p.location_id')
                     ->orOn('l.id', '=', 'org.location_id');
            })
            ->select(
                'svr.*',
                DB::raw('IFNULL(SUM(o.total), 0) as totalOrders'),
                DB::raw('(svr.areaSize * IFNULL(SUM(o.total), 0)) as projectionTotal'),
                'c.type as client_type',
                'c.category as client_category',
                'c.created_at as client_created_at',
                'c.updated_at as client_updated_at',
                'p.first_name',
                'p.last_name',
                'p.birth_date',
                'p.birth_place',
                'p.residence',
                'p.marital_status',
                'p.national_id',
                'p.gender',
                'p.phone',
                'p.children_count',
                'org.company_name',
                'org.nif',
                'org.rc',
                'org.creation_date',
                'org.head_office',
                'org.legal_form',
                'org.member_count',
                'org.women_count',
                'org.men_count',
                'org.youth_count',
                'a.account_number',
                'a.branch',
                'l.colline',
                'l.commune',
                'l.province',
                'l.region'
            )
            ->groupBy(
                'svr.id',
                'svr.scheduled_id',
                'svr.client_id',
                'svr.areaSize',
                'svr.perimeter',
                'svr.PropartieLocation',
                'svr.coordinates',
                'svr.createdby',
                'svr.created_at',
                'svr.updated_at',
                'c.type',
                'c.category',
                'c.created_at',
                'c.updated_at',
                'p.first_name',
                'p.last_name',
                'p.birth_date',
                'p.birth_place',
                'p.residence',
                'p.marital_status',
                'p.national_id',
                'p.gender',
                'p.phone',
                'p.children_count',
                'org.company_name',
                'org.nif',
                'org.rc',
                'org.creation_date',
                'org.head_office',
                'org.legal_form',
                'org.member_count',
                'org.women_count',
                'org.men_count',
                'org.youth_count',
                'a.account_number',
                'a.branch',
                'l.colline',
                'l.commune',
                'l.province',
                'l.region'
            )
            ->get();

        return response()->json($data);
    }


    /**
     * Show one scheduled visit with full client data
     */
    public function show1($id)
    {
        $visit = DB::table('scheduled_visitsReport')->where('id', $id)->first();

        if (!$visit) {
            return response()->json(['error' => 'Scheduled visit not found'], 404);
        }

        $clientId = $visit->client_id;

        // Client record
        $client = DB::table('clients')->where('id', $clientId)->first();

        $identity = null;
        $location = null;

        if ($client) {
            if ($client->type === 'person') {
                $identity = DB::table('persons')->where('client_id', $clientId)->first();

                if ($identity && $identity->location_id) {
                    $location = DB::table('locations')->where('id', $identity->location_id)->first();
                }
            } else {
                $identity = DB::table('organizations')->where('client_id', $clientId)->first();

                if ($identity && $identity->location_id) {
                    $location = DB::table('locations')->where('id', $identity->location_id)->first();
                }

                if ($identity) {
                    $identity->representatives = DB::table('representatives')
                        ->where('organization_id', $identity->id)
                        ->get();
                }
            }
        }

        // Accounts
        $accounts = DB::table('accounts')->where('client_id', $clientId)->get();

        // Credit requests
        $creditRequests = DB::table('credit_requests')->where('client_id', $clientId)->get();

        // Orders
        $orders = DB::table('orders')->where('createdby', $clientId)->get();

        // Build response
        $visit->client = $client;
        $visit->identity = $identity;
        $visit->location = $location;
        $visit->accounts = $accounts;
        $visit->credit_requests = $creditRequests;
        $visit->orders = $orders;

        return response()->json($visit);
    }
}
