<?php

namespace App\Http\Controllers;
use Illuminate\Http\Request;
use App\Models\SalaryStructure;
use App\Exports\SalaryStructureExport;
use Maatwebsite\Excel\Facades\Excel;
use App\Models\Client;
use App\Models\Employee;


use DataTables;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Auth;

/**
 * Controller for managing SalaryStructure resources.
 * 
 * This controller handles CRUD operations including:
 * - Listing, creating, updating, and deleting records
 * - Soft delete and restore functionality
 * - Excel/CSV export functionality
 * - Search and sorting capabilities
 * 
 * @package App\Http\Controllers
 */
class SalaryStructureController extends Controller {

    /**
     * Constructor - Set up middleware for permissions
     */
    public function __construct()
    {
        $this->middleware('permission:salary-structures-list')->only(['index', 'show']);
        $this->middleware('permission:salary-structures-create')->only(['create', 'store']);
        $this->middleware('permission:salary-structures-edit')->only(['edit', 'update']);
        $this->middleware('permission:salary-structures-delete')->only(['destroy']);
    }

    /**
     * Store a newly created resource in storage.
     * 
     * Validates the request data and creates a new record in the database.
     * 
     * @param Request $request
     * @return \Illuminate\Http\JsonResponse
     */
/*1- validate and insert into to database*/
public function store(Request $request) {

$dbs_salary_structures = view()->shared('dbs_salary_structures');

$validator = \Validator::make($request->all(), [
           'client_id'=>$dbs_salary_structures["client_id_validateas"].$dbs_salary_structures["client_id_unique"],

           'employee_id'=>$dbs_salary_structures["employee_id_validateas"].$dbs_salary_structures["employee_id_unique"],

           'basic_salary'=>$dbs_salary_structures["basic_salary_validateas"].$dbs_salary_structures["basic_salary_unique"],

           'house_allowance'=>$dbs_salary_structures["house_allowance_validateas"].$dbs_salary_structures["house_allowance_unique"],

           'medical_allowance'=>$dbs_salary_structures["medical_allowance_validateas"].$dbs_salary_structures["medical_allowance_unique"],

           'transport_allowance'=>$dbs_salary_structures["transport_allowance_validateas"].$dbs_salary_structures["transport_allowance_unique"],

           'bonus'=>$dbs_salary_structures["bonus_validateas"].$dbs_salary_structures["bonus_unique"],

           'tax'=>$dbs_salary_structures["tax_validateas"].$dbs_salary_structures["tax_unique"],

           'provident_fund'=>$dbs_salary_structures["provident_fund_validateas"].$dbs_salary_structures["provident_fund_unique"],

           'other_deductions'=>$dbs_salary_structures["other_deductions_validateas"].$dbs_salary_structures["other_deductions_unique"],

           'effective_from'=>$dbs_salary_structures["effective_from_validateas"].$dbs_salary_structures["effective_from_unique"],

           'status'=>$dbs_salary_structures["status_validateas"].$dbs_salary_structures["status_unique"],


]);


if ($validator->passes()) {
$table = new SalaryStructure();



            $table->client_id = $request->input('client_id');
            $table->employee_id = $request->input('employee_id');
            $table->basic_salary = $request->input('basic_salary');
            $table->house_allowance = $request->input('house_allowance');
            $table->medical_allowance = $request->input('medical_allowance');
            $table->transport_allowance = $request->input('transport_allowance');
            $table->bonus = $request->input('bonus');
            $table->tax = $request->input('tax');
            $table->provident_fund = $request->input('provident_fund');
            $table->other_deductions = $request->input('other_deductions');
            $table->effective_from = $request->input('effective_from');
            $table->status = $request->input('status');

$table->save();
$max_id = $table->id;

/*CHECKBOXQUERY*/


return response()->json(['success' => 'Record has been created successfully.']);
}
return response()->json(['error' => $validator->errors()]);
}

