<?php
declare(strict_types=1);

require_once __DIR__ . '/../lib/db.php';
require_once __DIR__ . '/../lib/helpers.php';
require_once __DIR__ . '/../lib/consent.php';
require_once __DIR__ . '/../lib/mail_settings.php';
require_once __DIR__ . '/../lib/smtp_mailer.php';

if (php_sapi_name() !== 'cli') {
  http_response_code(403);
  echo "CLI only\n";
  exit;
}

$sendAll = in_array('--full', $argv ?? [], true) || in_array('--all', $argv ?? [], true);

$cfg = app_config();
$tz = (string)($cfg['timezone'] ?? 'Europe/Istanbul');
if ($tz !== '') {
  @date_default_timezone_set($tz);
}

$pdo = db();
$mailCfg = mail_load_config($pdo, $cfg);
if (!is_array($mailCfg) || empty($mailCfg['enabled'])) {
  echo "Mail disabled\n";
  exit;
}

$required = ['host','port','username','password','from_email','to'];
foreach ($required as $rk) {
  if (!isset($mailCfg[$rk]) || $mailCfg[$rk] === '' || $mailCfg[$rk] === null) {
    throw new RuntimeException("Mail config eksik: {$rk}");
  }
}

$toList = $mailCfg['to'];
if (is_string($toList)) {
  $toList = array_filter(array_map('trim', explode(',', $toList)));
}
if (!is_array($toList) || count($toList) === 0) {
  throw new RuntimeException('Mail alici listesi bos.');
}

ensure_state_table($pdo);
ensure_log_table($pdo);

$current = fetch_current_states($pdo);
$sent = fetch_sent_states($pdo);

$changes = [];
foreach ($current as $arayan => $info) {
  $status = (string)($info['status'] ?? '');
  if ($status === '') continue;
  $prev = $sent[$arayan] ?? null;
  if (!$prev || (string)($prev['last_status'] ?? '') !== $status) {
    $changes[$arayan] = $info;
  }
}

if ($sendAll) {
  $changes = $current;
}

$subjectPrefix = (string)($mailCfg['subject_prefix'] ?? 'IVR Gunluk Onay Raporu');
$subject = $subjectPrefix . ' - ' . date('Y-m-d') . ($sendAll ? ' (FULL)' : '');

if (!$changes) {
  log_email($pdo, $mailCfg, 'skipped', $subject, 0, 0, 0, [], 'no_changes');
  echo "No changes\n";
  exit;
}

$rowsOnayli = [];
$rowsOnaysiz = [];

if ($sendAll) {
  try {
    $full = fetch_full_rows($pdo);
    $rowsOnayli = $full['onayli'];
    $rowsOnaysiz = $full['onaysiz'];
  } catch (Throwable $e) {
    // fallback to delta loop
    $sendAll = false;
  }
}

if (!$sendAll) {
  foreach ($changes as $arayan => $info) {
    $status = (string)$info['status'];
    $events = fetch_events_for_number($pdo, $arayan);

    $wantedTus = ($status === 'onayli') ? consent_digit_approved() : consent_digit_rejected();
    $label = ($status === 'onayli') ? 'Onayli' : 'Onaysiz';

    $filtered = [];
    foreach ($events as $ev) {
      if ((string)($ev['tus_bilgisi'] ?? '') === $wantedTus) {
        $filtered[] = $ev;
      }
    }

    if (!$filtered) {
      $row = [
        fmt_phone($arayan),
        $label,
        $wantedTus,
        '',
        '',
        '',
        '',
      ];
      if ($status === 'onayli') $rowsOnayli[] = $row;
      else $rowsOnaysiz[] = $row;
      continue;
    }

    foreach ($filtered as $ev) {
      $row = [
        fmt_phone($arayan),
        $label,
        (string)($ev['tus_bilgisi'] ?? ''),
        (string)($ev['arama_id'] ?? ''),
        (string)($ev['created_at'] ?? ''),
        (string)($ev['ip'] ?? ''),
        (string)($ev['id'] ?? ''),
      ];
      if ($status === 'onayli') $rowsOnayli[] = $row;
      else $rowsOnaysiz[] = $row;
    }
  }
}

