<?php
declare(strict_types=1);

namespace App\Controllers;

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

final class AdminCommitteesTenantV4Controller extends Controller
{
    private function cols($db, string $table): array {
        $out=[]; $st=$db->query("SHOW COLUMNS FROM `$table`");
        foreach(($st->fetchAll()?:[]) as $r){ $out[(string)$r['Field']]=$r; }
        return $out;
    }
    private function has(array $cols,string $c): bool { return isset($cols[$c]); }
    private function enumAllowed(array $cols,string $field): array {
        $t=(string)($cols[$field]['Type']??''); if(strpos($t,'enum(')!==0) return [];
        preg_match_all("/'([^']*)'/",$t,$m); return $m[1]??[];
    }
    private function normalizeType(string $t,array $allowed): string {
        $t=trim(strtolower($t));
        if($t==='') return in_array('other',$allowed,true)?'other':($allowed[0]??'other');
        $map=[
          'chair'=>'chair','conference_chair'=>'chair',
          'cochair'=>'co_chair','co-chair'=>'co_chair','co_chair'=>'co_chair',
          'tpc'=>'tpc','technical'=>'tpc',
          'panel'=>'panel',
          'oc'=>'organizing','organizing'=>'organizing','organizing_committee'=>'organizing',
          'scientific'=>'scientific',
          'other'=>'other',
        ];
        if(isset($map[$t])) $t=$map[$t];
        if($allowed && !in_array($t,$allowed,true)) return in_array('other',$allowed,true)?'other':$allowed[0];
        return $t;
    }
    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 saveCommittee(): 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();
        $ccols=$this->cols($db,'conference_committees');
        $allowed=$this->enumAllowed($ccols,'committee_type');

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

        $id=(int)($_POST['id']??0);
        $type=$this->normalizeType((string)($_POST['committee_type']??''),$allowed);
        $titleEn=trim((string)($_POST['title_en']??''));
        $titleAr=trim((string)($_POST['title_ar']??''));
        $sort=(int)($_POST['sort_order']??0);
        $active=(int)($_POST['is_active']??1)?1:0;

        $fields=[];
        if($this->has($ccols,'conference_id')) $fields['conference_id']=$confId;
        if($this->has($ccols,'committee_type')) $fields['committee_type']=$type;
        if($this->has($ccols,'title_en')) $fields['title_en']=$titleEn;
        if($this->has($ccols,'title_ar')) $fields['title_ar']=$titleAr;
        if($this->has($ccols,'sort_order')) $fields['sort_order']=$sort;
        if($this->has($ccols,'is_active')) $fields['is_active']=$active;

        if($id>0){
            $st=$db->prepare("SELECT cc.id FROM conference_committees cc JOIN conferences c ON c.id=cc.conference_id WHERE cc.id=:id AND c.org_id=:o LIMIT 1");
            $st->execute([':id'=>$id,':o'=>$orgId]);
            if(!(int)$st->fetchColumn()){ http_response_code(404); echo "Not found"; exit; }

            $set=[];$params=[':id'=>$id];
            foreach($fields as $k=>$v){ $set[]="`$k`=:$k"; $params[":$k"]=$v; }
            $db->prepare("UPDATE conference_committees SET ".implode(',',$set)." WHERE id=:id")->execute($params);
        } else {
            $colsList=[];$ph=[];$params=[];
            foreach($fields as $k=>$v){ $colsList[]="`$k`"; $ph[]=":$k"; $params[":$k"]=$v; }
            $db->prepare("INSERT INTO conference_committees (".implode(',',$colsList).") VALUES (".implode(',',$ph).")")->execute($params);
        }
        header('Location: '.base_path('admin/committees?conference_id='.$confId)); exit;
    }

    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'])){
            header('Location: '.base_path('admin/committees?conference_id='.$confId)); exit;
        }

        $ccols=$this->cols($db,'conference_committees');
        $allowed=$this->enumAllowed($ccols,'committee_type');
        $mcols=$this->cols($db,'conference_committee_members'); // your schema: full_name only

        $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){ header('Location: '.base_path('admin/committees?conference_id='.$confId)); exit; }

        $header=str_getcsv((string)array_shift($lines),',','"', "\\");
        $map=[]; foreach($header as $i=>$h){ $map[strtolower(trim((string)$h))]=$i; }
        foreach(['committee_type','committee_title_en','committee_title_ar','full_name'] as $n){
            if(!isset($map[$n])){ header('Location: '.base_path('admin/committees?conference_id='.$confId)); exit; }
        }

        foreach($lines as $ln){
            if(trim((string)$ln)==='') continue;
            $row=str_getcsv((string)$ln,',','"', "\\");
            $ctype=$this->normalizeType((string)($row[$map['committee_type']]??''),$allowed);
            $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;

            $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;

            $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();
            }

            $fields=[];
            if(isset($mcols['committee_id'])) $fields['committee_id']=$committeeId;
            if(isset($mcols['full_name'])) $fields['full_name']=$name;
            if(isset($mcols['role_title'])) $fields['role_title']=$role;
            if(isset($mcols['email'])) $fields['email']=$email;
            if(isset($mcols['affiliation'])) $fields['affiliation']=$aff;
            if(isset($mcols['sort_order'])) $fields['sort_order']=$sort;
            if(isset($mcols['is_active'])) $fields['is_active']=$act;

            $colsList=[];$ph=[];$params=[];
            foreach($fields as $k=>$v){ $colsList[]="`$k`"; $ph[]=":$k"; $params[":$k"]=$v; }
            $db->prepare("INSERT INTO conference_committee_members (".implode(',',$colsList).") VALUES (".implode(',',$ph).")")->execute($params);
        }

        header('Location: '.base_path('admin/committees?conference_id='.$confId)); exit;
    }
}