    /**
     * Display a listing of the resource.
     * 
     * Shows paginated list of records with search and sorting functionality.
     * 
     * @param Request $request
     * @return \Illuminate\Contracts\View\View
     */
/*2- view index page*/
public function index(Request $request) {
    $query = SalaryStructure::whereNull('deleted_at');
    
    
    $query->with(['Client', 'Employee']);
    
    // Search functionality
    if ($request->has('search') && $request->search != '') {
        $search = $request->search;
        $query->where(function($q) use ($search) {
            $q->orWhere('client_id', 'like', '%' . $search . '%');
            $q->orWhereHas('Client', function($subQ) use ($search) {
                $subQ->where('name', 'like', '%' . $search . '%');
            });
            $q->orWhere('employee_id', 'like', '%' . $search . '%');
            $q->orWhereHas('Employee', function($subQ) use ($search) {
                $subQ->where('first_name', 'like', '%' . $search . '%');
            });
            $q->orWhere('basic_salary', 'like', '%' . $search . '%');
            $q->orWhere('effective_from', 'like', '%' . $search . '%');
            $q->orWhere('status', 'like', '%' . $search . '%');
        });
    }
    
    // Ordering
    $sortBy = $request->get('sort_by', 'client_id');
    $sortOrder = $request->get('sort_order', 'asc');
    
    // Validate sort_by to prevent SQL injection
    $allowedSorts = ['id', 'client_id', 'employee_id', 'basic_salary', 'effective_from', 'status'];
    if (!in_array($sortBy, $allowedSorts)) {
        $sortBy = 'client_id';
    }
    
    // Validate sort_order
    $sortOrder = strtolower($sortOrder) === 'desc' ? 'desc' : 'asc';
    
    $query->orderBy($sortBy, $sortOrder);
    
    // Pagination
    $salary_structures = $query->paginate(10)->withQueryString();
    
    return view('salary-structures.index', compact('salary_structures'));
}

    /**
     * Display the specified resource.
     * 
     * Redirects to the edit page for the specified resource.
     * 
     * @param int $id
     * @return \Illuminate\Http\RedirectResponse
     */
public function show($id) {
    return redirect()->route('salary-structures.edit', $id);
}

    /**
     * Show the form for creating a new resource.
     * 
     * Displays the form to create a new record.
     * 
     * @return \Illuminate\Contracts\View\View
     */
/*3- View Add page*/
public function create() {
    $client_id=Client::pluck('name', 'id');
$employee_id=Employee::pluck('first_name', 'id');

return view('salary-structures.create', compact('client_id','employee_id'));
}


    /**
     * Show the form for editing the specified resource.
     * 
     * Displays the form to edit an existing record.
     * 
     * @param int $id
     * @return \Illuminate\Contracts\View\View
     * @throws \Illuminate\Database\Eloquent\ModelNotFoundException
     */
/*4- View update page*/
public function edit($id) {
    $row = SalaryStructure::findOrFail($id);

$client_id=Client::pluck('name', 'id');
$employee_id=Employee::pluck('first_name', 'id');



return view('salary-structures.edit', ['row' => $row], compact('client_id','employee_id')/*COMPACT_UPDATE*/);
}