$attachments = [];
$dateStamp = date('Ymd');
$headers = ['arayan_no','onay_turu','tus_bilgisi','arama_id','tarih','ip','log_id'];

if ($rowsOnayli) {
  $csv = build_csv($headers, $rowsOnayli);
  $attachments[] = [
    'name' => "onayli_{$dateStamp}.csv",
    'mime' => 'text/csv',
    'content' => $csv,
  ];
}
if ($rowsOnaysiz) {
  $csv = build_csv($headers, $rowsOnaysiz);
  $attachments[] = [
    'name' => "onaysiz_{$dateStamp}.csv",
    'mime' => 'text/csv',
    'content' => $csv,
  ];
}

if (!$attachments) {
  log_email($pdo, $mailCfg, 'skipped', $subject, 0, 0, 0, [], 'no_rows');
  echo "No rows to attach\n";
  exit;
}

$attachmentNames = array_map(function ($a) {
  return (string)($a['name'] ?? '');
}, $attachments);

$countOnayliNums = count(array_unique(array_column($rowsOnayli, 0)));
$countOnaysizNums = count(array_unique(array_column($rowsOnaysiz, 0)));
$totalNumbers = count_unique_numbers($rowsOnayli, $rowsOnaysiz);

$bodyLines = [];
$bodyLines[] = $sendAll ? 'Gunluk onay raporu (tum veri)' : 'Gunluk onay raporu (degisen/yeni numaralar)';
$bodyLines[] = 'Tarih: ' . date('Y-m-d H:i');
$bodyLines[] = ($sendAll ? 'Onayli (toplam): ' : 'Onayli (yeni/degisen): ') . $countOnayliNums;
$bodyLines[] = ($sendAll ? 'Onaysiz (toplam): ' : 'Onaysiz (yeni/degisen): ') . $countOnaysizNums;
$bodyLines[] = 'Toplam numara: ' . $totalNumbers;
$bodyLines[] = '';
$bodyLines[] = 'Ekler: onayli_*.csv / onaysiz_*.csv';
$body = implode("\n", $bodyLines);

$mail = [

  'from_email' => (string)$mailCfg['from_email'],
  'from_name' => (string)($mailCfg['from_name'] ?? 'Wany IVR'),
  'to' => $toList,
  'subject' => $subject,
  'body' => $body,
    'attachments' => $attachments,
];

$raw = mail_build_message($mail);

try {
  smtp_send($mailCfg, [
    'from_email' => $mail['from_email'],
    'to' => $mail['to'],
    'raw' => $raw,
  ]);

  update_sent_states($pdo, $changes);
  log_email($pdo, $mailCfg, 'sent', $subject, $countOnayliNums, $countOnaysizNums, $totalNumbers, $attachmentNames, null);
  echo "Sent OK\n";
} catch (Throwable $e) {
  log_email($pdo, $mailCfg, 'failed', $subject, $countOnayliNums, $countOnaysizNums, $totalNumbers, $attachmentNames, $e->getMessage());
  echo "FAIL: " . $e->getMessage() . "\n";
  exit(1);
}

/* ---------------- Helpers ---------------- */

