<?php
// app/Services/AccountingService.php
require_once __DIR__ . '/../db.php';

class AccountingService {
  public function __construct(private PDO $pdo) {}

  private function hasCashTxStatusColumn(): bool {
    static $has = null;
    if ($has !== null) return $has;
    try {
      $q = $this->pdo->query("SHOW COLUMNS FROM cash_transactions LIKE 'status'");
      $has = (bool)$q->fetch(PDO::FETCH_ASSOC);
    } catch (Throwable $e) {
      $has = false;
    }
    return $has;
  }

  /** Create cash transaction as DRAFT (no journal created yet) */
public function createCashTxDraft(array $data): int {
  $manageTx = !$this->pdo->inTransaction();
  if ($manageTx) $this->pdo->beginTransaction();
  try {
    $stmt = $this->pdo->prepare("
      INSERT INTO cash_transactions
      (company_id, tx_date, tx_type, cash_account_id, counter_account_id, amount, memo, attachment, created_by)
      VALUES (:company_id, :tx_date, :tx_type, :cash_account_id, :counter_account_id, :amount, :memo, :attachment, :created_by)
    ");
    $stmt->execute([
      ':company_id' => $data['company_id'],
      ':tx_date' => $data['tx_date'],
      ':tx_type' => $data['tx_type'],
      ':cash_account_id' => $data['cash_account_id'],
      ':counter_account_id' => $data['counter_account_id'],
      ':amount' => $data['amount'],
      ':memo' => $data['memo'] ?? null,
      ':attachment' => $data['attachment'] ?? null,
      ':created_by' => $data['created_by'] ?? null,
    ]);
    $cashTxId = (int)$this->pdo->lastInsertId();
    if ($manageTx) $this->pdo->commit();
    return $cashTxId;
  } catch (Throwable $e) {
    if ($manageTx && $this->pdo->inTransaction()) $this->pdo->rollBack();
    throw $e;
  }
}

/** Post one cash transaction: create journal entry + lines + mapping */
public function postCashTx(int $companyId, int $cashTxId, ?int $postedBy=null): int {
  $manageTx = !$this->pdo->inTransaction();
  if ($manageTx) $this->pdo->beginTransaction();
  try {
    // prevent double post
    $chk = $this->pdo->prepare("SELECT entry_id FROM cash_tx_journal_map WHERE cash_tx_id=?");
    $chk->execute([$cashTxId]);
    $exists = $chk->fetchColumn();
    if ($exists) { if ($manageTx) $this->pdo->commit(); return (int)$exists; }

    $st = $this->pdo->prepare("SELECT * FROM cash_transactions WHERE id=? AND company_id=?");
    $st->execute([$cashTxId, $companyId]);
    $tx = $st->fetch(PDO::FETCH_ASSOC);
    if (!$tx) throw new Exception("Cash transaction not found");

    $amount = (float)$tx['amount'];
    if ($amount <= 0) throw new Exception("Amount must be > 0");

    $ref = "CASH-" . $cashTxId;
    $stmt = $this->pdo->prepare("
      INSERT INTO journal_entries
      (company_id, entry_date, ref, memo, source, source_id, status, created_by)
      VALUES (:company_id, :entry_date, :ref, :memo, 'CASH_TX', :source_id, 'POSTED', :created_by)
    ");
    $stmt->execute([
      ':company_id' => $companyId,
      ':entry_date' => $tx['tx_date'],
      ':ref' => $ref,
      ':memo' => $tx['memo'] ?? null,
      ':source_id' => $cashTxId,
      ':created_by' => $postedBy,
    ]);
    $entryId = (int)$this->pdo->lastInsertId();

    $cashAcc = (int)$tx['cash_account_id'];
    $cntAcc  = (int)$tx['counter_account_id'];

    if ($tx['tx_type'] === 'IN') {
      $lines = [
        ['no'=>1,'acc'=>$cashAcc,'desc'=>'تحصيل نقدي','dr'=>$amount,'cr'=>0],
        ['no'=>2,'acc'=>$cntAcc ,'desc'=>'مقابل التحصيل','dr'=>0,'cr'=>$amount],
      ];
    } else {
      $lines = [
        ['no'=>1,'acc'=>$cntAcc ,'desc'=>'مصروف/أصل','dr'=>$amount,'cr'=>0],
        ['no'=>2,'acc'=>$cashAcc,'desc'=>'سداد نقدي','dr'=>0,'cr'=>$amount],
      ];
    }

    $stmt = $this->pdo->prepare("
      INSERT INTO journal_lines
      (company_id, entry_id, line_no, account_id, description, debit, credit)
      VALUES (:company_id, :entry_id, :line_no, :account_id, :description, :debit, :credit)
    ");
    foreach ($lines as $ln) {
      $stmt->execute([
        ':company_id' => $companyId,
        ':entry_id' => $entryId,
        ':line_no' => $ln['no'],
        ':account_id' => $ln['acc'],
        ':description' => $ln['desc'],
        ':debit' => $ln['dr'],
        ':credit' => $ln['cr'],
      ]);
    }

    $stmt = $this->pdo->prepare("INSERT INTO cash_tx_journal_map (cash_tx_id, entry_id) VALUES (?, ?)");
    $stmt->execute([$cashTxId, $entryId]);
    if ($this->hasCashTxStatusColumn()) {
      $this->pdo->prepare("UPDATE cash_transactions SET status='POSTED' WHERE id=? AND company_id=?")->execute([$cashTxId, $companyId]);
    }

    if ($manageTx) $this->pdo->commit();
    return $entryId;

  } catch (Throwable $e) {
    if ($manageTx && $this->pdo->inTransaction()) $this->pdo->rollBack();
    throw $e;
  }
}

/** Unpost one cash transaction: delete journal entry + mapping (allows edit/delete) */
public function unpostCashTx(int $companyId, int $cashTxId): void {
  $manageTx = !$this->pdo->inTransaction();
  if ($manageTx) $this->pdo->beginTransaction();
  try {
    $chk = $this->pdo->prepare("SELECT entry_id FROM cash_tx_journal_map WHERE cash_tx_id=?");
    $chk->execute([$cashTxId]);
    $entryId = (int)($chk->fetchColumn() ?: 0);
    if ($entryId) {
      // ensure entry belongs to company & source
      $st = $this->pdo->prepare("SELECT id FROM journal_entries WHERE id=? AND company_id=? AND source='CASH_TX' AND source_id=?");
      $st->execute([$entryId, $companyId, $cashTxId]);
      if ($st->fetchColumn()) {
        $this->pdo->prepare("DELETE FROM journal_lines WHERE company_id=? AND entry_id=?")->execute([$companyId,$entryId]);
        $this->pdo->prepare("DELETE FROM journal_entries WHERE company_id=? AND id=?")->execute([$companyId,$entryId]);
      }
      $this->pdo->prepare("DELETE FROM cash_tx_journal_map WHERE cash_tx_id=?")->execute([$cashTxId]);
      if ($this->hasCashTxStatusColumn()) {
        $this->pdo->prepare("UPDATE cash_transactions SET status='DRAFT' WHERE id=? AND company_id=?")->execute([$cashTxId, $companyId]);
      }
    }
    if ($manageTx) $this->pdo->commit();
  } catch (Throwable $e) {
    if ($manageTx && $this->pdo->inTransaction()) $this->pdo->rollBack();
    throw $e;
  }
}

  public function createManualJournal(int $companyId, string $entryDate, string $ref, string $memo, array $lines, ?int $createdBy = null, string $source='MANUAL', $sourceId=null): int {
    // $lines: each ['account_id'=>int,'debit'=>float,'credit'=>float,'description'=>string|null]
    $manageTx = !$this->pdo->inTransaction();
    if ($manageTx) $this->pdo->beginTransaction();
    try {
      $stmt = $this->pdo->prepare("INSERT INTO journal_entries (company_id, entry_date, ref, memo, source, source_id, status, created_by)
                                   VALUES (?,?,?,?,?,?, 'POSTED', ?)");
      $stmt->execute([$companyId, $entryDate, $ref, $memo, $source, $sourceId, $createdBy]);
      $entryId = (int)$this->pdo->lastInsertId();

      $ln = 1;
      $ins = $this->pdo->prepare("INSERT INTO journal_lines (company_id, entry_id, line_no, account_id, description, debit, credit)
                                  VALUES (?,?,?,?,?,?,?)");
      foreach ($lines as $l) {
        $ins->execute([
          $companyId,
          $entryId,
          $ln++,
          (int)$l['account_id'],
          (string)($l['description'] ?? ''),
          (float)($l['debit'] ?? 0),
          (float)($l['credit'] ?? 0),
        ]);
      }

      if ($manageTx) $this->pdo->commit();
      return $entryId;
    } catch (Throwable $e) {
      if ($manageTx) $this->pdo->rollBack();
      throw $e;
    }
  }



  /** Post all DRAFT financial events for a given day (creates journal entries/lines) */
  public function postFinancialEventsForDay(int $companyId, string $day, int $userId = 0): array {
    $manageTx = !$this->pdo->inTransaction();
    if ($manageTx) $this->pdo->beginTransaction();
    $posted = 0;
    try{
      $st = $this->pdo->prepare("
        SELECT id
        FROM financial_events
        WHERE company_id=? AND event_date=? AND status='DRAFT'
        ORDER BY id
      ");
      $st->execute([$companyId, $day]);
      $ids = $st->fetchAll(PDO::FETCH_COLUMN);

      foreach($ids as $eid){
        $eid = (int)$eid;

        // create journal entry
        $ev = $this->pdo->prepare("SELECT memo FROM financial_events WHERE id=? AND company_id=?");
        $ev->execute([$eid, $companyId]);
        $memo = (string)($ev->fetchColumn() ?: 'Financial Event');

        $je = $this->pdo->prepare("INSERT INTO journal_entries (company_id, entry_date, memo, created_by) VALUES (?,?,?,?)");
        $je->execute([$companyId, $day, $memo, $userId]);
        $entryId = (int)$this->pdo->lastInsertId();

        // lines
        $ln = $this->pdo->prepare("SELECT account_id, debit, credit FROM financial_event_lines WHERE event_id=?");
        $ln->execute([$eid]);
        $ins = $this->pdo->prepare("INSERT INTO journal_lines (entry_id, account_id, debit, credit) VALUES (?,?,?,?)");
        while($r = $ln->fetch(PDO::FETCH_ASSOC)){
          $ins->execute([$entryId, (int)$r['account_id'], (float)$r['debit'], (float)$r['credit']]);
        }

        $up = $this->pdo->prepare("UPDATE financial_events SET status='POSTED', entry_id=? WHERE id=? AND company_id=?");
        $up->execute([$entryId, $eid, $companyId]);
        $posted++;
      }

      if ($manageTx) $this->pdo->commit();
      return ['ok'=>true,'posted'=>$posted];
    }catch(Throwable $e){
      if ($manageTx && $this->pdo->inTransaction()) $this->pdo->rollBack();
      throw $e;
    }
  }

  /** Undo (delete) POSTED financial events journals for a day, and revert to DRAFT */
  public function undoFinancialEventsForDay(int $companyId, string $day): array {
    $manageTx = !$this->pdo->inTransaction();
    if ($manageTx) $this->pdo->beginTransaction();
    $undone = 0;
    try{
      $st = $this->pdo->prepare("
        SELECT id, entry_id
        FROM financial_events
        WHERE company_id=? AND event_date=? AND status='POSTED' AND entry_id IS NOT NULL
        ORDER BY id
      ");
      $st->execute([$companyId, $day]);
      $rows = $st->fetchAll(PDO::FETCH_ASSOC);

      foreach($rows as $r){
        $eid = (int)$r['id'];
        $entryId = (int)$r['entry_id'];

        $this->pdo->prepare("DELETE FROM journal_lines WHERE entry_id=?")->execute([$entryId]);
        $this->pdo->prepare("DELETE FROM journal_entries WHERE id=? AND company_id=?")->execute([$entryId, $companyId]);

        $this->pdo->prepare("UPDATE financial_events SET status='DRAFT', entry_id=NULL WHERE id=? AND company_id=?")
                  ->execute([$eid, $companyId]);
        $undone++;
      }

      if ($manageTx) $this->pdo->commit();
      return ['ok'=>true,'undone'=>$undone];
    }catch(Throwable $e){
      if ($manageTx && $this->pdo->inTransaction()) $this->pdo->rollBack();
      throw $e;
    }
  }

}
