<?php
declare(strict_types=1);

/**
 * modules/accounts/expenses/edit.php
 *
 * Edit an Operating Expense (transaction row with type='expense').
 *
 * Features:
 *  - Edit date, amount, COA expense account, reference, milestone, notes
 *  - Replace/remove primary attachment (transactions.attachment_url if present)
 *  - Append extra attachment (if transaction_attachments table exists)
 *  - Delete extra attachments
 *
 * File storage: /public/uploads/expenses
 */

////////////////////////////////////////////////////////////
// 1) Bootstrap & guards
////////////////////////////////////////////////////////////
require_once dirname(__DIR__, 3) . '/config/functions.php';
require_role(['Accounts','MD','Admin','Management']);

$user = current_user();
$cid  = (int)($user['company_id'] ?? 0);
if ($cid <= 0) redirect(url_public('login.php'));

$errors = [];
$notice = null;

$id = (int)($_GET['id'] ?? $_POST['id'] ?? 0);
if ($id <= 0) redirect(url_modules('accounts/expenses/list.php'));

////////////////////////////////////////////////////////////
// 2) Helpers
////////////////////////////////////////////////////////////
/** Check if a table has a column (cached) */
function table_has_column(string $table, string $column): bool {
  static $cache = [];
  $key = $table.'|'.$column;
  if (isset($cache[$key])) return $cache[$key];
  try {
    $q = db()->prepare("SHOW COLUMNS FROM `$table` LIKE :col");
    $q->execute([':col'=>$column]);
    $cache[$key] = (bool)$q->fetch();
    return $cache[$key];
  } catch (Throwable $e) {
    return false;
  }
}

/** Check if table exists */
function table_exists(string $table): bool {
  try {
    $q = db()->query("SHOW TABLES LIKE " . db()->quote($table));
    return (bool)$q->fetchColumn();
  } catch (Throwable $e) {
    return false;
  }
}

/** ensure directory exists */
function ensure_dir(string $dir): void {
  if (!is_dir($dir)) @mkdir($dir, 0775, true);
}

/** delete file if exists (public path relative) */
function delete_public_file(string $rel): void {
  $abs = dirname(__DIR__, 3) . '/public/' . ltrim($rel, '/');
  if (is_file($abs)) @unlink($abs);
}

////////////////////////////////////////////////////////////
// 3) Detect optional columns/tables
////////////////////////////////////////////////////////////
$hasRef   = table_has_column('transactions','reference');
$hasMile  = table_has_column('transactions','milestone');
$hasFile  = table_has_column('transactions','attachment_url');
$hasNotes = table_has_column('transactions','notes');
$hasUpdatedAt = table_has_column('transactions','updated_at');
$attTable = table_exists('transaction_attachments');

