<?php
declare(strict_types=1);

final class OffersController {

  private function canSeeAll(): bool {
    return Auth::hasRole('Admin') || Auth::hasRole('SuperUser');
  }

  // =========================
  // LIST (with search + status filter)
  // =========================
  public function index(): void {
    Auth::requireLogin();
    $pdo = DB::pdo();
    $uid = Auth::user()['id'];
    $all = $this->canSeeAll();

    $q = trim((string)($_GET['q'] ?? ''));
    $status = trim((string)($_GET['status'] ?? ''));

    $allowedStatuses = ['Pending','Won','Lost'];
    if (!in_array($status, $allowedStatuses, true)) {
      $status = '';
    }

    $hasSearch = ($q !== '');
    $qLike = "%$q%";

    $sql = "
      SELECT
        o.*,
        c.name AS client_name,
        c.client_code,
        b.name AS broker_name,
        v.version_no AS last_version_no,
        CASE
          WHEN SUM(CASE WHEN lv.status='Won' THEN 1 ELSE 0 END) > 0 THEN 'Won'
          WHEN SUM(CASE WHEN lv.status='Pending' THEN 1 ELSE 0 END) > 0 THEN 'Pending'
          WHEN COUNT(lv.id) = 0 THEN '—'
          ELSE 'Lost'
        END AS last_status
      FROM offers o
      JOIN companies c ON c.id = o.company_id
      LEFT JOIN brokers b ON b.id = o.broker_id
      LEFT JOIN (
        SELECT offer_id, MAX(version_no) AS version_no
        FROM offer_versions
        GROUP BY offer_id
      ) v ON v.offer_id = o.id
      LEFT JOIN offer_versions ov
        ON ov.offer_id = o.id AND ov.version_no = v.version_no
      LEFT JOIN offer_line_versions lv
        ON lv.offer_version_id = ov.id
      WHERE 1=1
    ";

    $params = [];

    if (!$all) {
      $sql .= " AND o.created_by_user_id = ? ";
      $params[] = $uid;
    }

    if ($hasSearch) {
      $sql .= " AND (o.offer_ref LIKE ? OR c.name LIKE ? OR c.client_code LIKE ?) ";
      $params[] = $qLike;
      $params[] = $qLike;
      $params[] = $qLike;
    }

    $sql .= "
      GROUP BY o.id, c.name, c.client_code, b.name, v.version_no
      ORDER BY o.id DESC
    ";

    $stmt = $pdo->prepare($sql);
    $stmt->execute($params);
    $offers = $stmt->fetchAll();

    if ($status !== '') {
      $offers = array_values(array_filter($offers, function($o) use ($status) {
        return ($o['last_status'] ?? '') === $status;
      }));
    }

    require __DIR__ . '/../views/layout/header.php';
    require __DIR__ . '/../views/offers/index.php';
    require __DIR__ . '/../views/layout/footer.php';
  }

