<?php
require __DIR__.'/lib/bootstrap.php';
$r=$_GET['r']??'dashboard';

if($r==='go'){
 $slug=$_GET['slug']??'';$s=db()->prepare('SELECT * FROM tracking_links WHERE slug=? AND active=1 AND (expires_at IS NULL OR expires_at>NOW())');$s->execute([$slug]);$l=$s->fetch();if(!$l){http_response_code(404);exit('Tracking link not found or expired.');}
 $ua=substr($_SERVER['HTTP_USER_AGENT']??'',0,1000);$ip=$_SERVER['REMOTE_ADDR']??'';if(cfg('privacy.trust_cloudflare_headers')&&!empty($_SERVER['HTTP_CF_CONNECTING_IP']))$ip=$_SERVER['HTTP_CF_CONNECTING_IP'];
 $salt=(string)cfg('privacy.ip_hash_salt','change-me');$ipHash=hash('sha256',$ip.$salt);$visitor=hash('sha256',$ipHash.'|'.$ua);$hours=(int)cfg('privacy.unique_window_hours',24);
 $q=db()->prepare('SELECT COUNT(*) FROM clicks WHERE tracking_link_id=? AND visitor_hash=? AND clicked_at>=DATE_SUB(NOW(),INTERVAL ? HOUR)');$q->execute([$l['id'],$visitor,$hours]);$unique=(int)$q->fetchColumn()===0;
 $country=$_SERVER['HTTP_CF_IPCOUNTRY']??null;$region=$_SERVER['HTTP_CF_REGION']??null;$ref=substr($_SERVER['HTTP_REFERER']??'',0,2000);
 $source=$l['source']?:($ref?(parse_url($ref,PHP_URL_HOST)?:'referral'):'direct');
 $q=db()->prepare('INSERT INTO clicks(tracking_link_id,ip_hash,visitor_hash,is_unique,user_agent,device,browser,referrer,source,country,region) VALUES(?,?,?,?,?,?,?,?,?,?,?)');$q->execute([$l['id'],$ipHash,$visitor,$unique,$ua,device($ua),browser($ua),$ref,$source,$country,$region]);
 header('Cache-Control: no-store');header('Location: '.$l['destination_url'],true,302);exit;
}
if($r==='qr'){
 $slug=$_GET['slug']??'';$s=db()->prepare('SELECT slug FROM tracking_links WHERE slug=?');$s->execute([$slug]);if(!$s->fetch()){http_response_code(404);exit;}
 $target=url('t/'.$slug);header('Location: https://api.qrserver.com/v1/create-qr-code/?size=600x600&format=svg&data='.rawurlencode($target));exit;
}

if($r==='client_portal'){
 $slug=strtolower(trim($_GET['slug']??''));$q=db()->prepare("SELECT * FROM clients WHERE LOWER(code)=? AND status='active'");$q->execute([$slug]);$client=$q->fetch();if(!$client){http_response_code(404);exit('Client portal not found. Ask Bright Marketing Solutions to set an English portal code for this client.');}$q=db()->prepare("SELECT ci.*,(SELECT GROUP_CONCAT(CONCAT(cpl.platform_key,'|',cpl.post_url) SEPARATOR ';;') FROM content_platform_links cpl WHERE cpl.content_item_id=ci.id) platform_links FROM content_items ci JOIN projects p ON p.id=ci.project_id WHERE p.client_id=? ORDER BY COALESCE(ci.published_at,ci.scheduled_at,ci.created_at) ASC,CASE WHEN ci.sort_order>0 THEN 0 ELSE 1 END,ci.sort_order");$q->execute([$client['id']]);$planning=[];$posted=[];foreach($q as $x){if(!$x['published_at'])$planning[]=$x;else $posted[]=$x;}$q=db()->prepare('SELECT token,date_from,date_to FROM report_shares WHERE client_id=? ORDER BY id DESC LIMIT 1');$q->execute([$client['id']]);$share=$q->fetch();
 render($client['name'].' Portal',function()use($client,$planning,$posted,$share){?><div class="toolbar"><div style="flex:1"><h1><?=h($client['name'])?> Marketing Portal</h1><p class="muted">Live planning, publishing progress and marketing results from Bright Marketing Solutions.</p></div><?php if($share):?><a class="btn" href="<?=h(route('shared_report',['token'=>$share['token']]))?>">View Marketing Report</a><?php endif;?></div><section class="card"><h2>Marketing Planning</h2><?php if(!$planning):?><p class="muted">No upcoming content currently scheduled.</p><?php else:?><table><tr><th>Planned date</th><th>Title</th><th>Content type</th><th>Planned platforms</th><th>Caption / copy</th><th>Status</th></tr><?php foreach($planning as $x):?><tr><td><?=h($x['scheduled_at']?:'To be confirmed')?></td><td><b><?=h($x['title'])?></b></td><td><?=h(ucwords(str_replace('_',' ',$x['content_type_key']?:$x['item_kind'])))?></td><td><?=h(implode(', ',json_decode($x['platforms_json']??'[]',true)?:[])?:'To be confirmed')?></td><td><?=nl2br(h($x['caption']?:'—'))?></td><td><span class="badge"><?=h($x['status_key']?:'Planning')?></span></td></tr><?php endforeach;?></table><?php endif;?></section><section class="card" style="margin-top:18px"><h2>Posted Content</h2><?php if(!$posted):?><p class="muted">No published content yet.</p><?php else:?><table><tr><th>Published</th><th>Title</th><th>Platforms / live links</th><th>Caption / copy</th><th>Status</th></tr><?php foreach($posted as $x):?><tr><td><?=h($x['published_at']?:$x['scheduled_at'])?></td><td><b><?=h($x['title'])?></b></td><td><?=h($x['published_at']?:'—')?></td><td><?=platformLinksHtml($x)?></td><td><?=nl2br(h($x['caption']?:'—'))?></td><td><span class="badge">Posted</span></td></tr><?php endforeach;?></table><?php endif;?></section><?php });exit;
}

if($r==='register'){
 if(user())go('dashboard');$token=$_GET['token']??($_POST['token']??'');$error='';$invite=null;
 if($token){$q=db()->prepare('SELECT * FROM staff_invites WHERE token_hash=? AND used_at IS NULL AND expires_at>NOW()');$q->execute([hash('sha256',$token)]);$invite=$q->fetch();}
 if($_SERVER['REQUEST_METHOD']==='POST'&&$invite){verify_csrf();$name=trim($_POST['name']??'');$email=strtolower(trim($_POST['email']??''));$password=$_POST['password']??'';
  if(!$name||!filter_var($email,FILTER_VALIDATE_EMAIL)||strlen($password)<10)$error='Please enter a valid name, email and password of at least 10 characters.';
  elseif($invite['email']&&!hash_equals(strtolower($invite['email']),$email))$error='Please register with the email address used for this invitation.';
  else try{db()->beginTransaction();db()->prepare('INSERT INTO users(client_id,name,email,password_hash,role) VALUES(NULL,?,?,?,"staff")')->execute([$name,$email,password_hash($password,PASSWORD_DEFAULT)]);$uid=(int)db()->lastInsertId();db()->prepare('UPDATE staff_invites SET used_at=NOW(),used_by=? WHERE id=?')->execute([$uid,$invite['id']]);db()->commit();flash('Staff account created. You can now sign in.');go('login');}catch(Throwable $e){if(db()->inTransaction())db()->rollBack();$error='This email is already registered.';}
 }
 render('Staff registration',function()use($invite,$token,$error){?><div class="login"><div class="card"><h1>Join Bright Marketing Solutions</h1><?php if(!$invite):?><div class="flash error">This invitation is invalid, expired or already used.</div><?php else:?><?php if($error):?><div class="flash error"><?=h($error)?></div><?php endif;?><p class="muted">Create your staff account. Staff can see all clients by default; an admin can restrict selected clients.</p><form method="post"><input type="hidden" name="_csrf" value="<?=csrf()?>"><input type="hidden" name="token" value="<?=h($token)?>"><div class="field"><label>Name</label><input name="name" required autofocus></div><div class="field"><label>Email</label><input type="email" name="email" value="<?=h($invite['email'])?>" required></div><div class="field"><label>Password</label><input type="password" name="password" minlength="10" required></div><button class="btn">Create staff account</button></form><?php endif;?></div></div><?php });exit;
}

if($r==='shared_report'){
 $token=$_GET['token']??'';$q=db()->prepare('SELECT rs.*,c.name client_name,u.name generated_by FROM report_shares rs JOIN clients c ON c.id=rs.client_id JOIN users u ON u.id=rs.created_by WHERE rs.token=? AND (rs.expires_at IS NULL OR rs.expires_at>NOW())');$q->execute([$token]);$share=$q->fetch();if(!$share){http_response_code(404);exit('Report link not found or expired.');}if(!empty($_GET['from'])&&preg_match('/^\d{4}-\d{2}-\d{2}$/',$_GET['from']))$share['date_from']=$_GET['from'];if(!empty($_GET['to'])&&preg_match('/^\d{4}-\d{2}-\d{2}$/',$_GET['to']))$share['date_to']=$_GET['to'];$where=' AND p.client_id=? AND COALESCE(DATE(ci.published_at),pe.metric_date) BETWEEN ? AND ?';$args=[$share['client_id'],$share['date_from'],$share['date_to']];$m=metricTotals($where,$args);$q=db()->prepare("SELECT ci.title,ci.platforms_json,SUM(pe.reach) reach,SUM(pe.impressions) impressions,SUM(pe.views) views,SUM(pe.likes+pe.comments+pe.shares+pe.saves) engagement,SUM(pe.clicks) clicks,SUM(pe.leads) leads FROM performance_entries pe JOIN projects p ON p.id=pe.project_id LEFT JOIN content_items ci ON ci.id=pe.content_item_id WHERE p.client_id=? AND COALESCE(DATE(ci.published_at),pe.metric_date) BETWEEN ? AND ? GROUP BY pe.content_item_id,ci.title,ci.platforms_json ORDER BY ci.title");$q->execute($args);$rows=$q->fetchAll();
 $rows=reportContentRows((int)$share['client_id'],$share['date_from'],$share['date_to']);render('Marketing Report',function()use($share,$m,$rows){?><div class="toolbar no-print"><button class="btn" onclick="window.print()">Export PDF</button></div><div class="card"><h1><?=h($share['client_name'])?> | Marketing Report</h1><p><b>Report Range:</b> <?=h($share['date_from'])?> to <?=h($share['date_to'])?><br><b>Report Generated By:</b> <?=h($share['generated_by'])?>, Bright Marketing Solutions</p><div class="grid"><?php foreach(['Reach'=>$m['reach'],'Impressions'=>$m['impressions'],'Views'=>$m['views'],'Engagement'=>$m['engagement'],'Leads'=>$m['leads']] as $k=>$v):?><div class="metric"><span><?=h($k)?></span><b><?=h($v)?></b></div><?php endforeach;?><?php if($share['include_financial']):?><div class="metric"><span>Sales</span><b>RM <?=number_format((float)$m['sales'],2)?></b></div><div class="metric"><span>Ads Expenses</span><b>RM <?=number_format((float)$m['spend'],2)?></b></div><?php endif;?></div><h2>Content Insights</h2><table><tr><th>Posting</th><th>Published date</th><th>Platforms</th><th>Reach</th><th>Views</th><th>Engagement</th><th>Clicks</th><th>Leads</th></tr><?php foreach($rows as $x):?><tr><td><?=h($x['title']?:'Project total')?></td><td><?=h($x['published_at']?:'—')?></td><td><?=platformLinksHtml($x)?></td><td><?=number_format($x['reach'])?></td><td><?=number_format($x['views'])?></td><td><?=number_format($x['engagement'])?></td><td><?=number_format($x['clicks'])?></td><td><?=number_format($x['leads'])?></td></tr><?php endforeach;?></table><?php if($share['consultant_comment']):?><h2>Comment from Bright Marketing Solutions</h2><div class="card"><?=nl2br(h($share['consultant_comment']))?></div><?php endif;?></div><?php });exit;
}

if($r==='login'){
 if(user())go('dashboard');$error='';if($_SERVER['REQUEST_METHOD']==='POST'){verify_csrf();$s=db()->prepare('SELECT * FROM users WHERE email=? AND active=1');$s->execute([trim(strtolower($_POST['email']??''))]);$u=$s->fetch();if($u&&password_verify($_POST['password']??'',$u['password_hash'])){unset($u['password_hash']);$_SESSION['user']=$u;if(!empty($_POST['remember']))setRememberCookie((int)$u['id']);db()->prepare('UPDATE users SET last_login_at=NOW() WHERE id=?')->execute([$u['id']]);go('dashboard');}$error='Invalid email or password.';}
 render('Sign in',function()use($error){?><div class="login"><div class="card"><h1>Bright Marketing Solutions Tracker Portal</h1><p class="muted">Sign in to continue.</p><?php if($error):?><div class="flash error"><?=h($error)?></div><?php endif;?><form method="post"><input type="hidden" name="_csrf" value="<?=csrf()?>"><div class="field"><label>Email</label><input type="email" name="email" required autofocus></div><div class="field"><label>Password</label><input type="password" name="password" required></div><label><input type="checkbox" name="remember" value="1"> Remember me for 30 days</label><br><br><button class="btn">Sign in</button></form></div></div><?php });exit;
}
if($r==='logout'){clearRememberCookie();session_destroy();header('Location: '.route('login'));exit;}
require_login();

