<?php
declare(strict_types=1);

/**
 * modules/quotes/supplier_quote.php
 *
 * View and edit the Supplier Quotation linked to a specific client quote.
 * - Shows supplier quote details (amount, currency, notes, attachment).
 * - Shows supplier profile snippet if a supplier is selected.
 * - Allows creating/updating the supplier quote and uploading an attachment (PDF/Image).
 *
 * This version:
 *  - Is schema-tolerant for the `suppliers` table (aliases available columns).
 *  - Auto-heals the `supplier_quotes` table schema to add missing columns (attachment_path, notes, currency, timestamps).
 *  - Builds dynamic INSERT/UPDATE queries referencing only existing columns (belt + braces).
 */

require_once dirname(__DIR__, 2) . '/config/functions.php';
require_role(['Sales','SalesManager','Admin','Management','MD']);

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

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

$errors = [];
$notice = null;

/* -------------------------------------------------------
   Helpers
   ------------------------------------------------------- */
function tbl_exists(string $name): bool {
  try { db()->query("SELECT 1 FROM `{$name}` LIMIT 1"); return true; }
  catch (Throwable $e) { return false; }
}
function col_exists(string $table, string $col): bool {
  try {
    $q = db()->prepare("
      SELECT 1
      FROM INFORMATION_SCHEMA.COLUMNS
      WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = :t AND COLUMN_NAME = :c
      LIMIT 1
    ");
    $q->execute([':t'=>$table, ':c'=>$col]);
    return (bool)$q->fetchColumn();
  } catch (Throwable $e) { return false; }
}

/** Create supplier_quotes table if missing (1-to-1 with a quote per company). */
function ensure_supplier_quotes_table(): void {
  if (tbl_exists('supplier_quotes')) return;
  db()->exec("
    CREATE TABLE IF NOT EXISTS supplier_quotes (
      id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
      company_id INT(10) UNSIGNED NOT NULL,
      quote_id BIGINT UNSIGNED NOT NULL,
      supplier_id BIGINT UNSIGNED DEFAULT NULL,
      amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
      currency CHAR(3) NOT NULL DEFAULT 'GBP',
      notes TEXT DEFAULT NULL,
      attachment_path VARCHAR(255) DEFAULT NULL,
      created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
      updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      UNIQUE KEY uq_company_quote (company_id, quote_id),
      KEY idx_company (company_id),
      KEY idx_supplier (supplier_id),
      KEY idx_quote (quote_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  ");
}

/** Ensure required columns exist (auto-heal older tables). */
function ensure_supplier_quotes_schema(): void {
  if (!tbl_exists('supplier_quotes')) return;

  $alters = [];
  if (!col_exists('supplier_quotes','supplier_id')) {
    $alters[] = "ADD COLUMN supplier_id BIGINT UNSIGNED DEFAULT NULL";
  }
  if (!col_exists('supplier_quotes','amount')) {
    $alters[] = "ADD COLUMN amount DECIMAL(12,2) NOT NULL DEFAULT 0.00";
  }
  if (!col_exists('supplier_quotes','currency')) {
    $alters[] = "ADD COLUMN currency CHAR(3) NOT NULL DEFAULT 'GBP'";
  }
  if (!col_exists('supplier_quotes','notes')) {
    $alters[] = "ADD COLUMN notes TEXT DEFAULT NULL";
  }
  if (!col_exists('supplier_quotes','attachment_path')) {
    $alters[] = "ADD COLUMN attachment_path VARCHAR(255) DEFAULT NULL";
  }
  if (!col_exists('supplier_quotes','created_at')) {
    $alters[] = "ADD COLUMN created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP";
  }
  if (!col_exists('supplier_quotes','updated_at')) {
    $alters[] = "ADD COLUMN updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP";
  }

  if ($alters) {
    try {
      db()->exec("ALTER TABLE supplier_quotes ".implode(', ', $alters));
    } catch (Throwable $e) {
      // If ALTER fails due to permissions, we still handle dynamically later.
    }
  }
}

/** Quote header (schema tolerant for quotes). */
function fetch_quote_header(int $cid, int $quoteId): array {
  if (!tbl_exists('quotes')) return [];
  $refCol = col_exists('quotes','quote_ref') ? 'quote_ref'
         : (col_exists('quotes','ref') ? 'ref'
         : (col_exists('quotes','reference') ? 'reference' : null));
  $clientCol = col_exists('quotes','client_name') ? 'client_name'
            : (col_exists('quotes','customer_name') ? 'customer_name'
            : (col_exists('quotes','contact_name')  ? 'contact_name' : null));
  $statusCol = col_exists('quotes','status') ? 'status'
            : (col_exists('quotes','state')  ? 'state'  : null);
  $createdCol = null;
  foreach (['created_at','created_on','created','date_created','quote_date'] as $c) {
    if (col_exists('quotes',$c)) { $createdCol = $c; break; }
  }
  $cols = ["q.id AS id"];
  $cols[] = $refCol ? "q.`$refCol` AS ref" : "CONCAT('Q-', q.id) AS ref";
  $cols[] = $clientCol ? "q.`$clientCol` AS client_name" : "NULL AS client_name";
  $cols[] = $statusCol ? "q.`$statusCol` AS status" : "NULL AS status";
  $cols[] = $createdCol ? "q.`$createdCol` AS created_at" : "NULL AS created_at";

  $sql = "SELECT ".implode(',', $cols)." FROM quotes q WHERE q.company_id=:c AND q.id=:id LIMIT 1";
  $st = db()->prepare($sql);
  $st->execute([':c'=>$cid, ':id'=>$quoteId]);
  return $st->fetch() ?: [];
}

/** Supplier field expressions (schema tolerant). */
function supplier_field_exprs(): array {
  $nameExpr = 'NULL';
  foreach (['name','company_name','supplier_name','display_name'] as $c) {
    if (col_exists('suppliers',$c)) { $nameExpr = "s.`$c`"; break; }
  }
  $emailExpr = 'NULL';
  foreach (['email','work_email','contact_email'] as $c) {
    if (col_exists('suppliers',$c)) { $emailExpr = "s.`$c`"; break; }
  }
  $phoneExpr = 'NULL';
  foreach (['phone','mobile','contact_phone','tel'] as $c) {
    if (col_exists('suppliers',$c)) { $phoneExpr = "s.`$c`"; break; }
  }
  $addressExpr = 'NULL';
  foreach (['address','address1','address_line1','addr'] as $c) {
    if (col_exists('suppliers',$c)) { $addressExpr = "s.`$c`"; break; }
  }
  $notesExpr = 'NULL';
  foreach (['notes','description','remarks'] as $c) {
    if (col_exists('suppliers',$c)) { $notesExpr = "s.`$c`"; break; }
  }
  return [
    'name'    => $nameExpr,
    'email'   => $emailExpr,
    'phone'   => $phoneExpr,
    'address' => $addressExpr,
    'notes'   => $notesExpr,
  ];
}

/** Fetch suppliers for dropdown (schema tolerant). */
function fetch_suppliers(int $cid): array {
  if (!tbl_exists('suppliers')) return [];
  $f = supplier_field_exprs();
  $idCol = col_exists('suppliers','id') ? 's.`id`' : (col_exists('suppliers','supplier_id') ? 's.`supplier_id`' : null);
  if (!$idCol) return [];
  $sql = "
    SELECT
      {$idCol} AS id,
      {$f['name']}    AS name,
      {$f['email']}   AS email,
      {$f['phone']}   AS phone,
      {$f['address']} AS address,
      {$f['notes']}   AS notes
    FROM suppliers s
    WHERE s.company_id = :c
    ORDER BY name IS NULL, name, id
  ";
  $st = db()->prepare($sql);
  $st->execute([':c'=>$cid]);
  return $st->fetchAll();
}

/** Fetch single supplier (normalized keys). */
function fetch_supplier(int $cid, int $sid): ?array {
  if (!tbl_exists('suppliers')) return null;
  $f = supplier_field_exprs();
  $idColName = col_exists('suppliers','id') ? 'id' : (col_exists('suppliers','supplier_id') ? 'supplier_id' : null);
  if (!$idColName) return null;

  $sql = "
    SELECT
      s.`{$idColName}` AS id,
      {$f['name']}    AS name,
      {$f['email']}   AS email,
      {$f['phone']}   AS phone,
      {$f['address']} AS address,
      {$f['notes']}   AS notes
    FROM suppliers s
    WHERE s.company_id=:c AND s.`{$idColName}`=:id
    LIMIT 1
  ";
  $st = db()->prepare($sql);
  $st->execute([':c'=>$cid, ':id'=>$sid]);
  $row = $st->fetch();
  return $row ?: null;
}

/* -------------------------------------------------------
   Ensure table + auto-heal
   ------------------------------------------------------- */
try {
  ensure_supplier_quotes_table();
  ensure_supplier_quotes_schema();
} catch (Throwable $e) {
  $errors[] = 'Unable to ensure supplier quotes schema.';
}

/* -------------------------------------------------------
   Fetch quote header + suppliers list
   ------------------------------------------------------- */
$quote = fetch_quote_header($cid, $quoteId);
if (!$quote) $errors[] = 'Quote not found.';

$suppliers = fetch_suppliers($cid);

/* -------------------------------------------------------
   Handle POST (upsert + upload)
   ------------------------------------------------------- */
if ($_SERVER['REQUEST_METHOD'] === 'POST' && !$errors) {
  try {
    csrf_verify((string)($_POST['csrf'] ?? ''));

    $supplier_id = (int)($_POST['supplier_id'] ?? 0);
    if ($supplier_id <= 0) $supplier_id = null;

    $amount   = (float)($_POST['amount'] ?? 0);
    $currency = trim((string)($_POST['currency'] ?? 'GBP'));
    $notes    = trim((string)($_POST['notes'] ?? ''));

    if ($amount < 0) throw new RuntimeException('Amount cannot be negative.');
    if ($currency === '') $currency = 'GBP';

    // Column availability (dynamic SQL safety)
    $hasAttachment = col_exists('supplier_quotes','attachment_path');
    $hasNotes      = col_exists('supplier_quotes','notes');
    $hasCurrency   = col_exists('supplier_quotes','currency');
    $hasSupplier   = col_exists('supplier_quotes','supplier_id');
    $hasAmount     = col_exists('supplier_quotes','amount');
    $hasCreatedAt  = col_exists('supplier_quotes','created_at');
    $hasUpdatedAt  = col_exists('supplier_quotes','updated_at');

    // Handle upload (optional)
    $relPath = null;
    if (!empty($_FILES['attachment']) && is_array($_FILES['attachment']) && ($_FILES['attachment']['error'] ?? UPLOAD_ERR_NO_FILE) !== UPLOAD_ERR_NO_FILE) {
      $err = (int)($_FILES['attachment']['error'] ?? 0);
      if ($err !== UPLOAD_ERR_OK) throw new RuntimeException('Upload failed (code '.$err.').');
      $tmp  = (string)$_FILES['attachment']['tmp_name'];
      $name = (string)$_FILES['attachment']['name'];
      $ext  = strtolower(pathinfo($name, PATHINFO_EXTENSION));
      $allowed = ['pdf','jpg','jpeg','png','webp'];
      if (!in_array($ext, $allowed, true)) {
        throw new RuntimeException('Only PDF/JPG/PNG/WEBP files are allowed.');
      }
      $safeBase = preg_replace('/[^A-Za-z0-9_\-\.]/', '_', pathinfo($name, PATHINFO_FILENAME));
      $dirRel = 'uploads/supplier_quotes';
      $dirAbs = project_root().'/public/'.$dirRel;
      if (!is_dir($dirAbs)) @mkdir($dirAbs, 0775, true);
      $newName = 'sq_'.$quoteId.'_'.date('Ymd_His').'_'.$safeBase.'.'.$ext;
      $absFile = $dirAbs.'/'.$newName;
      if (!move_uploaded_file($tmp, $absFile)) {
        throw new RuntimeException('Unable to move uploaded file.');
      }
      if ($hasAttachment) {
        $relPath = $dirRel.'/'.$newName;
      } else {
        // Column missing — keep file stored, but we can't persist path unless schema is altered.
        // You can add the column later; the file is preserved on disk.
      }
    }

    // Upsert supplier quote
    $exists = db()->prepare("SELECT id FROM supplier_quotes WHERE company_id=:c AND quote_id=:qid LIMIT 1");
    $exists->execute([':c'=>$cid, ':qid'=>$quoteId]);
    $row = $exists->fetch();

    if ($row) {
      // UPDATE — build SETs dynamically
      $sets = [];
      $args = [':c'=>$cid, ':qid'=>$quoteId];

      if ($hasSupplier) { $sets[] = "supplier_id = :sid"; $args[':sid'] = $supplier_id; }
      if ($hasAmount)   { $sets[] = "amount = :a";        $args[':a']   = $amount; }
      if ($hasCurrency) { $sets[] = "currency = :cur";    $args[':cur'] = $currency; }
      if ($hasNotes)    { $sets[] = "notes = :n";         $args[':n']   = ($notes !== '' ? $notes : null); }
      if ($hasAttachment && $relPath) { $sets[] = "attachment_path = :p"; $args[':p'] = $relPath; }
      if ($hasUpdatedAt) { $sets[] = "updated_at = NOW()"; }

      if (!$sets) throw new RuntimeException('No updatable columns exist on supplier_quotes.');

      $sql = "UPDATE supplier_quotes SET ".implode(', ', $sets)." WHERE company_id=:c AND quote_id=:qid";
      db()->prepare($sql)->execute($args);
    } else {
      // INSERT — build columns/values dynamically
      $cols = ['company_id','quote_id'];
      $vals = [':c',':qid'];
      $args = [':c'=>$cid, ':qid'=>$quoteId];

      if ($hasSupplier) { $cols[]='supplier_id'; $vals[]=' :sid'; $args[':sid']=$supplier_id; }
      if ($hasAmount)   { $cols[]='amount';      $vals[]=' :a';   $args[':a']=$amount; }
      if ($hasCurrency) { $cols[]='currency';    $vals[]=' :cur'; $args[':cur']=$currency; }
      if ($hasNotes)    { $cols[]='notes';       $vals[]=' :n';   $args[':n']=($notes !== '' ? $notes : null); }
      if ($hasAttachment && $relPath) { $cols[]='attachment_path'; $vals[]=' :p'; $args[':p']=$relPath; }
      if ($hasCreatedAt) { $cols[]='created_at'; $vals[]=' NOW()'; }
      if ($hasUpdatedAt) { $cols[]='updated_at'; $vals[]=' NOW()'; }

      $sql = "INSERT INTO supplier_quotes (".implode(',', $cols).") VALUES(".implode(',', $vals).")";
      db()->prepare($sql)->execute($args);
    }

    audit_log('quote.supplier_quote.save', 'quote', $quoteId, [
      'supplier_id' => $supplier_id, 'amount' => $amount, 'currency' => $currency
    ]);

    redirect(url_modules('quotes/supplier_quote.php').'?id='.$quoteId.'&saved=1');
  } catch (Throwable $e) {
    $errors[] = (defined('APP_ENV') && APP_ENV==='dev') ? $e->getMessage() : 'Unable to save supplier quote.';
  }
}

if (isset($_GET['saved'])) $notice = 'Supplier quote saved.';

/* -------------------------------------------------------
   Fetch current supplier quote + supplier profile
   ------------------------------------------------------- */
$sq = null; $supplier = null;
try {
  $st = db()->prepare("SELECT * FROM supplier_quotes WHERE company_id=:c AND quote_id=:qid LIMIT 1");
  $st->execute([':c'=>$cid, ':qid'=>$quoteId]);
  $sq = $st->fetch() ?: null;
  if ($sq && !empty($sq['supplier_id'])) {
    $supplier = fetch_supplier($cid, (int)$sq['supplier_id']);
  }
} catch (Throwable $e) {}

/* -------------------------------------------------------
   Render
   ------------------------------------------------------- */
include dirname(__DIR__, 2) . '/includes/header.php';
?>
<div class="d-flex justify-content-between align-items-center mb-3">
  <div>
    <h1 class="h4 mb-0">Supplier Quote</h1>
    <div class="text-muted">
      For Quote: <strong><?= e($quote['ref'] ?? ('#'.$quoteId)) ?></strong>
      <?php if (!empty($quote['client_name'])): ?> • Client: <?= e($quote['client_name']) ?><?php endif; ?>
      <?php if (!empty($quote['status'])): ?> • Status: <span class="badge text-bg-secondary"><?= e($quote['status']) ?></span><?php endif; ?>
    </div>
  </div>
  <div class="d-flex gap-2">
    <a class="btn btn-outline-secondary" href="<?= e(url_modules('quotes/view.php').'?id='.$quoteId) ?>">Back to Quote</a>
    <a class="btn btn-outline-dark" href="<?= e(url_modules('quotes/list.php')) ?>">Quotes</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; ?>

<div class="row g-3">
  <!-- Left: Supplier profile -->
  <div class="col-12 col-lg-4">
    <div class="card shadow-sm h-100">
      <div class="card-body">
        <h2 class="h6 text-uppercase text-muted mb-3">Supplier Profile</h2>
        <?php if ($supplier): ?>
          <div class="p-2 border rounded bg-light">
            <div class="fw-bold"><?= e($supplier['name'] ?? '—') ?></div>
            <?php if (!empty($supplier['email'])): ?><div class="small text-muted"><?= e($supplier['email']) ?></div><?php endif; ?>
            <?php if (!empty($supplier['phone'])): ?><div class="small text-muted"><?= e($supplier['phone']) ?></div><?php endif; ?>
            <?php if (!empty($supplier['address'])): ?><div class="mt-1"><?= nl2br(e((string)$supplier['address'])) ?></div><?php endif; ?>
          </div>
          <?php if (!empty($supplier['notes'])): ?>
            <div class="mt-2">
              <div class="small text-muted mb-1">Notes</div>
              <div class="border rounded p-2"><?= nl2br(e((string)$supplier['notes'])) ?></div>
            </div>
          <?php endif; ?>
          <?php if (file_exists(dirname(__DIR__).'/suppliers/view.php')): ?>
            <div class="mt-3">
              <a class="btn btn-sm btn-outline-secondary" href="<?= e(url_modules('suppliers/view.php').'?id='.(int)$supplier['id']) ?>" target="_blank" rel="noopener">Open Supplier</a>
            </div>
          <?php endif; ?>
        <?php else: ?>
          <div class="text-muted">No supplier selected for this quote.</div>
          <div class="mt-2 d-flex gap-2">
            <a class="btn btn-sm btn-outline-warning" href="<?= e(url_modules('suppliers/add.php')) ?>" target="_blank" rel="noopener">+ New Supplier</a>
            <a class="btn btn-sm btn-outline-warning" href="<?= e(url_modules('suppliers/list.php')) ?>" target="_blank" rel="noopener">Browse Suppliers</a>
          </div>
        <?php endif; ?>
      </div>
    </div>
  </div>

  <!-- Right: Supplier quote details + form -->
  <div class="col-12 col-lg-8">
    <div class="card shadow-sm mb-3">
      <div class="card-body">
        <h2 class="h6 text-uppercase text-muted mb-3">Supplier Quote Details</h2>

        <?php if ($sq): ?>
          <div class="row g-3 mb-3">
            <div class="col-md-4">
              <div class="p-2 border rounded bg-light">
                <div class="small text-muted">Amount</div>
                <div class="fw-bold"><?= e($sq['currency'] ?? 'GBP') ?> <?= number_format((float)($sq['amount'] ?? 0), 2) ?></div>
              </div>
            </div>
            <div class="col-md-4">
              <div class="p-2 border rounded bg-light">
                <div class="small text-muted">Supplier</div>
                <div class="fw-bold"><?= e($supplier['name'] ?? '—') ?></div>
              </div>
            </div>
            <div class="col-md-4">
              <div class="p-2 border rounded bg-light">
                <div class="small text-muted">Last Updated</div>
                <div class="fw-bold"><?= e(date('Y-m-d H:i', strtotime((string)($sq['updated_at'] ?? 'now')))) ?></div>
              </div>
            </div>
          </div>

          <?php if (!empty($sq['notes'])): ?>
            <div class="mb-3">
              <div class="small text-muted mb-1">Notes</div>
              <div class="border rounded p-2"><?= nl2br(e((string)$sq['notes'])) ?></div>
            </div>
          <?php endif; ?>

          <?php if (!empty($sq['attachment_path'])): ?>
            <div class="mb-3">
              <div class="small text-muted mb-1">Attachment</div>
              <a class="btn btn-sm btn-outline-secondary" href="<?= e(url_public($sq['attachment_path'])) ?>" target="_blank" rel="noopener">Open Attachment</a>
            </div>
          <?php endif; ?>
        <?php else: ?>
          <div class="alert alert-info mb-3">No supplier quote saved yet — use the form below to add one.</div>
        <?php endif; ?>

        <hr>

        <form method="post" enctype="multipart/form-data" autocomplete="off" class="row g-3">
          <input type="hidden" name="csrf" value="<?= e(csrf_token()) ?>">
          <input type="hidden" name="id" value="<?= (int)$quoteId ?>">

          <div class="col-md-6">
            <label class="form-label">Supplier</label>
            <select name="supplier_id" class="form-select">
              <option value="">— No Supplier —</option>
              <?php foreach ($suppliers as $sp): ?>
                <option value="<?= (int)$sp['id'] ?>" <?= ($sq && (int)$sq['supplier_id']===(int)$sp['id'])?'selected':'' ?>>
                  <?= e($sp['name'] ?? ('#'.$sp['id'])) ?>
                </option>
              <?php endforeach; ?>
            </select>
            <div class="form-text">Pick the supplier you requested the quote from.</div>
          </div>

          <div class="col-md-3">
            <label class="form-label">Amount</label>
            <input type="number" step="0.01" min="0" class="form-control" name="amount"
                   value="<?= e($sq ? (string)((float)$sq['amount']) : '') ?>" placeholder="0.00">
          </div>

          <div class="col-md-3">
            <label class="form-label">Currency</label>
            <select name="currency" class="form-select">
              <?php
                $cur = $sq['currency'] ?? 'GBP';
                foreach (['GBP','EUR','USD'] as $c) {
                  $sel = ($cur===$c) ? 'selected' : '';
                  echo '<option '.$sel.'>'.e($c).'</option>';
                }
              ?>
            </select>
          </div>

          <div class="col-12">
            <label class="form-label">Notes</label>
            <textarea name="notes" class="form-control" rows="3" placeholder="Optional internal notes..."><?= e($sq['notes'] ?? '') ?></textarea>
          </div>

          <div class="col-12">
            <label class="form-label">Attachment (PDF / Image)</label>
            <input type="file" class="form-control" name="attachment" accept=".pdf,image/*">
            <div class="form-text">Upload supplier's quote document or screenshot (optional).</div>
          </div>

          <div class="col-12 d-flex gap-2">
            <button class="btn btn-dark">Save Supplier Quote</button>
            <a class="btn btn-outline-secondary" href="<?= e(url_modules('quotes/view.php').'?id='.$quoteId) ?>">Cancel</a>
          </div>
        </form>
      </div>
    </div>
  </div>
</div>

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