    /**
     * Update the specified resource in storage.
     * 
     * Validates the request data and updates an existing record in the database.
     * 
     * @param Request $request
     * @param int $id
     * @return \Illuminate\Http\JsonResponse
     * @throws \Illuminate\Database\Eloquent\ModelNotFoundException
     */
/*5- validate and update data into database*/
public function update(Request $request, $id) {
$dbs_salary_structures = view()->shared('dbs_salary_structures');

$validator = \Validator::make($request->all(), [
           'client_id'=>$dbs_salary_structures["client_id_validateas"].str_replace('NULL_ID',$id,$dbs_salary_structures["client_id_unique_for_update"]),

           'employee_id'=>$dbs_salary_structures["employee_id_validateas"].str_replace('NULL_ID',$id,$dbs_salary_structures["employee_id_unique_for_update"]),

           'basic_salary'=>$dbs_salary_structures["basic_salary_validateas"].str_replace('NULL_ID',$id,$dbs_salary_structures["basic_salary_unique_for_update"]),

           'house_allowance'=>$dbs_salary_structures["house_allowance_validateas"].str_replace('NULL_ID',$id,$dbs_salary_structures["house_allowance_unique_for_update"]),

           'medical_allowance'=>$dbs_salary_structures["medical_allowance_validateas"].str_replace('NULL_ID',$id,$dbs_salary_structures["medical_allowance_unique_for_update"]),

           'transport_allowance'=>$dbs_salary_structures["transport_allowance_validateas"].str_replace('NULL_ID',$id,$dbs_salary_structures["transport_allowance_unique_for_update"]),

           'bonus'=>$dbs_salary_structures["bonus_validateas"].str_replace('NULL_ID',$id,$dbs_salary_structures["bonus_unique_for_update"]),

           'tax'=>$dbs_salary_structures["tax_validateas"].str_replace('NULL_ID',$id,$dbs_salary_structures["tax_unique_for_update"]),

           'provident_fund'=>$dbs_salary_structures["provident_fund_validateas"].str_replace('NULL_ID',$id,$dbs_salary_structures["provident_fund_unique_for_update"]),

           'other_deductions'=>$dbs_salary_structures["other_deductions_validateas"].str_replace('NULL_ID',$id,$dbs_salary_structures["other_deductions_unique_for_update"]),

           'effective_from'=>$dbs_salary_structures["effective_from_validateas"].str_replace('NULL_ID',$id,$dbs_salary_structures["effective_from_unique_for_update"]),

           'status'=>$dbs_salary_structures["status_validateas"].str_replace('NULL_ID',$id,$dbs_salary_structures["status_unique_for_update"]),


]);




if ($validator->passes()) {
$table = SalaryStructure::findOrFail($id);
            $table->client_id = $request->input('client_id');
            $table->employee_id = $request->input('employee_id');
            $table->basic_salary = $request->input('basic_salary');
            $table->house_allowance = $request->input('house_allowance');
            $table->medical_allowance = $request->input('medical_allowance');
            $table->transport_allowance = $request->input('transport_allowance');
            $table->bonus = $request->input('bonus');
            $table->tax = $request->input('tax');
            $table->provident_fund = $request->input('provident_fund');
            $table->other_deductions = $request->input('other_deductions');
            $table->effective_from = $request->input('effective_from');
            $table->status = $request->input('status');

$table->update();

$max_id = $id;
/*DeleteCHECKBOXQUERY*/
/*CHECKBOXQUERY*/

return response()->json(['success' => 'Record has been updated successfully.']);
}
return response()->json(['error' => $validator->errors()]);
}

    /**
     * Remove the specified resource from storage.
     * 
     * Soft deletes the specified record (can be restored later).
     * 
     * @param int $id
     * @return \Illuminate\Http\JsonResponse
     * @throws \Illuminate\Database\Eloquent\ModelNotFoundException
     */
/*6- delete record from database*/
public function destroy($id) {
$row = SalaryStructure::findOrFail($id);
$row->delete();
return response()->json(['success' => 'Record has been deleted successfully. You can restore it using the restore button.']);
}

    /**
     * Remove multiple resources from storage.
     * 
     * Soft deletes multiple records specified by comma-separated IDs.
     * 
     * @param string $ids Comma-separated list of IDs
     * @return \Illuminate\Http\JsonResponse
     */
/*7- delete all record from database*/
public function destroyAll($ids) {
$ids_arr = explode(',', $ids);
SalaryStructure::whereIn('id', $ids_arr)->delete();
return response()->json(['success' => 'Selected records have been deleted successfully. You can restore them using the restore button.']);
}

    /**
     * Restore a soft-deleted resource.
     * 
     * Restores a previously soft-deleted record.
     * 
     * @param  int  $id
     * @return \Illuminate\Http\JsonResponse
     * @throws \Illuminate\Database\Eloquent\ModelNotFoundException
     */
/*8- restore record from database*/
public function restore($id) {
$row = SalaryStructure::withTrashed()->findOrFail($id);
$row->restore();
return response()->json(['success' => 'Record has been restored successfully.']);
}

/*9- delete multiple image*/


