<?php
declare(strict_types=1);

namespace App\Controllers;

require_once BASE_PATH . '/app/helpers/permissions.php';

require_once BASE_PATH . '/app/helpers/conference_scope.php';

use App\Core\Controller;
use App\Core\Database;

final class AdminCommitteesTenantV2Controller extends Controller
{
    private function confOrFail($db, int $orgId, int $confId): void {
        $st=$db->prepare("SELECT id FROM conferences WHERE id=:id AND org_id=:o LIMIT 1");
        $st->execute([':id'=>$confId,':o'=>$orgId]);
        if(!(int)$st->fetchColumn()){ http_response_code(404); echo "Conference not found"; exit; }
    }

    public function index(): void
    {
        require_admin_or_chair();
        require_org_context();

        $db = Database::getInstance();
        $orgId = (int) current_org_id();

        $st=$db->prepare("SELECT id,title_en,title_ar FROM conferences WHERE org_id=:o ORDER BY id DESC");
        $st->execute([':o'=>$orgId]);
        $confs=$st->fetchAll()?:[];

        $confId=(int)($_GET['conference_id'] ?? ($confs[0]['id'] ?? 0));
        if($confId>0) $this->confOrFail($db,$orgId,$confId);

        $st=$db->prepare("SELECT * FROM conference_committees WHERE conference_id=:c ORDER BY sort_order ASC,id ASC");
        $st->execute([':c'=>$confId]);
        $committees=$st->fetchAll()?:[];

        $membersByCommittee=[];
        if($committees){
            $ids=array_map(fn($r)=>(int)$r['id'],$committees);
            $in='('.implode(',', array_fill(0,count($ids),'?')).')';
            $st=$db->prepare("SELECT * FROM conference_committee_members WHERE committee_id IN $in ORDER BY sort_order ASC,id ASC");
            $st->execute($ids);
            foreach(($st->fetchAll()?:[]) as $m){
                $cid=(int)$m['committee_id'];
                $membersByCommittee[$cid]=$membersByCommittee[$cid]??[];
                $membersByCommittee[$cid][]=$m;
            }
        }

        $this->view('admin/committees/index', compact('confs','confId','committees','membersByCommittee'));
    }

    public function importMembersCsv(): void
    {
        require_admin_or_chair();
        require_org_context();
        if(($_SERVER['REQUEST_METHOD']??'GET')!=='POST'){ http_response_code(405); return; }

        $db=Database::getInstance();
        $orgId=(int)current_org_id();
        $confId=(int)($_POST['conference_id']??0);
        $this->confOrFail($db,$orgId,$confId);

        if(empty($_FILES['csv']['tmp_name']) || !is_uploaded_file($_FILES['csv']['tmp_name'])){
            if(function_exists('flash_set')) flash_set('danger','CSV missing');
            header('Location: '.base_path('admin/committees?conference_id='.$confId)); exit;
        }

        $csv = file_get_contents($_FILES['csv']['tmp_name']);
        $csv = preg_replace("/^\xEF\xBB\xBF/", "", (string)$csv);
        $lines = preg_split("/\r\n|\n|\r/", trim((string)$csv));
        if(!$lines || count($lines)<2){
            if(function_exists('flash_set')) flash_set('danger','CSV empty');
            header('Location: '.base_path('admin/committees?conference_id='.$confId)); exit;
        }

        $header = str_getcsv((string)array_shift($lines), ',', '"', "\\");
        $map=[];
        foreach($header as $i=>$h){
            $k=strtolower(trim((string)$h));
            $map[$k]=$i;
        }

        foreach(['committee_type','committee_title_en','committee_title_ar','full_name'] as $n){
            if(!isset($map[$n])){
                if(function_exists('flash_set')) flash_set('danger',"CSV missing column: $n");
                header('Location: '.base_path('admin/committees?conference_id='.$confId)); exit;
            }
        }

        $created=0; $updated=0;

        foreach($lines as $ln){
            if(trim((string)$ln)==='') continue;
            $row = str_getcsv((string)$ln, ',', '"', "\\");

            $ctype = trim((string)($row[$map['committee_type']] ?? 'committee'));
            $cten  = trim((string)($row[$map['committee_title_en']] ?? ''));
            $ctar  = trim((string)($row[$map['committee_title_ar']] ?? ''));
            $name  = trim((string)($row[$map['full_name']] ?? ''));
            if($name==='') continue;

            // required by DB: full_name_en/full_name_ar -> always fill
            $fullNameEn = $name;
            $fullNameAr = $name;

            $role  = trim((string)($row[$map['role_title']] ?? ''));
            $email = trim((string)($row[$map['email']] ?? ''));
            $aff   = trim((string)($row[$map['affiliation']] ?? ''));
            $sort  = (int)trim((string)($row[$map['sort_order']] ?? '0'));
            $act   = (int)trim((string)($row[$map['is_active']] ?? '1'));
            if($act!==0) $act=1;

            // committee lookup/create
            $st=$db->prepare("
              SELECT id FROM conference_committees
              WHERE conference_id=:c AND committee_type=:t AND COALESCE(title_en,'')=:te AND COALESCE(title_ar,'')=:ta
              LIMIT 1
            ");
            $st->execute([':c'=>$confId,':t'=>$ctype,':te'=>$cten,':ta'=>$ctar]);
            $committeeId=(int)($st->fetchColumn()?:0);

            if($committeeId<=0){
                $db->prepare("
                  INSERT INTO conference_committees(conference_id,committee_type,title_en,title_ar,sort_order,is_active)
                  VALUES(:c,:t,:te,:ta,0,1)
                ")->execute([':c'=>$confId,':t'=>$ctype,':te'=>$cten,':ta'=>$ctar]);
                $committeeId=(int)$db->lastInsertId();
            }

            // upsert by email
            if($email!==''){
                $st=$db->prepare("SELECT id FROM conference_committee_members WHERE committee_id=:cid AND email=:em LIMIT 1");
                $st->execute([':cid'=>$committeeId,':em'=>$email]);
                $mid=(int)($st->fetchColumn()?:0);

                if($mid>0){
                    $db->prepare("
                      UPDATE conference_committee_members
                      SET full_name=:fn, full_name_en=:fne, full_name_ar=:fna,
                          role_title=:rt, affiliation=:af, sort_order=:so, is_active=:ia
                      WHERE id=:id
                    ")->execute([
                      ':fn'=>$name,':fne'=>$fullNameEn,':fna'=>$fullNameAr,
                      ':rt'=>$role,':af'=>$aff,':so'=>$sort,':ia'=>$act,':id'=>$mid
                    ]);
                    $updated++;
                    continue;
                }
            }

            $db->prepare("
              INSERT INTO conference_committee_members
                (committee_id,full_name,full_name_en,full_name_ar,role_title,email,affiliation,sort_order,is_active)
              VALUES
                (:cid,:fn,:fne,:fna,:rt,:em,:af,:so,:ia)
            ")->execute([
              ':cid'=>$committeeId,
              ':fn'=>$name,':fne'=>$fullNameEn,':fna'=>$fullNameAr,
              ':rt'=>$role,':em'=>$email,':af'=>$aff,':so'=>$sort,':ia'=>$act
            ]);
            $created++;
        }

        if(function_exists('flash_set')) flash_set('success',"CSV imported: created=$created updated=$updated");
        header('Location: '.base_path('admin/committees?conference_id='.$confId)); exit;
    }
}