if($r==='meta_connections'){
 require_role(['admin']);
 db()->exec("CREATE TABLE IF NOT EXISTS meta_pages (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,integration_id BIGINT UNSIGNED NOT NULL,client_id BIGINT UNSIGNED NULL,page_id VARCHAR(100) NOT NULL,page_name VARCHAR(255) NOT NULL,category VARCHAR(255) NULL,page_token_encrypted TEXT NULL,followers BIGINT NOT NULL DEFAULT 0,fan_count BIGINT NOT NULL DEFAULT 0,instagram_account_id VARCHAR(100) NULL,status ENUM('connected','error','disabled') NOT NULL DEFAULT 'connected',last_error TEXT NULL,last_synced_at DATETIME NULL,created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,UNIQUE KEY uq_meta_page(page_id),INDEX idx_meta_page_client(client_id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
 $pc=db()->query("SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='performance_entries' AND COLUMN_NAME='platform_key'")->fetchColumn();if(!(int)$pc){db()->exec("ALTER TABLE performance_entries ADD COLUMN platform_key VARCHAR(50) NOT NULL DEFAULT 'manual' AFTER metric_date");try{db()->exec("ALTER TABLE performance_entries DROP INDEX uq_content_date");}catch(Throwable $e){}db()->exec("ALTER TABLE performance_entries ADD UNIQUE KEY uq_content_date_platform(project_id,content_item_id,metric_date,platform_key)");}
 $appId=(string)cfg('meta.app_id','2081328052453718');$secret=(string)cfg('meta.app_secret','');
 $ready=$appId!==''&&$secret!==''&&$secret!=='PASTE_META_APP_SECRET_HERE';
 $callback=route('meta_callback');
 if($_SERVER['REQUEST_METHOD']==='POST'){
  verify_csrf();$action=$_POST['action']??'';$version=(string)cfg('meta.graph_version','v26.0');
  if($action==='assign'){$pageId=(int)($_POST['page_row_id']??0);$clientId=(int)($_POST['client_id']??0)?:null;db()->prepare('UPDATE meta_pages SET client_id=? WHERE id=?')->execute([$clientId,$pageId]);flash('Facebook Page assignment saved.');go('meta_connections');}
  if($action==='sync_pages'){
   $accounts=db()->query("SELECT * FROM integrations WHERE provider='meta_account' AND status='connected' ORDER BY id")->fetchAll();$found=0;$errors=[];
   foreach($accounts as $account){$token=base64_decode((string)$account['credentials_encrypted'],true);if(!$token)continue;$next='https://graph.facebook.com/'.$version.'/me/accounts?'.http_build_query(['fields'=>'id,name,category,access_token,fan_count,followers_count,instagram_business_account','limit'=>100,'access_token'=>$token]);
    while($next){$raw=@file_get_contents($next);$data=json_decode((string)$raw,true);if(!empty($data['error'])){$errors[]=$data['error']['message']??'Meta API error';break;}foreach(($data['data']??[]) as $page){$pageId=(string)($page['id']??'');if($pageId==='')continue;$q=db()->prepare("INSERT INTO meta_pages(integration_id,page_id,page_name,category,page_token_encrypted,followers,fan_count,instagram_account_id,status,last_error,last_synced_at) VALUES(?,?,?,?,?,?,?,?, 'connected',NULL,NOW()) ON DUPLICATE KEY UPDATE integration_id=VALUES(integration_id),page_name=VALUES(page_name),category=VALUES(category),page_token_encrypted=VALUES(page_token_encrypted),followers=VALUES(followers),fan_count=VALUES(fan_count),instagram_account_id=VALUES(instagram_account_id),status='connected',last_error=NULL,last_synced_at=NOW()");$q->execute([$account['id'],$pageId,$page['name']??'Facebook Page',$page['category']??null,base64_encode((string)($page['access_token']??'')),(int)($page['followers_count']??0),(int)($page['fan_count']??0),$page['instagram_business_account']['id']??null]);$found++;}$next=$data['paging']['next']??null;}
    db()->prepare('UPDATE integrations SET last_sync_at=NOW() WHERE id=?')->execute([$account['id']]);
   }
   flash($errors?'Pages partially synced: '.implode(' · ',$errors):$found.' Facebook Pages synced.',$errors?'error':'success');go('meta_connections');
  }
  if($action==='sync_totals'){$pages=db()->query("SELECT * FROM meta_pages WHERE client_id IS NOT NULL AND status='connected'")->fetchAll();$saved=0;foreach($pages as $page){$token=base64_decode((string)$page['page_token_encrypted'],true);if(!$token)continue;$raw=@file_get_contents('https://graph.facebook.com/'.$version.'/'.$page['page_id'].'?'.http_build_query(['fields'=>'fan_count,followers_count','access_token'=>$token]));$data=json_decode((string)$raw,true);if(!empty($data['error'])){db()->prepare("UPDATE meta_pages SET status='error',last_error=? WHERE id=?")->execute([$data['error']['message']??'Meta API error',$page['id']]);continue;}$followers=(int)($data['followers_count']??$data['fan_count']??0);db()->prepare("INSERT INTO daily_platform_stats(client_id,stat_date,platform_key,metric_key,metric_value,notes,entered_by) VALUES(?,CURDATE(),'facebook','followers',?,'Synced from Meta',?) ON DUPLICATE KEY UPDATE metric_value=VALUES(metric_value),notes=VALUES(notes),entered_by=VALUES(entered_by),updated_at=NOW()")->execute([$page['client_id'],$followers,user()['id']]);db()->prepare("UPDATE meta_pages SET followers=?,fan_count=?,status='connected',last_error=NULL,last_synced_at=NOW() WHERE id=?")->execute([$followers,(int)($data['fan_count']??0),$page['id']]);$saved++;}flash($saved.' assigned Facebook Pages updated in Daily Updates.');go('meta_connections');}
  if($action==='sync_linked_posts'){
   $q=db()->query("SELECT cpl.id link_id,cpl.content_item_id,cpl.post_url,ci.project_id,ci.published_at,p.client_id,mp.page_id,mp.page_token_encrypted FROM content_platform_links cpl JOIN content_items ci ON ci.id=cpl.content_item_id JOIN projects p ON p.id=ci.project_id LEFT JOIN meta_pages mp ON mp.client_id=p.client_id AND mp.status='connected' WHERE cpl.platform_key='facebook' AND cpl.post_url<>'' ORDER BY cpl.id");$links=$q->fetchAll();$synced=0;$failed=0;
   foreach($links as $link){$token=base64_decode((string)$link['page_token_encrypted'],true);if(!$token){db()->prepare("UPDATE content_platform_links SET sync_status='error',last_error='Assign the client Facebook Page in Meta Connections first' WHERE id=?")->execute([$link['link_id']]);$failed++;continue;}$url=(string)$link['post_url'];$objectId='';if(preg_match('~/(?:reel|videos|posts)/([0-9]+)~i',$url,$m))$objectId=$m[1];elseif(preg_match('~story_fbid=([0-9]+).*?[?&]id=([0-9]+)~i',$url,$m))$objectId=$m[2].'_'.$m[1];
    $fields='id,created_time,permalink_url,message,shares,reactions.limit(0).summary(true),comments.limit(0).summary(true)';$post=[];if($objectId){$raw=@file_get_contents('https://graph.facebook.com/'.$version.'/'.$objectId.'?'.http_build_query(['fields'=>$fields,'access_token'=>$token]));$post=json_decode((string)$raw,true)?:[];}
    if(!$objectId||!empty($post['error'])){$feedRaw=@file_get_contents('https://graph.facebook.com/'.$version.'/'.$link['page_id'].'/published_posts?'.http_build_query(['fields'=>$fields,'limit'=>100,'access_token'=>$token]));$feed=json_decode((string)$feedRaw,true)?:[];$needle=preg_match('~([0-9]{8,})~',$url,$nm)?$nm[1]:'';$post=[];foreach(($feed['data']??[]) as $candidate){if(($candidate['permalink_url']??'')===$url||($needle!==''&&(str_contains((string)($candidate['permalink_url']??''),$needle)||str_contains((string)($candidate['id']??''),$needle)))){$post=$candidate;break;}}$objectId=(string)($post['id']??'');}
    if(!$objectId||!empty($post['error'])){$message=$post['error']['message']??'The Facebook post could not be matched. Check that the link belongs to the assigned Page.';db()->prepare("UPDATE content_platform_links SET sync_status='error',last_error=?,last_synced_at=NOW() WHERE id=?")->execute([$message,$link['link_id']]);$failed++;continue;}
    $metrics=['post_impressions'=>0,'post_impressions_unique'=>0,'post_engaged_users'=>0,'post_clicks'=>0];$insRaw=@file_get_contents('https://graph.facebook.com/'.$version.'/'.$objectId.'/insights?'.http_build_query(['metric'=>implode(',',array_keys($metrics)),'access_token'=>$token]));$ins=json_decode((string)$insRaw,true)?:[];foreach(($ins['data']??[]) as $entry){$v=$entry['values'][0]['value']??0;if(is_array($v))$v=array_sum(array_map('intval',$v));$metrics[$entry['name']]=(int)$v;}$video=[];$vidRaw=@file_get_contents('https://graph.facebook.com/'.$version.'/'.$objectId.'/video_insights?'.http_build_query(['metric'=>'total_video_views,total_video_impressions','access_token'=>$token]));$vid=json_decode((string)$vidRaw,true)?:[];foreach(($vid['data']??[]) as $entry){$v=$entry['values'][0]['value']??0;if(is_array($v))$v=array_sum(array_map('intval',$v));$video[$entry['name']]=(int)$v;}
    $metricDate=!empty($post['created_time'])?date('Y-m-d',strtotime($post['created_time'])):(!empty($link['published_at'])?date('Y-m-d',strtotime($link['published_at'])):date('Y-m-d'));$likes=(int)($post['reactions']['summary']['total_count']??0);$comments=(int)($post['comments']['summary']['total_count']??0);$shares=(int)($post['shares']['count']??0);$reach=(int)($metrics['post_impressions_unique']??0);$impressions=(int)($metrics['post_impressions']??($video['total_video_impressions']??0));$views=(int)($video['total_video_views']??0);$clicks=(int)($metrics['post_clicks']??0);
    db()->prepare("INSERT INTO performance_entries(project_id,content_item_id,metric_date,platform_key,reach,impressions,views,likes,comments,shares,saves,clicks,leads,sales,ad_spend,notes,created_by) VALUES(?,?,?,'facebook',?,?,?,?,?,?,0,?,0,0,0,'Automatically synced from Meta',?) ON DUPLICATE KEY UPDATE reach=VALUES(reach),impressions=VALUES(impressions),views=VALUES(views),likes=VALUES(likes),comments=VALUES(comments),shares=VALUES(shares),clicks=VALUES(clicks),notes=VALUES(notes)")->execute([$link['project_id'],$link['content_item_id'],$metricDate,$reach,$impressions,$views,$likes,$comments,$shares,$clicks,user()['id']]);db()->prepare("UPDATE content_platform_links SET sync_status='connected',last_error=NULL,last_synced_at=NOW() WHERE id=?")->execute([$link['link_id']]);$synced++;
   }
   $iq=db()->query("SELECT cpl.id link_id,cpl.content_item_id,cpl.post_url,ci.project_id,ci.published_at,mp.instagram_account_id,mp.page_token_encrypted FROM content_platform_links cpl JOIN content_items ci ON ci.id=cpl.content_item_id JOIN projects p ON p.id=ci.project_id LEFT JOIN meta_pages mp ON mp.client_id=p.client_id AND mp.status='connected' WHERE cpl.platform_key='instagram' AND cpl.post_url<>'' ORDER BY cpl.id");
   foreach($iq as $link){$token=base64_decode((string)$link['page_token_encrypted'],true);$ig=(string)$link['instagram_account_id'];if(!$token||!$ig){db()->prepare("UPDATE content_platform_links SET sync_status='error',last_error='Connect this Instagram professional account to its assigned Facebook Page first' WHERE id=?")->execute([$link['link_id']]);$failed++;continue;}$short='';if(preg_match('~instagram\.com/(?:p|reel)/([^/?#]+)~i',$link['post_url'],$m))$short=$m[1];$raw=@file_get_contents('https://graph.facebook.com/'.$version.'/'.$ig.'/media?'.http_build_query(['fields'=>'id,permalink,timestamp,media_type,like_count,comments_count','limit'=>100,'access_token'=>$token]));$media=json_decode((string)$raw,true)?:[];$match=[];foreach(($media['data']??[]) as $candidate)if(($short&&str_contains((string)($candidate['permalink']??''),'/'.$short.'/'))||($candidate['permalink']??'')===$link['post_url']){$match=$candidate;break;}if(!$match){db()->prepare("UPDATE content_platform_links SET sync_status='error',last_error='Instagram post was not found in the connected professional account',last_synced_at=NOW() WHERE id=?")->execute([$link['link_id']]);$failed++;continue;}$names='reach,views,impressions,total_interactions,likes,comments,shares,saved';$iraw=@file_get_contents('https://graph.facebook.com/'.$version.'/'.$match['id'].'/insights?'.http_build_query(['metric'=>$names,'access_token'=>$token]));$idata=json_decode((string)$iraw,true)?:[];$im=[];foreach(($idata['data']??[]) as $entry){$v=$entry['values'][0]['value']??($entry['total_value']['value']??0);$im[$entry['name']]=(int)$v;}$date=!empty($match['timestamp'])?date('Y-m-d',strtotime($match['timestamp'])):(!empty($link['published_at'])?date('Y-m-d',strtotime($link['published_at'])):date('Y-m-d'));$likes=(int)($im['likes']??$match['like_count']??0);$comments=(int)($im['comments']??$match['comments_count']??0);db()->prepare("INSERT INTO performance_entries(project_id,content_item_id,metric_date,platform_key,reach,impressions,views,likes,comments,shares,saves,clicks,leads,sales,ad_spend,notes,created_by) VALUES(?,?,?,'instagram',?,?,?,?,?,?,?,0,0,0,0,'Automatically synced from Instagram',?) ON DUPLICATE KEY UPDATE reach=VALUES(reach),impressions=VALUES(impressions),views=VALUES(views),likes=VALUES(likes),comments=VALUES(comments),shares=VALUES(shares),saves=VALUES(saves),notes=VALUES(notes)")->execute([$link['project_id'],$link['content_item_id'],$date,(int)($im['reach']??0),(int)($im['impressions']??0),(int)($im['views']??0),$likes,$comments,(int)($im['shares']??0),(int)($im['saved']??0),user()['id']]);db()->prepare("UPDATE content_platform_links SET sync_status='connected',last_error=NULL,last_synced_at=NOW() WHERE id=?")->execute([$link['link_id']]);$synced++;}
   flash($synced.' linked Facebook / Instagram posts synced'.($failed?' · '.$failed.' need attention':''),$failed?'error':'success');go('meta_connections');
  }
 }
 $q=db()->prepare("SELECT * FROM integrations WHERE provider='meta_account' ORDER BY id DESC");$q->execute();$connections=$q->fetchAll();
 $clients=db()->query("SELECT id,name FROM clients WHERE status='active' ORDER BY name")->fetchAll();$pages=db()->query("SELECT mp.*,c.name client_name FROM meta_pages mp LEFT JOIN clients c ON c.id=mp.client_id ORDER BY mp.page_name")->fetchAll();
 render('Meta Connections',function()use($appId,$ready,$callback,$connections,$clients,$pages){?><div class="toolbar"><div style="flex:1"><h1>Meta Connections</h1><p class="muted">Connect administrator accounts, sync every managed Page, then assign each Page to its client.</p></div><?php if($ready):?><a class="btn gray" href="<?=h(route('meta_connect'))?>">Connect another account</a><?php endif;?></div><div class="toolbar"><form method="post"><input type="hidden" name="_csrf" value="<?=csrf()?>"><button class="btn" name="action" value="sync_pages">Sync Facebook Pages</button></form><form method="post"><input type="hidden" name="_csrf" value="<?=csrf()?>"><button class="btn" name="action" value="sync_totals">Update assigned Page data</button></form></div><section class="card"><h2>Facebook Pages (<?=number_format(count($pages))?>)</h2><?php if(!$pages):?><p class="muted">No Pages loaded yet. Click <b>Sync Facebook Pages</b>.</p><?php else:?><table><tr><th>Page</th><th>Followers</th><th>Instagram</th><th>Assigned client</th><th>Last sync</th></tr><?php foreach($pages as $page):?><tr><td><b><?=h($page['page_name'])?></b><br><small class="muted"><?=h($page['category']?:'Facebook Page')?></small><?php if($page['last_error']):?><br><small class="error"><?=h($page['last_error'])?></small><?php endif;?></td><td><?=number_format((int)($page['followers']?:$page['fan_count']))?></td><td><?=h($page['instagram_account_id']?'Connected':'—')?></td><td><form method="post" class="toolbar"><input type="hidden" name="_csrf" value="<?=csrf()?>"><input type="hidden" name="action" value="assign"><input type="hidden" name="page_row_id" value="<?=$page['id']?>"><select name="client_id"><option value="">Not assigned</option><?php foreach($clients as $client):?><option value="<?=$client['id']?>" <?=(int)$page['client_id']===(int)$client['id']?'selected':''?>><?=h($client['name'])?></option><?php endforeach;?></select><button class="btn small">Save</button></form></td><td><?=h($page['last_synced_at']?:'—')?></td></tr><?php endforeach;?></table><?php endif;?></section><section class="card" style="margin-top:18px"><h2>Connected administrator accounts</h2><?php if(!$connections):?><p class="muted">No Facebook administrator account connected yet.</p><?php else:?><table><tr><th>Account</th><th>Status</th><th>Last sync</th></tr><?php foreach($connections as $x):$c=json_decode($x['config_json']??'{}',true)?:[];?><tr><td><?=h($c['name']??'Facebook account')?></td><td><span class="badge"><?=h($x['status'])?></span></td><td><?=h($x['last_sync_at']?:'Not synced yet')?></td></tr><?php endforeach;?></table><?php endif;?></section><?php });exit;
}

if($r==='meta_connect'){
 require_role(['admin']);$appId=(string)cfg('meta.app_id','2081328052453718');$secret=(string)cfg('meta.app_secret','');
 if($secret===''||$secret==='PASTE_META_APP_SECRET_HERE'){flash('Meta App Secret is not configured yet.','error');go('meta_connections');}
 $_SESSION['meta_oauth_state']=bin2hex(random_bytes(24));$params=['client_id'=>$appId,'redirect_uri'=>route('meta_callback'),'state'=>$_SESSION['meta_oauth_state'],'response_type'=>'code','scope'=>'pages_show_list,pages_read_engagement,read_insights,business_management'];
 header('Location: https://www.facebook.com/v26.0/dialog/oauth?'.http_build_query($params));exit;
}

if($r==='meta_callback'){
 require_role(['admin']);$state=(string)($_GET['state']??'');if(!$state||!hash_equals((string)($_SESSION['meta_oauth_state']??''),$state)){http_response_code(400);exit('Invalid Meta authorization state.');}unset($_SESSION['meta_oauth_state']);
 if(!empty($_GET['error'])){flash('Facebook authorization was cancelled or denied.','error');go('meta_connections');}
 $code=(string)($_GET['code']??'');$appId=(string)cfg('meta.app_id');$secret=(string)cfg('meta.app_secret');$callback=route('meta_callback');$version=(string)cfg('meta.graph_version','v26.0');
 $tokenUrl='https://graph.facebook.com/'.$version.'/oauth/access_token?'.http_build_query(['client_id'=>$appId,'client_secret'=>$secret,'redirect_uri'=>$callback,'code'=>$code]);$raw=@file_get_contents($tokenUrl);$token=json_decode((string)$raw,true);if(empty($token['access_token'])){flash('Meta authorization could not be completed.','error');go('meta_connections');}
 $access=$token['access_token'];$meRaw=@file_get_contents('https://graph.facebook.com/'.$version.'/me?'.http_build_query(['fields'=>'id,name','access_token'=>$access]));$me=json_decode((string)$meRaw,true)?:[];$payload=base64_encode($access);
 $q=db()->prepare("INSERT INTO integrations(provider,credentials_encrypted,config_json,status,last_sync_at) VALUES('meta_account',?,?, 'connected',NOW())");$q->execute([$payload,json_encode(['id'=>$me['id']??null,'name'=>$me['name']??'Facebook administrator'],JSON_UNESCAPED_UNICODE)]);flash('Facebook administrator account connected.');go('meta_connections');
}

if($r==='item_reorder'){
 require_role(['admin','staff']);if(!canDo('edit')){http_response_code(403);exit('Edit permission required.');}if($_SERVER['REQUEST_METHOD']!=='POST'){http_response_code(405);exit;}verify_csrf();$projectId=(int)($_POST['project_id']??0);$ids=array_values(array_unique(array_filter(array_map('intval',explode(',',(string)($_POST['order']??''))))));if(!$projectId||!$ids){http_response_code(422);exit('Missing project or item order.');}$update=db()->prepare('UPDATE content_items SET sort_order=? WHERE project_id=? AND id=?');db()->beginTransaction();try{$order=10;foreach($ids as $itemId){$update->execute([$order,$projectId,$itemId]);$order+=10;}db()->commit();header('Content-Type: application/json');echo json_encode(['ok'=>true]);}catch(Throwable $e){if(db()->inTransaction())db()->rollBack();http_response_code(500);echo json_encode(['ok'=>false,'message'=>'Database update failed']);}exit;
}

if($r==='record_delete'){
 if(!canDo('delete')){http_response_code(403);exit('Delete permission is not enabled for this user.');}
 require_role(['admin','staff']);if($_SERVER['REQUEST_METHOD']!=='POST'){http_response_code(405);exit;}verify_csrf();$type=$_POST['type']??'';$id=(int)($_POST['id']??0);if($type==='item'&&$id){$q=db()->prepare('SELECT project_id FROM content_items WHERE id=?');$q->execute([$id]);$projectId=(int)$q->fetchColumn();if($projectId){$q=db()->prepare('SELECT stored_name FROM attachments WHERE content_item_id=?');$q->execute([$id]);foreach($q->fetchAll(PDO::FETCH_COLUMN) as $name){$path=__DIR__.'/uploads/'.basename($name);if(is_file($path))@unlink($path);}db()->beginTransaction();try{db()->prepare('DELETE FROM attachments WHERE content_item_id=?')->execute([$id]);db()->prepare('DELETE FROM performance_entries WHERE content_item_id=?')->execute([$id]);db()->prepare('DELETE FROM content_platform_links WHERE content_item_id=?')->execute([$id]);db()->prepare('UPDATE tracking_links SET content_item_id=NULL WHERE content_item_id=?')->execute([$id]);db()->prepare('DELETE FROM content_items WHERE id=?')->execute([$id]);db()->commit();flash('Item deleted.');}catch(Throwable $e){if(db()->inTransaction())db()->rollBack();flash('This item could not be deleted because related records still exist.','error');}go('project',['id'=>$projectId]);}}if($type==='project'&&$id){require_role(['admin']);try{deleteProjectData($id);flash('Client Project deleted.');}catch(Throwable $e){flash('Client Project could not be deleted.','error');}go('projects');}if($type==='client'&&$id){require_role(['admin']);try{deleteClientData($id,true);flash('Client and related data deleted.');}catch(Throwable $e){flash('Client could not be deleted.','error');}go('clients');}if($type==='clear_client'&&$id){require_role(['admin']);try{deleteClientData($id,false);flash('Client data cleared.');}catch(Throwable $e){flash('Client data could not be cleared.','error');}go('clients');}flash('Record not found.','error');go('projects');
}

if($r==='performance_manage'){
 require_role(['admin','staff']);$contentId=(int)($_REQUEST['content_id']??0);$date=$_REQUEST['date']??'';$q=db()->prepare('SELECT pe.*,ci.title,p.client_id FROM performance_entries pe JOIN content_items ci ON ci.id=pe.content_item_id JOIN projects p ON p.id=ci.project_id WHERE pe.content_item_id=? AND pe.metric_date=?');$q->execute([$contentId,$date]);$entry=$q->fetch();if(!$entry){http_response_code(404);exit('Insight entry not found');}if($_SERVER['REQUEST_METHOD']==='POST'){verify_csrf();if(($_POST['action']??'save')==='delete'){db()->prepare('DELETE FROM performance_entries WHERE id=?')->execute([$entry['id']]);flash('Insight entry deleted.');go('content',['id'=>$contentId]);}$fields=['reach','impressions','views','likes','comments','shares','saves','clicks','leads','sales','ad_spend'];$vals=[];foreach($fields as $f)$vals[]=(float)($_POST[$f]??0);$vals[]=trim($_POST['notes']??'');$vals[]=$entry['id'];db()->prepare('UPDATE performance_entries SET reach=?,impressions=?,views=?,likes=?,comments=?,shares=?,saves=?,clicks=?,leads=?,sales=?,ad_spend=?,notes=? WHERE id=?')->execute($vals);flash('Insight entry updated.');go('content',['id'=>$contentId]);}render('Edit insight',function()use($entry,$contentId,$date){?><h1>Edit insight</h1><p class="muted"><?=h($entry['title'])?> · <?=h($date)?></p><form method="post" class="card"><input type="hidden" name="_csrf" value="<?=csrf()?>"><input type="hidden" name="content_id" value="<?=$contentId?>"><input type="hidden" name="date" value="<?=h($date)?>"><div class="grid"><?php foreach(['reach','impressions','views','likes','comments','shares','saves','clicks','leads','sales','ad_spend'] as $f):?><div class="field"><label><?=h(ucwords(str_replace('_',' ',$f)))?></label><input type="number" step="0.01" min="0" name="<?=$f?>" value="<?=h($entry[$f])?>"></div><?php endforeach;?></div><div class="field"><label>Notes</label><textarea name="notes"><?=h($entry['notes'])?></textarea></div><button class="btn" name="action" value="save">Save changes</button> <button class="btn gray" name="action" value="delete" onclick="return confirm('Delete this insight entry? This cannot be undone.')">Delete insight</button> <a class="btn gray" href="<?=h(route('content',['id'=>$contentId]))?>">Cancel</a></form><?php });exit;
}

if($r==='upgrade_v20'){
 require_role(['admin']);$messages=[];foreach(["ALTER TABLE clients ADD COLUMN social_links_json JSON NULL AFTER phone","ALTER TABLE content_items ADD COLUMN sort_order INT NOT NULL DEFAULT 0 AFTER notes"] as $sql){try{db()->exec($sql);$messages[]='Database field added.';}catch(Throwable $e){$messages[]='Field already exists.';}}db()->exec("UPDATE content_items SET scheduled_at=COALESCE(scheduled_at,published_at),published_at=NULL WHERE published_at IS NOT NULL AND COALESCE(status_key,'') NOT IN ('published','completed')");db()->exec("INSERT INTO settings(setting_group,setting_key,label,value,sort_order,active) VALUES ('system_settings','reminder_email_enabled','Daily publishing email reminder','0',210,1),('system_settings','reminder_email_recipients','Reminder email recipients','',220,1) ON DUPLICATE KEY UPDATE label=VALUES(label)");render('Version 2.0 upgrade',function()use($messages){?><div class="card"><h1>Version 2.0 database update completed</h1><?php foreach($messages as $m):?><p><?=h($m)?></p><?php endforeach;?><a class="btn" href="<?=h(route('dashboard'))?>">Continue</a></div><?php });exit;
}

if($r==='reminder_settings'){
 require_role(['admin']);$keys=['reminder_email_enabled','reminder_email_recipients','reminder_email_from','reminder_email_time_morning','reminder_email_time_evening'];foreach($keys as $i=>$key){$default='';if($key==='reminder_email_from')$default='brandbuilders.consulting21@gmail.com';elseif($key==='reminder_email_time_morning')$default='09:30';elseif($key==='reminder_email_time_evening')$default='18:00';db()->prepare("INSERT INTO settings(setting_group,setting_key,label,value,sort_order,active) VALUES('system_settings',?,?,?,?,1) ON DUPLICATE KEY UPDATE label=VALUES(label)")->execute([$key,ucwords(str_replace('_',' ',$key)),$default,230+$i]);}
 if($_SERVER['REQUEST_METHOD']==='POST'){verify_csrf();foreach($keys as $key){$value=$key==='reminder_email_enabled'?(!empty($_POST[$key])?'1':'0'):trim($_POST[$key]??'');db()->prepare("UPDATE settings SET value=? WHERE setting_group='system_settings' AND setting_key=?")->execute([$value,$key]);}if(!empty($_POST['send_now'])){$q=db()->query("SELECT c.name client_name,ci.title,ci.scheduled_at,ci.published_at FROM content_items ci JOIN projects p ON p.id=ci.project_id JOIN clients c ON c.id=p.client_id WHERE DATE(ci.scheduled_at) BETWEEN CURDATE() AND DATE_ADD(CURDATE(),INTERVAL 3 DAY) ORDER BY ci.scheduled_at,c.name");$today=[];$next=[];foreach($q as $x){$line=$x['client_name'].' - '.$x['title'].' ('.$x['scheduled_at'].') - '.($x['published_at']?'Published':'Upcoming');if(date('Y-m-d',strtotime($x['scheduled_at']))===date('Y-m-d'))$today[]=$line;else $next[]=$line;}$body="TODAY - UPCOMING / PUBLISHED\n".($today?implode("\n",$today):'No content scheduled.')."\n\nNEXT 3 DAYS\n".($next?implode("\n",$next):'No upcoming content.');$recipients=trim($_POST['reminder_email_recipients']??'');$from=trim($_POST['reminder_email_from']??'brandbuilders.consulting21@gmail.com');if($recipients)@mail($recipients,'Bright Marketing Solutions - Content schedule',$body,"From: Bright Marketing Solutions <".$from.">\r\nReply-To: ".$from."\r\nContent-Type: text/plain; charset=UTF-8");}flash('Email reminder settings saved.');go('reminder_settings');}
 $values=[];$q=db()->query("SELECT setting_key,value FROM settings WHERE setting_group='system_settings'");foreach($q as $x)$values[$x['setting_key']]=$x['value'];render('Email reminder settings',function()use($values){?><h1>Email notification</h1><form method="post" class="card"><input type="hidden" name="_csrf" value="<?=csrf()?>"><label><input type="checkbox" name="reminder_email_enabled" value="1" <?=($values['reminder_email_enabled']??'0')==='1'?'checked':''?>> Enable scheduled publishing reminders</label><div class="grid"><div class="field"><label>Sender email</label><input type="email" name="reminder_email_from" value="<?=h($values['reminder_email_from']??'brandbuilders.consulting21@gmail.com')?>"></div><div class="field"><label>Team recipient email(s)</label><input name="reminder_email_recipients" value="<?=h($values['reminder_email_recipients']??'')?>" placeholder="team@example.com, manager@example.com"></div><div class="field"><label>Morning delivery</label><input type="time" name="reminder_email_time_morning" value="<?=h($values['reminder_email_time_morning']??'09:30')?>"></div><div class="field"><label>Evening delivery</label><input type="time" name="reminder_email_time_evening" value="<?=h($values['reminder_email_time_evening']??'18:00')?>"></div></div><button class="btn">Save schedule</button> <button class="btn gray" name="send_now" value="1">Save & send test now</button><p class="muted">Email includes today’s upcoming/published content and the next 3 days of scheduled content. Hosting cron is set separately to run at 9:30 AM and 6:00 PM.</p></form><?php });exit;
}

if($r==='upgrade_v21'){
 require_role(['admin']);try{db()->exec('ALTER TABLE clients ADD COLUMN account_manager_id BIGINT UNSIGNED NULL AFTER social_links_json');}catch(Throwable $e){}db()->exec("CREATE TABLE IF NOT EXISTS report_history (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,client_id BIGINT UNSIGNED NOT NULL,date_from DATE NOT NULL,date_to DATE NOT NULL,created_by BIGINT UNSIGNED NOT NULL,created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,UNIQUE KEY uq_client_period(client_id,date_from,date_to),INDEX idx_report_history_client(client_id,created_at)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");db()->exec('INSERT IGNORE INTO report_history(client_id,date_from,date_to,created_by,created_at) SELECT client_id,date_from,date_to,created_by,created_at FROM report_shares');render('Version 2.1 upgrade',function(){?><div class="card"><h1>Version 2.1 database update completed</h1><a class="btn" href="<?=h(route('dashboard'))?>">Continue</a></div><?php });exit;
}

if($r==='upgrade_v12'){
 require_role(['admin']);$done=false;$error='';
 if($_SERVER['REQUEST_METHOD']==='POST'){verify_csrf();try{
  $q=db()->query("SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='projects' AND COLUMN_NAME='platforms_json'");if(!(int)$q->fetchColumn())db()->exec('ALTER TABLE projects ADD COLUMN platforms_json JSON NULL AFTER platform_key');
  db()->exec("CREATE TABLE IF NOT EXISTS content_platform_links (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,content_item_id BIGINT UNSIGNED NOT NULL,platform_key VARCHAR(100) NOT NULL,post_url TEXT NOT NULL,sync_status ENUM('manual','pending','connected','error') NOT NULL DEFAULT 'manual',last_synced_at DATETIME NULL,last_error TEXT NULL,created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,UNIQUE KEY uq_content_platform(content_item_id,platform_key)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
  db()->exec("CREATE TABLE IF NOT EXISTS report_shares (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,token CHAR(48) NOT NULL UNIQUE,client_id BIGINT UNSIGNED NOT NULL,date_from DATE NOT NULL,date_to DATE NOT NULL,include_financial TINYINT(1) NOT NULL DEFAULT 0,consultant_comment TEXT NULL,created_by BIGINT UNSIGNED NOT NULL,expires_at DATETIME NULL,created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
  db()->exec("CREATE TABLE IF NOT EXISTS daily_platform_stats (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,client_id BIGINT UNSIGNED NOT NULL,stat_date DATE NOT NULL,platform_key VARCHAR(100) NOT NULL,metric_key VARCHAR(50) NOT NULL DEFAULT 'followers',metric_value BIGINT NOT NULL DEFAULT 0,notes VARCHAR(255) NULL,entered_by BIGINT UNSIGNED NULL,created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,UNIQUE KEY uq_daily_platform_metric(client_id,stat_date,platform_key,metric_key),INDEX idx_daily_client_date(client_id,stat_date)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
  db()->exec("CREATE TABLE IF NOT EXISTS content_updates (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,client_id BIGINT UNSIGNED NOT NULL,update_date DATE NOT NULL,content_kind ENUM('graphic','video') NOT NULL,platforms_json JSON NULL,entered_by BIGINT UNSIGNED NULL,created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,UNIQUE KEY uq_content_update(client_id,update_date,content_kind),INDEX idx_content_update_client_date(client_id,update_date)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
  db()->exec("INSERT INTO settings(setting_group,setting_key,label,sort_order) VALUES ('content_update_platforms','facebook','Facebook',10),('content_update_platforms','instagram','Instagram',20),('content_update_platforms','tiktok','TikTok',30) ON DUPLICATE KEY UPDATE label=VALUES(label)");
  $hasColumn=function(string $table,string $column):bool{$q=db()->prepare('SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME=? AND COLUMN_NAME=?');$q->execute([$table,$column]);return (int)$q->fetchColumn()>0;};
  if(!$hasColumn('users','job_title'))db()->exec('ALTER TABLE users ADD COLUMN job_title VARCHAR(150) NULL AFTER role');
  if(!$hasColumn('users','permissions_json'))db()->exec('ALTER TABLE users ADD COLUMN permissions_json JSON NULL AFTER job_title');
  db()->exec("UPDATE clients SET code=CASE WHEN name LIKE '%RealThai%' THEN 'realthai' WHEN name LIKE '%Mysoft%' THEN 'mysoft' WHEN name LIKE '%Hong Yi%' OR name LIKE '%HongYi%' THEN 'hongyiramen' WHEN name LIKE '%YSH%' THEN 'yshproperty' ELSE LOWER(REPLACE(REPLACE(code,' ',''),'-','')) END WHERE name LIKE '%RealThai%' OR name LIKE '%Mysoft%' OR name LIKE '%Hong Yi%' OR name LIKE '%HongYi%' OR name LIKE '%YSH%'");
  if(!$hasColumn('content_items','item_kind'))db()->exec("ALTER TABLE content_items ADD COLUMN item_kind ENUM('posting','ads_campaign','video','website') NOT NULL DEFAULT 'posting' AFTER title");
  if(!$hasColumn('content_items','campaign_budget'))db()->exec('ALTER TABLE content_items ADD COLUMN campaign_budget DECIMAL(12,2) NULL AFTER published_at');
  if(!$hasColumn('tracking_links','content_item_id'))db()->exec('ALTER TABLE tracking_links ADD COLUMN content_item_id BIGINT UNSIGNED NULL AFTER project_id');
  if(!$hasColumn('tracking_links','client_id'))db()->exec('ALTER TABLE tracking_links ADD COLUMN client_id BIGINT UNSIGNED NULL AFTER project_id');
  db()->exec('ALTER TABLE tracking_links MODIFY project_id BIGINT UNSIGNED NULL');
  $clients=db()->query('SELECT c.id,c.name,COUNT(p.id) total FROM clients c JOIN projects p ON p.client_id=c.id GROUP BY c.id,c.name HAVING total>1')->fetchAll();foreach($clients as $client){$q=db()->prepare('SELECT * FROM projects WHERE client_id=? ORDER BY id');$q->execute([$client['id']]);$projects=$q->fetchAll();$primary=(int)$projects[0]['id'];foreach($projects as $p){$marker='Migrated from project #'.$p['id'];$q=db()->prepare('SELECT id FROM content_items WHERE project_id=? AND notes=?');$q->execute([$primary,$marker]);$contentId=(int)($q->fetchColumn()?:0);if(!$contentId){$platforms=array_filter([$p['platform_key']]);db()->prepare('INSERT INTO content_items(project_id,title,content_type_key,platforms_json,status_key,original_url,published_url,published_at,caption,notes,created_by) VALUES(?,?,?,?,?,?,?,?,?,?,?)')->execute([$primary,$p['title'],$p['content_type_key'],json_encode($platforms),$p['status_key'],$p['original_url'],$p['original_url'],in_array($p['status_key'],['published','completed'],true)?date('Y-m-d H:i:s'):null,null,$marker,$p['created_by']]);$contentId=(int)db()->lastInsertId();if($p['platform_key']&&$p['original_url'])db()->prepare('INSERT IGNORE INTO content_platform_links(content_item_id,platform_key,post_url) VALUES(?,?,?)')->execute([$contentId,$p['platform_key'],$p['original_url']]);}db()->prepare('UPDATE performance_entries SET project_id=?,content_item_id=? WHERE project_id=?')->execute([$primary,$contentId,$p['id']]);db()->prepare('UPDATE attachments SET project_id=?,content_item_id=? WHERE project_id=?')->execute([$primary,$contentId,$p['id']]);db()->prepare('UPDATE tracking_links SET project_id=? WHERE project_id=?')->execute([$primary,$p['id']]);if((int)$p['id']!==$primary)db()->prepare('DELETE FROM projects WHERE id=?')->execute([$p['id']]);}db()->prepare('UPDATE projects SET title=?,campaign=COALESCE(NULLIF(campaign,""),"Ongoing Marketing") WHERE id=?')->execute([$client['name'].' Marketing Workspace',$primary]);}$done=true;
  $q=db()->query("SELECT p.id,p.budget,p.created_by FROM projects p JOIN clients c ON c.id=p.client_id WHERE c.name LIKE '%Hong%Yi%Ramen%' ORDER BY p.id LIMIT 1");$hongyi=$q->fetch();if($hongyi&&$hongyi['budget']!==null){$q=db()->prepare("SELECT id FROM content_items WHERE project_id=? AND title='HY-4thAnniversary-04'");$q->execute([$hongyi['id']]);if(!$q->fetchColumn())db()->prepare("INSERT INTO content_items(project_id,title,item_kind,content_type_key,platforms_json,status_key,campaign_budget,notes,created_by) VALUES(?,?,'ads_campaign','advertisement','[\"facebook\",\"instagram\"]','completed',?,?,?)")->execute([$hongyi['id'],'HY-4thAnniversary-04',$hongyi['budget'],'Moved from workspace budget',$hongyi['created_by']]);db()->prepare('UPDATE projects SET budget=NULL WHERE id=?')->execute([$hongyi['id']]);}$done=true;
  }catch(Throwable $e){$error=$e->getMessage();}}
 render('Upgrade structure',function()use($done,$error){?><div class="login"><div class="card"><h1>Client workspace upgrade</h1><?php if($done):?><div class="flash ok">Upgrade and existing-data grouping completed.</div><?php elseif($error):?><div class="flash error"><?=h($error)?></div><?php else:?><p>This groups existing projects under each client and preserves their content, links, files and insights.</p><form method="post"><input type="hidden" name="_csrf" value="<?=csrf()?>"><button class="btn">Upgrade and organise data</button></form><?php endif;?></div></div><?php });exit;
}

if($r==='repair_v11'){
 require_role(['admin']);$done=false;$error='';
 if($_SERVER['REQUEST_METHOD']==='POST'){verify_csrf();try{
  $has=function(string $table,string $column):bool{$q=db()->prepare('SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME=? AND COLUMN_NAME=?');$q->execute([$table,$column]);return (int)$q->fetchColumn()>0;};
  if(!$has('performance_entries','content_item_id'))db()->exec('ALTER TABLE performance_entries ADD COLUMN content_item_id BIGINT UNSIGNED NULL AFTER project_id');
  if(!$has('attachments','content_item_id'))db()->exec('ALTER TABLE attachments ADD COLUMN content_item_id BIGINT UNSIGNED NULL AFTER project_id');
  $q=db()->query("SELECT COUNT(*) FROM information_schema.STATISTICS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='performance_entries' AND INDEX_NAME='idx_perf_project'");if(!(int)$q->fetchColumn())db()->exec('ALTER TABLE performance_entries ADD INDEX idx_perf_project (project_id)');
  $q=db()->query("SELECT COUNT(*) FROM information_schema.STATISTICS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='performance_entries' AND INDEX_NAME='uq_project_date'");if((int)$q->fetchColumn())db()->exec('ALTER TABLE performance_entries DROP INDEX uq_project_date');
  $q=db()->query("SELECT COUNT(*) FROM information_schema.STATISTICS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='performance_entries' AND INDEX_NAME='uq_content_date'");if(!(int)$q->fetchColumn())db()->exec('ALTER TABLE performance_entries ADD UNIQUE KEY uq_content_date (project_id,content_item_id,metric_date)');
  db()->exec("CREATE TABLE IF NOT EXISTS user_client_access (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,user_id BIGINT UNSIGNED NOT NULL,client_id BIGINT UNSIGNED NOT NULL,can_access TINYINT(1) NOT NULL DEFAULT 1,created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,UNIQUE KEY uq_user_client(user_id,client_id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
  db()->exec("CREATE TABLE IF NOT EXISTS staff_invites (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,token_hash CHAR(64) NOT NULL UNIQUE,email VARCHAR(190) NULL,expires_at DATETIME NOT NULL,used_at DATETIME NULL,used_by BIGINT UNSIGNED NULL,created_by BIGINT UNSIGNED NOT NULL,created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
  db()->exec("INSERT INTO settings(setting_group,setting_key,label,sort_order) VALUES ('platforms','xiaohongshu','XiaoHongShu / 小红书',35),('platforms','linkedin','LinkedIn',45),('platforms','x','X / Twitter',55),('platforms','google-business','Google Business Profile',65),('platforms','pinterest','Pinterest',75),('platforms','threads','Threads',85),('platforms','whatsapp','WhatsApp',95) ON DUPLICATE KEY UPDATE label=VALUES(label),active=1");$done=true;
 }catch(Throwable $e){$error=$e->getMessage();}}
 render('Complete v1.1 upgrade',function()use($done,$error){?><div class="login"><div class="card"><h1>Complete v1.1 upgrade</h1><?php if($done):?><div class="flash ok">Database upgrade completed successfully.</div><?php elseif($error):?><div class="flash error"><?=h($error)?></div><?php else:?><form method="post"><input type="hidden" name="_csrf" value="<?=csrf()?>"><button class="btn">Complete upgrade</button></form><?php endif;?></div></div><?php });exit;
}

if($r==='migrate_v11'){
 require_role(['admin']);$message='';
 if($_SERVER['REQUEST_METHOD']==='POST'){verify_csrf();$sql=file_get_contents(__DIR__.'/database/migrations/002_client_workspace_content.sql');$statements=array_filter(array_map('trim',preg_split('/;\s*(?:\r?\n|$)/',$sql)));try{foreach($statements as $statement)db()->exec($statement);$message='Database upgrade completed successfully.';}catch(Throwable $e){$message='Upgrade stopped: '.$e->getMessage();}}
 render('System upgrade',function()use($message){?><div class="login"><div class="card"><h1>Marketing Tracker v1.1</h1><?php if($message):?><div class="flash <?=str_starts_with($message,'Database')?'ok':'error'?>"><?=h($message)?></div><?php else:?><p>This adds content workspaces, multi-platform postings, staff invitations and client visibility controls.</p><form method="post"><input type="hidden" name="_csrf" value="<?=csrf()?>"><button class="btn">Run database upgrade</button></form><?php endif;?></div></div><?php });exit;
}

if($r==='import_legacy_tracking'){
 require_role(['admin']);$done=false;$error='';
 if($_SERVER['REQUEST_METHOD']==='POST'){verify_csrf();try{$has=function(string $column):bool{$q=db()->prepare("SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='tracking_links' AND COLUMN_NAME=?");$q->execute([$column]);return (int)$q->fetchColumn()>0;};foreach(['legacy_url'=>'TEXT NULL','legacy_visitors'=>'BIGINT NOT NULL DEFAULT 0','legacy_views'=>'BIGINT NOT NULL DEFAULT 0','legacy_clicks'=>'BIGINT NOT NULL DEFAULT 0','legacy_leads'=>'BIGINT NOT NULL DEFAULT 0','import_source'=>'VARCHAR(100) NULL'] as $column=>$definition)if(!$has($column))db()->exec("ALTER TABLE tracking_links ADD COLUMN $column $definition");$sql='INSERT INTO tracking_links(project_id,client_id,slug,destination_url,source,medium,campaign,created_by,created_at,legacy_url,legacy_visitors,legacy_views,legacy_clicks,legacy_leads,import_source) VALUES(NULL,NULL,?,?,?,?,?,?,?, ?,?,?,?,?,?) ON DUPLICATE KEY UPDATE campaign=VALUES(campaign),source=VALUES(source),medium=VALUES(medium),legacy_url=VALUES(legacy_url),legacy_visitors=VALUES(legacy_visitors),legacy_views=VALUES(legacy_views),legacy_clicks=VALUES(legacy_clicks),legacy_leads=VALUES(legacy_leads),import_source=VALUES(import_source)';$q=db()->prepare($sql);$rows=[['legacy-1002','https://ai.onetapgo.my/ai-price/go/1002','facebook','paid_social','OTG-AI-Review-System-Test-Aug26','2026-08-16 07:10:51','https://ai.onetapgo.my/ai-price/go/1002',0,0,0,0],['legacy-1001','https://ai.onetapgo.my/ai-price/go/1001','facebook','paid_social','OTG-Sales-Aug26','2026-08-16 07:08:16','https://ai.onetapgo.my/ai-price/go/1001',0,0,0,0],['legacy-2207','https://ai.onetapgo.my/ai-price/go/2207','whatsapp','manual_followup','22/07 Follow Up','2026-08-16 06:26:46','https://ai.onetapgo.my/ai-price/go/2207',2,5,0,0]];foreach($rows as $x)$q->execute([$x[0],$x[1],$x[2],$x[3],$x[4],user()['id'],$x[5],$x[6],$x[7],$x[8],$x[9],$x[10],'onetapgo_ai_price']);$done=true;}catch(Throwable $e){$error=$e->getMessage();}}
 render('Import One Tap Go history',function()use($done,$error){?><div class="login"><div class="card"><h1>Import One Tap Go Tracking History</h1><?php if($done):?><div class="flash ok">3 legacy campaign links and their historical totals were integrated successfully.</div><a class="btn" href="<?=h(route('tracking_hub'))?>">Open Tracking Hub</a><?php elseif($error):?><div class="flash error"><?=h($error)?></div><?php else:?><p>This imports the 3 verified All Time campaign links without creating fake click events. Existing records can be updated safely by running it again.</p><form method="post"><input type="hidden" name="_csrf" value="<?=csrf()?>"><button class="btn">Import verified history</button></form><?php endif;?></div></div><?php });exit;
}

if($r==='dashboard'){$dashboardQuery=$_GET;unset($dashboardQuery['r']);go('dashboard_v3',$dashboardQuery);}

if($r==='contracts'){
 if(!canDo('manage_contracts')){http_response_code(403);exit('Contract System permission is not enabled for this user.');}
 render('Contract System',function(){?><div class="toolbar"><div style="flex:1"><h1>Contract System</h1><p class="muted">Existing One Tap Go and Marketing agreements are integrated here. Staff access is disabled unless enabled in User Settings.</p></div><a class="btn" href="https://agent.onetapgo.my/?r=login" target="_blank">Open full Contract System</a></div><div class="card"><iframe src="https://agent.onetapgo.my/?r=login" title="Contract System" style="width:100%;height:75vh;border:0;border-radius:12px"></iframe><p class="muted">If secure login cannot display here, use “Open full Contract System”. Existing contracts and PDFs remain protected in their original database.</p></div><?php });exit;
}

if($r==='tracking_hub'){
 if(!canDo('manage_tracking')){http_response_code(403);exit('Tracking Hub permission is not enabled for this user.');}require_role(['admin','staff']);if($_SERVER['REQUEST_METHOD']==='POST'){verify_csrf();$action=$_POST['action']??'create';if($action==='delete'){$id=(int)$_POST['id'];db()->prepare('DELETE FROM clicks WHERE tracking_link_id=?')->execute([$id]);db()->prepare('DELETE FROM tracking_links WHERE id=?')->execute([$id]);flash('Tracking link deleted.');go('tracking_hub');}$dest=trim($_POST['destination_url']??'');$slug=strtolower(trim($_POST['slug']??''))?:randomSlug();if(!validUrl($dest)||!preg_match('/^[a-z0-9_-]{3,100}$/',$slug)){flash('Use a valid destination URL and slug.','error');go('tracking_hub');}try{db()->prepare('INSERT INTO tracking_links(project_id,client_id,slug,destination_url,source,medium,campaign,created_by) VALUES(NULL,?,?,?,?,?,?,?)')->execute([(int)($_POST['client_id']??0)?:null,$slug,$dest,trim($_POST['source']??''),trim($_POST['medium']??''),trim($_POST['campaign']??''),user()['id']]);flash('Marketing tracking link created.');}catch(Throwable $e){flash('That slug is already in use.','error');}go('tracking_hub');}$clients=db()->query('SELECT id,name FROM clients WHERE status="active" ORDER BY name')->fetchAll();$rows=db()->query('SELECT tl.*,COALESCE(c1.name,c2.name) client_name,COALESCE(cs.clicks,0)+COALESCE(tl.legacy_clicks,0) clicks,COALESCE(cs.unique_clicks,0) unique_clicks FROM tracking_links tl LEFT JOIN projects tp ON tp.id=tl.project_id LEFT JOIN clients c1 ON c1.id=tl.client_id LEFT JOIN clients c2 ON c2.id=tp.client_id LEFT JOIN (SELECT tracking_link_id,COUNT(*) clicks,SUM(is_unique) unique_clicks FROM clicks GROUP BY tracking_link_id) cs ON cs.tracking_link_id=tl.id ORDER BY tl.created_at DESC')->fetchAll();render('Marketing Tracking Hub',function()use($clients,$rows){?><div class="toolbar"><div style="flex:1"><h1>Marketing Tracking Hub</h1><p class="muted">Global tracking links for the whole marketing operation — not a separate client project.</p></div><a class="btn gray" target="_blank" href="https://ai.onetapgo.my/ai-price/admin/index.php?period=month">Open One Tap Go Website Analytics</a></div><div class="split"><section><table><tr><th>Campaign</th><th>Client</th><th>Tracking link</th><th>Source / method</th><th>Visitors</th><th>Views</th><th>Clicks</th><th>Leads</th><th></th></tr><?php foreach($rows as $x):?><tr><td><?=h($x['campaign']?:'Untitled')?></td><td><?=h($x['client_name']?:'Global / One Tap Go')?></td><td><a target="_blank" href="<?=h(url('t/'.$x['slug']))?>"><?=h(url('t/'.$x['slug']))?></a></td><td><?=h($x['source'])?> / <?=h($x['medium'])?></td><td><?=number_format((int)($x['legacy_visitors']??0))?></td><td><?=number_format((int)($x['legacy_views']??0))?></td><td><?=number_format((int)$x['clicks'])?> (<?=number_format((int)$x['unique_clicks'])?> unique)</td><td><?=number_format((int)($x['legacy_leads']??0))?></td><td><form method="post" onsubmit="return confirm('Delete this tracking link and its click history?')"><input type="hidden" name="_csrf" value="<?=csrf()?>"><input type="hidden" name="action" value="delete"><input type="hidden" name="id" value="<?=$x['id']?>"><button class="btn small gray">Delete</button></form></td></tr><?php endforeach;?></table></section><aside class="card"><h2>Create global tracking link</h2><form method="post"><input type="hidden" name="_csrf" value="<?=csrf()?>"><div class="field"><label>Client (optional)</label><select name="client_id"><option value="">Global / One Tap Go</option><?php foreach($clients as $c):?><option value="<?=$c['id']?>"><?=h($c['name'])?></option><?php endforeach;?></select></div><div class="field"><label>Campaign</label><input name="campaign" required></div><div class="field"><label>Destination URL</label><input type="url" name="destination_url" required></div><div class="field"><label>Custom short name</label><input name="slug" placeholder="otg-aug26"></div><div class="field"><label>Source</label><input name="source" placeholder="facebook / whatsapp"></div><div class="field"><label>Method</label><input name="medium" placeholder="paid_social / follow_up / qr"></div><button class="btn">Generate tracking link</button></form></aside></div><?php });exit;
}

if($r==='dashboard_v3'){
 [$scope,$scopeArgs]=scopeSql();$days=$_GET['days']??'30';$allowed=['1','7','30','90','180'];$from=in_array($days,$allowed,true)?($days==='1'?date('Y-m-d'):date('Y-m-d',strtotime("-$days days"))):($_GET['from']??date('Y-m-01'));$to=$_GET['to']??date('Y-m-d');$clientId=user()['role']==='client'?(int)user()['client_id']:(int)($_GET['client_id']??0);$where=$scope.' AND pe.metric_date BETWEEN ? AND ?';$args=array_merge($scopeArgs,[$from,$to]);if($clientId){$where.=' AND p.client_id=?';$args[]=$clientId;}$m=metricTotals($where,$args);$q=db()->prepare("SELECT COUNT(*) clicks,COALESCE(SUM(c.is_unique),0) unique_clicks FROM clicks c JOIN tracking_links tl ON tl.id=c.tracking_link_id LEFT JOIN projects p ON p.id=tl.project_id WHERE DATE(c.clicked_at) BETWEEN ? AND ?".($clientId?' AND COALESCE(tl.client_id,p.client_id)=?':''));$clickArgs=[$from,$to];if($clientId)$clickArgs[]=$clientId;$q->execute($clickArgs);$cl=$q->fetch();$clients=db()->query('SELECT id,name FROM clients WHERE status="active" ORDER BY name')->fetchAll();$clientName='All Clients';foreach($clients as $c)if((int)$c['id']===$clientId)$clientName=$c['name'];$daily=[];$dailyDates=[];if($clientId){$q=db()->prepare('SELECT DISTINCT stat_date FROM daily_platform_stats WHERE client_id=? ORDER BY stat_date DESC LIMIT 2');$q->execute([$clientId]);$dailyDates=array_reverse($q->fetchAll(PDO::FETCH_COLUMN));if(count($dailyDates)===2){$q=db()->prepare('SELECT platform_key,stat_date,metric_value FROM daily_platform_stats WHERE client_id=? AND stat_date IN (?,?)');$q->execute([$clientId,$dailyDates[0],$dailyDates[1]]);foreach($q as $x)$daily[$x['platform_key']][$x['stat_date']]=$x['metric_value'];}}
 render('Dashboard',function()use($m,$cl,$days,$from,$to,$clients,$clientId,$clientName,$daily,$dailyDates){$labels=['facebook'=>'Facebook','instagram'=>'Instagram','google-business'=>'Google','tiktok'=>'TikTok','xiaohongshu'=>'小红书'];?><h1><?=h($clientName)?> Dashboard</h1><?php if(user()['role']!=='client'):?><div class="toolbar no-print"><a class="btn <?=$clientId?'gray':''?>" href="<?=h(route('dashboard_v3'))?>">All Clients</a><?php foreach($clients as $c):?><a class="btn <?=$clientId===(int)$c['id']?'':'gray'?>" href="<?=h(route('dashboard_v3',['client_id'=>$c['id']]))?>"><?=h($c['name'])?></a><?php endforeach;?></div><?php endif;?><form class="toolbar no-print"><input type="hidden" name="r" value="dashboard_v3"><?php if($clientId):?><input type="hidden" name="client_id" value="<?=$clientId?>"><?php endif;?><div class="field"><label>Range</label><select name="days"><?php foreach(['1','7','30','90','180'] as $d):?><option value="<?=$d?>" <?=$days===$d?'selected':''?>><?=$d?> days</option><?php endforeach;?><option value="custom" <?=$days==='custom'?'selected':''?>>Custom</option></select></div><div class="field"><label>From</label><input type="date" name="from" value="<?=h($from)?>"></div><div class="field"><label>To</label><input type="date" name="to" value="<?=h($to)?>"></div><button class="btn">Apply</button></form><?php if(count($dailyDates)===2):?><div class="card"><div class="toolbar"><div style="flex:1"><h2>Latest Daily Update</h2><span class="muted"><?=h($dailyDates[0])?> → <?=h($dailyDates[1])?></span></div><a class="btn gray" href="<?=h(route('daily_updates',['client_id'=>$clientId,'from'=>$dailyDates[0],'to'=>$dailyDates[1]]))?>">Open update</a></div><div class="grid"><?php foreach($labels as $key=>$label):$a=$daily[$key][$dailyDates[0]]??null;$b=$daily[$key][$dailyDates[1]]??null;$d=($a!==null&&$b!==null)?$b-$a:null;?><div class="metric"><span><?=h($label)?></span><b><?=h($b??'—')?></b><small><?=($d===null?'—':(($d>=0?'↑':'↓').abs($d)))?></small></div><?php endforeach;?></div></div><?php endif;?><div class="grid" style="margin-top:16px"><?php foreach(['Tracked clicks'=>$cl['clicks'],'Unique clicks'=>$cl['unique_clicks'],'Reach'=>$m['reach'],'Impressions'=>$m['impressions'],'Views'=>$m['views'],'Engagement'=>$m['engagement'],'Leads'=>$m['leads'],'Sales'=>'RM '.number_format((float)$m['sales'],2),'Ad spend'=>'RM '.number_format((float)$m['spend'],2)] as $k=>$v):?><div class="card metric"><span><?=h($k)?></span><b><?=h($v)?></b></div><?php endforeach;?></div><?php });exit;
}

if($r==='dashboard_v2'){
 [$scope,$scopeArgs]=scopeSql();$days=$_GET['days']??'30';$allowed=['1','7','30','90','180'];$from=in_array($days,$allowed,true)?($days==='1'?date('Y-m-d'):date('Y-m-d',strtotime("-$days days"))):($_GET['from']??date('Y-m-01'));$to=$_GET['to']??date('Y-m-d');$clientId=user()['role']==='client'?(int)user()['client_id']:(int)($_GET['client_id']??0);$where=$scope.' AND pe.metric_date BETWEEN ? AND ?';$args=array_merge($scopeArgs,[$from,$to]);if($clientId){$where.=' AND p.client_id=?';$args[]=$clientId;}$m=metricTotals($where,$args);$q=db()->prepare("SELECT COUNT(*) clicks,COALESCE(SUM(c.is_unique),0) unique_clicks FROM clicks c JOIN tracking_links tl ON tl.id=c.tracking_link_id LEFT JOIN projects p ON p.id=tl.project_id WHERE DATE(c.clicked_at) BETWEEN ? AND ?".($clientId?' AND COALESCE(tl.client_id,p.client_id)=?':''));$clickArgs=[$from,$to];if($clientId)$clickArgs[]=$clientId;$q->execute($clickArgs);$cl=$q->fetch();$clients=db()->query('SELECT id,name FROM clients WHERE status="active" ORDER BY name')->fetchAll();$clientName='All Clients';foreach($clients as $c)if((int)$c['id']===$clientId)$clientName=$c['name'];
 render('Dashboard',function()use($m,$cl,$days,$from,$to,$clients,$clientId,$clientName){?><h1><?=h($clientName)?> Dashboard</h1><?php if(user()['role']!=='client'):?><div class="toolbar no-print"><a class="btn <?=$clientId?'gray':''?>" href="<?=h(route('dashboard_v2'))?>">All Clients</a><?php foreach($clients as $c):?><a class="btn <?=$clientId===(int)$c['id']?'':'gray'?>" href="<?=h(route('dashboard_v2',['client_id'=>$c['id']]))?>"><?=h($c['name'])?></a><?php endforeach;?></div><?php endif;?><form class="toolbar no-print"><input type="hidden" name="r" value="dashboard_v2"><?php if($clientId):?><input type="hidden" name="client_id" value="<?=$clientId?>"><?php endif;?><div class="field"><label>Range</label><select name="days"><option value="1" <?=$days==='1'?'selected':''?>>Today</option><option value="7" <?=$days==='7'?'selected':''?>>7 days</option><option value="30" <?=$days==='30'?'selected':''?>>30 days</option><option value="90" <?=$days==='90'?'selected':''?>>90 days</option><option value="180" <?=$days==='180'?'selected':''?>>180 days</option><option value="custom" <?=$days==='custom'?'selected':''?>>Custom</option></select></div><div class="field"><label>From</label><input type="date" name="from" value="<?=h($from)?>"></div><div class="field"><label>To</label><input type="date" name="to" value="<?=h($to)?>"></div><button class="btn">Apply</button></form><div class="grid"><?php foreach(['Tracked clicks'=>$cl['clicks'],'Unique clicks'=>$cl['unique_clicks'],'Reach'=>$m['reach'],'Impressions'=>$m['impressions'],'Views'=>$m['views'],'Engagement'=>$m['engagement'],'Leads'=>$m['leads'],'Sales'=>'RM '.number_format((float)$m['sales'],2),'Ad spend'=>'RM '.number_format((float)$m['spend'],2)] as $k=>$v):?><div class="card metric"><span><?=h($k)?></span><b><?=h($v)?></b></div><?php endforeach;?></div><?php });exit;
}

if($r==='dashboard_legacy'){
 [$scope,$scopeArgs]=scopeSql();$days=$_GET['days']??'30';$allowed=['1','7','30','90','180'];$from=in_array($days,$allowed,true)?($days==='1'?date('Y-m-d'):date('Y-m-d',strtotime("-$days days"))):($_GET['from']??date('Y-m-01'));$to=$_GET['to']??date('Y-m-d');
 $where=$scope.' AND pe.metric_date BETWEEN ? AND ?';$args=array_merge($scopeArgs,[$from,$to]);foreach(['client_id'=>'p.client_id','platform'=>'p.platform_key','content_type'=>'p.content_type_key'] as $k=>$col)if(!empty($_GET[$k])){$where.=" AND $col=?";$args[]=$_GET[$k];}
 $m=metricTotals($where,$args);$cw=str_replace('pe.metric_date','c.clicked_at',$where);$cw=preg_replace('/c\.clicked_at BETWEEN \? AND \?/','DATE(c.clicked_at) BETWEEN ? AND ?',$cw);$q=db()->prepare("SELECT COUNT(*) clicks,COALESCE(SUM(c.is_unique),0) unique_clicks FROM clicks c JOIN tracking_links tl ON tl.id=c.tracking_link_id JOIN projects p ON p.id=tl.project_id WHERE 1=1 $cw");$q->execute($args);$cl=$q->fetch();
 $clients=db()->query('SELECT id,name FROM clients WHERE status="active" ORDER BY name')->fetchAll();
 render('Dashboard',function()use($m,$cl,$days,$from,$to,$clients){?><h1>Dashboard</h1><form class="toolbar no-print"><div class="field"><label>Range</label><select name="days"><option value="1" <?=$days==='1'?'selected':''?>>Today</option><option value="7" <?=$days==='7'?'selected':''?>>7 days</option><option value="30" <?=$days==='30'?'selected':''?>>30 days</option><option value="90" <?=$days==='90'?'selected':''?>>90 days</option><option value="180" <?=$days==='180'?'selected':''?>>180 days</option><option value="custom" <?=$days==='custom'?'selected':''?>>Custom</option></select></div><div class="field"><label>From</label><input type="date" name="from" value="<?=h($from)?>"></div><div class="field"><label>To</label><input type="date" name="to" value="<?=h($to)?>"></div><?php if(user()['role']!=='client'):?><div class="field"><label>Client</label><select name="client_id"><option value="">All</option><?php foreach($clients as $c):?><option value="<?=$c['id']?>" <?=($_GET['client_id']??'')==$c['id']?'selected':''?>><?=h($c['name'])?></option><?php endforeach;?></select></div><?php endif;?><div class="field"><label>Platform</label><select name="platform"><option value="">All</option><?=selectOptions(settingOptions('platforms'),$_GET['platform']??null)?></select></div><div class="field"><label>Content type</label><select name="content_type"><option value="">All</option><?=selectOptions(settingOptions('content_types'),$_GET['content_type']??null)?></select></div><button class="btn">Apply</button></form><div class="grid"><?php foreach(['Tracked clicks'=>$cl['clicks'],'Unique clicks'=>$cl['unique_clicks'],'Reach'=>$m['reach'],'Impressions'=>$m['impressions'],'Views'=>$m['views'],'Engagement'=>$m['engagement'],'Leads'=>$m['leads'],'Sales'=>'RM '.number_format((float)$m['sales'],2),'Ad spend'=>'RM '.number_format((float)$m['spend'],2)] as $k=>$v):?><div class="card metric"><span><?=h($k)?></span><b><?=h($v)?></b></div><?php endforeach;?></div><?php });exit;
}

if($r==='clients'){
 require_role(['admin','staff']);
 if($_SERVER['REQUEST_METHOD']==='POST'){
  verify_csrf();
  $id=(int)($_POST['id']??0);
  if($id&&user()['role']==='admin'&&in_array($_POST['client_action']??'',['clear','delete'],true)){if($_POST['client_action']==='clear'){deleteClientData($id,false);flash('Client data cleared.');go('clients',['edit'=>$id]);}deleteClientData($id,true);flash('Client deleted.');go('clients');}
  if(!canDo('manage_clients')||($id&&!canDo('edit'))||(!$id&&!canDo('create'))){http_response_code(403);exit('Client create/edit permission is not enabled for this user.');}
  $portalCode=strtolower(trim($_POST['code']??''));if($portalCode&&!preg_match('/^[a-z][a-z0-9_-]{2,49}$/',$portalCode)){flash('Client Code must be an English portal name such as realthai or mysoft.','error');go('clients',$id?['edit'=>$id]:[]);}$social=[];foreach(['facebook','instagram','tiktok','xiaohongshu','youtube','website'] as $platform)if(trim($_POST['social'][$platform]??''))$social[$platform]=trim($_POST['social'][$platform]);$a=[trim($_POST['name']),$portalCode?:null,trim($_POST['email'])?:null,trim($_POST['phone'])?:null,json_encode($social),(int)($_POST['account_manager_id']??0)?:null,$_POST['status']??'active'];
  if($id){$a[]=$id;db()->prepare('UPDATE clients SET name=?,code=?,email=?,phone=?,social_links_json=?,account_manager_id=?,status=? WHERE id=?')->execute($a);}
  else{db()->prepare('INSERT INTO clients(name,code,email,phone,social_links_json,account_manager_id,status) VALUES(?,?,?,?,?,?,?)')->execute($a);}
  flash('Client saved.');go('clients');
 }
 $edit=null;
 if(!empty($_GET['edit'])){$q=db()->prepare('SELECT * FROM clients WHERE id=?');$q->execute([(int)$_GET['edit']]);$edit=$q->fetch();}
 [$cs,$ca]=clientScopeSql();$q=db()->prepare('SELECT c.*,u.name account_manager,(SELECT COUNT(*) FROM projects p WHERE p.client_id=c.id) projects FROM clients c LEFT JOIN users u ON u.id=c.account_manager_id '.$cs.' ORDER BY c.name');$q->execute($ca);$rows=$q->fetchAll();$managers=db()->query("SELECT id,name,job_title FROM users WHERE active=1 AND role IN ('admin','staff') ORDER BY name")->fetchAll();
 render('Clients',function()use($rows,$edit,$managers){
  ?><div class="split"><section><h1>Clients</h1><table><tr><th>Client</th><th>Contact</th><th>Account manager</th><th>Projects</th><th>Status</th><th></th></tr><?php
  foreach($rows as $x){?><tr><td><b><?=h($x['name'])?></b></td><td><?=h($x['email'])?><br><?=h($x['phone'])?></td><td><?=h($x['account_manager']?:'—')?></td><td><?=h($x['projects'])?></td><td><span class="badge"><?=h($x['status'])?></span></td><td><a class="btn small gray client-settings" href="<?=h(route('clients',['edit'=>$x['id']]))?>">Settings</a></td></tr><?php }
  ?></table></section><aside class="card"><h2><?=($edit?'Edit':'Add')?> client</h2><form method="post"><input type="hidden" name="_csrf" value="<?=csrf()?>"><input type="hidden" name="id" value="<?=h($edit['id']??'')?>"><?php
  foreach(['name'=>'Name','code'=>'English portal name (e.g. realthai)','email'=>'Email','phone'=>'Phone'] as $k=>$l){?><div class="field"><label><?=h($l)?></label><input name="<?=h($k)?>" value="<?=h($edit[$k]??'')?>" <?=($k==='name'?'required':'')?>></div><?php }
  $editSocial=json_decode($edit['social_links_json']??'{}',true)?:[];?><div class="field"><label>Account manager</label><select name="account_manager_id"><option value="">Not assigned</option><?php foreach($managers as $manager):?><option value="<?=$manager['id']?>" <?=(int)($edit['account_manager_id']??0)===(int)$manager['id']?'selected':''?>><?=h($manager['name'].($manager['job_title']?' · '.$manager['job_title']:''))?></option><?php endforeach;?></select></div><h3>Social media links</h3><?php foreach(['facebook'=>'Facebook','instagram'=>'Instagram','tiktok'=>'TikTok','xiaohongshu'=>'XiaoHongShu / 小红书','youtube'=>'YouTube','website'=>'Website'] as $key=>$label):?><div class="field"><label><?=h($label)?></label><input type="url" name="social[<?=$key?>]" value="<?=h($editSocial[$key]??'')?>" placeholder="https://..."></div><?php endforeach;?><div class="field"><label>Status</label><select name="status"><option>active</option><option <?=((($edit['status']??'')==='inactive')?'selected':'')?>>inactive</option></select></div><button class="btn">Save settings</button><?php if($edit&&user()['role']==='admin'):?><hr><p class="muted">Danger zone</p><button class="btn gray" name="client_action" value="clear" onclick="return confirm('Clear all data for this client?')">Clear data</button> <button class="btn gray" name="client_action" value="delete" onclick="return confirm('Delete this client and all related data?')">Delete client</button><?php endif;?></form></aside></div><?php
 });exit;
}

if($r==='daily_updates'){$dailyQuery=$_GET;unset($dailyQuery['r']);go('daily_updates_v3',$dailyQuery);}
if($r==='daily_updates_v3'){
 $clients=db()->query('SELECT id,name FROM clients WHERE status="active" ORDER BY name')->fetchAll();$clientId=user()['role']==='client'?(int)user()['client_id']:(int)($_REQUEST['client_id']??($clients[0]['id']??0));$to=$_REQUEST['to']??date('Y-m-d');$manualFrom=($_REQUEST['manual_from']??'')==='1';$from=$manualFrom?($_REQUEST['from']??''):'';if($from===''&&$clientId){$q=db()->prepare('SELECT MAX(stat_date) FROM daily_platform_stats WHERE client_id=? AND stat_date<?');$q->execute([$clientId,$to]);$from=$q->fetchColumn()?:date('Y-m-d',strtotime('-7 days'));}if($from==='')$from=date('Y-m-d',strtotime('-7 days'));$metricPlatforms=['facebook'=>'Facebook','instagram'=>'Instagram','google-business'=>'Google','tiktok'=>'TikTok','xiaohongshu'=>'小红书'];$contentPlatforms=[];foreach(settingOptions('content_update_platforms') as $s)$contentPlatforms[$s['setting_key']]=$s['label'];if(!$contentPlatforms)$contentPlatforms=['facebook'=>'Facebook','instagram'=>'Instagram','tiktok'=>'TikTok'];$clientName='Client';foreach($clients as $c)if((int)$c['id']===$clientId)$clientName=$c['name'];
 if($_SERVER['REQUEST_METHOD']==='POST'){require_role(['admin','staff']);verify_csrf();$action=$_POST['action']??'stats';if($action==='stats'){$q=db()->prepare('INSERT INTO daily_platform_stats(client_id,stat_date,platform_key,metric_key,metric_value,entered_by) VALUES(?,?,?,?,?,?) ON DUPLICATE KEY UPDATE metric_value=VALUES(metric_value),entered_by=VALUES(entered_by)');foreach($metricPlatforms as $key=>$label){$metric=$key==='google-business'?'reviews':'followers';foreach(['start'=>$from,'end'=>$to] as $side=>$date)if(isset($_POST[$side][$key])&&$_POST[$side][$key]!=='')$q->execute([$clientId,$date,$key,$metric,(int)$_POST[$side][$key],user()['id']]);}flash('Daily data saved.');}elseif($action==='content'){$date=$_POST['update_date'];$kind=in_array($_POST['content_kind']??'',['graphic','video'],true)?$_POST['content_kind']:'graphic';$selected=array_values(array_intersect(array_keys($contentPlatforms),$_POST['content_platforms']??[]));db()->prepare('INSERT INTO content_updates(client_id,update_date,content_kind,platforms_json,entered_by) VALUES(?,?,?,?,?) ON DUPLICATE KEY UPDATE platforms_json=VALUES(platforms_json),entered_by=VALUES(entered_by)')->execute([$clientId,$date,$kind,json_encode($selected),user()['id']]);flash('Content update saved.');}elseif($action==='delete_content'){db()->prepare('DELETE FROM content_updates WHERE id=? AND client_id=?')->execute([(int)$_POST['content_id'],$clientId]);flash('Content update deleted.');}elseif($action==='delete_daily'){db()->prepare('DELETE FROM daily_platform_stats WHERE client_id=? AND stat_date IN (?,?)')->execute([$clientId,$from,$to]);flash('Daily data deleted.');}go('daily_updates_v3',['client_id'=>$clientId,'from'=>$from,'to'=>$to]);}
 $values=[];$q=db()->prepare('SELECT platform_key,stat_date,metric_value FROM daily_platform_stats WHERE client_id=? AND stat_date IN (?,?)');$q->execute([$clientId,$from,$to]);foreach($q as $x)$values[$x['platform_key']][$x['stat_date']]=$x['metric_value'];$q=db()->prepare('SELECT * FROM content_updates WHERE client_id=? AND update_date<=? ORDER BY update_date,content_kind');$q->execute([$clientId,$to]);$updates=$q->fetchAll();$contentFrom=$updates?($updates[0]['update_date']??$from):$from;$dailyLines=[];foreach($metricPlatforms as $key=>$label){$a=$values[$key][$from]??null;$b=$values[$key][$to]??null;$d=($a!==null&&$b!==null)?$b-$a:null;$dailyLines[]=$label.' '.($a??'—').' → '.($b??'—')."\n".($d===null?'—':(($d>=0?'↑':'↓').abs($d).($key==='google-business'?'条 reviews':'个')));}$dailyText=date('m月d号',strtotime($from)).'-'.date('m月d号',strtotime($to))."\n各平台数据\n".implode("\n",$dailyLines);
 render('Daily Updates',function()use($clientId,$clientName,$from,$to,$metricPlatforms,$contentPlatforms,$clients,$values,$updates,$dailyText){?><h1><?=h($clientName)?> Daily & Content Update</h1><form class="toolbar no-print"><input type="hidden" name="r" value="daily_updates_v3"><?php if(user()['role']!=='client'):?><div class="field"><label>Client</label><select name="client_id"><?php foreach($clients as $c):?><option value="<?=$c['id']?>" <?=$clientId===(int)$c['id']?'selected':''?>><?=h($c['name'])?></option><?php endforeach;?></select></div><?php endif;?><div class="field"><label>From</label><input type="date" name="from" value="<?=h($from)?>"></div><div class="field"><label>To</label><input type="date" name="to" value="<?=h($to)?>"></div><button class="btn">View</button></form><div class="toolbar no-print"><button class="btn" type="button" onclick="copyDaily()">Copy daily text</button><a class="btn" target="_blank" href="https://wa.me/?text=<?=rawurlencode($dailyText)?>">Share to WhatsApp</a></div><div class="card"><h2>Daily Update (text)</h2><pre id="dailyText" style="white-space:pre-wrap;font:inherit"><?=h($dailyText)?></pre><?php if(user()['role']!=='client'):?><form method="post" onsubmit="return confirm('Delete the daily data for both selected dates?')"><input type="hidden" name="_csrf" value="<?=csrf()?>"><input type="hidden" name="action" value="delete_daily"><input type="hidden" name="client_id" value="<?=$clientId?>"><input type="hidden" name="from" value="<?=h($from)?>"><input type="hidden" name="to" value="<?=h($to)?>"><button class="btn gray">Delete daily data</button></form><?php endif;?></div><div class="card" style="margin-top:16px"><div class="toolbar"><div style="flex:1"><h2>Content Update (image)</h2><p class="muted">Graphic / video published by date and platform.</p></div><button class="btn" type="button" onclick="downloadContent()">Generate content image</button></div><div id="contentCard"><h2><?=h($clientName)?></h2><h3><?=date('d/m',strtotime($from))?> - <?=date('d/m',strtotime($to))?></h3><table><tr><th>Date</th><th>Content</th><?php foreach($contentPlatforms as $label):?><th><?=h($label)?></th><?php endforeach;?></tr><?php foreach($updates as $u):$ps=json_decode($u['platforms_json']??'[]',true)?:[];?><tr><td><?=date('d/m',strtotime($u['update_date']))?></td><td><?=h($u['content_kind']==='video'?'视频 / Video':'图文 / Graphic')?></td><?php foreach($contentPlatforms as $key=>$label):?><td style="text-align:center;font-size:22px"><?=in_array($key,$ps,true)?'☑':'☐'?></td><?php endforeach;?></tr><?php endforeach;?></table></div><?php if(user()['role']!=='client'):?><form method="post" class="toolbar"><input type="hidden" name="_csrf" value="<?=csrf()?>"><input type="hidden" name="action" value="content"><input type="hidden" name="client_id" value="<?=$clientId?>"><input type="hidden" name="from" value="<?=h($from)?>"><input type="hidden" name="to" value="<?=h($to)?>"><div class="field"><label>Date</label><input type="date" name="update_date" value="<?=h($to)?>" required></div><div class="field"><label>Content</label><select name="content_kind"><option value="graphic">Graphic / 图文</option><option value="video">Video / 视频</option></select></div><?php foreach($contentPlatforms as $key=>$label):?><label><input type="checkbox" name="content_platforms[]" value="<?=h($key)?>"> <?=h($label)?></label><?php endforeach;?><button class="btn">Save content update</button></form><?php foreach($updates as $u):?><form method="post" style="display:inline" onsubmit="return confirm('Delete this content update? This cannot be undone.')"><input type="hidden" name="_csrf" value="<?=csrf()?>"><input type="hidden" name="action" value="delete_content"><input type="hidden" name="content_id" value="<?=$u['id']?>"><input type="hidden" name="client_id" value="<?=$clientId?>"><input type="hidden" name="from" value="<?=h($from)?>"><input type="hidden" name="to" value="<?=h($to)?>"><button class="btn small gray">Delete <?=h($u['update_date'])?> <?=h($u['content_kind'])?></button></form><?php endforeach;?><?php endif;?></div><?php if(user()['role']!=='client'):?><div class="card" style="margin-top:16px"><h2>Enter / update daily totals</h2><form method="post"><input type="hidden" name="_csrf" value="<?=csrf()?>"><input type="hidden" name="action" value="stats"><input type="hidden" name="client_id" value="<?=$clientId?>"><input type="hidden" name="from" value="<?=h($from)?>"><input type="hidden" name="to" value="<?=h($to)?>"><table><tr><th>Platform</th><th><?=h($from)?></th><th><?=h($to)?></th></tr><?php foreach($metricPlatforms as $key=>$label):?><tr><td><?=h($label)?></td><td><input type="number" min="0" name="start[<?=h($key)?>]" value="<?=h($values[$key][$from]??'')?>"></td><td><input type="number" min="0" name="end[<?=h($key)?>]" value="<?=h($values[$key][$to]??'')?>"></td></tr><?php endforeach;?></table><button class="btn">Save daily data</button></form></div><?php endif;?><script>async function copyDaily(){let t=document.getElementById('dailyText').innerText;await navigator.clipboard.writeText(t);alert('Daily update copied.')}function downloadContent(){let table=document.querySelector('#contentCard table'),rows=[...table.rows].map(r=>[...r.cells].map(c=>c.innerText));let c=document.createElement('canvas');c.width=1400;c.height=Math.max(500,230+rows.length*70);let x=c.getContext('2d');x.fillStyle='#fff';x.fillRect(0,0,c.width,c.height);x.fillStyle='#fff500';x.fillRect(0,0,c.width,160);x.fillStyle='#111';x.textAlign='center';x.font='bold 42px Arial';x.fillText(<?=json_encode($clientName,JSON_UNESCAPED_UNICODE)?>,700,65);x.font='bold 30px Arial';x.fillText(<?=json_encode(date('d/m',strtotime($from)).'-'.date('d/m',strtotime($to)))?>,700,115);let cw=c.width/rows[0].length,y=210;x.font='bold 25px Arial';for(let ri=0;ri<rows.length;ri++){for(let ci=0;ci<rows[ri].length;ci++){x.strokeStyle='#bbb';x.strokeRect(ci*cw,y-38,cw,62);x.fillText(rows[ri][ci],ci*cw+cw/2,y);}y+=62;}let a=document.createElement('a');a.download=<?=json_encode($clientName.'-content-'.$from.'-'.$to.'.png',JSON_UNESCAPED_UNICODE)?>;a.href=c.toDataURL('image/png');a.click();}</script><?php });exit;
}
if($r==='daily_updates_v2'){
 $clients=db()->query('SELECT id,name FROM clients WHERE status="active" ORDER BY name')->fetchAll();$clientId=user()['role']==='client'?(int)user()['client_id']:(int)($_REQUEST['client_id']??($clients[0]['id']??0));$from=$_REQUEST['from']??date('Y-m-d',strtotime('-3 days'));$to=$_REQUEST['to']??date('Y-m-d');$platforms=['facebook'=>'Facebook','instagram'=>'Instagram','google-business'=>'Google','tiktok'=>'TikTok','xiaohongshu'=>'小红书'];$clientName='Client';foreach($clients as $c)if((int)$c['id']===$clientId)$clientName=$c['name'];
 if($_SERVER['REQUEST_METHOD']==='POST'){require_role(['admin','staff']);verify_csrf();$q=db()->prepare('INSERT INTO daily_platform_stats(client_id,stat_date,platform_key,metric_key,metric_value,entered_by) VALUES(?,?,?,?,?,?) ON DUPLICATE KEY UPDATE metric_value=VALUES(metric_value),entered_by=VALUES(entered_by)');foreach($platforms as $key=>$label){$metric=$key==='google-business'?'reviews':'followers';foreach(['start'=>$from,'end'=>$to] as $side=>$date)if(isset($_POST[$side][$key])&&$_POST[$side][$key]!=='')$q->execute([$clientId,$date,$key,$metric,(int)$_POST[$side][$key],user()['id']]);}flash('Daily platform update saved.');go('daily_updates_v2',['client_id'=>$clientId,'from'=>$from,'to'=>$to]);}
 $values=[];if($clientId){$q=db()->prepare('SELECT platform_key,stat_date,metric_value FROM daily_platform_stats WHERE client_id=? AND stat_date IN (?,?)');$q->execute([$clientId,$from,$to]);foreach($q as $x)$values[$x['platform_key']][$x['stat_date']]=$x['metric_value'];}
 render('Daily Updates',function()use($clientId,$clientName,$from,$to,$platforms,$clients,$values){?><div class="toolbar"><div style="flex:1"><h1><?=h($clientName)?> Daily Update</h1><p class="muted">Staff updates this once; the dashboard and client view update immediately.</p></div></div><form class="toolbar no-print"><input type="hidden" name="r" value="daily_updates_v2"><?php if(user()['role']!=='client'):?><div class="field"><label>Client</label><select name="client_id"><?php foreach($clients as $c):?><option value="<?=$c['id']?>" <?=$clientId===(int)$c['id']?'selected':''?>><?=h($c['name'])?></option><?php endforeach;?></select></div><?php endif;?><div class="field"><label>Start date</label><input type="date" name="from" value="<?=h($from)?>"></div><div class="field"><label>End date</label><input type="date" name="to" value="<?=h($to)?>"></div><button class="btn">View</button></form><?php if($clientId):?><div class="toolbar no-print"><button type="button" class="btn" onclick="copyDaily()">Copy updated data</button><button type="button" class="btn gray" onclick="downloadDaily()">Generate image</button></div><div class="card" id="dailyCard"><h2><?=h($clientName)?></h2><h3><?=date('m月d号',strtotime($from))?> - <?=date('m月d号',strtotime($to))?></h3><h3>各平台数据</h3><div id="dailyText"><?php foreach($platforms as $key=>$label):$a=$values[$key][$from]??null;$b=$values[$key][$to]??null;$d=($a!==null&&$b!==null)?$b-$a:null;?><p><b><?=h($label)?></b> <?=h($a??'—')?>👉🏻<?=h($b??'—')?><br><?=($d===null?'—':(($d>=0?'↑':'↓').abs($d).($key==='google-business'?'条 reviews':'个')))?></p><?php endforeach;?></div></div><?php if(user()['role']!=='client'):?><div class="card" style="margin-top:16px"><h2>Enter / update totals</h2><form method="post"><input type="hidden" name="_csrf" value="<?=csrf()?>"><input type="hidden" name="client_id" value="<?=$clientId?>"><input type="hidden" name="from" value="<?=h($from)?>"><input type="hidden" name="to" value="<?=h($to)?>"><table><tr><th>Platform</th><th><?=h($from)?></th><th><?=h($to)?></th></tr><?php foreach($platforms as $key=>$label):?><tr><td><?=h($label)?></td><td><input type="number" min="0" name="start[<?=h($key)?>]" value="<?=h($values[$key][$from]??'')?>"></td><td><input type="number" min="0" name="end[<?=h($key)?>]" value="<?=h($values[$key][$to]??'')?>"></td></tr><?php endforeach;?></table><button class="btn">Save daily update</button></form></div><?php endif;?><script>function dailyPlain(){return <?=json_encode(date('m月d号',strtotime($from)).'-'.date('m月d号',strtotime($to))."\n各平台数据\n",JSON_UNESCAPED_UNICODE)?>+document.getElementById('dailyText').innerText}async function copyDaily(){let t=dailyPlain();try{await navigator.clipboard.writeText(t);alert('Updated data copied.')}catch(e){let x=document.createElement('textarea');x.value=t;document.body.appendChild(x);x.select();document.execCommand('copy');x.remove();}}function downloadDaily(){let lines=dailyPlain().split('\n'),c=document.createElement('canvas');c.width=1080;c.height=1080;let x=c.getContext('2d');x.fillStyle='#fff';x.fillRect(0,0,c.width,c.height);x.fillStyle='#fff500';x.fillRect(0,0,c.width,170);x.fillStyle='#162033';x.textAlign='center';x.font='bold 44px Arial, sans-serif';x.fillText(<?=json_encode($clientName,JSON_UNESCAPED_UNICODE)?>,540,70);x.font='bold 34px Arial, sans-serif';x.fillText(lines.shift(),540,125);x.textAlign='left';x.font='bold 34px Arial, sans-serif';let y=230;for(let line of lines){if(!line.trim())continue;x.fillText(line,90,y);y+=58;}let a=document.createElement('a');a.download=<?=json_encode($clientName.'-'.$from.'-'.$to.'.png',JSON_UNESCAPED_UNICODE)?>;a.href=c.toDataURL('image/png');a.click();}</script><?php endif;?><?php });exit;
}

if($r==='daily_updates_legacy'){
 $clientId=user()['role']==='client'?(int)user()['client_id']:(int)($_GET['client_id']??$_POST['client_id']??0);$from=$_GET['from']??$_POST['from']??date('Y-m-d',strtotime('-3 days'));$to=$_GET['to']??$_POST['to']??date('Y-m-d');$platforms=['facebook'=>'Facebook','instagram'=>'Instagram','google-business'=>'Google','tiktok'=>'TikTok','xiaohongshu'=>'小红书'];
 if($_SERVER['REQUEST_METHOD']==='POST'){require_role(['admin','staff']);verify_csrf();$q=db()->prepare('INSERT INTO daily_platform_stats(client_id,stat_date,platform_key,metric_key,metric_value,entered_by) VALUES(?,?,?,?,?,?) ON DUPLICATE KEY UPDATE metric_value=VALUES(metric_value),entered_by=VALUES(entered_by)');foreach($platforms as $key=>$label){$metric=$key==='google-business'?'reviews':'followers';if(isset($_POST['start'][$key])&&$_POST['start'][$key]!=='')$q->execute([$clientId,$from,$key,$metric,(int)$_POST['start'][$key],user()['id']]);if(isset($_POST['end'][$key])&&$_POST['end'][$key]!=='')$q->execute([$clientId,$to,$key,$metric,(int)$_POST['end'][$key],user()['id']]);}flash('Daily platform update saved.');go('daily_updates',['client_id'=>$clientId,'from'=>$from,'to'=>$to]);}
 $clients=db()->query('SELECT id,name FROM clients WHERE status="active" ORDER BY name')->fetchAll();$clientName='Select a client';foreach($clients as $c)if((int)$c['id']===$clientId)$clientName=$c['name'];$comparison=$clientId?dailyComparison($clientId,$from,$to):[];$values=[];foreach($comparison as $x)$values[$x['platform_key']]=$x;
 render('Daily Updates',function()use($clientId,$clientName,$from,$to,$platforms,$clients,$values){?><div class="toolbar"><div style="flex:1"><h1><?=h($clientName)?> Daily Update</h1><p class="muted">Compare platform totals between two dates. Changes are calculated automatically.</p></div></div><form class="toolbar no-print" method="get"><input type="hidden" name="r" value="daily_updates"><?php if(user()['role']!=='client'):?><div class="field"><label>Client</label><select name="client_id" required><?php foreach($clients as $c):?><option value="<?=$c['id']?>" <?=$clientId===(int)$c['id']?'selected':''?>><?=h($c['name'])?></option><?php endforeach;?></select></div><?php endif;?><div class="field"><label>Start date</label><input type="date" name="from" value="<?=h($from)?>"></div><div class="field"><label>End date</label><input type="date" name="to" value="<?=h($to)?>"></div><button class="btn">View</button></form><?php if($clientId):?><div class="card"><h2><?=date('m月d号',strtotime($from))?> - <?=date('m月d号',strtotime($to))?></h2><h3>各平台数据</h3><?php foreach($platforms as $key=>$label):$x=$values[$key]??null;$start=$x['start_value']??null;$end=$x['end_value']??null;$delta=($start!==null&&$end!==null)?(int)$end-(int)$start:null;?><p style="font-size:18px"><b><?=h($label)?></b> <?=h($start??'—')?> 👉🏻 <?=h($end??'—')?><?php if($delta!==null):?><br><span class="<?=($delta>=0?'ok':'error')?>"><?=($delta>=0?'↑':'↓')?><?=abs($delta)?><?=($key==='google-business'?'条 reviews':'个')?></span><?php endif;?></p><?php endforeach;?></div><?php if(user()['role']!=='client'):?><div class="card" style="margin-top:16px"><h2>Enter / update totals</h2><form method="post"><input type="hidden" name="_csrf" value="<?=csrf()?>"><input type="hidden" name="client_id" value="<?=$clientId?>"><input type="hidden" name="from" value="<?=h($from)?>"><input type="hidden" name="to" value="<?=h($to)?>"><table><tr><th>Platform</th><th><?=h($from)?></th><th><?=h($to)?></th></tr><?php foreach($platforms as $key=>$label):$x=$values[$key]??null;?><tr><td><?=h($label)?></td><td><input type="number" min="0" name="start[<?=h($key)?>]" value="<?=h($x['start_value']??'')?>"></td><td><input type="number" min="0" name="end[<?=h($key)?>]" value="<?=h($x['end_value']??'')?>"></td></tr><?php endforeach;?></table><button class="btn">Save daily update</button></form></div><?php endif;?><?php endif;?><?php });exit;
}

if($r==='user_edit'){
 require_role(['admin']);$id=(int)($_GET['id']??$_POST['id']??0);$q=db()->prepare('SELECT * FROM users WHERE id=?');$q->execute([$id]);$editUser=$q->fetch();if(!$editUser){http_response_code(404);exit('User not found');}
 if($_SERVER['REQUEST_METHOD']==='POST'){verify_csrf();$role=in_array($_POST['role']??'',['admin','staff','client'],true)?$_POST['role']:'staff';$clientId=$role==='client'?((int)($_POST['client_id']??0)?:null):null;$permissions=[];foreach(['create','edit','delete','manage_clients','manage_projects','manage_reports','manage_tracking','manage_contracts'] as $p)$permissions[$p]=!empty($_POST['permissions'][$p]);db()->prepare('UPDATE users SET name=?,email=?,role=?,job_title=?,permissions_json=?,client_id=?,active=? WHERE id=?')->execute([trim($_POST['name']),strtolower(trim($_POST['email'])),$role,trim($_POST['job_title']??''),json_encode($permissions),$clientId,!empty($_POST['active'])?1:0,$id]);flash('User role, title and permissions updated.');go('users');}
 $clients=db()->query('SELECT id,name FROM clients WHERE status="active" ORDER BY name')->fetchAll();$permissions=json_decode($editUser['permissions_json']??'{}',true)?:[];render('Edit user',function()use($editUser,$clients,$permissions){?><div class="card"><h1>Edit user</h1><form method="post"><input type="hidden" name="_csrf" value="<?=csrf()?>"><input type="hidden" name="id" value="<?=$editUser['id']?>"><div class="field"><label>Name</label><input name="name" value="<?=h($editUser['name'])?>" required></div><div class="field"><label>Email</label><input type="email" name="email" value="<?=h($editUser['email'])?>" required></div><div class="field"><label>System role</label><select name="role"><?php foreach(['admin','staff','client'] as $role):?><option value="<?=$role?>" <?=$editUser['role']===$role?'selected':''?>><?=h(ucfirst($role))?></option><?php endforeach;?></select></div><div class="field"><label>Company position / title</label><input name="job_title" value="<?=h($editUser['job_title']??'')?>" placeholder="Editor / Sales / Graphic Designer"></div><div class="field"><label>Client (client role only)</label><select name="client_id"><option value="">—</option><?php foreach($clients as $c):?><option value="<?=$c['id']?>" <?=(int)$editUser['client_id']===(int)$c['id']?'selected':''?>><?=h($c['name'])?></option><?php endforeach;?></select></div><h2>Staff permissions</h2><?php $defaults=['create'=>1,'edit'=>1,'delete'=>0,'manage_clients'=>1,'manage_projects'=>1,'manage_reports'=>1,'manage_tracking'=>0,'manage_contracts'=>0];foreach($defaults as $key=>$default):$checked=array_key_exists($key,$permissions)?$permissions[$key]:$default;?><label style="display:block;margin:8px 0"><input type="checkbox" name="permissions[<?=$key?>]" value="1" <?=$checked?'checked':''?>> <?=h(ucwords(str_replace('_',' ',$key)))?></label><?php endforeach;?><br><label><input type="checkbox" name="active" value="1" <?=$editUser['active']?'checked':''?>> Active</label><br><br><button class="btn">Save user settings</button> <a class="btn gray" href="<?=h(route('users'))?>">Cancel</a></form></div><?php });exit;
}

if($r==='users'){
 require_role(['admin','staff']);
 if($_SERVER['REQUEST_METHOD']==='POST'){
  require_role(['admin']);verify_csrf();$action=$_POST['action']??'user';
  if($action==='invite'){$token=bin2hex(random_bytes(24));db()->prepare('INSERT INTO staff_invites(token_hash,email,expires_at,created_by) VALUES(?,?,DATE_ADD(NOW(),INTERVAL 7 DAY),?)')->execute([hash('sha256',$token),strtolower(trim($_POST['invite_email']))?:null,user()['id']]);$_SESSION['invite_link']=route('register',['token'=>$token]);flash('Staff invitation link generated.');go('users');}
  if($action==='permissions'){$uid=(int)$_POST['user_id'];db()->prepare('DELETE FROM user_client_access WHERE user_id=?')->execute([$uid]);$denied=$_POST['denied_clients']??[];$q=db()->prepare('INSERT INTO user_client_access(user_id,client_id,can_access) VALUES(?,?,0)');foreach($denied as $cid)$q->execute([$uid,(int)$cid]);flash('Staff client access updated.');go('users',['access'=>$uid]);}
  $role=$_POST['role'];$client=$role==='client'?(int)$_POST['client_id']:null;$hash=password_hash($_POST['password'],PASSWORD_DEFAULT);db()->prepare('INSERT INTO users(client_id,name,email,password_hash,role) VALUES(?,?,?,?,?)')->execute([$client,trim($_POST['name']),strtolower(trim($_POST['email'])),$hash,$role]);flash('User created.');go('users');
 }
 $rows=db()->query('SELECT u.*,c.name client_name FROM users u LEFT JOIN clients c ON c.id=u.client_id ORDER BY u.created_at DESC')->fetchAll();
 $clients=db()->query('SELECT id,name FROM clients WHERE status="active" ORDER BY name')->fetchAll();
 $accessId=(int)($_GET['access']??0);$denied=[];if($accessId){$q=db()->prepare('SELECT client_id FROM user_client_access WHERE user_id=? AND can_access=0');$q->execute([$accessId]);$denied=array_map('intval',$q->fetchAll(PDO::FETCH_COLUMN));}$inviteLink=$_SESSION['invite_link']??null;unset($_SESSION['invite_link']);
 render('Users',function()use($rows,$clients,$accessId,$denied,$inviteLink){
  ?><div class="split"><section><h1>Users</h1><?php if($inviteLink):?><div class="card"><b>Invitation link (valid 7 days)</b><div class="code"><input value="<?=h($inviteLink)?>" readonly onclick="this.select()"></div></div><?php endif;?><table><tr><th>Name</th><th>Email</th><th>Role</th><th>Client access</th><th></th></tr><?php
  foreach($rows as $x){?><tr><td><?=h($x['name'])?><?php if(!empty($x['job_title'])):?><br><span class="muted"><?=h($x['job_title'])?></span><?php endif;?></td><td><?=h($x['email'])?></td><td><span class="badge"><?=h($x['role'])?></span></td><td><?=h($x['role']==='staff'?'All clients by default':($x['client_name']?:'—'))?></td><td><?php if(user()['role']==='admin'):?><a class="btn small gray" href="<?=h(route('user_edit',['id'=>$x['id']]))?>">Edit</a> <?php if($x['role']==='staff'):?><a class="btn small gray" href="<?=h(route('users',['access'=>$x['id']]))?>">Client access</a><?php endif;?><?php endif;?></td></tr><?php }
  ?></table></section><?php if(user()['role']==='admin'){?><aside><?php if($accessId):?><div class="card"><h2>Restrict client access</h2><p class="muted">Tick only clients this staff member must not see.</p><form method="post"><input type="hidden" name="_csrf" value="<?=csrf()?>"><input type="hidden" name="action" value="permissions"><input type="hidden" name="user_id" value="<?=$accessId?>"><?php foreach($clients as $c):?><label style="display:block;margin:8px 0"><input type="checkbox" name="denied_clients[]" value="<?=$c['id']?>" <?=in_array((int)$c['id'],$denied,true)?'checked':''?>> <?=h($c['name'])?></label><?php endforeach;?><button class="btn">Save access</button></form></div><?php endif;?><div class="card" style="margin-top:16px"><h2>Invite staff</h2><form method="post"><input type="hidden" name="_csrf" value="<?=csrf()?>"><input type="hidden" name="action" value="invite"><div class="field"><label>Email (optional)</label><input type="email" name="invite_email"></div><button class="btn">Generate invite link</button></form></div><div class="card" style="margin-top:16px"><h2>Add user manually</h2><form method="post"><input type="hidden" name="_csrf" value="<?=csrf()?>"><input type="hidden" name="action" value="user"><div class="field"><label>Name</label><input name="name" required></div><div class="field"><label>Email</label><input type="email" name="email" required></div><div class="field"><label>Password</label><input type="password" name="password" minlength="10" required></div><div class="field"><label>Role</label><select name="role"><option>staff</option><option>client</option><option>admin</option></select></div><div class="field"><label>Client (client role only)</label><select name="client_id"><option value="">—</option><?php foreach($clients as $c){?><option value="<?=$c['id']?>"><?=h($c['name'])?></option><?php }?></select></div><button class="btn">Create</button></form></div></aside><?php }?></div><?php
 });exit;
}

if($r==='projects'){
 [$scope,$args]=scopeSql();$q=db()->prepare("SELECT c.id client_id,c.name client_name,c.code,c.social_links_json,p.id project_id,p.title project_title,p.status_key,ci.id content_id,ci.title content_title,ci.status_key content_status,ci.platforms_json,ci.published_at,ci.sort_order FROM clients c JOIN projects p ON p.client_id=c.id LEFT JOIN content_items ci ON ci.project_id=p.id WHERE 1=1 $scope ORDER BY c.name,p.id,ci.sort_order,COALESCE(ci.published_at,ci.created_at) DESC");$q->execute($args);$groups=[];foreach($q->fetchAll() as $x){$cid=$x['client_id'];if(!isset($groups[$cid]))$groups[$cid]=['name'=>$x['client_name'],'code'=>$x['code'],'social'=>$x['social_links_json'],'projects'=>[]];$pid=$x['project_id'];if(!isset($groups[$cid]['projects'][$pid]))$groups[$cid]['projects'][$pid]=['title'=>$x['project_title'],'status'=>$x['status_key'],'contents'=>[]];if($x['content_id'])$groups[$cid]['projects'][$pid]['contents'][]=$x;}
 render('Client Projects',function()use($groups){?><div class="toolbar"><div style="flex:1"><h1>Client Projects</h1><p class="muted">Select a client, then select one of its workspaces.</p></div><?php if(user()['role']!=='client'):?><a class="btn" href="<?=h(route('workspace_edit'))?>">+ Add client project</a><?php endif;?></div><?php foreach($groups as $g):$portal=$g['code']?url($g['code']):'';$social=json_decode($g['social']??'{}',true)?:[];?><details class="card" style="margin-bottom:14px"><summary style="cursor:pointer;font-size:20px"><b><?=h($g['name'])?></b></summary><div style="margin:12px 0"><?php if($portal):?><b>Client portal:</b> <a href="<?=h($portal)?>" target="_blank"><?=h($portal)?></a> <button class="btn small gray" type="button" onclick="navigator.clipboard.writeText(<?=h(json_encode($portal))?>)">Copy</button><?php endif;?><?php foreach($social as $platform=>$link):?> · <a href="<?=h($link)?>" target="_blank"><?=h(ucfirst($platform))?></a><?php endforeach;?></div><?php foreach($g['projects'] as $pid=>$p):?><details style="margin-top:12px;border:1px solid #dbe3ef;border-radius:12px;padding:12px"><summary style="cursor:pointer"><b><?=h($p['title'])?></b> <span class="badge"><?=h($p['status'])?></span></summary><div class="toolbar"><span style="flex:1"></span><a class="btn small" href="<?=h(route('project',['id'=>$pid]))?>">Open workspace</a></div><?php if(!$p['contents']):?><p class="muted">No content added yet.</p><?php else:?><table><tr><th>No.</th><th>Posting</th><th>Platforms</th><th>Status</th><th>Published</th><th></th></tr><?php foreach($p['contents'] as $i=>$ci):?><tr><td><?=number_format($i+1)?></td><td><?=h($ci['content_title'])?></td><td><?=h(implode(', ',json_decode($ci['platforms_json']??'[]',true)?:[]))?></td><td><span class="badge"><?=h($ci['content_status'])?></span></td><td><?=h($ci['published_at']?:'Not published')?></td><td><a class="btn small" href="<?=h(route('content',['id'=>$ci['content_id']]))?>">Open</a></td></tr><?php endforeach;?></table><?php endif;?></details><?php endforeach;?></details><?php endforeach;?><?php });exit;
}

if($r==='projects_flat'){
 [$scope,$args]=scopeSql();$q=db()->prepare("SELECT p.*,c.name client_name,(SELECT COUNT(*) FROM content_items ci WHERE ci.project_id=p.id) contents,(SELECT COUNT(*) FROM tracking_links t WHERE t.project_id=p.id) links FROM projects p JOIN clients c ON c.id=p.client_id WHERE 1=1 $scope ORDER BY p.updated_at DESC");$q->execute($args);$rows=$q->fetchAll();render('Projects',function()use($rows){?><div class="toolbar"><div style="flex:1"><h1>Client Projects</h1><p class="muted">Open a client workspace to see every posting, video, website and ad in one place.</p></div><?php if(user()['role']!=='client'):?><a class="btn" href="<?=h(route('project_edit'))?>">+ Add client project</a><?php endif;?></div><table><tr><th>Project / Client</th><th>Service</th><th>Status</th><th>Contents</th><th>Links</th><th></th></tr><?php foreach($rows as $x):?><tr><td><b><?=h($x['title'])?></b><br><span class="muted"><?=h($x['client_name'])?> · <?=h($x['campaign'])?></span></td><td><?=h($x['service_type_key'])?></td><td><span class="badge"><?=h($x['status_key'])?></span></td><td><?=$x['contents']?></td><td><?=$x['links']?></td><td><a class="btn small" href="<?=h(route('project',['id'=>$x['id']]))?>">Open</a></td></tr><?php endforeach;?></table><?php });exit;
}

if($r==='project_edit')go('workspace_edit',!empty($_GET['id'])?['id'=>(int)$_GET['id']]:[]);

if($r==='workspace_edit'){
 require_role(['admin','staff']);$id=(int)($_GET['id']??0);$p=[];if($id){$q=db()->prepare('SELECT * FROM projects WHERE id=?');$q->execute([$id]);$p=$q->fetch()?:[];}
 if(!canDo('manage_projects')||($id&&!canDo('edit'))||(!$id&&!canDo('create'))){http_response_code(403);exit('You do not have permission to manage client projects.');}
 if($_SERVER['REQUEST_METHOD']==='POST'){verify_csrf();$platforms=array_values(array_unique(array_filter($_POST['platforms']??[])));$first=$platforms[0]??null;$a=[(int)$_POST['client_id'],trim($_POST['title']),trim($_POST['campaign']),$first,json_encode($platforms),$_POST['service_type_key']?:null,$_POST['status_key']?:null,$_POST['start_date']?:null,$_POST['end_date']?:null,$_POST['budget']?:null,trim($_POST['notes'])];if($id){$a[]=$id;db()->prepare('UPDATE projects SET client_id=?,title=?,campaign=?,platform_key=?,platforms_json=?,service_type_key=?,status_key=?,start_date=?,end_date=?,budget=?,notes=? WHERE id=?')->execute($a);}else{$a[]=user()['id'];db()->prepare('INSERT INTO projects(client_id,title,campaign,platform_key,platforms_json,service_type_key,status_key,start_date,end_date,budget,notes,created_by) VALUES(?,?,?,?,?,?,?,?,?,?,?,?)')->execute($a);$id=(int)db()->lastInsertId();}flash('Client project saved.');go('project',['id'=>$id]);}
 $selected=json_decode($p['platforms_json']??'[]',true)?:array_filter([$p['platform_key']??null]);$clients=db()->query('SELECT id,name FROM clients WHERE status="active" ORDER BY name')->fetchAll();render($id?'Edit client project':'Add client project',function()use($p,$clients,$selected){?><h1><?=!empty($p)?'Edit':'Add'?> client project</h1><form method="post" class="card"><input type="hidden" name="_csrf" value="<?=csrf()?>"><div class="grid"><div class="field"><label>Client</label><select name="client_id" required><?php foreach($clients as $c):?><option value="<?=$c['id']?>" <?=($p['client_id']??'')==$c['id']?'selected':''?>><?=h($c['name'])?></option><?php endforeach;?></select></div><div class="field"><label>Workspace title</label><input name="title" value="<?=h($p['title']??'Monthly Marketing')?>" required></div><div class="field"><label>Campaign</label><input name="campaign" value="<?=h($p['campaign']??'')?>"></div><div class="field"><label>Platforms (multi-select)</label><select name="platforms[]" multiple size="8"><?php foreach(settingOptions('platforms') as $x):?><option value="<?=h($x['setting_key'])?>" <?=in_array($x['setting_key'],$selected,true)?'selected':''?>><?=h($x['label'])?></option><?php endforeach;?></select></div><div class="field"><label>Service type</label><select name="service_type_key"><?=selectOptions(settingOptions('service_types'),$p['service_type_key']??null)?></select></div><div class="field"><label>Status</label><select name="status_key"><?=selectOptions(settingOptions('statuses'),$p['status_key']??null)?></select></div><div class="field"><label>Start date</label><input type="date" name="start_date" value="<?=h($p['start_date']??'')?>"></div><div class="field"><label>End date</label><input type="date" name="end_date" value="<?=h($p['end_date']??'')?>"></div><div class="field"><label>Budget</label><input type="number" step="0.01" name="budget" value="<?=h($p['budget']??'')?>"></div></div><div class="field"><label>Notes</label><textarea name="notes"><?=h($p['notes']??'')?></textarea></div><button class="btn">Save client project</button></form><?php });exit;
}

if($r==='project_edit_legacy'){
 require_role(['admin','staff']);$id=(int)($_GET['id']??0);$p=[];if($id){$q=db()->prepare('SELECT * FROM projects WHERE id=?');$q->execute([$id]);$p=$q->fetch()?:[];}$custom=settingOptions('custom_fields');if($_SERVER['REQUEST_METHOD']==='POST'){verify_csrf();$customData=[];foreach($custom as $f)$customData[$f['setting_key']]=$_POST['custom'][$f['setting_key']]??null;$a=[(int)$_POST['client_id'],trim($_POST['title']),trim($_POST['campaign']),$_POST['platform_key'],$_POST['content_type_key'],$_POST['service_type_key'],$_POST['status_key'],trim($_POST['original_url']),$_POST['start_date']?:null,$_POST['end_date']?:null,$_POST['budget']?:null,trim($_POST['notes']),json_encode($customData)];if($id){$a[]=$id;db()->prepare('UPDATE projects SET client_id=?,title=?,campaign=?,platform_key=?,content_type_key=?,service_type_key=?,status_key=?,original_url=?,start_date=?,end_date=?,budget=?,notes=?,custom_data=? WHERE id=?')->execute($a);}else{$a[]=user()['id'];db()->prepare('INSERT INTO projects(client_id,title,campaign,platform_key,content_type_key,service_type_key,status_key,original_url,start_date,end_date,budget,notes,custom_data,created_by) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?)')->execute($a);$id=(int)db()->lastInsertId();}flash('Project saved.');go('project',['id'=>$id]);}$clients=db()->query('SELECT id,name FROM clients WHERE status="active" ORDER BY name')->fetchAll();$existing=json_decode($p['custom_data']??'{}',true)?:[];render($id?'Edit project':'Add project',function()use($p,$clients,$custom,$existing){?><h1><?=!empty($p)?'Edit':'Add'?> project</h1><form method="post" class="card"><input type="hidden" name="_csrf" value="<?=csrf()?>"><div class="grid"><div class="field"><label>Client</label><select name="client_id" required><?php foreach($clients as $c):?><option value="<?=$c['id']?>" <?=($p['client_id']??'')==$c['id']?'selected':''?>><?=h($c['name'])?></option><?php endforeach;?></select></div><div class="field"><label>Title</label><input name="title" value="<?=h($p['title']??'')?>" required></div><div class="field"><label>Campaign</label><input name="campaign" value="<?=h($p['campaign']??'')?>"></div><div class="field"><label>Original URL</label><input type="url" name="original_url" value="<?=h($p['original_url']??'')?>"></div><div class="field"><label>Platform</label><select name="platform_key"><?=selectOptions(settingOptions('platforms'),$p['platform_key']??null)?></select></div><div class="field"><label>Content type</label><select name="content_type_key"><?=selectOptions(settingOptions('content_types'),$p['content_type_key']??null)?></select></div><div class="field"><label>Service type</label><select name="service_type_key"><?=selectOptions(settingOptions('service_types'),$p['service_type_key']??null)?></select></div><div class="field"><label>Status</label><select name="status_key"><?=selectOptions(settingOptions('statuses'),$p['status_key']??null)?></select></div><div class="field"><label>Start date</label><input type="date" name="start_date" value="<?=h($p['start_date']??'')?>"></div><div class="field"><label>End date</label><input type="date" name="end_date" value="<?=h($p['end_date']??'')?>"></div><div class="field"><label>Budget</label><input type="number" step="0.01" name="budget" value="<?=h($p['budget']??'')?>"></div><?php foreach($custom as $f): $meta=json_decode($f['meta_json']??'{}',true);if(($meta['module']??'projects')!=='projects')continue;$type=$meta['type']??'text';?><div class="field"><label><?=h($f['label'])?></label><?php if($type==='textarea'):?><textarea name="custom[<?=h($f['setting_key'])?>]"><?=h($existing[$f['setting_key']]??'')?></textarea><?php else:?><input type="<?=in_array($type,['number','date'],true)?$type:'text'?>" name="custom[<?=h($f['setting_key'])?>]" value="<?=h($existing[$f['setting_key']]??'')?>" <?=!empty($meta['required'])?'required':''?>><?php endif;?></div><?php endforeach;?></div><div class="field"><label>Notes</label><textarea name="notes"><?=h($p['notes']??'')?></textarea></div><button class="btn">Save project</button></form><?php });exit;
}

if($r==='content_edit')go('item_edit',array_filter(['project_id'=>(int)($_GET['project_id']??0),'id'=>(int)($_GET['id']??0),'kind'=>$_GET['kind']??null]));

if($r==='item_edit'){
 require_role(['admin','staff']);$id=(int)($_GET['id']??0);$projectId=(int)($_GET['project_id']??0);$item=null;if($id){$q=db()->prepare('SELECT * FROM content_items WHERE id=?');$q->execute([$id]);$item=$q->fetch();if(!$item){http_response_code(404);exit('Item not found');}$projectId=(int)$item['project_id'];}$q=db()->prepare('SELECT p.*,c.name client_name FROM projects p JOIN clients c ON c.id=p.client_id WHERE p.id=?');$q->execute([$projectId]);$project=$q->fetch();if(!$project){http_response_code(404);exit('Workspace not found');}$settings=[];foreach(db()->query("SELECT setting_group,setting_key,label FROM settings WHERE active=1 AND setting_group IN ('platforms','content_types','statuses') ORDER BY sort_order,label") as $s)$settings[$s['setting_group']][]=$s;$kind=$item['item_kind']??($_GET['kind']??'posting');
 if(!canDo('manage_projects')||($id&&!canDo('edit'))||(!$id&&!canDo('create'))){http_response_code(403);exit('You do not have permission to manage content items.');}
 if($_SERVER['REQUEST_METHOD']==='POST'){verify_csrf();$platformKeys=array_values(array_filter($_POST['platforms']??[]));$kind=in_array($_POST['item_kind']??'', ['posting','ads_campaign','video','website'],true)?$_POST['item_kind']:'posting';$budget=$_POST['campaign_budget']!==''?(float)$_POST['campaign_budget']:null;$vals=[trim($_POST['title']),$kind,$_POST['content_type_key']??'',json_encode($platformKeys),$_POST['status_key']??'',($_POST['scheduled_at']??'')?:null,($_POST['published_at']??'')?:null,$budget,trim($_POST['caption']??''),trim($_POST['notes']??''),(int)($_POST['sort_order']??0)];if($id){$vals[]=$id;db()->prepare('UPDATE content_items SET title=?,item_kind=?,content_type_key=?,platforms_json=?,status_key=?,scheduled_at=?,published_at=?,campaign_budget=?,caption=?,notes=?,sort_order=? WHERE id=?')->execute($vals);}else{$vals=array_merge([$projectId],$vals,[user()['id']]);db()->prepare('INSERT INTO content_items(project_id,title,item_kind,content_type_key,platforms_json,status_key,scheduled_at,published_at,campaign_budget,caption,notes,sort_order,created_by) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?)')->execute($vals);$id=(int)db()->lastInsertId();}flash('Item saved.');go('content',['id'=>$id]);}
 render($id?'Edit item':'Add item',function()use($id,$item,$project,$settings,$kind){$selected=json_decode($item['platforms_json']??'[]',true)?:[];?><h1><?=$id?'Edit':'Add'?> <?=h(ucwords(str_replace('_',' ',$kind)))?></h1><p class="muted"><?=h($project['client_name'])?> · <?=h($project['title'])?></p><form method="post" class="card"><input type="hidden" name="_csrf" value="<?=csrf()?>"><div class="grid"><div class="field"><label>Item type</label><select name="item_kind"><?php foreach(['posting'=>'Posting','ads_campaign'=>'Ads Campaign','video'=>'Video','website'=>'Website'] as $k=>$v):?><option value="<?=$k?>" <?=$kind===$k?'selected':''?>><?=$v?></option><?php endforeach;?></select></div><div class="field"><label>Title / campaign name</label><input name="title" value="<?=h($item['title']??'')?>" required></div><div class="field"><label>Content type</label><select name="content_type_key"><?php foreach($settings['content_types']??[] as $s):?><option value="<?=h($s['setting_key'])?>" <?=($item['content_type_key']??'')===$s['setting_key']?'selected':''?>><?=h($s['label'])?></option><?php endforeach;?></select></div><div class="field"><label>Status</label><select name="status_key"><?php foreach($settings['statuses']??[] as $s):?><option value="<?=h($s['setting_key'])?>" <?=($item['status_key']??'')===$s['setting_key']?'selected':''?>><?=h($s['label'])?></option><?php endforeach;?></select></div><div class="field"><label>Published platforms (multi-select)</label><select name="platforms[]" multiple size="8"><?php foreach($settings['platforms']??[] as $s):?><option value="<?=h($s['setting_key'])?>" <?=in_array($s['setting_key'],$selected,true)?'selected':''?>><?=h($s['label'])?></option><?php endforeach;?></select></div><div><div class="field"><label>Published date/time</label><input type="datetime-local" name="published_at" value="<?=h(!empty($item['published_at'])?date('Y-m-d\TH:i',strtotime($item['published_at'])):'')?>"></div><div class="field"><label>Campaign budget (RM)</label><input type="number" min="0" step="0.01" name="campaign_budget" value="<?=h($item['campaign_budget']??'')?>"></div></div></div><div class="field"><label>Notes</label><textarea name="notes"><?=h($item['notes']??'')?></textarea></div><button class="btn">Save item</button> <a class="btn gray" href="<?=h(route('project',['id'=>$project['id']]))?>">Cancel</a></form><?php });exit;
}

if($r==='posting_edit'){
 require_role(['admin','staff']);$projectId=(int)($_GET['project_id']??0);$id=(int)($_GET['id']??0);$item=[];$links=[];
 if($id){$q=db()->prepare('SELECT * FROM content_items WHERE id=?');$q->execute([$id]);$item=$q->fetch()?:[];$projectId=(int)($item['project_id']??0);$q=db()->prepare('SELECT platform_key,post_url FROM content_platform_links WHERE content_item_id=?');$q->execute([$id]);foreach($q->fetchAll() as $x)$links[$x['platform_key']]=$x['post_url'];}
 [$scope,$args]=scopeSql();$q=db()->prepare("SELECT p.* FROM projects p WHERE p.id=? $scope");$q->execute(array_merge([$projectId],$args));$project=$q->fetch();if(!$project){http_response_code(404);exit('Project not found');}
 if($_SERVER['REQUEST_METHOD']==='POST'){verify_csrf();$platforms=array_values(array_unique(array_filter($_POST['platforms']??[])));$a=[$projectId,trim($_POST['title']),$_POST['content_type_key']?:null,json_encode($platforms),$_POST['status_key']?:null,trim($_POST['scheduled_at'])?:null,trim($_POST['published_at'])?:null,trim($_POST['caption']),trim($_POST['notes'])];if($id){$a[]=$id;db()->prepare('UPDATE content_items SET project_id=?,title=?,content_type_key=?,platforms_json=?,status_key=?,scheduled_at=?,published_at=?,caption=?,notes=? WHERE id=?')->execute($a);}else{$a[]=user()['id'];db()->prepare('INSERT INTO content_items(project_id,title,content_type_key,platforms_json,status_key,scheduled_at,published_at,caption,notes,created_by) VALUES(?,?,?,?,?,?,?,?,?,?)')->execute($a);$id=(int)db()->lastInsertId();}$q=db()->prepare('INSERT INTO content_platform_links(content_item_id,platform_key,post_url,sync_status) VALUES(?,?,?,"manual") ON DUPLICATE KEY UPDATE post_url=VALUES(post_url),sync_status="manual"');foreach($platforms as $platform){$postUrl=trim($_POST['platform_urls'][$platform]??'');if($postUrl)$q->execute([$id,$platform,$postUrl]);}flash('Posting and platform links saved.');go('content',['id'=>$id]);}
 $selected=json_decode($item['platforms_json']??'[]',true)?:[];$options=settingOptions('platforms');render($id?'Edit posting':'Add posting',function()use($project,$item,$selected,$links,$options){?><h1><?=!empty($item)?'Edit':'Add'?> posting</h1><p class="muted"><?=h($project['title'])?></p><form method="post" class="card"><input type="hidden" name="_csrf" value="<?=csrf()?>"><div class="grid"><div class="field"><label>Posting title</label><input name="title" value="<?=h($item['title']??'')?>" required></div><div class="field"><label>Content type</label><select name="content_type_key"><?=selectOptions(settingOptions('content_types'),$item['content_type_key']??null)?></select></div><div class="field"><label>Published platforms (multi-select)</label><select name="platforms[]" multiple size="10"><?php foreach($options as $x):?><option value="<?=h($x['setting_key'])?>" <?=in_array($x['setting_key'],$selected,true)?'selected':''?>><?=h($x['label'])?></option><?php endforeach;?></select></div><div class="field"><label>Publishing status</label><select name="status_key"><?=selectOptions(settingOptions('statuses'),$item['status_key']??null)?></select></div></div><h2>Posting links by platform</h2><p class="muted">Select the platforms above, then paste the published link for each platform. Automatic private Insights require that platform's API connection.</p><div class="grid"><?php foreach($options as $x):?><div class="field"><label><?=h($x['label'])?> link</label><input type="url" name="platform_urls[<?=h($x['setting_key'])?>]" value="<?=h($links[$x['setting_key']]??'')?>" placeholder="https://..."></div><?php endforeach;?></div><div class="grid"><div class="field"><label>Scheduled time</label><input type="datetime-local" name="scheduled_at" value="<?=h(isset($item['scheduled_at'])?str_replace(' ','T',substr($item['scheduled_at'],0,16)):'')?>"></div><div class="field"><label>Published time</label><input type="datetime-local" name="published_at" value="<?=h(isset($item['published_at'])?str_replace(' ','T',substr($item['published_at'],0,16)):'')?>"></div></div><div class="field"><label>Caption</label><textarea name="caption"><?=h($item['caption']??'')?></textarea></div><div class="field"><label>Notes</label><textarea name="notes"><?=h($item['notes']??'')?></textarea></div><button class="btn">Save posting</button></form><?php });exit;
}

if($r==='content_edit_legacy'){
 require_role(['admin','staff']);$projectId=(int)($_GET['project_id']??0);$id=(int)($_GET['id']??0);$item=[];
 if($id){$q=db()->prepare('SELECT * FROM content_items WHERE id=?');$q->execute([$id]);$item=$q->fetch()?:[];$projectId=(int)($item['project_id']??0);}
 [$scope,$args]=scopeSql();$q=db()->prepare("SELECT p.* FROM projects p WHERE p.id=? $scope");$q->execute(array_merge([$projectId],$args));$project=$q->fetch();if(!$project){http_response_code(404);exit('Project not found');}
 if($_SERVER['REQUEST_METHOD']==='POST'){verify_csrf();$platforms=array_values(array_unique(array_filter($_POST['platforms']??[])));$a=[$projectId,trim($_POST['title']),$_POST['content_type_key']?:null,json_encode($platforms),$_POST['status_key']?:null,trim($_POST['original_url'])?:null,trim($_POST['published_url'])?:null,$_POST['scheduled_at']?:null,$_POST['published_at']?:null,trim($_POST['caption']),trim($_POST['notes'])];if($id){$a[]=$id;db()->prepare('UPDATE content_items SET project_id=?,title=?,content_type_key=?,platforms_json=?,status_key=?,original_url=?,published_url=?,scheduled_at=?,published_at=?,caption=?,notes=? WHERE id=?')->execute($a);}else{$a[]=user()['id'];db()->prepare('INSERT INTO content_items(project_id,title,content_type_key,platforms_json,status_key,original_url,published_url,scheduled_at,published_at,caption,notes,created_by) VALUES(?,?,?,?,?,?,?,?,?,?,?,?)')->execute($a);$id=(int)db()->lastInsertId();}flash('Content saved.');go('content',['id'=>$id]);}
 $selected=json_decode($item['platforms_json']??'[]',true)?:[];render($id?'Edit content':'Add content',function()use($project,$item,$selected){?><h1><?=!empty($item)?'Edit':'Add'?> content</h1><p class="muted"><?=h($project['title'])?></p><form method="post" class="card"><input type="hidden" name="_csrf" value="<?=csrf()?>"><div class="grid"><div class="field"><label>Content / posting title</label><input name="title" value="<?=h($item['title']??'')?>" required></div><div class="field"><label>Content type</label><select name="content_type_key"><?=selectOptions(settingOptions('content_types'),$item['content_type_key']??null)?></select></div><div class="field"><label>Platforms (multi-select)</label><select name="platforms[]" multiple size="8"><?php foreach(settingOptions('platforms') as $x):?><option value="<?=h($x['setting_key'])?>" <?=in_array($x['setting_key'],$selected,true)?'selected':''?>><?=h($x['label'])?></option><?php endforeach;?></select><small class="muted">Hold Ctrl (Windows) or Command (Mac) to select several.</small></div><div class="field"><label>Publishing status</label><select name="status_key"><?=selectOptions(settingOptions('statuses'),$item['status_key']??null)?></select></div><div class="field"><label>Original URL</label><input type="url" name="original_url" value="<?=h($item['original_url']??'')?>"></div><div class="field"><label>Published URL</label><input type="url" name="published_url" value="<?=h($item['published_url']??'')?>"></div><div class="field"><label>Scheduled time</label><input type="datetime-local" name="scheduled_at" value="<?=h(isset($item['scheduled_at'])?str_replace(' ','T',substr($item['scheduled_at'],0,16)):'')?>"></div><div class="field"><label>Published time</label><input type="datetime-local" name="published_at" value="<?=h(isset($item['published_at'])?str_replace(' ','T',substr($item['published_at'],0,16)):'')?>"></div></div><div class="field"><label>Caption</label><textarea name="caption"><?=h($item['caption']??'')?></textarea></div><div class="field"><label>Notes</label><textarea name="notes"><?=h($item['notes']??'')?></textarea></div><button class="btn">Save content</button></form><?php });exit;
}

if($r==='item_links'){
 require_login();$id=(int)($_GET['id']??0);$q=db()->prepare('SELECT ci.id,ci.title,ci.project_id,p.client_id FROM content_items ci JOIN projects p ON p.id=ci.project_id WHERE ci.id=?');$q->execute([$id]);$item=$q->fetch();if(!$item){http_response_code(404);exit('Content not found');}if(user()['role']==='client'&&(int)user()['client_id']!==(int)$item['client_id']){http_response_code(403);exit('Forbidden');}$q=db()->prepare('SELECT platform_key,post_url FROM content_platform_links WHERE content_item_id=?');$q->execute([$id]);$links=[];foreach($q as $x)$links[$x['platform_key']]=$x['post_url'];
 if($_SERVER['REQUEST_METHOD']==='POST'){require_role(['admin','staff']);if(!canDo('edit')){http_response_code(403);exit('No edit permission');}verify_csrf();$up=db()->prepare('INSERT INTO content_platform_links(content_item_id,platform_key,post_url,sync_status) VALUES(?,?,?,"manual") ON DUPLICATE KEY UPDATE post_url=VALUES(post_url),sync_status="manual"');$del=db()->prepare('DELETE FROM content_platform_links WHERE content_item_id=? AND platform_key=?');foreach(settingOptions('platforms') as $p){$key=$p['setting_key'];$url=trim($_POST['links'][$key]??'');if($url&&validUrl($url))$up->execute([$id,$key,$url]);elseif(!$url)$del->execute([$id,$key]);}flash('Published links updated.');go('content',['id'=>$id]);}
 render('Published links',function()use($item,$links){?><div class="toolbar"><div style="flex:1"><h1>Published links</h1><p class="muted"><?=h($item['title'])?> · Add the live link for every platform where this content was published.</p></div><a class="btn gray" href="<?=h(route('content',['id'=>$item['id']]))?>">Back</a></div><form method="post" class="card"><input type="hidden" name="_csrf" value="<?=csrf()?>"><div class="grid"><?php foreach(settingOptions('platforms') as $p):?><div class="field"><label><?=h($p['label'])?></label><input type="url" name="links[<?=h($p['setting_key'])?>]" value="<?=h($links[$p['setting_key']]??'')?>" placeholder="https://..."></div><?php endforeach;?></div><?php if(user()['role']!=='client'):?><button class="btn">Save published links</button><?php endif;?></form><?php });exit;
}

if($r==='content'){
 $id=(int)($_GET['id']??0);$q=db()->prepare('SELECT ci.*,p.client_id,p.title project_title,c.name client_name FROM content_items ci JOIN projects p ON p.id=ci.project_id JOIN clients c ON c.id=p.client_id WHERE ci.id=?');$q->execute([$id]);$item=$q->fetch();if(!$item){http_response_code(404);exit('Item not found');}if(user()['role']==='client'&&(int)user()['client_id']!==(int)$item['client_id']){http_response_code(403);exit('Forbidden');}
 if($_SERVER['REQUEST_METHOD']==='POST'){require_role(['admin','staff']);verify_csrf();$action=$_POST['action']??'';
  if($action==='performance'){$fields=['reach','impressions','views','likes','comments','shares','saves','clicks','leads','sales','ad_spend'];$vals=[$item['project_id'],$id,$_POST['metric_date']];foreach($fields as $f)$vals[]=(float)($_POST[$f]??0);$vals[]=trim($_POST['notes']??'');$vals[]=user()['id'];db()->prepare('INSERT INTO performance_entries(project_id,content_item_id,metric_date,reach,impressions,views,likes,comments,shares,saves,clicks,leads,sales,ad_spend,notes,created_by) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) ON DUPLICATE KEY UPDATE reach=VALUES(reach),impressions=VALUES(impressions),views=VALUES(views),likes=VALUES(likes),comments=VALUES(comments),shares=VALUES(shares),saves=VALUES(saves),clicks=VALUES(clicks),leads=VALUES(leads),sales=VALUES(sales),ad_spend=VALUES(ad_spend),notes=VALUES(notes)')->execute($vals);flash('Insights saved.');}
  if($action==='attachments'){$deleted=0;$uploaded=0;foreach(array_map('intval',$_POST['delete_files']??[]) as $fid){$q=db()->prepare('SELECT stored_name FROM attachments WHERE id=? AND content_item_id=?');$q->execute([$fid,$id]);if($name=$q->fetchColumn()){$path=__DIR__.'/uploads/'.basename($name);if(is_file($path))@unlink($path);db()->prepare('DELETE FROM attachments WHERE id=? AND content_item_id=?')->execute([$fid,$id]);$deleted++;}}$f=$_FILES['files']??null;if($f&&is_array($f['name'])){$map=['image/jpeg'=>'jpg','image/png'=>'png','image/webp'=>'webp','image/heic'=>'heic','image/heif'=>'heif','application/pdf'=>'pdf'];foreach($f['name'] as $i=>$original){if(($f['error'][$i]??UPLOAD_ERR_NO_FILE)!==UPLOAD_ERR_OK)continue;$tmp=$f['tmp_name'][$i];$mime=(new finfo(FILEINFO_MIME_TYPE))->file($tmp);$ext=$map[$mime]??null;if(!$ext||$f['size'][$i]>(int)cfg('uploads.max_bytes'))continue;$name=bin2hex(random_bytes(16)).'.'.$ext;if(move_uploaded_file($tmp,__DIR__.'/uploads/'.$name)){db()->prepare('INSERT INTO attachments(project_id,content_item_id,original_name,stored_name,mime_type,file_size,uploaded_by) VALUES(?,?,?,?,?,?,?)')->execute([$item['project_id'],$id,basename($original),$name,$mime,$f['size'][$i],user()['id']]);$uploaded++;}}}flash($uploaded.' file(s) uploaded, '.$deleted.' removed.');}go('content',['id'=>$id]);
 }
 $q=db()->prepare('SELECT * FROM performance_entries WHERE content_item_id=? ORDER BY metric_date DESC');$q->execute([$id]);$perf=$q->fetchAll();$q=db()->prepare('SELECT * FROM attachments WHERE content_item_id=? ORDER BY created_at DESC');$q->execute([$id]);$files=$q->fetchAll();$platforms=implode(', ',json_decode($item['platforms_json']??'[]',true)?:[]);
 render($item['title'],function()use($item,$perf,$files,$platforms,$id){?><div class="toolbar"><div style="flex:1"><h1><?=h($item['title'])?></h1><p class="muted"><?=h($item['client_name'])?> · <?=h(ucwords(str_replace('_',' ',$item['item_kind']??'posting')))?> · <?=h($platforms?:'No platform')?></p></div><span class="badge"><?=h($item['status_key']?:'No status')?></span><?php if(user()['role']!=='client'):?><a class="btn gray" href="<?=h(route('item_edit',['id'=>$id]))?>">Edit item</a><?php endif;?><a class="btn gray" href="<?=h(route('project',['id'=>$item['project_id']]))?>">Back</a></div><?php if($item['campaign_budget']!==null):?><div class="card metric"><span>Campaign budget</span><b>RM <?=number_format((float)$item['campaign_budget'],2)?></b></div><?php endif;?><div class="split"><section><div class="card"><h2>Performance insights</h2><table><tr><th>Date</th><th>Reach</th><th>Impressions</th><th>Views</th><th>Engagement</th><th>Clicks</th><th>Leads</th></tr><?php foreach($perf as $x):?><tr><td><?=h($x['metric_date'])?></td><td><?=number_format($x['reach'])?></td><td><?=number_format($x['impressions'])?></td><td><?=number_format($x['views'])?></td><td><?=number_format($x['likes']+$x['comments']+$x['shares']+$x['saves'])?></td><td><?=number_format($x['clicks'])?></td><td><?=number_format($x['leads'])?></td></tr><?php endforeach;?></table></div><div class="card" style="margin-top:16px"><h2>Campaign evidence / attachments</h2><?php if(!$files):?><p class="muted">No files uploaded yet.</p><?php endif;?><div class="grid"><?php foreach($files as $f):?><label class="card" style="display:block"><?php if(str_starts_with($f['mime_type'],'image/')):?><a target="_blank" href="<?=h(url('uploads/'.$f['stored_name']))?>"><img src="<?=h(url('uploads/'.$f['stored_name']))?>" alt="" style="width:100%;height:180px;object-fit:contain"></a><?php endif;?><a target="_blank" href="<?=h(url('uploads/'.$f['stored_name']))?>"><?=h($f['original_name'])?></a><?php if(user()['role']!=='client'):?><br><input form="attachmentsForm" type="checkbox" name="delete_files[]" value="<?=$f['id']?>"> Remove on update<?php endif;?></label><?php endforeach;?></div><?php if(user()['role']!=='client'):?><form id="attachmentsForm" method="post" enctype="multipart/form-data"><input type="hidden" name="_csrf" value="<?=csrf()?>"><input type="hidden" name="action" value="attachments"><div class="field"><label>Add one or more screenshots / files</label><input type="file" name="files[]" multiple accept="image/jpeg,image/png,image/webp,image/heic,image/heif,application/pdf"></div><button class="btn">Update attachments</button></form><?php endif;?></div></section><?php if(user()['role']!=='client'):?><aside class="card"><h2>Add / update insights</h2><form method="post"><input type="hidden" name="_csrf" value="<?=csrf()?>"><input type="hidden" name="action" value="performance"><div class="field"><label>Date</label><input type="date" name="metric_date" value="<?=date('Y-m-d')?>" required></div><div class="grid" style="grid-template-columns:1fr 1fr"><?php foreach(['reach','impressions','views','likes','comments','shares','saves','clicks','leads','sales','ad_spend'] as $f):?><div class="field"><label><?=h(ucwords(str_replace('_',' ',$f)))?></label><input type="number" step="0.01" min="0" name="<?=$f?>" value="0"></div><?php endforeach;?></div><div class="field"><label>Notes</label><textarea name="notes"></textarea></div><button class="btn">Save insights</button></form></aside><?php endif;?></div><?php });exit;
}

if($r==='content_legacy'){
 $id=(int)($_GET['id']??0);[$scope,$args]=scopeSql();$q=db()->prepare("SELECT ci.*,p.client_id,p.title project_title,c.name client_name FROM content_items ci JOIN projects p ON p.id=ci.project_id JOIN clients c ON c.id=p.client_id WHERE ci.id=? $scope");$q->execute(array_merge([$id],$args));$item=$q->fetch();if(!$item){http_response_code(404);exit('Content not found');}
 if($_SERVER['REQUEST_METHOD']==='POST'){require_role(['admin','staff']);verify_csrf();$action=$_POST['action']??'';
  if($action==='performance'){$fields=['reach','impressions','views','likes','comments','shares','saves','clicks','leads','sales','ad_spend'];$vals=[$item['project_id'],$id,$_POST['metric_date']];foreach($fields as $f)$vals[]=(float)($_POST[$f]??0);$vals[]=trim($_POST['notes']);$vals[]=user()['id'];db()->prepare('INSERT INTO performance_entries(project_id,content_item_id,metric_date,reach,impressions,views,likes,comments,shares,saves,clicks,leads,sales,ad_spend,notes,created_by) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) ON DUPLICATE KEY UPDATE reach=VALUES(reach),impressions=VALUES(impressions),views=VALUES(views),likes=VALUES(likes),comments=VALUES(comments),shares=VALUES(shares),saves=VALUES(saves),clicks=VALUES(clicks),leads=VALUES(leads),sales=VALUES(sales),ad_spend=VALUES(ad_spend),notes=VALUES(notes)')->execute($vals);flash('Insights saved.');}
  if($action==='upload'&&!empty($_FILES['file']['tmp_name'])){$f=$_FILES['file'];$mime=(new finfo(FILEINFO_MIME_TYPE))->file($f['tmp_name']);if($f['size']>(int)cfg('uploads.max_bytes')||!in_array($mime,cfg('uploads.allowed',[]),true))flash('File type or size is not allowed.','error');else{$ext=['image/jpeg'=>'jpg','image/png'=>'png','image/webp'=>'webp','application/pdf'=>'pdf'][$mime];$name=bin2hex(random_bytes(16)).'.'.$ext;move_uploaded_file($f['tmp_name'],__DIR__.'/uploads/'.$name);db()->prepare('INSERT INTO attachments(project_id,content_item_id,original_name,stored_name,mime_type,file_size,uploaded_by) VALUES(?,?,?,?,?,?,?)')->execute([$item['project_id'],$id,basename($f['name']),$name,$mime,$f['size'],user()['id']]);flash('Insight file uploaded.');}}go('content',['id'=>$id]);
 }
 $q=db()->prepare('SELECT * FROM performance_entries WHERE content_item_id=? ORDER BY metric_date DESC');$q->execute([$id]);$perf=$q->fetchAll();$q=db()->prepare('SELECT * FROM attachments WHERE content_item_id=? ORDER BY created_at DESC');$q->execute([$id]);$files=$q->fetchAll();$platforms=implode(', ',json_decode($item['platforms_json']??'[]',true)?:[]);
 render($item['title'],function()use($item,$perf,$files,$platforms,$id){?><div class="toolbar"><div style="flex:1"><h1><?=h($item['title'])?></h1><p class="muted"><?=h($item['client_name'])?> · <?=h($platforms)?></p></div><span class="badge"><?=h($item['status_key']?:'No status')?></span><?php if(user()['role']!=='client'):?><a class="btn gray" href="<?=h(route('content_edit',['id'=>$id]))?>">Edit</a><?php endif;?><a class="btn gray" href="<?=h(route('project',['id'=>$item['project_id']]))?>">Back</a></div><div class="split"><section><h2>Manual insights</h2><table><tr><th>Date</th><th>Reach</th><th>Impressions</th><th>Views</th><th>Engagement</th><th>Clicks</th><th>Leads</th></tr><?php foreach($perf as $x):?><tr><td><?=h($x['metric_date'])?></td><td><?=number_format($x['reach'])?></td><td><?=number_format($x['impressions'])?></td><td><?=number_format($x['views'])?></td><td><?=number_format($x['likes']+$x['comments']+$x['shares']+$x['saves'])?></td><td><?=number_format($x['clicks'])?></td><td><?=number_format($x['leads'])?></td></tr><?php endforeach;?></table><h2>Insight screenshots / files</h2><?php foreach($files as $f):?><div class="card"><a target="_blank" href="<?=h(url('uploads/'.$f['stored_name']))?>"><?=h($f['original_name'])?></a></div><?php endforeach;?></section><?php if(user()['role']!=='client'):?><aside><div class="card"><h2>Add manual insights</h2><form method="post"><input type="hidden" name="_csrf" value="<?=csrf()?>"><input type="hidden" name="action" value="performance"><div class="field"><label>Date</label><input type="date" name="metric_date" value="<?=date('Y-m-d')?>" required></div><div class="grid" style="grid-template-columns:1fr 1fr"><?php foreach(['reach','impressions','views','likes','comments','shares','saves','clicks','leads','sales','ad_spend'] as $f):?><div class="field"><label><?=h(ucwords(str_replace('_',' ',$f)))?></label><input type="number" step="0.01" min="0" name="<?=$f?>" value="0"></div><?php endforeach;?></div><div class="field"><label>Notes</label><textarea name="notes"></textarea></div><button class="btn">Save insights</button></form></div><div class="card" style="margin-top:16px"><h2>Upload insight</h2><form method="post" enctype="multipart/form-data"><input type="hidden" name="_csrf" value="<?=csrf()?>"><input type="hidden" name="action" value="upload"><input type="file" name="file" required><button class="btn" style="margin-top:10px">Upload</button></form></div></aside><?php endif;?></div><?php });exit;
}

if($r==='project'){
 $id=(int)($_GET['id']??0);[$scope,$args]=scopeSql();$q=db()->prepare("SELECT p.*,c.name client_name FROM projects p JOIN clients c ON c.id=p.client_id WHERE p.id=? $scope");$q->execute(array_merge([$id],$args));$p=$q->fetch();if(!$p){http_response_code(404);exit('Workspace not found');}$q=db()->prepare("SELECT ci.*,(SELECT COUNT(*) FROM performance_entries pe WHERE pe.content_item_id=ci.id) insight_entries,(SELECT COUNT(*) FROM attachments a WHERE a.content_item_id=ci.id) attachment_count FROM content_items ci WHERE ci.project_id=? AND ci.published_at IS NOT NULL ORDER BY ci.sort_order,ci.published_at DESC");$q->execute([$id]);$items=$q->fetchAll();
 render($p['title'],function()use($p,$items,$id){?><div class="toolbar"><div style="flex:1"><h1><?=h($p['client_name'])?></h1><p class="muted"><?=h($p['title'])?> · Workspace groups the client's records; data belongs to each item below.</p></div><a class="btn" href="<?=h(route('report',['client_id'=>$p['client_id']]))?>">Generate report</a><?php if(user()['role']!=='client'):?><a class="btn" href="<?=h(route('item_edit',['project_id'=>$id,'kind'=>'posting']))?>">+ Add posting</a><a class="btn" href="<?=h(route('item_edit',['project_id'=>$id,'kind'=>'ads_campaign']))?>">+ Add ads campaign</a><a class="btn gray" href="<?=h(route('project_edit',['id'=>$id]))?>">Edit workspace</a><?php endif;?></div><div class="card"><h2>Postings, ads campaigns and other items</h2><?php if(!$items):?><p class="muted">No items yet. Add a posting or ads campaign to enter data.</p><?php else:?><table><tr><th>Item</th><th>Type</th><th>Platforms</th><th>Status</th><th>Budget</th><th>Insights</th><th>Files</th><th></th></tr><?php foreach($items as $x):?><tr><td><b><?=h($x['title'])?></b></td><td><?=h(ucwords(str_replace('_',' ',$x['item_kind']??'posting')))?></td><td><?=h(implode(', ',json_decode($x['platforms_json']??'[]',true)?:[])?:'—')?></td><td><span class="badge"><?=h($x['status_key']?:'Not set')?></span></td><td><?=$x['campaign_budget']===null?'—':'RM '.number_format((float)$x['campaign_budget'],2)?></td><td><?=number_format($x['insight_entries'])?></td><td><?=number_format($x['attachment_count'])?></td><td><a class="btn small" href="<?=h(route('content',['id'=>$x['id']]))?>">Open</a><?php if(user()['role']!=='client'):?> <a class="btn small gray" href="<?=h(route('item_edit',['id'=>$x['id']]))?>">Edit</a><?php endif;?></td></tr><?php endforeach;?></table><?php endif;?></div><?php });exit;
}

if($r==='project_legacy'){
 $id=(int)($_GET['id']??0);[$scope,$args]=scopeSql();$q=db()->prepare("SELECT p.*,c.name client_name FROM projects p JOIN clients c ON c.id=p.client_id WHERE p.id=? $scope");$q->execute(array_merge([$id],$args));$p=$q->fetch();if(!$p){http_response_code(404);exit('Project not found');}
 if($_SERVER['REQUEST_METHOD']==='POST'){require_role(['admin','staff']);verify_csrf();$action=$_POST['action']??'';
  if($action==='link'){$dest=trim($_POST['destination_url']);$slug=strtolower(trim($_POST['slug']))?:randomSlug();if(!validUrl($dest)||!preg_match('/^[a-z0-9_-]{3,100}$/',$slug)){flash('Use a valid URL and a slug with 3–100 letters, numbers, hyphens or underscores.','error');go('project',['id'=>$id]);}try{db()->prepare('INSERT INTO tracking_links(project_id,slug,destination_url,source,medium,campaign,created_by) VALUES(?,?,?,?,?,?,?)')->execute([$id,$slug,$dest,trim($_POST['source']),trim($_POST['medium']),trim($_POST['campaign']),user()['id']]);flash('Tracking link generated.');}catch(PDOException $e){flash('That custom slug is already in use.','error');}}
  if($action==='performance'){$fields=['reach','impressions','views','likes','comments','shares','saves','clicks','leads','sales','ad_spend'];$vals=[$id,$_POST['metric_date']];foreach($fields as $f)$vals[]=(float)($_POST[$f]??0);$vals[]=trim($_POST['notes']);$vals[]=user()['id'];db()->prepare('INSERT INTO performance_entries(project_id,metric_date,reach,impressions,views,likes,comments,shares,saves,clicks,leads,sales,ad_spend,notes,created_by) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) ON DUPLICATE KEY UPDATE reach=VALUES(reach),impressions=VALUES(impressions),views=VALUES(views),likes=VALUES(likes),comments=VALUES(comments),shares=VALUES(shares),saves=VALUES(saves),clicks=VALUES(clicks),leads=VALUES(leads),sales=VALUES(sales),ad_spend=VALUES(ad_spend),notes=VALUES(notes)')->execute($vals);flash('Performance saved.');}
  if($action==='upload'&&!empty($_FILES['file']['tmp_name'])){$f=$_FILES['file'];$mime=(new finfo(FILEINFO_MIME_TYPE))->file($f['tmp_name']);if($f['size']>(int)cfg('uploads.max_bytes')||!in_array($mime,cfg('uploads.allowed',[]),true)){flash('File type or size is not allowed.','error');}else{$ext=['image/jpeg'=>'jpg','image/png'=>'png','image/webp'=>'webp','application/pdf'=>'pdf'][$mime];$name=bin2hex(random_bytes(16)).'.'.$ext;move_uploaded_file($f['tmp_name'],__DIR__.'/uploads/'.$name);db()->prepare('INSERT INTO attachments(project_id,original_name,stored_name,mime_type,file_size,uploaded_by) VALUES(?,?,?,?,?,?)')->execute([$id,basename($f['name']),$name,$mime,$f['size'],user()['id']]);flash('File uploaded.');}}
  go('project',['id'=>$id]);
 }
 $q=db()->prepare('SELECT tl.*,COUNT(c.id) clicks,COALESCE(SUM(c.is_unique),0) unique_clicks FROM tracking_links tl LEFT JOIN clicks c ON c.tracking_link_id=tl.id WHERE tl.project_id=? GROUP BY tl.id ORDER BY tl.created_at DESC');$q->execute([$id]);$links=$q->fetchAll();$q=db()->prepare('SELECT ci.*,(SELECT COUNT(*) FROM performance_entries pe WHERE pe.content_item_id=ci.id) insight_entries FROM content_items ci WHERE ci.project_id=? ORDER BY COALESCE(ci.published_at,ci.scheduled_at,ci.created_at) DESC');$q->execute([$id]);$contents=$q->fetchAll();$q=db()->prepare('SELECT * FROM performance_entries WHERE project_id=? AND content_item_id IS NULL ORDER BY metric_date DESC');$q->execute([$id]);$perf=$q->fetchAll();$q=db()->prepare('SELECT * FROM attachments WHERE project_id=? AND content_item_id IS NULL ORDER BY created_at DESC');$q->execute([$id]);$files=$q->fetchAll();
 render($p['title'],function()use($p,$links,$contents,$perf,$files,$id){?><div class="toolbar"><div style="flex:1"><h1><?=h($p['title'])?></h1><span class="muted"><?=h($p['client_name'])?> · <?=h($p['campaign'])?></span></div><a class="btn" href="<?=h(route('report',['client_id'=>$p['client_id']]))?>">Generate report</a><?php if(user()['role']!=='client'):?><a class="btn" href="<?=h(route('content_edit',['project_id'=>$id]))?>">+ Add content</a><a class="btn gray" href="<?=h(route('project_edit',['id'=>$id]))?>">Edit</a><?php endif;?></div><div class="card"><h2>All content / postings</h2><?php if(!$contents):?><p class="muted">No content yet. Add the first posting, video, website or ad.</p><?php else:?><table><tr><th>Content</th><th>Platforms</th><th>Publishing status</th><th>Published</th><th>Insights</th><th></th></tr><?php foreach($contents as $ci):$platforms=implode(', ',json_decode($ci['platforms_json']??'[]',true)?:[]);?><tr><td><b><?=h($ci['title'])?></b><br><span class="muted"><?=h($ci['content_type_key'])?></span></td><td><?=h($platforms?:'—')?></td><td><span class="badge"><?=h($ci['status_key']?:'Not set')?></span></td><td><?=h($ci['published_at']?:'Not published')?></td><td><?=$ci['insight_entries']?> entr<?=((int)$ci['insight_entries']===1?'y':'ies')?></td><td><a class="btn small" href="<?=h(route('content',['id'=>$ci['id']]))?>">Open</a></td></tr><?php endforeach;?></table><?php endif;?></div><div class="split"><section><div class="card"><h2>Tracking links</h2><?php if(!$links):?><p class="muted">No tracking links yet.</p><?php endif;?><?php foreach($links as $l):?><div style="border-top:1px solid var(--line);padding:13px 0"><b><?=h($l['source']?:'Direct')?> / <?=h($l['campaign'])?></b><div class="code"><a href="<?=h(url('t/'.$l['slug']))?>" target="_blank"><?=h(url('t/'.$l['slug']))?></a></div><span class="muted"><?=$l['clicks']?> clicks · <?=$l['unique_clicks']?> unique</span> <a class="btn small gray" target="_blank" href="<?=h(url('qr/'.$l['slug'].'.svg'))?>">QR Code</a></div><?php endforeach;?></div><h2>Legacy project-level performance</h2><table><tr><th>Date</th><th>Reach</th><th>Views</th><th>Engagement</th><th>Clicks</th><th>Leads</th></tr><?php foreach($perf as $x):?><tr><td><?=h($x['metric_date'])?></td><td><?=number_format($x['reach'])?></td><td><?=number_format($x['views'])?></td><td><?=number_format($x['likes']+$x['comments']+$x['shares']+$x['saves'])?></td><td><?=number_format($x['clicks'])?></td><td><?=number_format($x['leads'])?></td></tr><?php endforeach;?></table><h2>Project files</h2><?php foreach($files as $f):?><div class="card"><a href="<?=h(url('uploads/'.$f['stored_name']))?>" target="_blank"><?=h($f['original_name'])?></a></div><?php endforeach;?></section>
 <?php if(user()['role']!=='client'):?><aside><div class="card"><h2>Generate tracking link</h2><form method="post"><input type="hidden" name="_csrf" value="<?=csrf()?>"><input type="hidden" name="action" value="link"><div class="field"><label>Destination URL</label><input type="url" name="destination_url" value="<?=h($p['original_url'])?>" required></div><div class="field"><label>Custom slug (optional)</label><input name="slug" placeholder="hongyi-merdeka"></div><div class="field"><label>Source</label><input name="source" placeholder="facebook"></div><div class="field"><label>Medium</label><input name="medium" placeholder="social / cpc / qr"></div><div class="field"><label>Campaign</label><input name="campaign" value="<?=h($p['campaign'])?>"></div><button class="btn">Generate</button></form></div><div class="card" style="margin-top:16px"><h2>Add performance</h2><form method="post"><input type="hidden" name="_csrf" value="<?=csrf()?>"><input type="hidden" name="action" value="performance"><div class="field"><label>Date</label><input type="date" name="metric_date" value="<?=date('Y-m-d')?>" required></div><div class="grid" style="grid-template-columns:1fr 1fr"><?php foreach(['reach','impressions','views','likes','comments','shares','saves','clicks','leads','sales','ad_spend'] as $f):?><div class="field"><label><?=h(ucwords(str_replace('_',' ',$f)))?></label><input type="number" step="0.01" min="0" name="<?=$f?>" value="0"></div><?php endforeach;?></div><div class="field"><label>Notes</label><textarea name="notes"></textarea></div><button class="btn">Save metrics</button></form></div><div class="card" style="margin-top:16px"><h2>Upload insight</h2><form method="post" enctype="multipart/form-data"><input type="hidden" name="_csrf" value="<?=csrf()?>"><input type="hidden" name="action" value="upload"><input type="file" name="file" accept="image/jpeg,image/png,image/webp,application/pdf" required><button class="btn" style="margin-top:10px">Upload</button></form></div></aside><?php endif;?></div><?php });exit;
}

if($r==='settings')go('settings_v2',array_filter(['edit'=>(int)($_GET['edit']??0)]));
if($r==='settings_v2'){
 require_role(['admin']);$editId=(int)($_GET['edit']??0);$edit=null;if($editId){$q=db()->prepare('SELECT * FROM settings WHERE id=?');$q->execute([$editId]);$edit=$q->fetch();}if($_SERVER['REQUEST_METHOD']==='POST'){verify_csrf();$action=$_POST['action']??'save';$id=(int)($_POST['id']??0);if($action==='toggle'&&$id){db()->prepare('UPDATE settings SET active=1-active WHERE id=?')->execute([$id]);flash('Setting status updated.');go('settings_v2');}$group=$_POST['setting_group'];$label=trim($_POST['label']);$key=strtolower(trim($_POST['setting_key']))?:strtolower(preg_replace('/[^a-z0-9]+/i','-',$label));$active=!empty($_POST['active'])?1:0;$meta=null;if($group==='custom_fields')$meta=json_encode(['module'=>$_POST['module']??'projects','type'=>$_POST['field_type']??'text','required'=>!empty($_POST['required'])]);if($id)db()->prepare('UPDATE settings SET setting_group=?,setting_key=?,label=?,value=?,sort_order=?,meta_json=?,active=? WHERE id=?')->execute([$group,$key,$label,trim($_POST['value']??''),(int)($_POST['sort_order']??0),$meta,$active,$id]);else db()->prepare('INSERT INTO settings(setting_group,setting_key,label,value,sort_order,meta_json,active) VALUES(?,?,?,?,?,?,?)')->execute([$group,$key,$label,trim($_POST['value']??''),(int)($_POST['sort_order']??0),$meta,$active]);flash('Setting saved.');go('settings_v2');}$rows=db()->query('SELECT * FROM settings ORDER BY setting_group,sort_order,label')->fetchAll();
 render('Dynamic Settings',function()use($rows,$edit){?><div class="split"><section><h1>Dynamic Settings</h1><table><tr><th>Group</th><th>Key</th><th>Label</th><th>Status</th><th></th></tr><?php foreach($rows as $x):?><tr><td><?=h($x['setting_group'])?></td><td><?=h($x['setting_key'])?></td><td><?=h($x['label'])?></td><td><span class="badge"><?=$x['active']?'Active':'Disabled'?></span></td><td><a class="btn small gray" href="<?=h(route('settings_v2',['edit'=>$x['id']]))?>">Edit</a> <form method="post" style="display:inline" onsubmit="return confirm('Change this setting status?')"><input type="hidden" name="_csrf" value="<?=csrf()?>"><input type="hidden" name="action" value="toggle"><input type="hidden" name="id" value="<?=$x['id']?>"><button class="btn small gray"><?=$x['active']?'Disable':'Enable'?></button></form></td></tr><?php endforeach;?></table></section><aside class="card"><h2><?=$edit?'Edit setting':'Add setting'?></h2><form method="post"><input type="hidden" name="_csrf" value="<?=csrf()?>"><input type="hidden" name="id" value="<?=h($edit['id']??'')?>"><div class="field"><label>Group</label><select name="setting_group"><?php foreach(['platforms','content_update_platforms','content_types','service_types','statuses','custom_fields','report_settings','system_settings'] as $g):?><option value="<?=$g?>" <?=($edit['setting_group']??'')===$g?'selected':''?>><?=h(ucwords(str_replace('_',' ',$g)))?></option><?php endforeach;?></select></div><div class="field"><label>Key</label><input name="setting_key" value="<?=h($edit['setting_key']??'')?>"></div><div class="field"><label>Label</label><input name="label" value="<?=h($edit['label']??'')?>" required></div><div class="field"><label>Value</label><textarea name="value"><?=h($edit['value']??'')?></textarea></div><div class="field"><label>Sort order</label><input type="number" name="sort_order" value="<?=h($edit['sort_order']??0)?>"></div><label><input type="checkbox" name="active" value="1" <?=!isset($edit['active'])||$edit['active']?'checked':''?>> Active</label><br><br><button class="btn">Save setting</button></form></aside></div><?php });exit;
}

if($r==='settings_legacy'){
 require_role(['admin']);if($_SERVER['REQUEST_METHOD']==='POST'){verify_csrf();$group=$_POST['setting_group'];$label=trim($_POST['label']);$key=strtolower(trim($_POST['setting_key']))?:strtolower(preg_replace('/[^a-z0-9]+/i','-',$label));$meta=null;if($group==='custom_fields')$meta=json_encode(['module'=>$_POST['module']??'projects','type'=>$_POST['field_type']??'text','required'=>!empty($_POST['required'])]);db()->prepare('INSERT INTO settings(setting_group,setting_key,label,value,sort_order,meta_json) VALUES(?,?,?,?,?,?) ON DUPLICATE KEY UPDATE label=VALUES(label),value=VALUES(value),sort_order=VALUES(sort_order),meta_json=VALUES(meta_json),active=1')->execute([$group,$key,$label,trim($_POST['value']??''),(int)($_POST['sort_order']??0),$meta]);flash('Setting saved.');go('settings');}$rows=db()->query('SELECT * FROM settings ORDER BY setting_group,sort_order,label')->fetchAll();render('Settings',function()use($rows){?><div class="split"><section><h1>Dynamic Settings</h1><table><tr><th>Group</th><th>Key</th><th>Label / value</th><th>Active</th></tr><?php foreach($rows as $x):?><tr><td><?=h($x['setting_group'])?></td><td class="code"><?=h($x['setting_key'])?></td><td><?=h($x['label'])?><br><span class="muted"><?=h($x['value'])?></span></td><td><?=$x['active']?'Yes':'No'?></td></tr><?php endforeach;?></table></section><aside class="card"><h2>Add or update</h2><form method="post"><input type="hidden" name="_csrf" value="<?=csrf()?>"><div class="field"><label>Group</label><select name="setting_group"><option value="platforms">Platforms</option><option value="content_types">Content Types</option><option value="service_types">Service Types</option><option value="statuses">Statuses</option><option value="custom_fields">Custom Fields</option><option value="report_settings">Report Settings</option><option value="system_settings">System Settings</option></select></div><div class="field"><label>Label</label><input name="label" required></div><div class="field"><label>Key (optional)</label><input name="setting_key"></div><div class="field"><label>Value (report/system)</label><textarea name="value"></textarea></div><div class="field"><label>Custom field module</label><select name="module"><option>projects</option><option>clients</option><option>performance</option></select></div><div class="field"><label>Custom field type</label><select name="field_type"><option>text</option><option>textarea</option><option>number</option><option>date</option><option>select</option><option>checkbox</option></select></div><div class="field"><label>Sort order</label><input type="number" name="sort_order" value="0"></div><button class="btn">Save setting</button></form></aside></div><?php });exit;
}

if($r==='report'){$reportQuery=$_GET;unset($reportQuery['r']);go('report_v2',$reportQuery);}

if($r==='report_v2'){
 [$scope,$scopeArgs]=scopeSql();$from=$_GET['from']??date('Y-m-01');$to=$_GET['to']??date('Y-m-t');$clientId=user()['role']==='client'?(int)user()['client_id']:(int)($_GET['client_id']??0);$include=!empty($_GET['include_financial']);$comment=trim($_GET['comment']??'');$shareLink='';
 if($_SERVER['REQUEST_METHOD']==='POST'){require_role(['admin','staff']);verify_csrf();$clientId=(int)$_POST['client_id'];$from=$_POST['from'];$to=$_POST['to'];$include=!empty($_POST['include_financial']);$includeValue=$include?1:0;$comment=trim($_POST['comment']??'');db()->prepare('INSERT INTO report_history(client_id,date_from,date_to,created_by) VALUES(?,?,?,?) ON DUPLICATE KEY UPDATE created_by=VALUES(created_by),created_at=CURRENT_TIMESTAMP')->execute([$clientId,$from,$to,user()['id']]);$q=db()->prepare('SELECT token FROM report_shares WHERE client_id=? ORDER BY id LIMIT 1');$q->execute([$clientId]);$token=$q->fetchColumn();if($token)db()->prepare('UPDATE report_shares SET date_from=?,date_to=?,include_financial=?,consultant_comment=?,created_by=?,expires_at=NULL WHERE client_id=?')->execute([$from,$to,$includeValue,$comment,user()['id'],$clientId]);else{$token=randomSlug().randomSlug().randomSlug();db()->prepare('INSERT INTO report_shares(token,client_id,date_from,date_to,include_financial,consultant_comment,created_by,expires_at) VALUES(?,?,?,?,?,?,?,NULL)')->execute([$token,$clientId,$from,$to,$includeValue,$comment,user()['id']]);}$shareLink=route('shared_report',['token'=>$token]);}
 $clients=db()->query('SELECT id,name FROM clients WHERE status="active" ORDER BY name')->fetchAll();$clientName='All Clients';foreach($clients as $c)if((int)$c['id']===$clientId)$clientName=$c['name'];if($clientId&&!$shareLink){$q=db()->prepare('SELECT token FROM report_shares WHERE client_id=? ORDER BY id LIMIT 1');$q->execute([$clientId]);if($existingToken=$q->fetchColumn())$shareLink=route('shared_report',['token'=>$existingToken]);}$where=$scope.' AND COALESCE(DATE(ci.published_at),pe.metric_date) BETWEEN ? AND ?';$args=array_merge($scopeArgs,[$from,$to]);if($clientId){$where.=' AND p.client_id=?';$args[]=$clientId;}$m=metricTotals($where,$args);$q=db()->prepare("SELECT ci.title,ci.platforms_json,c.name client_name,SUM(pe.reach) reach,SUM(pe.impressions) impressions,SUM(pe.views) views,SUM(pe.likes+pe.comments+pe.shares+pe.saves) engagement,SUM(pe.clicks) clicks,SUM(pe.leads) leads FROM performance_entries pe JOIN projects p ON p.id=pe.project_id JOIN clients c ON c.id=p.client_id LEFT JOIN content_items ci ON ci.id=pe.content_item_id WHERE 1=1 $where GROUP BY c.id,pe.content_item_id,ci.title,ci.platforms_json ORDER BY c.name,ci.title");$q->execute($args);$rows=$q->fetchAll();
 $rows=reportContentRows($clientId,$from,$to);render('Marketing Report',function()use($from,$to,$clientId,$clientName,$include,$comment,$shareLink,$clients,$m,$rows){?><div class="toolbar no-print"><h1 style="flex:1">Generate Marketing Report</h1><form class="toolbar" method="get"><input type="hidden" name="r" value="report_v2"><div class="field"><label>Client</label><select name="client_id" required><?php foreach($clients as $c):?><option value="<?=$c['id']?>" <?=$clientId==(int)$c['id']?'selected':''?>><?=h($c['name'])?></option><?php endforeach;?></select></div><div class="field"><label>From</label><input type="date" name="from" value="<?=h($from)?>"></div><div class="field"><label>To</label><input type="date" name="to" value="<?=h($to)?>"></div><label><input type="checkbox" name="include_financial" value="1" <?=$include?'checked':''?>> Include Sales & Ads Expenses</label><div class="field"><label>Comment from Bright Marketing Solutions</label><textarea name="comment"><?=h($comment)?></textarea></div><button class="btn">Generate</button></form></div><?php if($shareLink):?><div class="flash ok"><b>Client report link:</b> <a href="<?=h($shareLink)?>" target="_blank"><?=h($shareLink)?></a></div><?php endif;?><div class="toolbar no-print"><button class="btn gray" onclick="window.print()">Export PDF</button><?php if($clientId&&user()['role']!=='client'&&!$shareLink):?><form method="post"><input type="hidden" name="_csrf" value="<?=csrf()?>"><input type="hidden" name="client_id" value="<?=$clientId?>"><input type="hidden" name="from" value="<?=h($from)?>"><input type="hidden" name="to" value="<?=h($to)?>"><input type="hidden" name="comment" value="<?=h($comment)?>"><?php if($include):?><input type="hidden" name="include_financial" value="1"><?php endif;?><button class="btn">Generate client link</button></form><?php endif;?></div><div class="card"><h1><?=h($clientName)?> | Marketing Report</h1><p><b>Report Range:</b> <?=h($from)?> to <?=h($to)?><br><b>Report Generated By:</b> <?=h(user()['name'])?>, Bright Marketing Solutions</p><div class="grid"><?php foreach(['Reach'=>$m['reach'],'Impressions'=>$m['impressions'],'Views'=>$m['views'],'Engagement'=>$m['engagement'],'Leads'=>$m['leads']] as $k=>$v):?><div class="metric"><span><?=h($k)?></span><b><?=h($v)?></b></div><?php endforeach;?><?php if($include):?><div class="metric"><span>Sales</span><b>RM <?=number_format((float)$m['sales'],2)?></b></div><div class="metric"><span>Ads Expenses</span><b>RM <?=number_format((float)$m['spend'],2)?></b></div><?php endif;?></div><h2>Content Insights</h2><table><tr><th>Posting</th><th>Published date</th><th>Platforms</th><th>Reach</th><th>Views</th><th>Engagement</th><th>Clicks</th><th>Leads</th></tr><?php foreach($rows as $x):?><tr><td><?=h($x['title']?:'Project total')?></td><td><?=h($x['published_at']?:'—')?></td><td><?=platformLinksHtml($x)?></td><td><?=number_format($x['reach'])?></td><td><?=number_format($x['views'])?></td><td><?=number_format($x['engagement'])?></td><td><?=number_format($x['clicks'])?></td><td><?=number_format($x['leads'])?></td></tr><?php endforeach;?></table><?php if($comment):?><h2>Comment from Bright Marketing Solutions</h2><div class="card"><?=nl2br(h($comment))?></div><?php endif;?></div><?php });exit;
}

if($r==='report_legacy'){
 [$scope,$args]=scopeSql();$from=$_GET['from']??date('Y-m-01');$to=$_GET['to']??date('Y-m-t');$where=$scope.' AND pe.metric_date BETWEEN ? AND ?';$args=array_merge($args,[$from,$to]);if(!empty($_GET['client_id'])&&user()['role']!=='client'){$where.=' AND p.client_id=?';$args[]=(int)$_GET['client_id'];}$m=metricTotals($where,$args);$q=db()->prepare("SELECT p.title,p.campaign,c.name client_name,SUM(pe.reach) reach,SUM(pe.views) views,SUM(pe.clicks) clicks,SUM(pe.leads) leads,SUM(pe.sales) sales,SUM(pe.ad_spend) spend FROM performance_entries pe JOIN projects p ON p.id=pe.project_id JOIN clients c ON c.id=p.client_id WHERE 1=1 $where GROUP BY p.id ORDER BY c.name,p.title");$q->execute($args);$rows=$q->fetchAll();$clients=db()->query('SELECT id,name FROM clients ORDER BY name')->fetchAll();render('Report',function()use($from,$to,$m,$rows,$clients){?><div class="toolbar no-print"><h1 style="flex:1">Client / Monthly Report</h1><form class="toolbar"><input type="hidden" name="r" value="report"><div class="field"><label>From</label><input type="date" name="from" value="<?=h($from)?>"></div><div class="field"><label>To</label><input type="date" name="to" value="<?=h($to)?>"></div><?php if(user()['role']!=='client'):?><div class="field"><label>Client</label><select name="client_id"><option value="">All</option><?php foreach($clients as $c):?><option value="<?=$c['id']?>" <?=($_GET['client_id']??'')==$c['id']?'selected':''?>><?=h($c['name'])?></option><?php endforeach;?></select></div><?php endif;?><button class="btn">Generate</button><button type="button" onclick="window.print()" class="btn gray">Export / Save PDF</button></form></div><div class="card"><h1><?=h(cfg('app_name'))?></h1><p>Performance report · <?=h($from)?> to <?=h($to)?></p><div class="grid"><?php foreach(['Reach'=>$m['reach'],'Impressions'=>$m['impressions'],'Views'=>$m['views'],'Engagement'=>$m['engagement'],'Leads'=>$m['leads'],'Sales'=>'RM '.number_format($m['sales'],2),'Ad spend'=>'RM '.number_format($m['spend'],2)] as $k=>$v):?><div class="metric"><span><?=h($k)?></span><b><?=h($v)?></b></div><?php endforeach;?></div><h2>Project breakdown</h2><table><tr><th>Client / Project</th><th>Reach</th><th>Views</th><th>Clicks</th><th>Leads</th><th>Sales</th><th>Spend</th></tr><?php foreach($rows as $x):?><tr><td><b><?=h($x['client_name'])?></b><br><?=h($x['title'])?></td><td><?=number_format($x['reach'])?></td><td><?=number_format($x['views'])?></td><td><?=number_format($x['clicks'])?></td><td><?=number_format($x['leads'])?></td><td>RM <?=number_format($x['sales'],2)?></td><td>RM <?=number_format($x['spend'],2)?></td></tr><?php endforeach;?></table></div><?php });exit;
}
http_response_code(404);echo 'Page not found';