function ensure_state_table(PDO $pdo): void
{
  $pdo->exec("
    CREATE TABLE IF NOT EXISTS consent_email_state (
      arayan_no varchar(32) NOT NULL PRIMARY KEY,
      last_status varchar(16) NOT NULL,
      last_sent_at datetime NOT NULL,
      last_event_at datetime DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
  ");
}

function ensure_log_table(PDO $pdo): void
{
  $pdo->exec("
    CREATE TABLE IF NOT EXISTS consent_email_logs (
      id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
      sent_at datetime NOT NULL DEFAULT current_timestamp(),
      status varchar(16) NOT NULL,
      from_email varchar(190) DEFAULT NULL,
      to_emails text DEFAULT NULL,
      subject varchar(255) DEFAULT NULL,
      onayli_count int(10) UNSIGNED NOT NULL DEFAULT 0,
      onaysiz_count int(10) UNSIGNED NOT NULL DEFAULT 0,
      total_numbers int(10) UNSIGNED NOT NULL DEFAULT 0,
      attachments text DEFAULT NULL,
      error text DEFAULT NULL,
      PRIMARY KEY (id),
      KEY idx_sent_at (sent_at),
      KEY idx_status (status)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
  ");
}

function fetch_current_states(PDO $pdo): array
{
  $states = [];

  try {
    $sql = "
      SELECT
        JSON_UNQUOTE(JSON_EXTRACT(raw_body,'$.arayan_no')) AS arayan_no,
        MAX(CASE WHEN JSON_UNQUOTE(JSON_EXTRACT(raw_body,'$.tus_bilgisi'))='" . CONSENT_DIGIT_APPROVED . "' THEN 1 ELSE 0 END) AS has_approved,
        MAX(CASE WHEN JSON_UNQUOTE(JSON_EXTRACT(raw_body,'$.tus_bilgisi'))='" . CONSENT_DIGIT_REJECTED . "' THEN 1 ELSE 0 END) AS has_rejected,
        MAX(created_at) AS last_event_at
      FROM webhook_logs
      WHERE raw_body IS NOT NULL
        AND JSON_VALID(raw_body)
        AND JSON_EXTRACT(raw_body,'$.arayan_no') IS NOT NULL
        AND JSON_EXTRACT(raw_body,'$.tus_bilgisi') IS NOT NULL
      GROUP BY arayan_no
    ";
    $rows = $pdo->query($sql)->fetchAll() ?: [];
    foreach ($rows as $r) {
      $arayan = (string)($r['arayan_no'] ?? '');
      if ($arayan === '') continue;
      $hasApproved = ((int)($r['has_approved'] ?? 0) === 1);
      $hasRejected = ((int)($r['has_rejected'] ?? 0) === 1);
      $status = $hasApproved ? 'onayli' : ($hasRejected ? 'onaysiz' : '');
      if ($status === '') continue;
      $states[$arayan] = [
        'status' => $status,
        'last_event_at' => (string)($r['last_event_at'] ?? ''),
      ];
    }
    return $states;
  } catch (Throwable $e) {
    // fallback
  }

  try {
    $st = $pdo->query("SELECT id, created_at, raw_body FROM webhook_logs WHERE raw_body IS NOT NULL ORDER BY id DESC");
    $rows = $st ? ($st->fetchAll() ?: []) : [];

    $tmp = [];
    foreach ($rows as $r) {
      $raw = (string)($r['raw_body'] ?? '');
      if ($raw === '') continue;
      $payload = json_decode($raw, true);
      if (!is_array($payload)) continue;

      $arayan = isset($payload['arayan_no']) ? (string)$payload['arayan_no'] : '';
      $tus = isset($payload['tus_bilgisi']) ? (string)$payload['tus_bilgisi'] : '';
      if ($arayan === '' || $tus === '') continue;

      if (!isset($tmp[$arayan])) {
        $tmp[$arayan] = ['has_rejected' => false, 'has_approved' => false, 'last_event_at' => ''];
      }
      if (consent_is_approved_digit($tus)) $tmp[$arayan]['has_approved'] = true;
      if (consent_is_rejected_digit($tus)) $tmp[$arayan]['has_rejected'] = true;

      $dt = (string)($r['created_at'] ?? '');
      if ($dt !== '' && ($tmp[$arayan]['last_event_at'] === '' || $dt > $tmp[$arayan]['last_event_at'])) {
        $tmp[$arayan]['last_event_at'] = $dt;
      }
    }

    foreach ($tmp as $arayan => $v) {
      $status = $v['has_approved'] ? 'onayli' : ($v['has_rejected'] ? 'onaysiz' : '');
      if ($status === '') continue;
      $states[$arayan] = [
        'status' => $status,
        'last_event_at' => (string)$v['last_event_at'],
      ];
    }
  } catch (Throwable $e2) {
    // ignore
  }

  return $states;
}

function fetch_sent_states(PDO $pdo): array
{
  $rows = $pdo->query("SELECT arayan_no, last_status, last_sent_at, last_event_at FROM consent_email_state")?->fetchAll() ?: [];
  $map = [];
  foreach ($rows as $r) {
    $arayan = (string)($r['arayan_no'] ?? '');
    if ($arayan === '') continue;
    $map[$arayan] = $r;
  }
  return $map;
}

function fetch_events_for_number(PDO $pdo, string $arayanNo): array
{
  $sql = "
    SELECT
      id,
      arama_id,
      ip,
      created_at,
      JSON_UNQUOTE(JSON_EXTRACT(raw_body,'$.arayan_no')) AS arayan_no,
      JSON_UNQUOTE(JSON_EXTRACT(raw_body,'$.tus_bilgisi')) AS tus_bilgisi
    FROM webhook_logs
    WHERE raw_body IS NOT NULL
      AND JSON_VALID(raw_body)
      AND JSON_UNQUOTE(JSON_EXTRACT(raw_body,'$.arayan_no')) = ?
      AND JSON_EXTRACT(raw_body,'$.tus_bilgisi') IS NOT NULL
    ORDER BY created_at DESC, id DESC
  ";

  try {
    $st = $pdo->prepare($sql);
    $st->execute([$arayanNo]);
    return $st->fetchAll() ?: [];
  } catch (Throwable $e) {
    return [];
  }
}

function fetch_full_rows(PDO $pdo): array
{
  $rowsOnayli = [];
  $rowsOnaysiz = [];
  $onayliNums = [];

  $sql2 = "
    SELECT
      id,
      arama_id,
      ip,
      created_at,
      JSON_UNQUOTE(JSON_EXTRACT(raw_body,'$.arayan_no')) AS arayan_no,
      JSON_UNQUOTE(JSON_EXTRACT(raw_body,'$.tus_bilgisi')) AS tus_bilgisi
    FROM webhook_logs
    WHERE raw_body IS NOT NULL
      AND JSON_VALID(raw_body)
      AND JSON_EXTRACT(raw_body,'$.arayan_no') IS NOT NULL
      AND JSON_UNQUOTE(JSON_EXTRACT(raw_body,'$.tus_bilgisi')) = '" . CONSENT_DIGIT_APPROVED . "'
    ORDER BY created_at DESC, id DESC
  ";

  $st = $pdo->query($sql2);
  $rows = $st ? ($st->fetchAll() ?: []) : [];
  foreach ($rows as $ev) {
    $arayan = (string)($ev['arayan_no'] ?? '');
    if ($arayan === '') continue;
    $onayliNums[$arayan] = true;
    $rowsOnayli[] = [
      fmt_phone($arayan),
      'Onayli',
      (string)($ev['tus_bilgisi'] ?? ''),
      (string)($ev['arama_id'] ?? ''),
      (string)($ev['created_at'] ?? ''),
      (string)($ev['ip'] ?? ''),
      (string)($ev['id'] ?? ''),
    ];
  }

  $sql1 = "
    SELECT
      id,
      arama_id,
      ip,
      created_at,
      JSON_UNQUOTE(JSON_EXTRACT(raw_body,'$.arayan_no')) AS arayan_no,
      JSON_UNQUOTE(JSON_EXTRACT(raw_body,'$.tus_bilgisi')) AS tus_bilgisi
    FROM webhook_logs
    WHERE raw_body IS NOT NULL
      AND JSON_VALID(raw_body)
      AND JSON_EXTRACT(raw_body,'$.arayan_no') IS NOT NULL
      AND JSON_UNQUOTE(JSON_EXTRACT(raw_body,'$.tus_bilgisi')) = '" . CONSENT_DIGIT_REJECTED . "'
    ORDER BY created_at DESC, id DESC
  ";

  $st = $pdo->query($sql1);
  $rows = $st ? ($st->fetchAll() ?: []) : [];
  foreach ($rows as $ev) {
    $arayan = (string)($ev['arayan_no'] ?? '');
    if ($arayan === '' || isset($onayliNums[$arayan])) continue;
    $rowsOnaysiz[] = [
      fmt_phone($arayan),
      'Onaysiz',
      (string)($ev['tus_bilgisi'] ?? ''),
      (string)($ev['arama_id'] ?? ''),
      (string)($ev['created_at'] ?? ''),
      (string)($ev['ip'] ?? ''),
      (string)($ev['id'] ?? ''),
    ];
  }

  return ['onayli' => $rowsOnayli, 'onaysiz' => $rowsOnaysiz];
}

function update_sent_states(PDO $pdo, array $changes): void
{
  $pdo->beginTransaction();
  $st = $pdo->prepare("
    INSERT INTO consent_email_state (arayan_no, last_status, last_sent_at, last_event_at)
    VALUES (?, ?, NOW(), ?)
    ON DUPLICATE KEY UPDATE
      last_status = VALUES(last_status),
      last_sent_at = VALUES(last_sent_at),
      last_event_at = VALUES(last_event_at)
  ");

  foreach ($changes as $arayan => $info) {
    $status = (string)($info['status'] ?? '');
    $lastAt = (string)($info['last_event_at'] ?? '');
    $st->execute([$arayan, $status, ($lastAt !== '' ? $lastAt : null)]);
  }

  $pdo->commit();
}

function log_email(
  PDO $pdo,
  array $mailCfg,
  string $status,
  string $subject,
  int $countOnayli,
  int $countOnaysiz,
  int $totalNumbers,
  array $attachments,
  ?string $error
): void {
  $from = (string)($mailCfg['from_email'] ?? '');
  $to = $mailCfg['to'] ?? [];
  if (is_string($to)) {
    $to = array_filter(array_map('trim', explode(',', $to)));
  }
  if (!is_array($to)) $to = [];

  $toJson = json_encode($to, JSON_UNESCAPED_UNICODE);
  $attJson = json_encode($attachments, JSON_UNESCAPED_UNICODE);

  $st = $pdo->prepare("
    INSERT INTO consent_email_logs
      (status, from_email, to_emails, subject, onayli_count, onaysiz_count, total_numbers, attachments, error)
    VALUES
      (?, ?, ?, ?, ?, ?, ?, ?, ?)
  ");
  $st->execute([
    $status,
    $from !== '' ? $from : null,
    $toJson ?: null,
    $subject !== '' ? $subject : null,
    $countOnayli,
    $countOnaysiz,
    $totalNumbers,
    $attJson ?: null,
    $error,
  ]);
}

function build_csv(array $headers, array $rows): string
{
  $fh = fopen('php://temp', 'w+');
  fputcsv($fh, $headers, ';');
  foreach ($rows as $r) {
    fputcsv($fh, $r, ';');
  }
  rewind($fh);
  $csv = stream_get_contents($fh) ?: '';
  fclose($fh);
  return "\xEF\xBB\xBF" . $csv;
}

function fmt_phone(string $num): string
{
  $num = trim($num);
  if ($num === '') return '';
  return '="' . $num . '"';
}

function count_unique_numbers(array $rowsOnayli, array $rowsOnaysiz): int
{
  $set = [];
  foreach ([$rowsOnayli, $rowsOnaysiz] as $rows) {
    foreach ($rows as $r) {
      $raw = (string)($r[0] ?? '');
      $num = preg_replace('/\D+/', '', $raw) ?? '';
      if ($num !== '') $set[$num] = true;
    }
  }
  return count($set);
}
