<?php
// Marketing Department API: campuses, service centers, agents, phones and monthly KPIs.
declare(strict_types=1);
ini_set('display_errors', '0');
header('Content-Type: application/json; charset=UTF-8');
header('Cache-Control: no-store, no-cache, must-revalidate');

function mkt_reply(int $status, bool $ok, string $message, array $data = []): void {
    http_response_code($status);
    echo json_encode(['ok'=>$ok, 'message'=>$message, 'data'=>$data], JSON_UNESCAPED_SLASHES | JSON_UNESCAPED_UNICODE | JSON_INVALID_UTF8_SUBSTITUTE);
    exit;
}
function mkt_input(): array {
    $value = json_decode(file_get_contents('php://input') ?: '{}', true);
    return is_array($value) ? $value : [];
}
function mkt_id($value): int { return filter_var($value, FILTER_VALIDATE_INT, ['options'=>['min_range'=>1]]) ?: 0; }
function mkt_count($value) { return filter_var($value, FILTER_VALIDATE_INT, ['options'=>['min_range'=>0]]); }
function mkt_text($value, int $max): string {
    $text = trim((string)($value ?? ''));
    return mb_strlen($text) <= $max ? $text : '';
}
function mkt_closed_report(PDO $pdo, int $year, int $month): array {
    $stmt = $pdo->prepare(
      "SELECT c.id AS center_id, c.center_name, c.center_code, cp.name AS campus_name,
              COALESCE(p.student_inquiries,0) AS student_inquiries,
              COALESCE(p.applications_started,0) AS applications_started,
              COALESCE(p.students_enrolled,0) AS students_enrolled,
              COALESCE(p.enrollment_target,0) AS enrollment_target,
              COALESCE(p.outreach_activities,0) AS outreach_activities,
              CASE WHEN COALESCE(p.enrollment_target,0)=0 THEN NULL
                   ELSE ROUND(100.0*p.students_enrolled/NULLIF(p.enrollment_target,0),1) END AS target_attainment
       FROM mkt_service_centers c JOIN campuses cp ON cp.id=c.campus_id
       LEFT JOIN mkt_monthly_performance p ON p.center_id=c.id AND p.report_year=? AND p.report_month=?
       WHERE c.is_active=TRUE
       ORDER BY COALESCE(p.students_enrolled,0) DESC, c.center_name"
    );
    $stmt->execute([$year, $month]);
    $centers = $stmt->fetchAll(PDO::FETCH_ASSOC);
    $totals = ['student_inquiries'=>0,'applications_started'=>0,'students_enrolled'=>0,'enrollment_target'=>0,'outreach_activities'=>0];
    foreach ($centers as &$center) {
        foreach ($totals as $key=>$unused) { $center[$key] = (int)$center[$key]; $totals[$key] += $center[$key]; }
        $center['target_attainment'] = $center['target_attainment'] === null ? null : (float)$center['target_attainment'];
    }
    unset($center);
    $totals['target_attainment'] = $totals['enrollment_target'] === 0 ? null : round(100 * $totals['students_enrolled'] / $totals['enrollment_target'], 1);
    $leader = array_sum($totals) > 0 ? ($centers[0] ?? null) : null;
    $summary = ['year'=>$year,'month'=>$month,'center_count'=>count($centers), ...$totals,
        'top_center'=>$leader ? ['id'=>(int)$leader['center_id'],'name'=>$leader['center_name'],'campus'=>$leader['campus_name'],'students_enrolled'=>(int)$leader['students_enrolled'],'target_attainment'=>$leader['target_attainment']] : null];
    return ['summary'=>$summary, 'centers'=>$centers];
}
try {
    require_once __DIR__ . '/../config/database.php';
    require_once __DIR__ . '/session_auth.php';
    if (!isset($pdo) || !($pdo instanceof PDO)) throw new RuntimeException('PostgreSQL connection is not available.');
    $user = rvu_current_user($pdo);
    if (!$user) mkt_reply(401, false, 'Your session is missing or expired. Please sign in again.');
    $method = strtoupper((string)($_SERVER['REQUEST_METHOD'] ?? 'GET'));
    $action = trim((string)($_GET['action'] ?? 'overview'));
    $actor = mkt_id($user['id'] ?? 0) ?: null;

    if ($method === 'GET') {
        // The first Marketing request after a month closes creates the final snapshot automatically.
        $today = new DateTimeImmutable('today');
        $closed = $today->modify('first day of this month')->modify('-1 day');
        $closedYear = (int)$closed->format('Y'); $closedMonth = (int)$closed->format('n');
        $closedReport = mkt_closed_report($pdo, $closedYear, $closedMonth);
        $save = $pdo->prepare('INSERT INTO mkt_monthly_reports(report_year,report_month,report_data) VALUES(?,?,?::jsonb) ON CONFLICT(report_year,report_month) DO UPDATE SET report_data=EXCLUDED.report_data, generated_at=CURRENT_TIMESTAMP');
        $save->execute([$closedYear, $closedMonth, json_encode($closedReport, JSON_UNESCAPED_UNICODE|JSON_UNESCAPED_SLASHES)]);

        if ($action === 'overview' || $action === 'centers' || $action === 'agents' || $action === 'phones' || $action === 'performance' || $action === 'analytics') {
            $campuses = $pdo->query('SELECT id,name AS campus_name,description FROM campuses ORDER BY name')->fetchAll(PDO::FETCH_ASSOC);
            $centers = $pdo->query('SELECT c.id,c.campus_id,cp.name AS campus_name,c.center_name,c.center_code,c.location,c.is_active,c.created_at FROM mkt_service_centers c JOIN campuses cp ON cp.id=c.campus_id ORDER BY cp.name,c.center_name')->fetchAll(PDO::FETCH_ASSOC);
            $agents = $pdo->query('SELECT a.id,a.center_id,a.agent_name,a.employee_number,a.email,a.contact_number,a.is_active,c.center_name,cp.name AS campus_name FROM mkt_agents a JOIN mkt_service_centers c ON c.id=a.center_id JOIN campuses cp ON cp.id=c.campus_id ORDER BY c.center_name,a.agent_name')->fetchAll(PDO::FETCH_ASSOC);
            foreach ($centers as &$row) $row['is_active'] = in_array($row['is_active'], [true, 't', 'true', 1, '1'], true);
            unset($row);
            foreach ($agents as &$row) $row['is_active'] = in_array($row['is_active'], [true, 't', 'true', 1, '1'], true);
            unset($row);
            $phones = $pdo->query("SELECT ph.id,ph.agent_id,a.agent_name,c.center_name,cp.name AS campus_name,ph.phone_number,ph.device_model,ph.asset_tag,ph.issued_on,ph.returned_on,ph.status,ph.notes FROM mkt_agent_phones ph JOIN mkt_agents a ON a.id=ph.agent_id JOIN mkt_service_centers c ON c.id=a.center_id JOIN campuses cp ON cp.id=c.campus_id ORDER BY CASE WHEN ph.status='assigned' THEN 0 ELSE 1 END, ph.phone_number")->fetchAll(PDO::FETCH_ASSOC);
            $performance = $pdo->query('SELECT p.id,p.center_id,c.center_name,cp.name AS campus_name,p.report_year,p.report_month,p.student_inquiries,p.applications_started,p.students_enrolled,p.enrollment_target,p.outreach_activities,p.notes,p.updated_at FROM mkt_monthly_performance p JOIN mkt_service_centers c ON c.id=p.center_id JOIN campuses cp ON cp.id=c.campus_id ORDER BY p.report_year DESC,p.report_month DESC,c.center_name')->fetchAll(PDO::FETCH_ASSOC);
            $year = filter_var($_GET['year'] ?? date('Y'), FILTER_VALIDATE_INT, ['options'=>['min_range'=>2000,'max_range'=>2200]]) ?: (int)date('Y');
            $month = filter_var($_GET['month'] ?? date('n'), FILTER_VALIDATE_INT, ['options'=>['min_range'=>1,'max_range'=>12]]) ?: (int)date('n');
            $report = mkt_closed_report($pdo, $year, $month);
            $trendStmt = $pdo->query("SELECT report_year,report_month,TO_CHAR(MAKE_DATE(report_year,report_month,1),'Mon YYYY') AS label,SUM(student_inquiries)::int AS student_inquiries,SUM(applications_started)::int AS applications_started,SUM(students_enrolled)::int AS students_enrolled,SUM(enrollment_target)::int AS enrollment_target,SUM(outreach_activities)::int AS outreach_activities FROM mkt_monthly_performance GROUP BY report_year,report_month ORDER BY report_year,report_month");
            $trend = $trendStmt->fetchAll(PDO::FETCH_ASSOC);
            foreach ($trend as &$row) foreach (['report_year','report_month','student_inquiries','applications_started','students_enrolled','enrollment_target','outreach_activities'] as $key) $row[$key]=(int)$row[$key];
            unset($row);
            $reports = $pdo->query('SELECT id,report_year,report_month,generated_at,report_data FROM mkt_monthly_reports ORDER BY report_year DESC,report_month DESC')->fetchAll(PDO::FETCH_ASSOC);
            foreach ($reports as &$row) $row['report_data'] = is_string($row['report_data']) ? json_decode($row['report_data'],true) : $row['report_data'];
            unset($row);
            $summary = $report['summary'];
            $summary['campus_count'] = count($campuses); $summary['agent_count'] = count(array_filter($agents,fn($a)=>$a['is_active']===true));
            $summary['phone_count'] = count(array_filter($phones,fn($p)=>$p['status']==='assigned'));
            $summary['service_center_count'] = count(array_filter($centers,fn($c)=>$c['is_active']===true));
            mkt_reply(200,true,'Marketing department data loaded.',[
              'campuses'=>$campuses,'centers'=>$centers,'agents'=>$agents,'phones'=>$phones,'performance'=>$performance,
              'trend'=>$trend,'reports'=>$reports,'selected_report'=>$report,'summary'=>$summary,
              'years'=>array_values(array_unique(array_map(fn($x)=>(int)$x,array_column($performance,'report_year'))))
            ]);
        }
        if ($action === 'report') {
            $year = filter_var($_GET['year'] ?? 0,FILTER_VALIDATE_INT,['options'=>['min_range'=>2000,'max_range'=>2200]]) ?: 0;
            $month = filter_var($_GET['month'] ?? 0,FILTER_VALIDATE_INT,['options'=>['min_range'=>1,'max_range'=>12]]) ?: 0;
            if (!$year || !$month) mkt_reply(422,false,'Choose a valid report month and year.');
            $data=mkt_closed_report($pdo,$year,$month);
            mkt_reply(200,true,'Monthly report generated.', $data);
        }
        mkt_reply(404,false,'Unknown Marketing request.');
    }
    if ($method !== 'POST') mkt_reply(405,false,'Unsupported request method.');
    $input=mkt_input();
    if ($action === 'create_center') {
        $campus=mkt_id($input['campus_id']??0); $name=mkt_text($input['center_name']??'',150); $code=mkt_text($input['center_code']??'',30); $location=mkt_text($input['location']??'',180);
        if (!$campus || $name==='' || $code==='') mkt_reply(422,false,'Choose a campus and enter a service center name and unique code.');
        $s=$pdo->prepare('INSERT INTO mkt_service_centers(campus_id,center_name,center_code,location,created_by) VALUES(?,?,?,?,?) RETURNING id');
        $s->execute([$campus,$name,$code,$location?:null,$actor]); mkt_reply(201,true,'Service center created.',['id'=>(int)$s->fetchColumn()]);
    }
    if ($action === 'create_agent') {
        $center=mkt_id($input['center_id']??0); $name=mkt_text($input['agent_name']??'',150); $employee=mkt_text($input['employee_number']??'',40); $email=mkt_text($input['email']??'',180); $contact=mkt_text($input['contact_number']??'',30);
        if (!$center || $name==='') mkt_reply(422,false,'Choose a service center and enter the agent name.');
        $s=$pdo->prepare('INSERT INTO mkt_agents(center_id,agent_name,employee_number,email,contact_number,created_by) VALUES(?,?,NULLIF(?,\'\'),NULLIF(?,\'\'),NULLIF(?,\'\'),?) RETURNING id');
        $s->execute([$center,$name,$employee,$email,$contact,$actor]); mkt_reply(201,true,'Agent added.',['id'=>(int)$s->fetchColumn()]);
    }
    if ($action === 'assign_phone') {
        $agent=mkt_id($input['agent_id']??0); $number=mkt_text($input['phone_number']??'',30); $model=mkt_text($input['device_model']??'',100); $asset=mkt_text($input['asset_tag']??'',50); $date=mkt_text($input['issued_on']??date('Y-m-d'),10);
        if (!$agent || $number==='' || !preg_match('/^\d{4}-\d{2}-\d{2}$/',$date)) mkt_reply(422,false,'Choose an agent and enter a valid institutional phone number and issue date.');
        $s=$pdo->prepare("INSERT INTO mkt_agent_phones(agent_id,phone_number,device_model,asset_tag,issued_on,created_by) VALUES(?,?,NULLIF(?,''),NULLIF(?,''),?,?) RETURNING id");
        $s->execute([$agent,$number,$model,$asset,$date,$actor]); mkt_reply(201,true,'Institutional phone assigned.',['id'=>(int)$s->fetchColumn()]);
    }
    if ($action === 'save_performance') {
        $center=mkt_id($input['center_id']??0); $year=filter_var($input['report_year']??null,FILTER_VALIDATE_INT,['options'=>['min_range'=>2000,'max_range'=>2200]]); $month=filter_var($input['report_month']??null,FILTER_VALIDATE_INT,['options'=>['min_range'=>1,'max_range'=>12]]);
        $keys=['student_inquiries','applications_started','students_enrolled','enrollment_target','outreach_activities']; $values=[];
        foreach($keys as $key){$v=mkt_count($input[$key]??null);if($v===false)mkt_reply(422,false,'All monthly indicators must be whole non-negative numbers.');$values[]=$v;}
        $notes=mkt_text($input['notes']??'',4000);
        if(!$center||$year===false||$month===false) mkt_reply(422,false,'Choose a service center and a valid month and year.');
        $s=$pdo->prepare('INSERT INTO mkt_monthly_performance(center_id,report_year,report_month,student_inquiries,applications_started,students_enrolled,enrollment_target,outreach_activities,notes,created_by,updated_by) VALUES(?,?,?,?,?,?,?,?,?,?,?) ON CONFLICT(center_id,report_year,report_month) DO UPDATE SET student_inquiries=EXCLUDED.student_inquiries,applications_started=EXCLUDED.applications_started,students_enrolled=EXCLUDED.students_enrolled,enrollment_target=EXCLUDED.enrollment_target,outreach_activities=EXCLUDED.outreach_activities,notes=EXCLUDED.notes,updated_by=EXCLUDED.updated_by,updated_at=CURRENT_TIMESTAMP RETURNING id');
        $s->execute([$center,$year,$month,...$values,$notes?:null,$actor,$actor]); mkt_reply(200,true,'Monthly performance saved.',['id'=>(int)$s->fetchColumn()]);
    }
    mkt_reply(404,false,'Unknown Marketing action.');
} catch (PDOException $e) {
    $sqlState = (string)($e->errorInfo[0] ?? $e->getCode());
    $detail = preg_replace('/[\r\n\t]+/', ' ', $e->getMessage()) ?: 'No database detail was provided.';
    error_log('Marketing Department API database error ['.$sqlState.']: '.$detail);

    // Give a useful, safe hint to every user and include the SQL error only on localhost.
    $message = match ($sqlState) {
        '42P01' => 'A required PostgreSQL table is missing. Confirm the Marketing tables and campuses table are installed in the database used by the API.',
        '42703' => 'A required PostgreSQL column is missing. Check the shared campuses table columns and rerun the Marketing schema if needed.',
        '23503' => 'The selected campus, service center or agent no longer exists. Refresh and choose an active record.',
        '23505' => 'That service center code, employee number or phone number is already in use.',
        default => 'The Marketing API database query failed. Check the PHP server error log for its SQLSTATE and query detail.',
    };
    $host = strtolower((string)($_SERVER['HTTP_HOST'] ?? ''));
    if (str_starts_with($host, 'localhost') || str_starts_with($host, '127.0.0.1')) {
        $message .= ' (SQLSTATE '.$sqlState.') '.$detail;
    }
    mkt_reply(400,false,$message);
} catch (Throwable $e) {
    error_log('Marketing Department API error: '.$e->getMessage());
    mkt_reply(500,false,'Marketing department request failed. Check the server error log.');
}
