<?php
require_once __DIR__.'/_bootstrap.php';
use App\Core\Database;
$job='monthly_statement';$started=date('Y-m-d H:i:s');try{$start=date('Y-m-01',strtotime('first day of last month'));$end=date('Y-m-t',strtotime('last day of last month'));$members=Database::fetchAll('SELECT id,user_id FROM affiliate_members');foreach($members as $m){$sales=(float)(Database::safeFetchOne('SELECT COALESCE(SUM(amount),0) v FROM affiliate_conversions WHERE affiliate_member_id=? AND status="completed" AND DATE(created_at) BETWEEN ? AND ?',[$m['id'],$start,$end])['v']??0);$psa=(float)(Database::safeFetchOne('SELECT COALESCE(SUM(marketing_psa),0) v FROM psa_transactions WHERE affiliate_member_id=? AND DATE(created_at) BETWEEN ? AND ?',[$m['id'],$start,$end])['v']??0);$royalty=(float)(Database::safeFetchOne('SELECT COALESCE(SUM(amount),0) v FROM royalty_distributions WHERE affiliate_member_id=? AND DATE(created_at) BETWEEN ? AND ?',[$m['id'],$start,$end])['v']??0);$lead=(float)(Database::safeFetchOne('SELECT COALESCE(SUM(amount),0) v FROM leadership_royalties WHERE affiliate_member_id=? AND DATE(created_at) BETWEEN ? AND ?',[$m['id'],$start,$end])['v']??0);$withdraw=(float)(Database::safeFetchOne('SELECT COALESCE(SUM(amount),0) v FROM withdraw_requests WHERE user_id=? AND status IN ("approved","paid") AND DATE(created_at) BETWEEN ? AND ?',[$m['user_id'],$start,$end])['v']??0);Database::execute('INSERT INTO affiliate_statements (affiliate_member_id,statement_type,period_start,period_end,total_sales,total_psa,total_royalty,total_withdraw,team_income,leadership_bonus,incentive_earnings,status,created_at) VALUES (?,"monthly",?,?,?,?,?,?,0,?,0,"generated",NOW()) ON DUPLICATE KEY UPDATE total_sales=VALUES(total_sales),total_psa=VALUES(total_psa),total_royalty=VALUES(total_royalty),total_withdraw=VALUES(total_withdraw),leadership_bonus=VALUES(leadership_bonus),status="generated"',[$m['id'],$start,$end,$sales,$psa,$royalty,$withdraw,$lead]);}cron_log($job,'success','Generated monthly statements for '.count($members).' member(s).',$started);}catch(Throwable $e){cron_log($job,'failed',$e->getMessage(),$started);exit(1);}
