<?php
/** Daily per-installation cashbox snapshots. */

require_once __DIR__ . '/app_runtime.php';

if (!function_exists('finance_daily_migrate')) {
  function finance_daily_migrate(mysqli $conn): void {
    static $done = [];
    $connectionKey = spl_object_id($conn);
    if (!empty($done[$connectionKey])) return;
    $conn->query("CREATE TABLE IF NOT EXISTS cashbox_daily_accounts (
      id BIGINT AUTO_INCREMENT PRIMARY KEY,
      day DATE NOT NULL,
      account_key VARCHAR(140) NOT NULL,
      account_type VARCHAR(24) NOT NULL,
      account_ref VARCHAR(100) NOT NULL,
      account_name VARCHAR(150) NOT NULL,
      opening_balance DECIMAL(14,2) NOT NULL DEFAULT 0,
      income_total DECIMAL(14,2) NOT NULL DEFAULT 0,
      expense_total DECIMAL(14,2) NOT NULL DEFAULT 0,
      expected_balance DECIMAL(14,2) NOT NULL DEFAULT 0,
      closing_balance DECIMAL(14,2) NOT NULL DEFAULT 0,
      close_mode VARCHAR(16) NOT NULL DEFAULT 'AUTO',
      closed_by VARCHAR(100) NULL,
      closed_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
      totals_json LONGTEXT NULL,
      created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
      updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      UNIQUE KEY uq_cashbox_day_account (day,account_key),
      KEY idx_cashbox_account_day (account_key,day),
      KEY idx_cashbox_type_day (account_type,day)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

    if (function_exists('finance_table_exists') && finance_table_exists($conn, 'expenses')) {
      $columns = [
        'collection_source_type' => "VARCHAR(24) NULL",
        'collection_source_ref' => "VARCHAR(100) NULL",
        'collection_source_name' => "VARCHAR(150) NULL",
        'source_instance_id' => "VARCHAR(100) NULL",
        'collection_channel' => "VARCHAR(24) NULL",
      ];
      foreach ($columns as $column => $definition) {
        if (!finance_column_exists($conn, 'expenses', $column)) {
          @$conn->query("ALTER TABLE expenses ADD COLUMN `{$column}` {$definition}");
        }
      }
    }
    $done[$connectionKey] = true;
  }
}

if (!function_exists('finance_daily_source_context')) {
  function finance_daily_source_context(array $options = []): array {
    $installType = function_exists('ua_install_type') ? ua_install_type() : 'server';
    $type = strtolower(trim((string)($options['source_type'] ?? ($installType === 'branch' ? 'branch' : ($installType === 'agent' ? 'agent' : 'main')))));
    if (!in_array($type, ['main','branch','agent'], true)) $type = 'main';
    $instance = trim((string)($options['source_instance_id'] ?? ''));
    if ($instance === '' && function_exists('ua_install_instance_key')) $instance = ua_install_instance_key();
    $ref = trim((string)($options['source_ref'] ?? ''));
    if ($ref === '') $ref = $type === 'main' ? 'main' : $instance;
    if ($ref === '') $ref = $type;
    $labels = ['main'=>'النسخة الرئيسية','branch'=>'نسخة فرعية','agent'=>'نسخة مندوب'];
    $name = trim((string)($options['source_name'] ?? $labels[$type]));
    return [
      'type'=>$type,
      'ref'=>substr($ref, 0, 100),
      'name'=>substr($name, 0, 150),
      'instance'=>substr($instance, 0, 100),
      'channel'=>substr(trim((string)($options['channel'] ?? 'desktop')), 0, 24),
    ];
  }
}

if (!function_exists('finance_daily_account_key')) {
  function finance_daily_account_key(string $type, string $ref): string {
    $type = in_array($type, ['main','branch','agent'], true) ? $type : 'main';
    $ref = trim($ref) !== '' ? trim($ref) : ($type === 'main' ? 'main' : $type);
    return strtoupper($type) . ':' . substr($ref, 0, 100);
  }
}

if (!function_exists('finance_daily_add_amount')) {
  function finance_daily_add_amount(array &$rows, array $source, string $field, float $amount): void {
    $key = finance_daily_account_key((string)$source['type'], (string)$source['ref']);
    if (!isset($rows[$key])) {
      $rows[$key] = [
        'account_key'=>$key,
        'account_type'=>(string)$source['type'],
        'account_ref'=>(string)$source['ref'],
        'account_name'=>(string)$source['name'],
        'income'=>0.0,
        'expenses'=>0.0,
      ];
    }
    $rows[$key][$field] = round((float)$rows[$key][$field] + $amount, 2);
  }
}

if (!function_exists('finance_daily_movement')) {
  function finance_daily_movement(mysqli $conn, string $day): array {
    finance_daily_migrate($conn);
    $dayEsc = $conn->real_escape_string($day);
    $rows = [];
    $paymentSql = "SELECT amount,agent_id,source_instance_id,collection_source_type,collection_source_ref,collection_source_name
      FROM payments WHERE DATE(COALESCE(paid_at,created_at))='{$dayEsc}'";
    if (finance_column_exists($conn, 'payments', 'payment_status')) $paymentSql .= " AND COALESCE(payment_status,'POSTED')='POSTED'";
    $rs = @$conn->query($paymentSql);
    if ($rs) while ($row = $rs->fetch_assoc()) {
      $agentId = (int)($row['agent_id'] ?? 0);
      $type = strtolower(trim((string)($row['collection_source_type'] ?? '')));
      if (!in_array($type, ['main','branch','agent'], true)) $type = $agentId > 0 ? 'agent' : 'main';
      $ref = trim((string)($row['collection_source_ref'] ?? ''));
      if ($ref === '') $ref = $type === 'agent' && $agentId > 0 ? (string)$agentId : ($type === 'main' ? 'main' : (string)($row['source_instance_id'] ?? $type));
      $name = trim((string)($row['collection_source_name'] ?? ''));
      if ($name === '') $name = $type === 'main' ? 'النسخة الرئيسية' : ($type === 'agent' ? 'نسخة مندوب' : 'نسخة فرعية');
      finance_daily_add_amount($rows, ['type'=>$type,'ref'=>$ref,'name'=>$name], 'income', (float)$row['amount']);
    }

    if (finance_table_exists($conn, 'expenses')) {
      $rs = @$conn->query("SELECT amount,collection_source_type,collection_source_ref,collection_source_name,source_instance_id
        FROM expenses WHERE DATE(COALESCE(spent_at,expense_date))='{$dayEsc}' AND COALESCE(expense_status,'POSTED')='POSTED'");
      if ($rs) while ($row = $rs->fetch_assoc()) {
        $type = strtolower(trim((string)($row['collection_source_type'] ?? '')));
        if (!in_array($type, ['main','branch','agent'], true)) $type = 'main';
        $ref = trim((string)($row['collection_source_ref'] ?? ''));
        if ($ref === '') $ref = $type === 'main' ? 'main' : (string)($row['source_instance_id'] ?? $type);
        $name = trim((string)($row['collection_source_name'] ?? ''));
        if ($name === '') $name = $type === 'main' ? 'النسخة الرئيسية' : ($type === 'agent' ? 'نسخة مندوب' : 'نسخة فرعية');
        finance_daily_add_amount($rows, ['type'=>$type,'ref'=>$ref,'name'=>$name], 'expenses', (float)$row['amount']);
      }
    }
    return $rows;
  }
}

if (!function_exists('finance_daily_previous_closing')) {
  function finance_daily_previous_closing(mysqli $conn, string $accountKey, string $day): float {
    $stmt = $conn->prepare('SELECT closing_balance FROM cashbox_daily_accounts WHERE account_key=? AND day<? ORDER BY day DESC LIMIT 1');
    $stmt->bind_param('ss', $accountKey, $day);
    $stmt->execute();
    $stmt->bind_result($balance);
    $found = $stmt->fetch();
    $stmt->close();
    return $found ? round((float)$balance, 2) : 0.0;
  }
}

if (!function_exists('finance_daily_known_accounts')) {
  function finance_daily_known_accounts(mysqli $conn, string $day): array {
    $rows = finance_daily_movement($conn, $day);
    $rs = @$conn->query("SELECT account_key,account_type,account_ref,account_name FROM cashbox_daily_accounts
      WHERE day<'" . $conn->real_escape_string($day) . "' ORDER BY day DESC,id DESC");
    if ($rs) while ($row = $rs->fetch_assoc()) {
      $key = (string)$row['account_key'];
      if (!isset($rows[$key])) $rows[$key] = $row + ['income'=>0.0,'expenses'=>0.0];
    }
    if (!$rows) {
      $source = finance_daily_source_context();
      finance_daily_add_amount($rows, $source, 'income', 0.0);
    }
    return $rows;
  }
}

if (!function_exists('finance_daily_close_day')) {
  function finance_daily_close_day(mysqli $conn, string $day, string $mode = 'AUTO', string $by = 'system'): array {
    finance_daily_migrate($conn);
    if (!preg_match('/^\d{4}-\d{2}-\d{2}$/', $day)) return ['ok'=>false,'closed'=>0,'error'=>'invalid_day'];
    $rows = finance_daily_known_accounts($conn, $day);
    $closed = 0;
    foreach ($rows as $row) {
      $key = (string)$row['account_key'];
      $opening = finance_daily_previous_closing($conn, $key, $day);
      $income = round((float)($row['income'] ?? 0), 2);
      $expenses = round((float)($row['expenses'] ?? 0), 2);
      $expected = round($opening + $income - $expenses, 2);
      $json = json_encode(['day'=>$day,'opening'=>$opening,'income'=>$income,'expenses'=>$expenses,'expected'=>$expected,'closing'=>$expected], JSON_UNESCAPED_UNICODE|JSON_UNESCAPED_SLASHES);
      $stmt = $conn->prepare("INSERT IGNORE INTO cashbox_daily_accounts
        (day,account_key,account_type,account_ref,account_name,opening_balance,income_total,expense_total,expected_balance,closing_balance,close_mode,closed_by,closed_at,totals_json)
        VALUES (?,?,?,?,?,?,?,?,?,?,?,?,NOW(),?)");
      $type = (string)$row['account_type']; $ref = (string)$row['account_ref']; $name = (string)$row['account_name'];
      $stmt->bind_param('sssssdddddsss', $day,$key,$type,$ref,$name,$opening,$income,$expenses,$expected,$expected,$mode,$by,$json);
      if ($stmt->execute()) $closed += max(0, $stmt->affected_rows);
      $stmt->close();
    }
    return ['ok'=>true,'closed'=>$closed,'day'=>$day];
  }
}

if (!function_exists('finance_daily_auto_close')) {
  function finance_daily_auto_close(mysqli $conn, ?string $today = null): array {
    finance_daily_migrate($conn);
    $today = $today ?: date('Y-m-d');
    $days = [];
    $rs = @$conn->query("SELECT DISTINCT day_value FROM (
      SELECT DATE(COALESCE(paid_at,created_at)) day_value FROM payments WHERE DATE(COALESCE(paid_at,created_at))<'" . $conn->real_escape_string($today) . "'
      UNION SELECT DATE(COALESCE(spent_at,expense_date)) day_value FROM expenses WHERE DATE(COALESCE(spent_at,expense_date))<'" . $conn->real_escape_string($today) . "' AND COALESCE(expense_status,'POSTED')='POSTED'
    ) d WHERE day_value IS NOT NULL ORDER BY day_value ASC");
    if ($rs) while ($row = $rs->fetch_assoc()) $days[] = (string)$row['day_value'];
    $yesterday = date('Y-m-d', strtotime($today . ' -1 day'));
    if (!in_array($yesterday, $days, true)) $days[] = $yesterday;
    sort($days);
    $closed = 0;
    foreach ($days as $day) {
      $result = finance_daily_close_day($conn, $day, 'AUTO', 'system');
      $closed += (int)($result['closed'] ?? 0);
    }
    return ['ok'=>true,'closed'=>$closed,'days'=>count($days)];
  }
}

if (!function_exists('finance_daily_view')) {
  function finance_daily_view(mysqli $conn, string $day): array {
    finance_daily_migrate($conn);
    $isPast = $day < date('Y-m-d');
    if ($isPast) finance_daily_close_day($conn, $day, 'AUTO', 'system');
    $movement = finance_daily_known_accounts($conn, $day);
    $out = [];
    foreach ($movement as $key => $source) {
      $closed = null;
      $stmt = $conn->prepare('SELECT * FROM cashbox_daily_accounts WHERE day=? AND account_key=? LIMIT 1');
      $stmt->bind_param('ss', $day, $key); $stmt->execute();
      $result = $stmt->get_result(); if ($result && $result->num_rows) $closed = $result->fetch_assoc(); $stmt->close();
      $opening = $closed ? (float)$closed['opening_balance'] : finance_daily_previous_closing($conn, $key, $day);
      $income = $closed ? (float)$closed['income_total'] : (float)($source['income'] ?? 0);
      $expenses = $closed ? (float)$closed['expense_total'] : (float)($source['expenses'] ?? 0);
      $expected = $closed ? (float)$closed['expected_balance'] : round($opening + $income - $expenses, 2);
      $out[] = [
        'account_key'=>$key,'account_type'=>$source['account_type'],'account_ref'=>$source['account_ref'],'account_name'=>$source['account_name'],
        'opening'=>$opening,'income'=>$income,'expenses'=>$expenses,'net'=>round($income-$expenses,2),'final'=>$closed ? (float)$closed['closing_balance'] : $expected,
        'closed'=>(bool)$closed,'closed_at'=>$closed['closed_at'] ?? null,'close_mode'=>$closed['close_mode'] ?? null,
      ];
    }
    usort($out, static fn($a,$b) => [$a['account_type'] === 'main' ? 0 : 1,$a['account_type'],$a['account_name']] <=> [$b['account_type'] === 'main' ? 0 : 1,$b['account_type'],$b['account_name']]);
    return $out;
  }
}

if (!function_exists('finance_daily_account_closed')) {
  function finance_daily_account_closed(mysqli $conn, string $day, ?array $source = null): bool {
    finance_daily_migrate($conn);
    $source = $source ?: finance_daily_source_context();
    $key = finance_daily_account_key((string)$source['type'], (string)$source['ref']);
    $stmt = $conn->prepare('SELECT id FROM cashbox_daily_accounts WHERE day=? AND account_key=? LIMIT 1');
    $stmt->bind_param('ss', $day, $key); $stmt->execute(); $stmt->store_result();
    $found = $stmt->num_rows > 0; $stmt->close();
    return $found;
  }
}