  // =========================
  // CREATE FORM
  // =========================
  public function create(): void {
    Auth::requireLogin();
    $pdo = DB::pdo();
    $uid = Auth::user()['id'];
    $all = $this->canSeeAll();

    if ($all) {
      $stmt = $pdo->prepare("
        SELECT c.id, c.name, c.client_code, c.broker_id, b.name AS broker_name, b.broker_number
        FROM companies c
        LEFT JOIN brokers b ON b.id = c.broker_id
        ORDER BY c.name ASC
      ");
      $stmt->execute();
    } else {
      $stmt = $pdo->prepare("
        SELECT c.id, c.name, c.client_code, c.broker_id, b.name AS broker_name, b.broker_number
        FROM companies c
        LEFT JOIN brokers b ON b.id = c.broker_id
        WHERE c.owner_user_id = :uid
        ORDER BY c.name ASC
      ");
      $stmt->execute(['uid' => $uid]);
    }

    $clients = $stmt->fetchAll();

    // include code + name
    $lobs = $pdo->query("SELECT id, code, name FROM line_of_business ORDER BY name ASC")->fetchAll();

    // products used for dropdown mapping
    $products = $pdo->query("SELECT id, code, lob_id, description FROM products ORDER BY code ASC")->fetchAll();

    require __DIR__ . '/../views/layout/header.php';
    require __DIR__ . '/../views/offers/form.php';
    require __DIR__ . '/../views/layout/footer.php';
  }

  // =========================
  // STORE
  // =========================
  public function store(): void {
    Auth::requireLogin();
    Csrf::verify();

    $pdo = DB::pdo();
    $uid = Auth::user()['id'];
    $all = $this->canSeeAll();

    $company_id = (int)($_POST['company_id'] ?? 0);
    if ($company_id <= 0) {
      flash('error', 'Please select a client.');
      redirect('/offers/create');
    }

    $lob_ids      = $_POST['lob_id'] ?? [];
    $product_ids  = $_POST['product_id'] ?? [];
    $currencies   = $_POST['currency'] ?? [];
    $sum_insured  = $_POST['sum_insured'] ?? [];
    $premiums     = $_POST['premium'] ?? [];
    $statuses     = $_POST['status'] ?? [];

    // Get broker_id from client
    if ($all) {
      $stmt = $pdo->prepare("SELECT broker_id FROM companies WHERE id=:id LIMIT 1");
      $stmt->execute(['id' => $company_id]);
    } else {
      $stmt = $pdo->prepare("SELECT broker_id FROM companies WHERE id=:id AND owner_user_id=:uid LIMIT 1");
      $stmt->execute(['id' => $company_id, 'uid' => $uid]);
    }

    $clientRow = $stmt->fetch();
    if (!$clientRow) {
      flash('error', 'Client not found or not allowed.');
      redirect('/offers/create');
    }
    $broker_id = $clientRow['broker_id'] ?? null;

    // Validate lines + compute total
    $totalPremium = 0.0;
    $lines = [];

    $count = is_array($lob_ids) ? count($lob_ids) : 0;
    for ($i = 0; $i < $count; $i++) {
      $lobId = (int)($lob_ids[$i] ?? 0);
      if ($lobId <= 0) continue;

      $prodId = (int)($product_ids[$i] ?? 0);
      if ($prodId <= 0) $prodId = null;

      // validate product belongs to same LOB (only if selected)
      if ($prodId !== null) {
        $chkP = $pdo->prepare("SELECT 1 FROM products WHERE id=:pid AND lob_id=:lob LIMIT 1");
        $chkP->execute(['pid' => $prodId, 'lob' => $lobId]);
        if (!$chkP->fetch()) {
          flash('error', 'Selected product does not belong to the chosen line of business.');
          redirect('/offers/create');
        }
      }

      $cur = strtoupper(trim((string)($currencies[$i] ?? 'USD')));
      if ($cur === '') $cur = 'USD';

      $si = (float)($sum_insured[$i] ?? 0);
      $pr = (float)($premiums[$i] ?? 0);

      $st = (string)($statuses[$i] ?? 'Pending');
      if (!in_array($st, ['Pending','Won','Lost'], true)) $st = 'Pending';

      $totalPremium += $pr;
      $lines[] = ['lobId'=>$lobId,'prodId'=>$prodId,'cur'=>$cur,'si'=>$si,'pr'=>$pr,'st'=>$st];
    }

    if (count($lines) === 0) {
      flash('error', 'Please add at least one valid line of business.');
      redirect('/offers/create');
    }

    $pdo->beginTransaction();
    try {
      // offer_ref: HD/YYYY/NUMBER
      $year = (int)date('Y');
      $pdo->prepare("INSERT IGNORE INTO offer_counters (year, last_number) VALUES (:y, 0)")
          ->execute(['y' => $year]);

      $stmtCnt = $pdo->prepare("SELECT last_number FROM offer_counters WHERE year=:y FOR UPDATE");
      $stmtCnt->execute(['y' => $year]);
      $rowCnt = $stmtCnt->fetch();
      if (!$rowCnt) throw new RuntimeException("offer_counters missing row.");

      $nextNumber = ((int)$rowCnt['last_number']) + 1;

      $pdo->prepare("UPDATE offer_counters SET last_number=:n WHERE year=:y")
          ->execute(['n' => $nextNumber, 'y' => $year]);

      $offerRef = "HD/$year/$nextNumber";

      // Insert master offer
      $stmtOffer = $pdo->prepare("
        INSERT INTO offers (offer_ref, company_id, broker_id, total_premium, created_by_user_id)
        VALUES (:offer_ref, :company_id, :broker_id, :total_premium, :uid)
      ");
      $stmtOffer->execute([
        'offer_ref' => $offerRef,
        'company_id' => $company_id,
        'broker_id' => $broker_id,
        'total_premium' => $totalPremium,
        'uid' => $uid,
      ]);

      $offerId = (int)$pdo->lastInsertId();

      // Version 1
      $stmtVer = $pdo->prepare("
        INSERT INTO offer_versions (offer_id, version_no, company_id, broker_id, total_premium, created_by_user_id)
        VALUES (:offer_id, 1, :company_id, :broker_id, :total_premium, :uid)
      ");
      $stmtVer->execute([
        'offer_id' => $offerId,
        'company_id' => $company_id,
        'broker_id' => $broker_id,
        'total_premium' => $totalPremium,
        'uid' => $uid,
      ]);

      $versionId = (int)$pdo->lastInsertId();

      $stmtLine = $pdo->prepare("
        INSERT INTO offer_line_versions (offer_version_id, lob_id, product_id, currency, sum_insured, premium, status)
        VALUES (:vid, :lob_id, :product_id, :currency, :sum_insured, :premium, :status)
      ");
      foreach ($lines as $ln) {
        $stmtLine->execute([
          'vid' => $versionId,
          'lob_id' => $ln['lobId'],
          'product_id' => $ln['prodId'],
          'currency' => $ln['cur'],
          'sum_insured' => $ln['si'],
          'premium' => $ln['pr'],
          'status' => $ln['st'],
        ]);
      }

      $pdo->commit();
    } catch (Throwable $e) {
      $pdo->rollBack();
      flash('error', 'Error saving offer: ' . $e->getMessage());
      redirect('/offers/create');
    }

    flash('success', 'Offer created: ' . $offerRef);
    redirect('/offers');
  }

  // =========================
  // VIEW
  // =========================
  public function view(): void {
    Auth::requireLogin();
    $pdo = DB::pdo();
    $uid = Auth::user()['id'];
    $all = $this->canSeeAll();

    $id = (int)($_GET['id'] ?? 0);
    if ($id <= 0) redirect('/offers');

    if ($all) {
      $stmt = $pdo->prepare("SELECT o.* FROM offers o WHERE o.id=:id LIMIT 1");
      $stmt->execute(['id' => $id]);
    } else {
      $stmt = $pdo->prepare("SELECT o.* FROM offers o WHERE o.id=:id AND o.created_by_user_id=:uid LIMIT 1");
      $stmt->execute(['id' => $id, 'uid' => $uid]);
    }

    $offer = $stmt->fetch();
    if (!$offer) {
      flash('error', 'Offer not found.');
      redirect('/offers');
    }

    $stmtV = $pdo->prepare("
      SELECT v.version_no, v.total_premium, v.created_at,
             c.name AS client_name, c.client_code,
             b.name AS broker_name, b.broker_number
      FROM offer_versions v
      JOIN companies c ON c.id = v.company_id
      LEFT JOIN brokers b ON b.id = v.broker_id
      WHERE v.offer_id = :oid
      ORDER BY v.version_no DESC
    ");
    $stmtV->execute(['oid' => $id]);
    $versions = $stmtV->fetchAll();

    $selectedVersionNo = (int)($_GET['version'] ?? 0);
    if ($selectedVersionNo <= 0 && !empty($versions)) {
      $selectedVersionNo = (int)$versions[0]['version_no'];
    }

    $stmtVH = $pdo->prepare("
      SELECT v.version_no, v.total_premium, v.created_at,
             c.name AS client_name, c.client_code,
             b.name AS broker_name, b.broker_number
      FROM offer_versions v
      JOIN companies c ON c.id = v.company_id
      LEFT JOIN brokers b ON b.id = v.broker_id
      WHERE v.offer_id=:oid AND v.version_no=:vn
      LIMIT 1
    ");
    $stmtVH->execute(['oid' => $id, 'vn' => $selectedVersionNo]);
    $versionHeader = $stmtVH->fetch();

    $lines = [];
    if ($versionHeader) {
      $stmtL = $pdo->prepare("
        SELECT lv.*,
               lob.name AS lob_name,
               p.code AS product_code,
               p.description AS product_desc
        FROM offer_line_versions lv
        JOIN offer_versions v ON v.id = lv.offer_version_id
        JOIN line_of_business lob ON lob.id = lv.lob_id
        LEFT JOIN products p ON p.id = lv.product_id
        WHERE v.offer_id=:oid AND v.version_no=:vn
        ORDER BY lv.id ASC
      ");
      $stmtL->execute(['oid' => $id, 'vn' => $selectedVersionNo]);
      $lines = $stmtL->fetchAll();
    }

    require __DIR__ . '/../views/layout/header.php';
    require __DIR__ . '/../views/offers/view.php';
    require __DIR__ . '/../views/layout/footer.php';
  }

  // =========================
  // EDIT
  // =========================
  public function edit(): void {
    Auth::requireLogin();
    $pdo = DB::pdo();
    $uid = Auth::user()['id'];
    $all = $this->canSeeAll();

    $id = (int)($_GET['id'] ?? 0);
    if ($id <= 0) redirect('/offers');

    if ($all) {
      $stmt = $pdo->prepare("SELECT * FROM offers WHERE id=:id LIMIT 1");
      $stmt->execute(['id' => $id]);
    } else {
      $stmt = $pdo->prepare("SELECT * FROM offers WHERE id=:id AND created_by_user_id=:uid LIMIT 1");
      $stmt->execute(['id' => $id, 'uid' => $uid]);
    }

    $offer = $stmt->fetch();
    if (!$offer) {
      flash('error', 'Offer not found.');
      redirect('/offers');
    }

    // clients list
    if ($all) {
      $stmtC = $pdo->prepare("
        SELECT c.id, c.name, c.client_code, c.broker_id, b.name AS broker_name, b.broker_number
        FROM companies c
        LEFT JOIN brokers b ON b.id = c.broker_id
        ORDER BY c.name ASC
      ");
      $stmtC->execute();
    } else {
      $stmtC = $pdo->prepare("
        SELECT c.id, c.name, c.client_code, c.broker_id, b.name AS broker_name, b.broker_number
        FROM companies c
        LEFT JOIN brokers b ON b.id = c.broker_id
        WHERE c.owner_user_id = :uid
        ORDER BY c.name ASC
      ");
      $stmtC->execute(['uid' => $uid]);
    }
    $clients = $stmtC->fetchAll();

    $lobs = $pdo->query("SELECT id, code, name FROM line_of_business ORDER BY name ASC")->fetchAll();
    $products = $pdo->query("SELECT id, code, lob_id, description FROM products ORDER BY code ASC")->fetchAll();

    // latest version
    $stmtV = $pdo->prepare("SELECT MAX(version_no) AS mx FROM offer_versions WHERE offer_id=:oid");
    $stmtV->execute(['oid' => $id]);
    $mx = (int)($stmtV->fetch()['mx'] ?? 0);

    $offerLines = [];
    $currentClientLabel = '';

    if ($mx > 0) {
      $stmtVH = $pdo->prepare("
        SELECT c.name AS client_name, c.client_code
        FROM offer_versions v
        JOIN companies c ON c.id = v.company_id
        WHERE v.offer_id=:oid AND v.version_no=:vn
        LIMIT 1
      ");
      $stmtVH->execute(['oid' => $id, 'vn' => $mx]);
      $vh = $stmtVH->fetch();
      if ($vh) {
        $currentClientLabel = $vh['client_name'] . ' (' . $vh['client_code'] . ')';
      }

      $stmtL = $pdo->prepare("
        SELECT lv.*
        FROM offer_line_versions lv
        JOIN offer_versions v ON v.id = lv.offer_version_id
        WHERE v.offer_id=:oid AND v.version_no=:vn
        ORDER BY lv.id ASC
      ");
      $stmtL->execute(['oid' => $id, 'vn' => $mx]);
      $offerLines = $stmtL->fetchAll();
    }

    require __DIR__ . '/../views/layout/header.php';
    require __DIR__ . '/../views/offers/edit.php';
    require __DIR__ . '/../views/layout/footer.php';
  }

  // =========================
  // UPDATE
  // =========================
  public function update(): void {
    Auth::requireLogin();
    Csrf::verify();

    $pdo = DB::pdo();
    $uid = Auth::user()['id'];
    $all = $this->canSeeAll();

    $offer_id = (int)($_POST['offer_id'] ?? 0);
    if ($offer_id <= 0) redirect('/offers');

    if ($all) {
      $stmtO = $pdo->prepare("SELECT id FROM offers WHERE id=:id LIMIT 1");
      $stmtO->execute(['id' => $offer_id]);
    } else {
      $stmtO = $pdo->prepare("SELECT id FROM offers WHERE id=:id AND created_by_user_id=:uid LIMIT 1");
      $stmtO->execute(['id' => $offer_id, 'uid' => $uid]);
    }

    if (!$stmtO->fetch()) {
      flash('error', 'Offer not found.');
      redirect('/offers');
    }

    $company_id = (int)($_POST['company_id'] ?? 0);
    if ($company_id <= 0) {
      flash('error', 'Please select a client.');
      redirect('/offers/edit?id=' . $offer_id);
    }

    // broker from client
    if ($all) {
      $stmt = $pdo->prepare("SELECT broker_id FROM companies WHERE id=:id LIMIT 1");
      $stmt->execute(['id' => $company_id]);
    } else {
      $stmt = $pdo->prepare("SELECT broker_id FROM companies WHERE id=:id AND owner_user_id=:uid LIMIT 1");
      $stmt->execute(['id' => $company_id, 'uid' => $uid]);
    }

    $clientRow = $stmt->fetch();
    if (!$clientRow) {
      flash('error', 'Client not found or not allowed.');
      redirect('/offers/edit?id=' . $offer_id);
    }
    $broker_id = $clientRow['broker_id'] ?? null;

    $lob_ids      = $_POST['lob_id'] ?? [];
    $product_ids  = $_POST['product_id'] ?? [];
    $currencies   = $_POST['currency'] ?? [];
    $sum_insured  = $_POST['sum_insured'] ?? [];
    $premiums     = $_POST['premium'] ?? [];
    $statuses     = $_POST['status'] ?? [];

    $totalPremium = 0.0;
    $lines = [];

    $count = is_array($lob_ids) ? count($lob_ids) : 0;
    for ($i = 0; $i < $count; $i++) {
      $lobId = (int)($lob_ids[$i] ?? 0);
      if ($lobId <= 0) continue;

      $prodId = (int)($product_ids[$i] ?? 0);
      if ($prodId <= 0) $prodId = null;

      if ($prodId !== null) {
        $chkP = $pdo->prepare("SELECT 1 FROM products WHERE id=:pid AND lob_id=:lob LIMIT 1");
        $chkP->execute(['pid' => $prodId, 'lob' => $lobId]);
        if (!$chkP->fetch()) {
          flash('error', 'Selected product does not belong to the chosen line of business.');
          redirect('/offers/edit?id=' . $offer_id);
        }
      }

      $cur = strtoupper(trim((string)($currencies[$i] ?? 'USD')));
      if ($cur === '') $cur = 'USD';

      $si = (float)($sum_insured[$i] ?? 0);
      $pr = (float)($premiums[$i] ?? 0);

      $st = (string)($statuses[$i] ?? 'Pending');
      if (!in_array($st, ['Pending','Won','Lost'], true)) $st = 'Pending';

      $totalPremium += $pr;
      $lines[] = ['lobId'=>$lobId,'prodId'=>$prodId,'cur'=>$cur,'si'=>$si,'pr'=>$pr,'st'=>$st];
    }

    if (count($lines) === 0) {
      flash('error', 'Please add at least one valid line of business.');
      redirect('/offers/edit?id=' . $offer_id);
    }

    $pdo->beginTransaction();
    try {
      $stmtV = $pdo->prepare("SELECT COALESCE(MAX(version_no),0) AS mx FROM offer_versions WHERE offer_id=:oid");
      $stmtV->execute(['oid' => $offer_id]);
      $mx = (int)($stmtV->fetch()['mx'] ?? 0);
      $nextVersion = $mx + 1;

      $stmtVer = $pdo->prepare("
        INSERT INTO offer_versions (offer_id, version_no, company_id, broker_id, total_premium, created_by_user_id)
        VALUES (:offer_id, :ver, :company_id, :broker_id, :total_premium, :uid)
      ");
      $stmtVer->execute([
        'offer_id' => $offer_id,
        'ver' => $nextVersion,
        'company_id' => $company_id,
        'broker_id' => $broker_id,
        'total_premium' => $totalPremium,
        'uid' => $uid,
      ]);
      $versionId = (int)$pdo->lastInsertId();

      $stmtLine = $pdo->prepare("
        INSERT INTO offer_line_versions (offer_version_id, lob_id, product_id, currency, sum_insured, premium, status)
        VALUES (:vid, :lob_id, :product_id, :currency, :sum_insured, :premium, :status)
      ");
      foreach ($lines as $ln) {
        $stmtLine->execute([
          'vid' => $versionId,
          'lob_id' => $ln['lobId'],
          'product_id' => $ln['prodId'],
          'currency' => $ln['cur'],
          'sum_insured' => $ln['si'],
          'premium' => $ln['pr'],
          'status' => $ln['st'],
        ]);
      }

      $pdo->prepare("
        UPDATE offers
        SET company_id=:company_id, broker_id=:broker_id, total_premium=:total_premium
        WHERE id=:id
      ")->execute([
        'company_id' => $company_id,
        'broker_id' => $broker_id,
        'total_premium' => $totalPremium,
        'id' => $offer_id,
      ]);

      $pdo->commit();
    } catch (Throwable $e) {
      $pdo->rollBack();
      flash('error', 'Error updating offer: ' . $e->getMessage());
      redirect('/offers/edit?id=' . $offer_id);
    }

    flash('success', 'Offer updated. New version created.');
    redirect('/offers/view?id=' . $offer_id);
  }

  // =========================
  // EXPORT
  // =========================
  public function generate(): void {
  Auth::requireLogin();
  $pdo = DB::pdo();
  $uid = Auth::user()['id'];
  $all = $this->canSeeAll();

  $offer_id = (int)($_GET['id'] ?? 0);
  if ($offer_id <= 0) redirect('/offers');

  // ensure access
  if ($all) {
    $stmt = $pdo->prepare("SELECT * FROM offers WHERE id=:id LIMIT 1");
    $stmt->execute(['id' => $offer_id]);
  } else {
    $stmt = $pdo->prepare("SELECT * FROM offers WHERE id=:id AND created_by_user_id=:uid LIMIT 1");
    $stmt->execute(['id' => $offer_id, 'uid' => $uid]);
  }
  $offer = $stmt->fetch();
  if (!$offer) { flash('error','Offer not found'); redirect('/offers'); }

  // latest version id
  $stmtV = $pdo->prepare("
    SELECT v.id, v.version_no
    FROM offer_versions v
    WHERE v.offer_id=:oid
    ORDER BY v.version_no DESC
    LIMIT 1
  ");
  $stmtV->execute(['oid'=>$offer_id]);
  $ver = $stmtV->fetch();
  if (!$ver) { flash('error','No versions found'); redirect('/offers/view?id='.$offer_id); }

  $offer_version_id = (int)$ver['id'];

  // lines with product selected
  $stmtL = $pdo->prepare("
    SELECT lv.id, lv.lob_id, lv.product_id,
           lob.name AS lob_name,
           p.code AS product_code, p.description AS product_desc
    FROM offer_line_versions lv
    JOIN offer_versions v ON v.id = lv.offer_version_id
    JOIN line_of_business lob ON lob.id = lv.lob_id
    LEFT JOIN products p ON p.id = lv.product_id
    WHERE lv.offer_version_id = :vid
    ORDER BY lv.id ASC
  ");
  $stmtL->execute(['vid'=>$offer_version_id]);
  $lines = $stmtL->fetchAll();

  // must have at least one product selected
  $selectedProducts = [];
  foreach ($lines as $ln) {
    if (!empty($ln['product_id'])) $selectedProducts[] = (int)$ln['product_id'];
  }
  $selectedProducts = array_values(array_unique($selectedProducts));

  if (count($selectedProducts) === 0) {
    flash('error', 'You must select at least one product in the offer lines before generating.');
    redirect('/offers/edit?id=' . $offer_id);
  }

  // Load fields for these products
  $in = implode(',', array_fill(0, count($selectedProducts), '?'));
  $stmtF = $pdo->prepare("
    SELECT * FROM product_form_fields
    WHERE product_id IN ($in)
    ORDER BY product_id ASC, sort_order ASC, id ASC
  ");
  $stmtF->execute($selectedProducts);
  $fields = $stmtF->fetchAll();

  // Existing answers (if already generated before)
  $stmtA = $pdo->prepare("
    SELECT field_id, answer_text
    FROM offer_generated_answers
    WHERE offer_version_id=:vid
  ");
  $stmtA->execute(['vid'=>$offer_version_id]);
  $answersRows = $stmtA->fetchAll();
  $answers = [];
  foreach ($answersRows as $a) $answers[(int)$a['field_id']] = (string)$a['answer_text'];

  require __DIR__ . '/../views/layout/header.php';
  require __DIR__ . '/../views/offers/generate.php';
  require __DIR__ . '/../views/layout/footer.php';
}
// public function saveGenerated(): void {
//   Auth::requireLogin();
//   Csrf::verify();

//   $pdo = DB::pdo();
//   $uid = Auth::user()['id'];
//   $all = $this->canSeeAll();

//   $offer_id = (int)($_POST['offer_id'] ?? 0);
//   $offer_version_id = (int)($_POST['offer_version_id'] ?? 0);
//   if ($offer_id<=0 || $offer_version_id<=0) redirect('/offers');

 
//   if ($all) {
//     $stmt = $pdo->prepare("SELECT 1 FROM offers WHERE id=:id LIMIT 1");
//     $stmt->execute(['id'=>$offer_id]);
//   } else {
//     $stmt = $pdo->prepare("SELECT 1 FROM offers WHERE id=:id AND created_by_user_id=:uid LIMIT 1");
//     $stmt->execute(['id'=>$offer_id, 'uid'=>$uid]);
//   }
//   if (!$stmt->fetch()) {
//     flash('error','Not allowed');
//     redirect('/offers');
//   }

//   $stmtP = $pdo->prepare("
//     SELECT DISTINCT lv.product_id
//     FROM offer_line_versions lv
//     WHERE lv.offer_version_id=:vid AND lv.product_id IS NOT NULL
//   ");
//   $stmtP->execute(['vid'=>$offer_version_id]);
//   $productIds = array_values(array_filter(array_map(fn($r)=> (int)$r['product_id'], $stmtP->fetchAll())));

//   if (count($productIds) === 0) {
//     flash('error', 'You must select at least one product in the offer lines before generating.');
//     redirect('/offers/edit?id='.$offer_id);
//   }

//   $answers = $_POST['answer'] ?? []; 

//   $pdo->beginTransaction();
//   try {
  
//     foreach ($answers as $fieldId => $val) {
//       $fid = (int)$fieldId;
//       $value = is_string($val) ? trim($val) : '';

//       $stmtF = $pdo->prepare("SELECT product_id FROM product_form_fields WHERE id=:id LIMIT 1");
//       $stmtF->execute(['id'=>$fid]);
//       $rowF = $stmtF->fetch();
//       if (!$rowF) continue;

//       $pid = (int)$rowF['product_id'];

//       $stmtUp = $pdo->prepare("
//         INSERT INTO offer_generated_answers (offer_id, offer_version_id, product_id, field_id, answer_text)
//         VALUES (:oid, :vid, :pid, :fid, :ans)
//         ON DUPLICATE KEY UPDATE answer_text=VALUES(answer_text)
//       ");
//       $stmtUp->execute([
//         'oid'=>$offer_id,
//         'vid'=>$offer_version_id,
//         'pid'=>$pid,
//         'fid'=>$fid,
//         'ans'=>$value
//       ]);
//     }


//     foreach ($productIds as $pid) {
//       $result = $this->calculateExcelResultForProduct($pdo, $pid, $offer_version_id);

//       $stmtR = $pdo->prepare("
//         INSERT INTO offer_generated_results (offer_id, offer_version_id, product_id, result_value)
//         VALUES (:oid,:vid,:pid,:res)
//         ON DUPLICATE KEY UPDATE result_value=VALUES(result_value)
//       ");
//       $stmtR->execute([
//         'oid'=>$offer_id,
//         'vid'=>$offer_version_id,
//         'pid'=>$pid,
//         'res'=>$result
//       ]);
//     }

//     $pdo->commit();
//   } catch (Throwable $e) {
//     $pdo->rollBack();
//     flash('error','Failed to save answers: '.$e->getMessage());
//     redirect('/offers/generate?id='.$offer_id);
//   }


//   $token = $this->buildGeneratedZip($pdo, $offer_id, $offer_version_id, $productIds);

//   flash('success','Form saved. Download generated files.');
//   redirect('/offers/generate/download?id='.$offer_id.'&vid='.$offer_version_id.'&t='.$token);
// }
  public function export(): void {
    Auth::requireLogin();
    $pdo = DB::pdo();
    $uid = Auth::user()['id'];
    $all = $this->canSeeAll();

    $mode = (string)($_GET['mode'] ?? 'latest');
    if (!in_array($mode, ['latest','all'], true)) $mode = 'latest';

    header('Content-Type: text/csv; charset=utf-8');
    header('Content-Disposition: attachment; filename="offers_'.$mode.'_'.date('Ymd_His').'.csv"');

    $out = fopen('php://output', 'w');
    fputcsv($out, [
      'Offer Ref','Version','Version Date','Client','Client Code','Broker',
      'LOB','Product Code','Product Description',
      'Currency','Sum Insured','Premium','Status','Total Premium'
    ]);

    if ($mode === 'all') {
      $sql = "
        SELECT o.offer_ref, v.version_no, v.created_at AS version_date,
               c.name AS client_name, c.client_code,
               COALESCE(b.name,'') AS broker_name,
               lob.name AS lob_name,
               p.code AS product_code,
               p.description AS product_desc,
               lv.currency, lv.sum_insured, lv.premium, lv.status,
               v.total_premium
        FROM offers o
        JOIN offer_versions v ON v.offer_id = o.id
        JOIN companies c ON c.id = v.company_id
        LEFT JOIN brokers b ON b.id = v.broker_id
        JOIN offer_line_versions lv ON lv.offer_version_id = v.id
        JOIN line_of_business lob ON lob.id = lv.lob_id
        LEFT JOIN products p ON p.id = lv.product_id
        WHERE 1=1
      ";
      $params = [];
      if (!$all) { $sql .= " AND o.created_by_user_id = :uid "; $params['uid'] = $uid; }
      $sql .= " ORDER BY o.id DESC, v.version_no DESC, lv.id ASC ";
      $stmt = $pdo->prepare($sql);
      $stmt->execute($params);
    } else {
      $sql = "
        SELECT o.offer_ref, v.version_no, v.created_at AS version_date,
               c.name AS client_name, c.client_code,
               COALESCE(b.name,'') AS broker_name,
               lob.name AS lob_name,
               p.code AS product_code,
               p.description AS product_desc,
               lv.currency, lv.sum_insured, lv.premium, lv.status,
               v.total_premium
        FROM offers o
        JOIN (
          SELECT offer_id, MAX(version_no) AS mx
          FROM offer_versions
          GROUP BY offer_id
        ) mv ON mv.offer_id = o.id
        JOIN offer_versions v ON v.offer_id = mv.offer_id AND v.version_no = mv.mx
        JOIN companies c ON c.id = v.company_id
        LEFT JOIN brokers b ON b.id = v.broker_id
        JOIN offer_line_versions lv ON lv.offer_version_id = v.id
        JOIN line_of_business lob ON lob.id = lv.lob_id
        LEFT JOIN products p ON p.id = lv.product_id
        WHERE 1=1
      ";
      $params = [];
      if (!$all) { $sql .= " AND o.created_by_user_id = :uid "; $params['uid'] = $uid; }
      $sql .= " ORDER BY o.id DESC, lv.id ASC ";
      $stmt = $pdo->prepare($sql);
      $stmt->execute($params);
    }

    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
      fputcsv($out, [
        $row['offer_ref'],
        $row['version_no'],
        $row['version_date'],
        $row['client_name'],
        $row['client_code'],
        $row['broker_name'],
        $row['lob_name'],
        $row['product_code'] ?? '',
        $row['product_desc'] ?? '',
        $row['currency'],
        $row['sum_insured'],
        $row['premium'],
        $row['status'],
        $row['total_premium'],
      ]);
    }

    fclose($out);
    exit;
  }


  // ------------------------------
// Excel calculation (PhpSpreadsheet)
// ------------------------------
// private function calculateExcelResultForProduct(PDO $pdo, int $productId, int $offerVersionId): string {
//   // load template + result ref
//   $stmt = $pdo->prepare("SELECT excel_data, excel_result_ref FROM products WHERE id=:id LIMIT 1");
//   $stmt->execute(['id'=>$productId]);
//   $p = $stmt->fetch();
//   if (!$p || empty($p['excel_data'])) return '';

//   $resultRef = trim((string)($p['excel_result_ref'] ?? ''));
//   if ($resultRef === '') $resultRef = 'RESULT'; // default named range

//   // load product fields with excel_ref + answers for this version
//   $stmtF = $pdo->prepare("
//     SELECT f.id, f.excel_ref, a.answer_text
//     FROM product_form_fields f
//     LEFT JOIN offer_generated_answers a
//       ON a.field_id = f.id AND a.offer_version_id = :vid
//     WHERE f.product_id=:pid AND f.excel_ref IS NOT NULL AND f.excel_ref <> ''
//     ORDER BY f.sort_order ASC, f.id ASC
//   ");
//   $stmtF->execute(['pid'=>$productId, 'vid'=>$offerVersionId]);
//   $maps = $stmtF->fetchAll();

//   // save xlsx to temp
//   $tmpDir = sys_get_temp_dir() . '/crm_excel_' . bin2hex(random_bytes(4));
//   @mkdir($tmpDir, 0777, true);
//   $xlsxPath = $tmpDir . '/template.xlsx';
//   file_put_contents($xlsxPath, $p['excel_data']);

//   // PhpSpreadsheet
//   $autoload = __DIR__ . '/../../vendor/autoload.php';
//   if (!file_exists($autoload)) {
//     // no composer installed
//     return '';
//   }
//   require_once $autoload;

//   $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($xlsxPath);
//   $sheet = $spreadsheet->getActiveSheet();

//   foreach ($maps as $m) {
//     $ref = trim((string)$m['excel_ref']);
//     $val = (string)($m['answer_text'] ?? '');

//     if ($ref === '') continue;

//     // Named range (example: AGE)
//     if (strpos($ref, '!') === false && preg_match('/^[A-Za-z_][A-Za-z0-9_]*$/', $ref)) {
//       $named = $spreadsheet->getNamedRange($ref);
//       if ($named) {
//         $ws = $named->getWorksheet();
//         $coords = $named->getRange();
//         // if range is multi, we write first cell only
//         $first = explode(':', $coords)[0];
//         $ws->setCellValue($first, is_numeric($val) ? (float)$val : $val);
//       }
//       continue;
//     }

//     // Sheet1!B2
//     if (strpos($ref, '!') !== false) {
//       [$sheetName, $cell] = explode('!', $ref, 2);
//       $sheetName = trim($sheetName);
//       $cell = trim($cell);
//       $ws = $spreadsheet->getSheetByName($sheetName) ?: $spreadsheet->getActiveSheet();
//       if ($cell !== '') $ws->setCellValue($cell, is_numeric($val) ? (float)$val : $val);
//       continue;
//     }

//     // fallback: plain cell like B2 on active sheet
//     $sheet->setCellValue($ref, is_numeric($val) ? (float)$val : $val);
//   }

//   // Read result
//   $resultValue = '';

//   // Named range result
//   if (strpos($resultRef, '!') === false && preg_match('/^[A-Za-z_][A-Za-z0-9_]*$/', $resultRef)) {
//     $named = $spreadsheet->getNamedRange($resultRef);
//     if ($named) {
//       $ws = $named->getWorksheet();
//       $coords = $named->getRange();
//       $first = explode(':', $coords)[0];
//       $resultValue = (string)$ws->getCell($first)->getCalculatedValue();
//     }
//   } else {
//     // Sheet1!H10 or H10
//     if (strpos($resultRef, '!') !== false) {
//       [$sheetName, $cell] = explode('!', $resultRef, 2);
//       $ws = $spreadsheet->getSheetByName(trim($sheetName)) ?: $spreadsheet->getActiveSheet();
//       $resultValue = (string)$ws->getCell(trim($cell))->getCalculatedValue();
//     } else {
//       $resultValue = (string)$spreadsheet->getActiveSheet()->getCell(trim($resultRef))->getCalculatedValue();
//     }
//   }

//   // cleanup
//   @unlink($xlsxPath);
//   @rmdir($tmpDir);

//   return $resultValue;
// }

// ------------------------------

// Build generated ZIP (PDF if possible, else DOCX)
// Returns token saved in tmp file
// ------------------------------
private function buildGeneratedZip(PDO $pdo, int $offerId, int $offerVersionId, array $productIds): string {
  $token = bin2hex(random_bytes(16));
  $tmpBase = sys_get_temp_dir() . '/crm_gen_' . $token;
  @mkdir($tmpBase, 0777, true);

  $zipPath = $tmpBase . "/generated.zip";
  $zip = new ZipArchive();
  $zip->open($zipPath, ZipArchive::CREATE);

  foreach ($productIds as $pid) {
    // load product templates
    $stmt = $pdo->prepare("SELECT code, template_data, template_name FROM products WHERE id=:id LIMIT 1");
    $stmt->execute(['id'=>$pid]);
    $p = $stmt->fetch();
    if (!$p || empty($p['template_data'])) continue;

    // build replacements from word_key mappings + answers
    $repl = $this->buildWordReplacements($pdo, $pid, $offerVersionId);

    // fill docx
    $docxBinary = $this->fillDocxPlaceholders((string)$p['template_data'], $repl);

    $docxName = ($p['code'] ?? ('product_'.$pid)) . '.docx';
    $docxPath = $tmpBase . '/' . $docxName;
    file_put_contents($docxPath, $docxBinary);

    // convert to pdf if possible
    $pdfPath = $this->convertDocxToPdfIfPossible($docxPath, $tmpBase);

    if ($pdfPath) {
      $zip->addFile($pdfPath, basename($pdfPath));
    } else {
      // fallback docx
      $zip->addFile($docxPath, $docxName);
    }
  }

  $zip->close();

  // store token->path in session so we can download safely
  $_SESSION['gen_zip'][$token] = $zipPath;

  return $token;
}

// ------------------------------
// Download endpoint
// ------------------------------
public function downloadGeneratedZip(): void {
  Auth::requireLogin();

  $token = (string)($_GET['t'] ?? '');
  if ($token === '' || empty($_SESSION['gen_zip'][$token])) {
    http_response_code(404);
    echo "File not found.";
    exit;
  }

  $zipPath = $_SESSION['gen_zip'][$token];
  if (!is_file($zipPath)) {
    http_response_code(404);
    echo "File not found.";
    exit;
  }

  header('Content-Type: application/zip');
  header('Content-Disposition: attachment; filename="generated_offer_files.zip"');
  header('Content-Length: ' . filesize($zipPath));
  readfile($zipPath);
  exit;
}

// ------------------------------
// Build replacements: [[key]] => value
// from product_form_fields.word_key + answers
// ------------------------------
private function buildWordReplacements(PDO $pdo, int $productId, int $offerVersionId): array {
  $stmt = $pdo->prepare("
    SELECT f.word_key, a.answer_text
    FROM product_form_fields f
    LEFT JOIN offer_generated_answers a
      ON a.field_id = f.id AND a.offer_version_id=:vid
    WHERE f.product_id=:pid AND f.word_key IS NOT NULL AND f.word_key <> ''
  ");
  $stmt->execute(['pid'=>$productId, 'vid'=>$offerVersionId]);
  $rows = $stmt->fetchAll();

  $repl = [];
  foreach ($rows as $r) {
    $k = trim((string)$r['word_key']);
    if ($k === '') continue;
    $repl['[['.$k.']]'] = (string)($r['answer_text'] ?? '');
  }
  return $repl;
}

// ------------------------------
// Replace placeholders in DOCX (zip xml replace)
// ------------------------------
private function fillDocxPlaceholders(string $docxBinary, array $replacements): string {
  $tmpDir = sys_get_temp_dir() . '/crm_docx_' . bin2hex(random_bytes(6));
  @mkdir($tmpDir, 0777, true);

  $inPath = $tmpDir . '/in.docx';
  file_put_contents($inPath, $docxBinary);

  $zip = new ZipArchive();
  if ($zip->open($inPath) !== true) {
    return $docxBinary;
  }

  // files where placeholders usually exist
  $targets = [];
  for ($i=0; $i<$zip->numFiles; $i++) {
    $name = $zip->getNameIndex($i);
    if (strpos($name, 'word/') === 0 && substr($name, -4) === '.xml') {
      $targets[] = $name;
    }
  }

  foreach ($targets as $xmlFile) {
    $xml = $zip->getFromName($xmlFile);
    if ($xml === false) continue;

    foreach ($replacements as $key => $val) {
      $xml = str_replace($key, htmlspecialchars((string)$val, ENT_QUOTES | ENT_SUBSTITUTE, 'UTF-8'), $xml);
    }
    $zip->addFromString($xmlFile, $xml);
  }

  $zip->close();

  $out = file_get_contents($inPath);
  return $out !== false ? $out : $docxBinary;
}

// ------------------------------
// Convert DOCX to PDF using LibreOffice if available
// ------------------------------
private function convertDocxToPdfIfPossible(string $docxPath, string $outDir): ?string {
  // try soffice
  $soffice = trim((string)@shell_exec("command -v soffice"));
  if ($soffice === '') return null;

  $cmd = escapeshellcmd($soffice) . " --headless --nologo --nofirststartwizard --convert-to pdf --outdir " .
         escapeshellarg($outDir) . " " . escapeshellarg($docxPath) . " 2>/dev/null";

  @shell_exec($cmd);

  $pdfPath = preg_replace('/\.docx$/i', '.pdf', $docxPath);
  if ($pdfPath && is_file($pdfPath)) return $pdfPath;

  // sometimes libreoffice writes different name; scan directory
  $base = pathinfo($docxPath, PATHINFO_FILENAME);
  $try = $outDir . '/' . $base . '.pdf';
  if (is_file($try)) return $try;

  return null;
  
}


// =============================
// CALCULATE ONLY (AJAX)
// =============================
public function calcGenerate(): void {
  Auth::requireLogin();
  Csrf::verify();

  $pdo = DB::pdo();
  $uid = Auth::user()['id'];
  $all = $this->canSeeAll();

  $offer_id = (int)($_POST['offer_id'] ?? 0);
  $offer_version_id = (int)($_POST['offer_version_id'] ?? 0);
  if ($offer_id<=0 || $offer_version_id<=0) {
    $this->json(['ok'=>false,'error'=>'Invalid request']); return;
  }

  // access
  if ($all) {
    $st = $pdo->prepare("SELECT 1 FROM offers WHERE id=:id LIMIT 1");
    $st->execute(['id'=>$offer_id]);
  } else {
    $st = $pdo->prepare("SELECT 1 FROM offers WHERE id=:id AND created_by_user_id=:uid LIMIT 1");
    $st->execute(['id'=>$offer_id,'uid'=>$uid]);
  }
  if (!$st->fetch()) { $this->json(['ok'=>false,'error'=>'Not allowed']); return; }

  // selected product ids in this version
  $stmtP = $pdo->prepare("
    SELECT DISTINCT lv.product_id
    FROM offer_line_versions lv
    WHERE lv.offer_version_id=:vid AND lv.product_id IS NOT NULL
  ");
  $stmtP->execute(['vid'=>$offer_version_id]);
  $productIds = array_values(array_filter(array_map(fn($r)=> (int)$r['product_id'], $stmtP->fetchAll())));
  $productIds = array_values(array_unique($productIds));

  if (count($productIds) === 0) {
    $this->json(['ok'=>false,'error'=>'No product selected in offer lines.']); return;
  }

  // temp answers from request (not saved yet)
  $answers = $_POST['answer'] ?? [];
  // store in session for submit to use if you want
  $_SESSION['calc_answers'][$offer_version_id] = $answers;

  $results = [];
  foreach ($productIds as $pid) {
    $res = $this->calculateExcelResultFromTempAnswers($pdo, $pid, $offer_version_id, $answers);
    $results[] = ['product_id'=>$pid, 'result'=>$res];
  }

  $this->json(['ok'=>true,'results'=>$results]);
}

private function json(array $data): void {
  header('Content-Type: application/json; charset=utf-8');
  echo json_encode($data);
  exit;
}

// =============================
// SUBMIT (SAVE + GENERATE ZIP)
// =============================
public function saveGenerated(): void {
  Auth::requireLogin();
  Csrf::verify();

  $pdo = DB::pdo();
  $uid = Auth::user()['id'];
  $all = $this->canSeeAll();

  $offer_id = (int)($_POST['offer_id'] ?? 0);
  $offer_version_id = (int)($_POST['offer_version_id'] ?? 0);
  if ($offer_id<=0 || $offer_version_id<=0) redirect('/offers');

  // access
  if ($all) {
    $stmt = $pdo->prepare("SELECT 1 FROM offers WHERE id=:id LIMIT 1");
    $stmt->execute(['id'=>$offer_id]);
  } else {
    $stmt = $pdo->prepare("SELECT 1 FROM offers WHERE id=:id AND created_by_user_id=:uid LIMIT 1");
    $stmt->execute(['id'=>$offer_id, 'uid'=>$uid]);
  }
  if (!$stmt->fetch()) { flash('error','Not allowed'); redirect('/offers'); }

  // selected products in this version
  $stmtP = $pdo->prepare("
    SELECT DISTINCT lv.product_id
    FROM offer_line_versions lv
    WHERE lv.offer_version_id=:vid AND lv.product_id IS NOT NULL
  ");
  $stmtP->execute(['vid'=>$offer_version_id]);
  $productIds = array_values(array_filter(array_map(fn($r)=> (int)$r['product_id'], $stmtP->fetchAll())));
  $productIds = array_values(array_unique($productIds));

  if (count($productIds) === 0) {
    flash('error', 'You must select at least one product in the offer lines before generating.');
    redirect('/offers/edit?id='.$offer_id);
  }

  $answers = $_POST['answer'] ?? [];

  $pdo->beginTransaction();
  try {
    // Save answers
    foreach ($answers as $fieldId => $val) {
      $fid = (int)$fieldId;
      $value = is_string($val) ? trim($val) : '';

      $stmtF = $pdo->prepare("SELECT product_id FROM product_form_fields WHERE id=:id LIMIT 1");
      $stmtF->execute(['id'=>$fid]);
      $rowF = $stmtF->fetch();
      if (!$rowF) continue;

      $pid = (int)$rowF['product_id'];

      $stmtUp = $pdo->prepare("
        INSERT INTO offer_generated_answers (offer_id, offer_version_id, product_id, field_id, answer_text)
        VALUES (:oid, :vid, :pid, :fid, :ans)
        ON DUPLICATE KEY UPDATE answer_text=VALUES(answer_text)
      ");
      $stmtUp->execute([
        'oid'=>$offer_id,'vid'=>$offer_version_id,'pid'=>$pid,'fid'=>$fid,'ans'=>$value
      ]);
    }

    // Calculate and store results
    foreach ($productIds as $pid) {
      $res = $this->calculateExcelResultForProduct($pdo, $pid, $offer_version_id);

      $stmtR = $pdo->prepare("
        INSERT INTO offer_generated_results (offer_id, offer_version_id, product_id, result_value)
        VALUES (:oid,:vid,:pid,:res)
        ON DUPLICATE KEY UPDATE result_value=VALUES(result_value)
      ");
      $stmtR->execute(['oid'=>$offer_id,'vid'=>$offer_version_id,'pid'=>$pid,'res'=>$res]);
    }

    $pdo->commit();
  } catch (Throwable $e) {
    $pdo->rollBack();
    flash('error','Failed to save: '.$e->getMessage());
    redirect('/offers/generate?id='.$offer_id);
  }

  // Build ZIP of generated files and redirect download
  $token = $this->buildGeneratedZip($pdo, $offer_id, $offer_version_id, $productIds);
  redirect('/offers/generate/download?id='.$offer_id.'&vid='.$offer_version_id.'&t='.$token);
}


private function calculateExcelResultFromTempAnswers(PDO $pdo, int $productId, int $offerVersionId, array $tempAnswers): string {
  // same as calculateExcelResultForProduct but uses tempAnswers instead of DB answers
  $stmt = $pdo->prepare("SELECT excel_data, excel_result_ref FROM products WHERE id=:id LIMIT 1");
  $stmt->execute(['id'=>$productId]);
  $p = $stmt->fetch();
  if (!$p || empty($p['excel_data'])) return '';

  $resultRef = trim((string)($p['excel_result_ref'] ?? ''));
  if ($resultRef === '') $resultRef = 'RESULT';

  // Load fields for product with excel_ref
  $stmtF = $pdo->prepare("
    SELECT id, excel_ref
    FROM product_form_fields
    WHERE product_id=:pid AND excel_ref IS NOT NULL AND excel_ref <> ''
    ORDER BY sort_order ASC, id ASC
  ");
  $stmtF->execute(['pid'=>$productId]);
  $maps = $stmtF->fetchAll();

  $autoload = __DIR__ . '/../../vendor/autoload.php';
  if (!file_exists($autoload)) return '';
  require_once $autoload;

  $tmpDir = sys_get_temp_dir() . '/crm_excel_' . bin2hex(random_bytes(4));
  @mkdir($tmpDir, 0777, true);
  $xlsxPath = $tmpDir . '/template.xlsx';
  file_put_contents($xlsxPath, $p['excel_data']);

  $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($xlsxPath);

  // write temp values
  foreach ($maps as $m) {
    $fid = (int)$m['id'];
    $ref = trim((string)$m['excel_ref']);
    if ($ref === '') continue;

    $val = '';
    if (isset($tempAnswers[$fid])) $val = is_string($tempAnswers[$fid]) ? trim($tempAnswers[$fid]) : '';

    // convert Yes/No to 1/0 (recommended for formulas)
    if (strcasecmp($val, 'Yes') === 0) $val = '1';
    if (strcasecmp($val, 'No') === 0) $val = '0';

    $this->writeExcelValue($spreadsheet, $ref, $val);
  }

  $result = $this->readExcelValue($spreadsheet, $resultRef);

  @unlink($xlsxPath);
  @rmdir($tmpDir);

  return $result;
}

private function calculateExcelResultForProduct(PDO $pdo, int $productId, int $offerVersionId): string {
  $stmt = $pdo->prepare("SELECT excel_data, excel_result_ref FROM products WHERE id=:id LIMIT 1");
  $stmt->execute(['id'=>$productId]);
  $p = $stmt->fetch();
  if (!$p || empty($p['excel_data'])) return '';

  $resultRef = trim((string)($p['excel_result_ref'] ?? ''));
  if ($resultRef === '') $resultRef = 'RESULT';

  $stmtF = $pdo->prepare("
    SELECT f.id, f.excel_ref, a.answer_text
    FROM product_form_fields f
    LEFT JOIN offer_generated_answers a
      ON a.field_id = f.id AND a.offer_version_id = :vid
    WHERE f.product_id=:pid AND f.excel_ref IS NOT NULL AND f.excel_ref <> ''
    ORDER BY f.sort_order ASC, f.id ASC
  ");
  $stmtF->execute(['pid'=>$productId, 'vid'=>$offerVersionId]);
  $maps = $stmtF->fetchAll();

  $autoload = __DIR__ . '/../../vendor/autoload.php';
  if (!file_exists($autoload)) return '';
  require_once $autoload;

  $tmpDir = sys_get_temp_dir() . '/crm_excel_' . bin2hex(random_bytes(4));
  @mkdir($tmpDir, 0777, true);
  $xlsxPath = $tmpDir . '/template.xlsx';
  file_put_contents($xlsxPath, $p['excel_data']);

  $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($xlsxPath);

  foreach ($maps as $m) {
    $ref = trim((string)$m['excel_ref']);
    $val = (string)($m['answer_text'] ?? '');

    if (strcasecmp($val, 'Yes') === 0) $val = '1';
    if (strcasecmp($val, 'No') === 0) $val = '0';

    $this->writeExcelValue($spreadsheet, $ref, $val);
  }

  $result = $this->readExcelValue($spreadsheet, $resultRef);

  @unlink($xlsxPath);
  @rmdir($tmpDir);

  return $result;
}

private function writeExcelValue($spreadsheet, string $ref, string $val): void {
  // named range
  if (strpos($ref, '!') === false && preg_match('/^[A-Za-z_][A-Za-z0-9_]*$/', $ref)) {
    $named = $spreadsheet->getNamedRange($ref);
    if ($named) {
      $ws = $named->getWorksheet();
      $coords = explode(':', $named->getRange())[0];
      $ws->setCellValue($coords, is_numeric($val) ? (float)$val : $val);
    }
    return;
  }

  if (strpos($ref, '!') !== false) {
    [$sheetName, $cell] = explode('!', $ref, 2);
    $ws = $spreadsheet->getSheetByName(trim($sheetName)) ?: $spreadsheet->getActiveSheet();
    $ws->setCellValue(trim($cell), is_numeric($val) ? (float)$val : $val);
    return;
  }

  $spreadsheet->getActiveSheet()->setCellValue($ref, is_numeric($val) ? (float)$val : $val);
}

private function readExcelValue($spreadsheet, string $ref): string {
  if (strpos($ref, '!') === false && preg_match('/^[A-Za-z_][A-Za-z0-9_]*$/', $ref)) {
    $named = $spreadsheet->getNamedRange($ref);
    if ($named) {
      $ws = $named->getWorksheet();
      $coords = explode(':', $named->getRange())[0];
      return (string)$ws->getCell($coords)->getCalculatedValue();
    }
    return '';
  }

  if (strpos($ref, '!') !== false) {
    [$sheetName, $cell] = explode('!', $ref, 2);
    $ws = $spreadsheet->getSheetByName(trim($sheetName)) ?: $spreadsheet->getActiveSheet();
    return (string)$ws->getCell(trim($cell))->getCalculatedValue();
  }

  return (string)$spreadsheet->getActiveSheet()->getCell($ref)->getCalculatedValue();
}
}