    /**
     * Export data to Excel/CSV format.
     * 
     * Downloads the resource data as a CSV file.
     * 
     * @return \Symfony\Component\HttpFoundation\BinaryFileResponse
     */
/*10- export csv*/
public function exportExcel()
{
    return Excel::download(new SalaryStructureExport, 'SalaryStructure.csv');
}

/*11- import csv*/

public function importCsv(Request $request)
{
    $request->validate([
        'file' => 'required|file|mimes:csv,txt',
    ]);

    $dbs_salary_structures = view()->shared('dbs_salary_structures');
    $importable = ['client_id','employee_id','basic_salary','house_allowance','medical_allowance','transport_allowance','bonus','tax','provident_fund','other_deductions','effective_from','status'];
    $pictureFields = [''];
    $relationMaps = array (
  0 => 
  array (
    'field' => 'client_id',
    'table' => 'clients',
    'id' => 'id',
    'text' => 'name',
    'multiple' => false,
  ),
  1 => 
  array (
    'field' => 'employee_id',
    'table' => 'employees',
    'id' => 'id',
    'text' => 'first_name',
    'multiple' => false,
  ),
);
    $rules = [];
    foreach ($importable as $f) {
        if (!isset($dbs_salary_structures["${f}_validateas"])) {
            continue;
        }
        $rules[$f] = $dbs_salary_structures["${f}_validateas"] . ($dbs_salary_structures["${f}_unique"] ?? '');
    }
    // Multi dropdown fields store comma-separated IDs; validate via mapping, not Laravel rule string
    foreach ($relationMaps as $m) {
        if (!empty($m['multiple']) && isset($rules[$m['field']])) {
            unset($rules[$m['field']]);
        }
    }
    // Picture fields are imported as URL/path strings; skip image64 validator in CSV import.
    foreach ($pictureFields as $pf) {
        if (isset($rules[$pf])) { unset($rules[$pf]); }
    }

    $path = $request->file('file')->getRealPath();
    if (!$path) {
        return redirect()->back()->with('error', 'Invalid file.');
    }

    $handle = fopen($path, 'r');
    if (!$handle) {
        return redirect()->back()->with('error', 'Unable to read file.');
    }

    $header = fgetcsv($handle);
    if (!$header) {
        fclose($handle);
        return redirect()->back()->with('error', 'Empty CSV file.');
    }

    // Normalize headings
    $header = array_map(function($h) {
        $h = trim((string) $h);
        $h = strtolower(str_replace(' ', '_', $h));
        return $h;
    }, $header);
    // Drop Sr. column if present
    if (isset($header[0]) && in_array($header[0], ['sr.', 'sr', '#'], true)) {
        array_shift($header);
    }

    // Allow friendly headings from exported CSV (e.g. "client" => "client_id")
    $aliases = [];
    foreach ($relationMaps as $m) {
        if (empty($m['field'])) { continue; }
        $f = (string) $m['field'];
        $aliases[$f] = $f;
        if (substr($f, -3) === '_id') {
            $aliases[substr($f, 0, -3)] = $f;
        }
    }

    // Read all rows first (so we can resolve relations in bulk)
    $rows = [];
    $rowNumber = 1; // header is row 1
    while (($row = fgetcsv($handle)) !== false) {
        $rowNumber++;
        if (count($row) === 0) { continue; }
        if (count($row) > count($header) && count($header) > 0) {
            array_shift($row); // assume first col is Sr.
        }
        $data = [];
        foreach ($header as $idx => $key) {
            if (!isset($row[$idx])) { continue; }
            $mappedKey = $key;
            if (!in_array($mappedKey, $importable, true)) {
                if (isset($aliases[$mappedKey]) && in_array($aliases[$mappedKey], $importable, true)) {
                    $mappedKey = $aliases[$mappedKey];
                } else {
                    continue;
                }
            }
            $val = $row[$idx];
            if (is_string($val)) { $val = trim($val); }
            // Normalize empty strings to null (prevents '' into integer/date columns)
            if ($val === '') { $val = null; }
            $data[$mappedKey] = $val;
        }
        if (!empty($data)) {
            $rows[] = ['n' => $rowNumber, 'data' => $data];
        }
    }
    fclose($handle);

    // Bulk resolve relations: value in CSV is TEXT, store ID(s) in main
    foreach ($relationMaps as $m) {
        $field = $m['field'];
        $relTable = $m['table'];
        $relId = $m['id'];
        $relText = $m['text'];
        $isMultiple = !empty($m['multiple']);

        // collect unique names + numeric IDs from CSV
        $names = [];
        $numericIds = [];
        foreach ($rows as $r) {
            if (!array_key_exists($field, $r['data'])) { continue; }
            $raw = (string) ($r['data'][$field] ?? '');
            if ($raw === '') { continue; }
            if ($isMultiple) {
                foreach (array_filter(array_map('trim', explode(',', $raw))) as $one) {
                    if ($one === '') { continue; }
                    if (preg_match('/^\d+$/', $one)) {
                        $numericIds[$one] = true;
                    } else {
                        $names[$one] = true;
                    }
                }
            } else {
                $one = trim($raw);
                if ($one === '') { continue; }
                if (preg_match('/^\d+$/', $one)) {
                    $numericIds[$one] = true;
                } else {
                    $names[$one] = true;
                }
            }
        }
        $names = array_keys(array_filter($names, function($k){ return trim((string)$k) !== ''; }, ARRAY_FILTER_USE_KEY));
        $numericIds = array_keys(array_filter($numericIds, function($k){ return trim((string)$k) !== ''; }, ARRAY_FILTER_USE_KEY));
        if (empty($names) && empty($numericIds)) { continue; }

        // 1 query: existing map
        $existing = [];
        if (!empty($names)) {
            $existing = \DB::table($relTable)->whereIn($relText, $names)->pluck($relId, $relText)->toArray();
        }
        $validIds = [];
        if (!empty($numericIds)) {
            // Validate numeric ids exist (prevents importing invalid FK ids)
            $validIds = \DB::table($relTable)->whereIn($relId, $numericIds)->pluck($relId)->toArray();
            $validIds = array_fill_keys(array_map('strval', $validIds), true);
        }
        $missing = !empty($names) ? array_values(array_diff($names, array_keys($existing))) : [];

        // 1 query: insert missing (batch) (ignore failures; we'll re-fetch)
        if (!empty($missing)) {
            $ins = [];
            $hasCreated = \Schema::hasColumn($relTable, 'created_at');
            $hasUpdated = \Schema::hasColumn($relTable, 'updated_at');
            $now = now();
            foreach ($missing as $nm) {
                $row = [$relText => $nm];
                if ($hasCreated) { $row['created_at'] = $now; }
                if ($hasUpdated) { $row['updated_at'] = $now; }
                $ins[] = $row;
            }
            try {
                \DB::table($relTable)->insert($ins);
            } catch (\Throwable $e) {
                // ignore (unique constraints may race); we'll re-fetch
            }
            // 1 query: refresh map
            $existing = \DB::table($relTable)->whereIn($relText, $names)->pluck($relId, $relText)->toArray();
        }

        // replace text values with ids in rows
        foreach ($rows as &$r) {
            if (!array_key_exists($field, $r['data'])) { continue; }
            $raw = (string) ($r['data'][$field] ?? '');
            if ($raw === '') { $r['data'][$field] = null; continue; }
            if ($isMultiple) {
                $ids = [];
                foreach (array_filter(array_map('trim', explode(',', $raw))) as $nm) {
                    if ($nm === '') { continue; }
                    if (preg_match('/^\d+$/', $nm)) {
                        if (isset($validIds[(string)$nm])) { $ids[] = (string) $nm; }
                        continue;
                    }
                    if (isset($existing[$nm])) { $ids[] = (string) $existing[$nm]; }
                }
                $r['data'][$field] = !empty($ids) ? implode(',', $ids) : null;
            } else {
                $nm = trim($raw);
                if (preg_match('/^\d+$/', $nm)) {
                    $r['data'][$field] = isset($validIds[(string)$nm]) ? (int) $nm : null;
                } else {
                    $r['data'][$field] = isset($existing[$nm]) ? $existing[$nm] : null;
                }
            }
        }
        unset($r);
    }

    // Validate + batch insert main table
    $count = 0;
    $batch = [];
    $batchSize = 500;
    $hasCreatedMain = \Schema::hasColumn('salary_structures', 'created_at');
    $hasUpdatedMain = \Schema::hasColumn('salary_structures', 'updated_at');
    $now = now();

    foreach ($rows as $r) {
        $data = $r['data'];
        $n = $r['n'];

        // Picture import from URL: download into public/images/Y/m/d and store relative path.
        foreach ($pictureFields as $pf) {
            if (!array_key_exists($pf, $data) || empty($data[$pf])) { continue; }
            $pv = (string) $data[$pf];
            if (preg_match('/^https?:\/\//i', $pv)) {
                $urlPath = parse_url($pv, PHP_URL_PATH);
                $ext = strtolower((string) pathinfo((string) $urlPath, PATHINFO_EXTENSION));
                if ($ext === '') { $ext = 'jpg'; }
                $safeExt = preg_replace('/[^a-z0-9]/i', '', $ext);
                if ($safeExt === '') { $safeExt = 'jpg'; }
                $currentDate = now();
                $storagePath = $currentDate->year . '/' . $currentDate->month . '/' . $currentDate->day . '/';
                $dir = public_path('images/' . $storagePath);
                if (!is_dir($dir)) { @mkdir($dir, 0755, true); }
                $filename = uniqid('img_') . '.' . $safeExt;
                $context = stream_context_create(['http' => ['timeout' => 10], 'https' => ['timeout' => 10]]);
                $bin = @file_get_contents($pv, false, $context);
                if ($bin === false || $bin === '') {
                    $msg = 'CSV row ' . $n . ' picture URL not accessible: ' . $pv;
                    if ($request->ajax() || $request->expectsJson()) {
                        return response()->json(['error' => $msg], 422);
                    }
                    return redirect()->back()->with('error', $msg);
                }
                if (@file_put_contents($dir . $filename, $bin) === false) {
                    $msg = 'CSV row ' . $n . ' picture save failed.';
                    if ($request->ajax() || $request->expectsJson()) {
                        return response()->json(['error' => $msg], 422);
                    }
                    return redirect()->back()->with('error', $msg);
                }
                $data[$pf] = $storagePath . $filename;
            }
        }

        $v = \Validator::make($data, $rules);
        if ($v->fails()) {
            $first = \implode(' ', \array_map(function($arr){ return is_array($arr) ? ($arr[0] ?? '') : (string)$arr; }, $v->errors()->toArray()));
            $msg = 'CSV row ' . $n . ' validation failed: ' . $first;
            if ($request->ajax() || $request->expectsJson()) {
                return response()->json(['error' => $msg], 422);
            }
            return redirect()->back()->with('error', $msg);
        }

        if ($hasCreatedMain && !isset($data['created_at'])) { $data['created_at'] = $now; }
        if ($hasUpdatedMain && !isset($data['updated_at'])) { $data['updated_at'] = $now; }
        $batch[] = $data;
        if (count($batch) >= $batchSize) {
            try { \DB::table('salary_structures')->insert($batch); } catch (\Throwable $e) {
                $msg = 'CSV batch insert failed near row ' . $n . ': ' . $e->getMessage();
                if ($request->ajax() || $request->expectsJson()) {
                    return response()->json(['error' => $msg], 422);
                }
                return redirect()->back()->with('error', $msg);
            }
            $count += count($batch);
            $batch = [];
        }
    }
    if (!empty($batch)) {
        try { \DB::table('salary_structures')->insert($batch); } catch (\Throwable $e) {
            $msg = 'CSV batch insert failed: ' . $e->getMessage();
            if ($request->ajax() || $request->expectsJson()) {
                return response()->json(['error' => $msg], 422);
            }
            return redirect()->back()->with('error', $msg);
        }
        $count += count($batch);
    }

    $msg = "Imported {$count} row(s) successfully.";
    if ($request->ajax() || $request->expectsJson()) {
        return response()->json(['success' => $msg, 'count' => $count]);
    }
    return redirect()->back()->with('success', $msg);
}



}
