<?php
require_once __DIR__ . '/finance_bootstrap.php';
require_once __DIR__ . '/billing_account_lib.php';

if (!function_exists('finance_expense_migrate')) {
  function finance_expense_migrate(mysqli $conn): void {
    static $done=[];$key=spl_object_id($conn);if(!empty($done[$key]))return;
    $conn->query("CREATE TABLE IF NOT EXISTS expense_types (
      id INT AUTO_INCREMENT PRIMARY KEY,name VARCHAR(120) NOT NULL,normalized_name VARCHAR(120) NOT NULL,
      is_active TINYINT(1) NOT NULL DEFAULT 1,created_by INT NULL,created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
      updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      UNIQUE KEY uq_expense_type_normalized(normalized_name),KEY idx_expense_type_active(is_active,name)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
    $columns=[
      'expense_type_id'=>'INT NULL','collection_account_key'=>'VARCHAR(140) NULL','reference'=>'VARCHAR(120) NULL',
      'expense_status'=>"VARCHAR(20) NOT NULL DEFAULT 'POSTED'",'reversed_at'=>'DATETIME NULL','reversed_by'=>'INT NULL','updated_at'=>'DATETIME NULL',
    ];
    foreach($columns as $column=>$definition)if(!finance_column_exists($conn,'expenses',$column))@$conn->query("ALTER TABLE expenses ADD COLUMN `{$column}` {$definition}");
    try { $conn->query("CREATE INDEX idx_expenses_type_date ON expenses(expense_type_id,spent_at)"); } catch (Throwable $e) {}
    try { $conn->query("CREATE INDEX idx_expenses_account_date ON expenses(collection_account_key,spent_at)"); } catch (Throwable $e) {}
    $rs=@$conn->query("SELECT DISTINCT TRIM(category) name FROM expenses WHERE TRIM(COALESCE(category,''))<>''");
    if($rs)while($row=$rs->fetch_assoc()){
      $name=trim((string)$row['name']);$normalized=finance_expense_normalize_type($name);
      $stmt=$conn->prepare("INSERT IGNORE INTO expense_types(name,normalized_name) VALUES(?,?)");$stmt->bind_param('ss',$name,$normalized);$stmt->execute();$stmt->close();
    }
    try {
      $conn->query("UPDATE expenses e JOIN expense_types t ON BINARY t.normalized_name=BINARY LOWER(TRIM(e.category)) SET e.expense_type_id=t.id WHERE e.expense_type_id IS NULL");
    } catch (Throwable $e) {
      // Existing installations can have mixed legacy collations; binary UTF-8 matching avoids changing user tables.
    }
    $done[$key]=true;
  }
}

if (!function_exists('finance_expense_normalize_type')) {
  function finance_expense_normalize_type(string $name): string {
    $name=preg_replace('/\s+/u',' ',trim($name))??trim($name);
    return function_exists('mb_strtolower')?mb_strtolower($name,'UTF-8'):strtolower($name);
  }
}

if (!function_exists('finance_expense_resolve_type')) {
  function finance_expense_resolve_type(mysqli $conn,string $name,int $actorId=0): array {
    finance_expense_migrate($conn);$name=preg_replace('/\s+/u',' ',trim($name))??trim($name);
    if($name==='')return ['ok'=>false,'error'=>'نوع المصروف مطلوب.'];
    $length=function_exists('mb_strlen')?mb_strlen($name,'UTF-8'):strlen($name);
    if($length>120)return ['ok'=>false,'error'=>'اسم نوع المصروف طويل جداً.'];
    $normalized=finance_expense_normalize_type($name);
    $stmt=$conn->prepare("INSERT INTO expense_types(name,normalized_name,created_by) VALUES(?,?,?) ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id),name=VALUES(name),is_active=1");
    $actorDb=$actorId>0?$actorId:null;$stmt->bind_param('ssi',$name,$normalized,$actorDb);
    if(!$stmt->execute()){ $error=$stmt->error;$stmt->close();return ['ok'=>false,'error'=>$error]; }
    $id=(int)$stmt->insert_id;$stmt->close();return ['ok'=>true,'id'=>$id,'name'=>$name,'normalized'=>$normalized];
  }
}

if (!function_exists('finance_expense_types')) {
  function finance_expense_types(mysqli $conn): array {
    finance_expense_migrate($conn);$rows=[];$rs=$conn->query("SELECT id,name FROM expense_types WHERE is_active=1 ORDER BY name");
    while($rs&&($row=$rs->fetch_assoc()))$rows[]=$row;return $rows;
  }
}

if (!function_exists('finance_expense_accounts')) {
  function finance_expense_accounts(mysqli $conn): array {
    billing_accounts_migrate($conn);$accounts=billing_collection_balances($conn);
    if(!$accounts)$accounts[]=['account_key'=>'MAIN','account_type'=>'main','account_ref'=>'main','account_name'=>'النسخة الرئيسية','balance'=>0];
    return $accounts;
  }
}

if (!function_exists('finance_expense_source_from_account')) {
  function finance_expense_source_from_account(mysqli $conn,string $accountKey,string $channel='desktop'): ?array {
    $stmt=$conn->prepare("SELECT account_key,account_type,account_ref,account_name,balance FROM collection_accounts WHERE account_key=? LIMIT 1");
    $stmt->bind_param('s',$accountKey);$stmt->execute();$rs=$stmt->get_result();$row=$rs&&$rs->num_rows?$rs->fetch_assoc():null;$stmt->close();
    if(!$row)return null;
    return ['account_key'=>(string)$row['account_key'],'type'=>(string)$row['account_type'],'ref'=>(string)$row['account_ref'],'name'=>(string)$row['account_name'],'balance'=>(float)$row['balance'],'instance'=>(string)$row['account_ref'],'channel'=>$channel];
  }
}

if (!function_exists('finance_expense_record')) {
  function finance_expense_record(mysqli $conn,array $data): array {
    finance_expense_migrate($conn);
    $amount=round((float)($data['amount']??0),2);$day=(string)($data['spent_at']??date('Y-m-d'));
    $accountKey=trim((string)($data['account_key']??''));$typeName=trim((string)($data['type_name']??''));
    if($amount<=0)return ['ok'=>false,'error'=>'قيمة المدفوعات يجب أن تكون أكبر من صفر.'];
    if(!preg_match('/^\d{4}-\d{2}-\d{2}$/',$day)||strtotime($day)===false)return ['ok'=>false,'error'=>'تاريخ المدفوعات غير صالح.'];
    if(finance_is_month_closed($conn,(int)date('Y',strtotime($day)),(int)date('n',strtotime($day))))return ['ok'=>false,'error'=>'الشهر المحدد مغلق محاسبياً.'];
    $source=finance_expense_source_from_account($conn,$accountKey,(string)($data['channel']??'desktop'));
    if(!$source)return ['ok'=>false,'error'=>'الصندوق المختار غير موجود.'];
    if(finance_daily_account_closed($conn,$day,$source))return ['ok'=>false,'error'=>'الصندوق المختار مغلق في هذا اليوم.'];
    if((float)$source['balance']+0.00001<$amount)return ['ok'=>false,'error'=>'رصيد الصندوق المختار غير كافٍ.'];
    $actorId=(int)($data['created_by']??0);
    $normalizedType=finance_expense_normalize_type($typeName);
    $typeLength=function_exists('mb_strlen')?mb_strlen($typeName,'UTF-8'):strlen($typeName);
    if($normalizedType===''||$typeLength>120)return ['ok'=>false,'error'=>$normalizedType===''?'نوع المصروف مطلوب.':'اسم نوع المصروف طويل جداً.'];
    $note=substr(trim((string)($data['note']??'')),0,255);$reference=substr(trim((string)($data['reference']??'')),0,120);
    try{
      $conn->begin_transaction();
      $type=finance_expense_resolve_type($conn,$typeName,$actorId);if(empty($type['ok']))throw new RuntimeException((string)($type['error']??'تعذر اعتماد نوع المصروف.'));
      $stmt=$conn->prepare("INSERT INTO expenses
        (title,category,expense_type_id,amount,spent_at,expense_date,note,reference,created_by,expense_status,collection_account_key,collection_source_type,collection_source_ref,collection_source_name,source_instance_id,collection_channel,created_at)
        VALUES(?,?,?,?,?,?,?,?,?,'POSTED',?,?,?,?,?,?,NOW())");
      $typeId=(int)$type['id'];$actorDb=$actorId>0?$actorId:null;$name=(string)$type['name'];
      $stmt->bind_param('ssidssssissssss',$name,$name,$typeId,$amount,$day,$day,$note,$reference,$actorDb,$source['account_key'],$source['type'],$source['ref'],$source['name'],$source['instance'],$source['channel']);
      if(!$stmt->execute())throw new RuntimeException($stmt->error);$expenseId=(int)$stmt->insert_id;$stmt->close();
      $posted=billing_record_collection_expense($conn,$expenseId,$amount,$source,['note'=>$note!==''?$note:('مدفوعات '.$name)]);
      if(empty($posted['ok']))throw new RuntimeException((string)($posted['error']??'تعذر خصم المدفوعات من الصندوق.'));
      $conn->commit();return ['ok'=>true,'id'=>$expenseId,'account_balance'=>(float)($posted['source_balance']??0)];
    }catch(Throwable $e){@$conn->rollback();return ['ok'=>false,'error'=>$e->getMessage()];}
  }
}

if (!function_exists('finance_expense_reverse')) {
  function finance_expense_reverse(mysqli $conn,int $expenseId,int $actorId=0): array {
    finance_expense_migrate($conn);$rs=$conn->query("SELECT * FROM expenses WHERE id=".(int)$expenseId." LIMIT 1");
    $expense=$rs&&$rs->num_rows?$rs->fetch_assoc():null;if(!$expense)return ['ok'=>false,'error'=>'القيد غير موجود.'];
    if(strtoupper((string)($expense['expense_status']??'POSTED'))!=='POSTED')return ['ok'=>false,'error'=>'القيد ملغى مسبقاً.'];
    $day=date('Y-m-d',strtotime((string)($expense['spent_at']??$expense['expense_date'])));
    $source=finance_expense_source_from_account($conn,(string)$expense['collection_account_key'],'desktop');
    if($source&&finance_daily_account_closed($conn,$day,$source))return ['ok'=>false,'error'=>'لا يمكن عكس قيد من صندوق مغلق.'];
    try{$conn->begin_transaction();$reversed=billing_reverse_collection_expense($conn,$expenseId);if(empty($reversed['ok']))throw new RuntimeException((string)($reversed['error']??''));
      $actorDb=$actorId>0?$actorId:null;$stmt=$conn->prepare("UPDATE expenses SET expense_status='REVERSED',reversed_at=NOW(),reversed_by=?,updated_at=NOW() WHERE id=? AND expense_status='POSTED'");
      $stmt->bind_param('ii',$actorDb,$expenseId);if(!$stmt->execute()||$stmt->affected_rows!==1)throw new RuntimeException('تعذر تثبيت عكس القيد.');$stmt->close();$conn->commit();return ['ok'=>true,'reversed'=>(int)($reversed['reversed']??0)];
    }catch(Throwable $e){@$conn->rollback();return ['ok'=>false,'error'=>$e->getMessage()];}
  }
}

if (!function_exists('finance_expense_backfill_ledger')) {
  function finance_expense_backfill_ledger(mysqli $conn,int $limit=1000): array {
    finance_expense_migrate($conn);$out=['ok'=>true,'posted'=>0,'failed'=>0];
    $rs=@$conn->query("SELECT e.* FROM expenses e LEFT JOIN collection_ledger_entries l ON l.expense_id=e.id AND l.entry_type='EXPENSE'
      WHERE COALESCE(e.expense_status,'POSTED')='POSTED' AND e.amount>0 AND l.id IS NULL ORDER BY e.id ASC LIMIT ".max(1,min(5000,$limit)));
    while($rs&&($row=$rs->fetch_assoc())){
      $accountKey=trim((string)($row['collection_account_key']??''));if($accountKey==='')$accountKey='MAIN';
      $source=finance_expense_source_from_account($conn,$accountKey,(string)($row['collection_channel']??'legacy'));if(!$source){$out['failed']++;$out['ok']=false;continue;}
      try{$conn->begin_transaction();$posted=billing_record_collection_expense($conn,(int)$row['id'],(float)$row['amount'],$source,['note'=>'ترحيل مصروف سابق']);if(empty($posted['ok']))throw new RuntimeException((string)($posted['error']??''));
        $stmt=$conn->prepare("UPDATE expenses SET collection_account_key=?,collection_source_type=?,collection_source_ref=?,collection_source_name=? WHERE id=?");$stmt->bind_param('ssssi',$source['account_key'],$source['type'],$source['ref'],$source['name'],$row['id']);$stmt->execute();$stmt->close();$conn->commit();$out['posted']++;
      }catch(Throwable $e){@$conn->rollback();$out['failed']++;$out['ok']=false;}
    }return $out;
  }
}