////////////////////////////////////////////////////////////
// 4) Load expense accounts from COA
////////////////////////////////////////////////////////////
$accountsByCat = [];
try {
  $st = db()->prepare("
    SELECT account_code, name, COALESCE(category,'') AS cat
    FROM chart_of_accounts
    WHERE company_id = :cid
      AND type = 'expense'
      AND (is_active = 1 OR is_active IS NULL)
    ORDER BY cat ASC, name ASC, account_code ASC
  ");
  $st->execute([':cid'=>$cid]);
  $rows = $st->fetchAll();
  foreach ($rows as $r) {
    $cat = (string)$r['cat'];
    $label = $cat === '' ? 'Uncategorised' : ucwords(strtolower(str_replace('_',' ', $cat)));
    $accountsByCat[$label][] = [
      'code' => (string)$r['account_code'],
      'name' => (string)$r['name'],
    ];
  }
} catch (Throwable $e) {
  $errors[] = (APP_ENV === 'dev') ? $e->getMessage() : 'Unable to load expense accounts.';
}

////////////////////////////////////////////////////////////
// 5) Load transaction + any extra attachments
////////////////////////////////////////////////////////////
$tx = null;
$extraAtt = [];
try {
  $sel = db()->prepare("
    SELECT *
    FROM transactions
    WHERE id = :id AND company_id = :cid AND type = 'expense'
    LIMIT 1
  ");
  $sel->execute([':id'=>$id, ':cid'=>$cid]);
  $tx = $sel->fetch();
  if (!$tx) {
    $errors[] = 'Expense not found.';
  } else if ($attTable) {
    $ga = db()->prepare("
      SELECT id, file_url, uploaded_at
      FROM transaction_attachments
      WHERE company_id = :cid AND transaction_id = :tid
      ORDER BY id DESC
    ");
    $ga->execute([':cid'=>$cid, ':tid'=>$id]);
    $extraAtt = $ga->fetchAll() ?: [];
  }
} catch (Throwable $e) {
  $errors[] = (APP_ENV === 'dev') ? $e->getMessage() : 'Unable to load expense.';
}

////////////////////////////////////////////////////////////
// 6) Handle POST Actions
////////////////////////////////////////////////////////////
/* Delete an extra attachment */
if ($_SERVER['REQUEST_METHOD'] === 'POST' && ($_POST['action'] ?? '') === 'del_attach' && $attTable) {
  try {
    csrf_verify((string)($_POST['csrf'] ?? ''));
    $aid = (int)($_POST['attach_id'] ?? 0);
    if ($aid > 0) {
      $chk = db()->prepare("
        SELECT file_url FROM transaction_attachments
        WHERE id=:id AND company_id=:cid AND transaction_id=:tid
        LIMIT 1
      ");
      $chk->execute([':id'=>$aid, ':cid'=>$cid, ':tid'=>$id]);
      if ($row = $chk->fetch()) {
        $del = db()->prepare("DELETE FROM transaction_attachments WHERE id=:id");
        $del->execute([':id'=>$aid]);
        if (!empty($row['file_url'])) delete_public_file((string)$row['file_url']);
        $notice = 'Attachment removed.';
      }
    }
    // Refresh to avoid resubmission
    redirect(url_modules('accounts/expenses/edit.php').'?id='.$id.'&ok=1');
  } catch (Throwable $e) {
    $errors[] = (APP_ENV === 'dev') ? $e->getMessage() : 'Unable to remove attachment.';
  }
}

/* Save changes (+optional replace/append file) */
if ($_SERVER['REQUEST_METHOD'] === 'POST' && ($_POST['action'] ?? '') === 'save') {
  try {
    csrf_verify((string)($_POST['csrf'] ?? ''));

    $date   = trim((string)($_POST['date'] ?? ''));
    $acct   = trim((string)($_POST['account_code'] ?? ''));
    $amount = (float)($_POST['amount'] ?? 0);
    $ref    = trim((string)($_POST['reference'] ?? ''));
    $mile   = trim((string)($_POST['milestone'] ?? ''));
    $notes  = trim((string)($_POST['notes'] ?? ''));

    $replacePrimary = (int)($_POST['replace_primary'] ?? 0); // 1 = replace, 2 = remove
    $extraAppend    = !empty($_FILES['extra_file']['name']);

    // Validate basics
    if (!preg_match('/^\d{4}-\d{2}-\d{2}$/', $date)) {
      $errors[] = 'Invalid date.';
    }
    if ($acct === '') {
      $errors[] = 'Please select an expense account.';
    } else {
      $chk = db()->prepare("
        SELECT 1 FROM chart_of_accounts
        WHERE company_id=:cid AND account_code=:code AND type='expense'
        LIMIT 1
      ");
      $chk->execute([':cid'=>$cid, ':code'=>$acct]);
      if (!$chk->fetch()) $errors[] = 'Selected account not found.';
    }
    if ($amount <= 0) {
      $errors[] = 'Amount must be greater than zero.';
    }

    // Prepare file upload dir
    $uploadedPrimaryUrl = null;
    $extraUploadedUrl   = null;
    $uploadDir = dirname(__DIR__, 3) . '/public/uploads/expenses';
    ensure_dir($uploadDir);

    // Replace primary?
    if ($hasFile && $replacePrimary === 1 && !empty($_FILES['primary_file']['name'])) {
      if (!is_uploaded_file($_FILES['primary_file']['tmp_name']) || $_FILES['primary_file']['error'] !== UPLOAD_ERR_OK) {
        $errors[] = 'Primary file upload failed.';
      } else {
        $ext = strtolower(pathinfo((string)$_FILES['primary_file']['name'], PATHINFO_EXTENSION));
        $safe = 'exp_' . $cid . '_' . date('Ymd_His') . '_' . bin2hex(random_bytes(4)) . ($ext ? '.'.$ext : '');
        $dest = $uploadDir . '/' . $safe;
        if (!move_uploaded_file($_FILES['primary_file']['tmp_name'], $dest)) {
          $errors[] = 'Could not move primary uploaded file.';
        } else {
          $uploadedPrimaryUrl = 'uploads/expenses/' . $safe;
        }
      }
    }

    // Append extra attachment?
    if ($attTable && $extraAppend) {
      if (!is_uploaded_file($_FILES['extra_file']['tmp_name']) || $_FILES['extra_file']['error'] !== UPLOAD_ERR_OK) {
        $errors[] = 'Extra attachment upload failed.';
      } else {
        $ext = strtolower(pathinfo((string)$_FILES['extra_file']['name'], PATHINFO_EXTENSION));
        $safe = 'exp_' . $cid . '_' . date('Ymd_His') . '_' . bin2hex(random_bytes(4)) . ($ext ? '.'.$ext : '');
        $dest = $uploadDir . '/' . $safe;
        if (!move_uploaded_file($_FILES['extra_file']['tmp_name'], $dest)) {
          $errors[] = 'Could not move extra uploaded file.';
        } else {
          $extraUploadedUrl = 'uploads/expenses/' . $safe;
        }
      }
    }

    if (!$errors) {
      // Build dynamic UPDATE
      $set = [
        "date = :dt",
        "account_code = :acc",
        "type = 'expense'",
        "amount = :amt",
      ];
      $args = [
        ':dt'  => $date,
        ':acc' => $acct,
        ':amt' => $amount,
        ':id'  => $id,
        ':cid' => $cid,
      ];

      if ($hasNotes) { $set[] = "notes = :notes"; $args[':notes'] = ($notes !== '' ? $notes : null); }
      if ($hasRef)   { $set[] = "reference = :ref"; $args[':ref'] = ($ref !== '' ? $ref : null); }
      if ($hasMile)  { $set[] = "milestone = :mile"; $args[':mile'] = ($mile !== '' ? $mile : null); }

      // Primary attachment replace/remove
      $oldFile = $hasFile ? (string)($tx['attachment_url'] ?? '') : '';
      if ($hasFile) {
        if ($replacePrimary === 1 && $uploadedPrimaryUrl) {
          $set[] = "attachment_url = :file";
          $args[':file'] = $uploadedPrimaryUrl;
        } elseif ($replacePrimary === 2) {
          $set[] = "attachment_url = NULL";
        }
      } else {
        // If column missing but user tried to replace, fold info into notes
        if ($replacePrimary === 1 && $uploadedPrimaryUrl && $hasNotes) {
          $args[':notes'] = trim(($args[':notes'] ?? '') . "\nAttachment: " . $uploadedPrimaryUrl);
        }
      }

      if ($hasUpdatedAt) $set[] = "updated_at = NOW()";

      $sql = "UPDATE transactions SET ".implode(', ', $set)." WHERE id=:id AND company_id=:cid AND type='expense' LIMIT 1";
      $upd = db()->prepare($sql);
      $upd->execute($args);

      // If we replaced primary, delete old file
      if ($hasFile && $replacePrimary === 1 && $uploadedPrimaryUrl && $oldFile) {
        delete_public_file($oldFile);
      }
      // If we removed primary, delete the old file
      if ($hasFile && $replacePrimary === 2 && $oldFile) {
        delete_public_file($oldFile);
      }

      // Append extra attachment into transaction_attachments
      if ($attTable && $extraUploadedUrl) {
        $ins = db()->prepare("
          INSERT INTO transaction_attachments (company_id, transaction_id, file_url, uploaded_at)
          VALUES (:cid, :tid, :url, NOW())
        ");
        $ins->execute([':cid'=>$cid, ':tid'=>$id, ':url'=>$extraUploadedUrl]);
      }

      audit_log('expense.update','transaction', $id, [
        'account'=>$acct,'amount'=>$amount,'date'=>$date,'replaced'=>$replacePrimary
      ]);

      // Success → back to list pinned to month of date
      $mStart = (new DateTimeImmutable($date))->modify('first day of this month')->format('Y-m-d');
      $mEnd   = (new DateTimeImmutable($date))->modify('last day of this month')->format('Y-m-d');
      redirect(url_modules('accounts/expenses/list.php').'?from='.$mStart.'&to='.$mEnd.'&ok=1');
    }

  } catch (Throwable $e) {
    $errors[] = (APP_ENV === 'dev') ? $e->getMessage() : 'Unable to save changes.';
  }
}

// Success toast from other sub-actions
if (isset($_GET['ok'])) $notice = 'Saved.';

////////////////////////////////////////////////////////////
// 7) Render
////////////////////////////////////////////////////////////
include dirname(__DIR__, 3) . '/includes/header.php';
?>
<div class="d-flex justify-content-between align-items-center mb-3">
  <div>
    <h1 class="h4 mb-0">Edit Expense</h1>
    <div class="text-muted">Update fields, replace/remove primary attachment, or attach more files.</div>
  </div>
  <div class="d-flex flex-wrap gap-2">
    <a class="btn btn-outline-secondary" href="<?= e(url_modules('accounts/expenses/list.php')) ?>">← Back to Expenses</a>
  </div>
</div>

<?php if ($notice): ?><div class="alert alert-success"><?= e($notice) ?></div><?php endif; ?>
<?php if ($errors): ?>
  <div class="alert alert-danger"><ul class="mb-0"><?php foreach ($errors as $er) echo '<li>'.e($er).'</li>'; ?></ul></div>
<?php endif; ?>

<?php if ($tx): ?>
<?php
  $defDate = e($_POST['date'] ?? (string)$tx['date']);
  $defAmt  = e($_POST['amount'] ?? (string)$tx['amount']);
  $defAcc  = (string)($_POST['account_code'] ?? (string)$tx['account_code']);
  $defRef  = e($_POST['reference'] ?? (string)($tx['reference'] ?? ''));
  $defMil  = e($_POST['milestone'] ?? (string)($tx['milestone'] ?? ''));
  $defNotes= e($_POST['notes'] ?? (string)($tx['notes'] ?? ''));
  $currentFile = $hasFile ? (string)($tx['attachment_url'] ?? '') : '';
?>
<div class="row g-3">
  <div class="col-12 col-xl-7">
    <div class="card shadow-sm h-100">
      <div class="card-body">
        <form method="post" enctype="multipart/form-data" class="row g-3">
          <input type="hidden" name="csrf" value="<?= e(csrf_token()) ?>">
          <input type="hidden" name="action" value="save">
          <input type="hidden" name="id"     value="<?= (int)$id ?>">

          <div class="col-md-6">
            <label class="form-label">Date</label>
            <input type="date" class="form-control" name="date" value="<?= $defDate ?>" required>
          </div>

          <div class="col-md-6">
            <label class="form-label">Amount (£)</label>
            <input type="number" step="0.01" min="0" class="form-control" name="amount" value="<?= $defAmt ?>" required>
          </div>

          <div class="col-12">
            <label class="form-label">Expense Account (COA)</label>
            <select class="form-select" name="account_code" required>
              <option value="">Select an expense account…</option>
              <?php foreach ($accountsByCat as $cat => $list): ?>
                <optgroup label="<?= e($cat) ?>">
                  <?php foreach ($list as $a): ?>
                    <option value="<?= e($a['code']) ?>" <?= $defAcc===$a['code'] ? 'selected':'' ?>>
                      <?= e($a['name']) ?> (<?= e($a['code']) ?>)
                    </option>
                  <?php endforeach; ?>
                </optgroup>
              <?php endforeach; ?>
            </select>
          </div>

          <?php if ($hasRef): ?>
            <div class="col-md-6">
              <label class="form-label">Reference</label>
              <input class="form-control" name="reference" placeholder="Receipt / Supplier / Ref" value="<?= $defRef ?>">
            </div>
          <?php endif; ?>

          <?php if ($hasMile): ?>
            <div class="col-md-6">
              <label class="form-label">Milestone</label>
              <input class="form-control" name="milestone" placeholder="e.g., Week 2 / Phase A" value="<?= $defMil ?>">
            </div>
          <?php endif; ?>

          <?php if ($hasNotes): ?>
            <div class="col-12">
              <label class="form-label">Notes</label>
              <textarea class="form-control" name="notes" rows="3" placeholder="Any details"><?= $defNotes ?></textarea>
            </div>
          <?php endif; ?>

          <?php if ($hasFile): ?>
            <div class="col-12">
              <label class="form-label d-flex align-items-center justify-content-between">
                <span>Primary Attachment</span>
                <?php if ($currentFile): ?>
                  <a class="small" target="_blank" href="<?= e(url_public($currentFile)) ?>">Open current</a>
                <?php endif; ?>
              </label>
              <div class="border rounded p-2">
                <div class="form-check">
                  <input class="form-check-input" type="radio" name="replace_primary" id="rp0" value="0" checked>
                  <label class="form-check-label" for="rp0">Keep current file</label>
                </div>
                <div class="form-check mt-2">
                  <input class="form-check-input" type="radio" name="replace_primary" id="rp1" value="1">
                  <label class="form-check-label" for="rp1">Replace with new file</label>
                </div>
                <div class="mt-2">
                  <input type="file" class="form-control" name="primary_file" accept=".pdf,.png,.jpg,.jpeg,.heic,.webp,.gif,.doc,.docx,.xls,.xlsx,.csv,.txt">
                </div>
                <div class="form-check mt-2">
                  <input class="form-check-input" type="radio" name="replace_primary" id="rp2" value="2">
                  <label class="form-check-label" for="rp2">Remove file</label>
                </div>
              </div>
              <div class="form-text">If you choose “Replace”, select a file above. Files are stored under uploads/expenses/</div>
            </div>
          <?php endif; ?>

          <?php if ($attTable): ?>
            <div class="col-12">
              <label class="form-label">Append Extra Attachment (optional)</label>
              <input type="file" class="form-control" name="extra_file" accept=".pdf,.png,.jpg,.jpeg,.heic,.webp,.gif,.doc,.docx,.xls,.xlsx,.csv,.txt">
              <div class="form-text">Adds a secondary attachment (keeps the primary file intact).</div>
            </div>
          <?php endif; ?>

          <div class="col-12 d-flex gap-2">
            <button class="btn btn-dark">Save Changes</button>
            <a class="btn btn-outline-secondary" href="<?= e(url_modules('accounts/expenses/list.php')) ?>">Cancel</a>
          </div>
        </form>
      </div>
    </div>
  </div>

  <?php if ($attTable): ?>
  <div class="col-12 col-xl-5">
    <div class="card shadow-sm h-100">
      <div class="card-body">
        <h2 class="h6 text-uppercase text-muted mb-2">Extra Attachments</h2>
        <?php if ($extraAtt): ?>
          <div class="table-responsive">
            <table class="table table-sm align-middle mb-0">
              <thead class="table-light">
                <tr>
                  <th>File</th>
                  <th style="width: 140px;">Uploaded</th>
                  <th class="text-end" style="width: 100px;">Actions</th>
                </tr>
              </thead>
              <tbody>
                <?php foreach ($extraAtt as $a): ?>
                  <tr>
                    <td>
                      <?php if (!empty($a['file_url'])): ?>
                        <a target="_blank" href="<?= e(url_public((string)$a['file_url'])) ?>"><?= e(basename((string)$a['file_url'])) ?></a>
                      <?php else: ?>
                        <span class="text-muted">—</span>
                      <?php endif; ?>
                    </td>
                    <td><span class="small text-muted"><?= e((string)($a['uploaded_at'] ?? '')) ?></span></td>
                    <td class="text-end">
                      <form method="post" class="d-inline" onsubmit="return confirm('Remove this attachment?')">
                        <input type="hidden" name="csrf" value="<?= e(csrf_token()) ?>">
                        <input type="hidden" name="action" value="del_attach">
                        <input type="hidden" name="id" value="<?= (int)$id ?>">
                        <input type="hidden" name="attach_id" value="<?= (int)$a['id'] ?>">
                        <button class="btn btn-sm btn-outline-danger">Delete</button>
                      </form>
                    </td>
                  </tr>
                <?php endforeach; ?>
              </tbody>
            </table>
          </div>
        <?php else: ?>
          <div class="text-muted">No extra attachments.</div>
        <?php endif; ?>
      </div>
    </div>
  </div>
  <?php endif; ?>
</div>
<?php endif; ?>

<?php include dirname(__DIR__, 3) . '/includes/footer.php';
