<?php

namespace App\Http\Controllers;

use App\Models\Formula;
use App\Models\Item;
use App\Models\FormulaDetail;
use App\Models\DeleteLog;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\Log;
use Illuminate\Support\Facades\Auth;
use Illuminate\Database\QueryException;
use Illuminate\Support\Facades\Schema;
use DB;

class FormulaController extends Controller
{
    public function index()
    {
        $formulas = DB::table("formulas as x")
        ->join("items as i","x.item_id","=","i.id")
        ->join("item_units as iu","iu.id","=","x.unit_id")
        ->select("x.id","x.formula_name","i.item_code","i.item_name","iu.unit_name")->get();
        return response()->json(["formulas" => $formulas,"role" => $this->getRole()]);
    }

    public function store(Request $request)
    {
        try{
            DB::beginTransaction();
            $formula = new Formula();
            foreach($request->all() as $key=>$val){
                if(Schema::hasColumn($formula->getTable(), $key)){
                    $formula->{$key} = $val;
                }
            }
            $formula->is_active = 1;
            $formula->created_by = Auth::user()->id;
            $formula->updated_by = Auth::user()->id;
            $formula->save();
            
            foreach($request->details as $val){
                $formulaDetail = new FormulaDetail();
                foreach($val as $key2=>$val2){
                    if(Schema::hasColumn($formulaDetail->getTable(), $key2)){
                        $formulaDetail->{$key2} = $val2;
                    }
                }
                $formulaDetail->formula_id = $formula->id;
                $formulaDetail->created_by = Auth::user()->id;
                $formulaDetail->updated_by = Auth::user()->id;
                $formulaDetail->save();    
            }

            DB::commit();
        }catch(\Exception $e){
            DB::rollback();
            return response()->json(['error' => 'Failed to create Formula'], 500);
        }
        return response()->json($formula, 201);
    }

    public function show($id)
    {
        $formula = Formula::with('formulaDetails')->findOrFail($id);
        return response()->json($formula);
    }

    public function update(Request $request, $id)
    {
        try{
            DB::beginTransaction();
            $formula = Formula::findOrFail($id);
            foreach($request->all() as $key=>$val){
                if(Schema::hasColumn($formula->getTable(), $key)){
                    $formula->{$key} = $val;
                }
            }
            $formula->updated_by = Auth::user()->id;
            $formula->save();

            FormulaDetail::where("formula_id",$formula->id)->delete();
            foreach($request->details as $val){
                $formulaDetail = new FormulaDetail();
                foreach($val as $key2=>$val2){
                    if(Schema::hasColumn($formulaDetail->getTable(), $key2)){
                        $formulaDetail->{$key2} = $val2;
                    }
                }
                $formulaDetail->formula_id = $formula->id;
                $formulaDetail->created_by = Auth::user()->id;
                $formulaDetail->updated_by = Auth::user()->id;
                $formulaDetail->save();    
            }

            DB::commit();
        }catch(\Exception $e){
            DB::rollback();
            return response()->json(['error' => 'Failed to create Formula'], 500);
        }
        return response()->json($formula);
    }

    public function destroy(Request $request, $id)
    {
        try {
            DB::beginTransaction();
            $formula = Formula::findOrFail($id);
            
            FormulaDetail::where("formula_id",$formula->id)->delete();
            $formula->delete();

            DeleteLog::create([
                'document_number' => $id,
                'document_date' => now(),
                'delete_notes' => $request->delete_notes ?? 'No notes provided',
                'company_code' => null,
                'department_code' => null,
                'deleted_by' => Auth::check() ? Auth::user()->name : 'system',
                'type' => 'Master Formula',
            ]);
            DB::commit();
            return response()->json(null, 204);
        } catch (QueryException $e) {
            DB::rollback();
            if ($e->getCode() === '23000') {
                return response()->json(['error' => 'Cannot delete formula because it is being used in other records'], 422);
            }
            return response()->json(['error' => 'Failed to delete formula'], 500);
        } catch (\Exception $e) {
            DB::rollback();
            return response()->json(['error' => 'Failed to delete formula'], 500);
        }
    }
    public function getMasterData()
    {
        $items = Item::with(['itemDetails.unit',])->orderBy('item_code')->get();
        $units = DB::table("item_units")->select("id","unit_name")->get();
        return response()->json(["items" => $items,"units" => $units,"role" => $this->getRole()]);
    }
}