<?php
require_once "../../config/settings.inc.php";
define("IEM_APPID", 43);
require_once "../../include/myview.php";
require_once "../../include/database.inc.php";
require_once "../../include/forms.php";
$t = new MyView();
$t->iemselect2 = TRUE;
$t->headextra = <<<EOM
<link rel="stylesheet" href="history.css">
<link rel="stylesheet" href="https://unpkg.com/tabulator-tables@6.3.1/dist/css/tabulator_bootstrap5.min.css">
EOM;
$dbconn = iemdb('postgis');

$month = get_int404("month", intval(date("m")));
$year = get_int404("year", intval(date("Y")));
$segid = get_int404("segid", null);
$valid = isset($_GET["valid"]) ? DateTime::createFromFormat("Y-m-d H:i", xssafe($_GET['valid'])) : null;

function segmentSelect($dbconn, $year, $month, $selected, $name = "segid")
{
    $s = "<select name=\"$name\" id=\"$name\" class=\"form-select iemselect2\">\n";
    $s .= "<option value=\"\">Choose a road segment...</option>\n";
    $stname = iem_pg_prepare(
        $dbconn, <<<EOM
    SELECT segid, major, minor from roads_base
    WHERE archive_begin <= $1 and (archive_end > $1 or archive_end is null)
    ORDER by major ASC
EOM
    );
    $rs = pg_execute($dbconn, $stname, array("{$year}-{$month}-01"));
    while ($row = pg_fetch_assoc($rs)) {
        $s .= "<option value=\"" . $row["segid"] . "\" ";
        if ($row["segid"] == $selected) $s .= "SELECTED";
        $s .= ">{$row['major']} -- {$row['minor']}</option>";
    }
    $s .= "</select>";
    return $s;
} // End of segmentSelect

$t->title = "Historical Iowa Winter Road Conditions";
$t->jsextra = <<<EOM
<script src="history.module.js" type="module"></script>
EOM;

$segselect = segmentSelect($dbconn, $year, $month, strval($segid));

// Create year and month selects with Bootstrap 5 classes
$yearOptions = array();
for ($i = 2006; $i <= intval(date("Y")); $i++) {
    $yearOptions[$i] = (string)$i;
}
$ys = make_select("year", $year, $yearOptions, "", "form-select");

$monthOptions = array();
for ($i = 1; $i <= 12; $i++) {
    $ts = new DateTime("2000-$i-01");
    $monthOptions[$i] = $ts->format("M");
}
$ms = make_select("month", $month, $monthOptions, "", "form-select");

$content = <<<EOM
<nav aria-label="breadcrumb">
  <ol class="breadcrumb">
    <li class="breadcrumb-item">
      <a href="/roads/"><i class="bi bi-arrow-left-circle me-1"></i>Roads Mainpage</a>
    </li>
    <li class="breadcrumb-item active" aria-current="page">Historical Listing</li>
  </ol>
</nav>

<div class="d-flex align-items-center mb-4">
  <i class="bi bi-clock-history me-2 text-primary fs-4"></i>
  <h3 class="mb-0">Historical Road Conditions</h3>
</div>

<div class="card mb-4">
  <div class="card-header bg-light">
    <h5 class="card-title mb-0">
      <i class="bi bi-funnel me-2"></i>Filter Options
    </h5>
  </div>
  <div class="card-body">
    <form method="GET" action="history.phtml">
      <div class="row g-3 align-items-end">
        <div class="col-md-4">
          <label for="segid" class="form-label">
            <i class="bi bi-signpost-2 me-1"></i>Road Segment
          </label>
          {$segselect}
        </div>
        <div class="col-md-3">
          <label for="year" class="form-label">
            <i class="bi bi-calendar me-1"></i>Year
          </label>
          {$ys}
        </div>
        <div class="col-md-3">
          <label for="month" class="form-label">
            <i class="bi bi-calendar-month me-1"></i>Month
          </label>
          {$ms}
        </div>
        <div class="col-md-2">
          <button type="submit" class="btn btn-primary w-100">
            <i class="bi bi-search me-1"></i>View History
          </button>
        </div>
      </div>
    </form>
  </div>
</div>
EOM;


if (isset($_GET["month"]) && isset($_GET["segid"])) {
    $content .= <<<EOM
<div class="alert alert-info d-flex align-items-start" role="alert">
  <i class="bi bi-info-circle-fill me-2 mt-1 flex-shrink-0"></i>
  <div>
    <strong>Historical Data:</strong> Here is a listing of historical road conditions for the selected segment. 
    Updates during active weather can be as frequent as every five minutes, but this display only shows 
    when the segment changed condition. Click on the time of any report to get a listing of all road 
    conditions for that time.
  </div>
</div>
EOM;
    
    $content .= <<<EOM
<div class="card">
  <div class="card-header bg-primary text-white">
    <h5 class="card-title mb-0">
      <i class="bi bi-table me-2"></i>Road Condition History
    </h5>
  </div>
  <div class="card-body p-0">
    <table id='table1' class="table table-striped table-hover mb-0">
      <thead class="table-dark">
        <tr>
          <th><i class="bi bi-calendar-date me-1"></i>Date</th>
          <th><i class="bi bi-clock me-1"></i>Report Time</th>
          <th><i class="bi bi-signpost-2 me-1"></i>Road Segment</th>
          <th><i class="bi bi-exclamation-triangle me-1"></i>Condition</th>
        </tr>
      </thead>
      <tbody>
EOM;
    $sts = new DateTime("{$year}-{$month}-01");
    $ets = new DateTime("{$year}-{$month}-01");
    $ets->modify("+1 month");
    $stname = iem_pg_prepare($dbconn, <<<EOM
    SELECT c.label, l.valid,
    b.major, b.minor from roads_base b,
    roads_log l, roads_conditions c WHERE l.segid = $1 and
    l.valid >= $2 and l.valid < $3 and l.cond_code = c.code
    and b.segid = l.segid ORDER by valid ASC
EOM);
    $rs = pg_execute($dbconn, $stname, array($segid, $sts->format("Y-m-d"), $ets->format("Y-m-d")));

    $lastcond = "";
    while ($row = pg_fetch_assoc($rs)) {
        if ($lastcond == $row["label"]) continue;
        $lastcond = $row["label"];
        $ts = strtotime(substr($row["valid"], 0, 16));
        $content .= sprintf(
            "<tr><td>%s</td>
            <td><a href=\"history.phtml?valid=%s\">%s</a></td>
            <td>%s -- %s</td><td>%s</td></tr>",
            date('d M Y', $ts),
            date('Y-m-d H:i', $ts),
            date('h:i A', $ts),
            $row["major"],
            $row["minor"],
            $row["label"]
        );
    }
    $content .= <<<EOM
      </tbody>
    </table>
  </div>
</div>
EOM;
} else if (!is_null($valid)) {
    $year = intval($valid->format("Y"));
    $content .= <<<EOM
<div class="alert alert-info d-flex align-items-start" role="alert">
  <i class="bi bi-info-circle-fill me-2 mt-1 flex-shrink-0"></i>
  <div>
    <strong>Snapshot View:</strong> Here is a listing of historical road conditions for a particular
    time for all roads monitored in Iowa. Click on the segment name to get 
    a listing of historical conditions for that segment.
  </div>
</div>
EOM;
    
    $gisurl = "{$EXTERNAL_BASEURL}/archive/data/" . $valid->format('Y/m/d/') . "/GIS/";
    $content .= <<<EOM
<div class="alert alert-secondary d-flex align-items-center" role="alert">
  <i class="bi bi-download me-2"></i>
  <span>Directory of <a href="$gisurl" class="alert-link">GIS shapefiles</a> for this UTC date.</span>
</div>
EOM;
    
    $content .= <<<EOM
<div class="card">
  <div class="card-header bg-primary text-white">
    <h5 class="card-title mb-0">
      <i class="bi bi-table me-2"></i>Road Conditions Snapshot
    </h5>
  </div>
  <div class="card-body p-0">
    <table id='table1' class="table table-striped table-hover mb-0">
      <thead class="table-dark">
        <tr>
          <th><i class="bi bi-calendar-date me-1"></i>Date</th>
          <th><i class="bi bi-clock me-1"></i>Report Time</th>
          <th><i class="bi bi-signpost-2 me-1"></i>Road Segment</th>
          <th><i class="bi bi-exclamation-triangle me-1"></i>Condition</th>
        </tr>
      </thead>
      <tbody>
EOM;
    $stname = iem_pg_prepare(
        $dbconn,
        "SELECT c.label, l.valid, b.major, b.minor, " .
            "b.segid from roads_base b, roads_log l, roads_conditions c " .
            "WHERE l.valid > $1 and l.valid < $2 and l.cond_code = c.code " .
            "and b.segid = l.segid ORDER by major ASC"
    );
    $sts = DateTime::createFromFormat("Y-m-d H:i", $_GET["valid"]);
    $sts->sub(new DateInterval('P1D'));
    $ets = DateTime::createFromFormat("Y-m-d H:i", $_GET["valid"]);
    $ets->add(new DateInterval('P1D'));
    $rs = pg_execute(
        $dbconn,
        $stname,
        array($sts->format("Y-m-d H:i"), $ets->format("Y-m-d H:i"))
    );

    while ($row = pg_fetch_assoc($rs)) {
        $ts = strtotime(substr($row["valid"], 0, 16));
        $content .= sprintf(
            "<tr><td>%s</td><td>%s</td>
            <td><a href=\"history.phtml?segid=%s&month=%s\">%s -- %s</a></td>
            <td>%s</td></tr>",
            date('d M Y', $ts),
            date('h:i A', $ts),
            $row["segid"],
            date('m', $ts),
            $row["major"],
            $row["minor"],
            $row["label"]
        );
    }
    $content .= <<<EOM
      </tbody>
    </table>
  </div>
</div>
EOM;
} else {
    $content .= <<<EOM
<div class="card">
  <div class="card-header bg-light">
    <h5 class="card-title mb-0">
      <i class="bi bi-info-circle me-2"></i>About This Archive
    </h5>
  </div>
  <div class="card-body">
    <p class="mb-3">
      <i class="bi bi-archive me-2 text-primary"></i>
      The Iowa Environmental Mesonet archives the road condition reports as 
      generated by the Iowa State Patrol. From this page, you can generate a 
      listing of road conditions per road segment for a particular month or a listing
      of all road conditions for a particular time.
    </p>
    
    <div class="alert alert-success d-flex align-items-center" role="alert">
      <i class="bi bi-check-circle-fill me-2"></i>
      <span><strong>Archive Coverage:</strong> Our archive is basically complete back to 14 Feb 2006.</span>
    </div>
    
    <h6 class="mt-4 mb-3">
      <i class="bi bi-map me-2"></i>GIS Data Archive
    </h6>
    <p class="mb-2">Archive of GIS shapefiles per report available at:</p>
    <div class="bg-light p-3 rounded">
      <code>
        <a href="/archive/data/2005/01/30/GIS/" class="text-decoration-none">
          <i class="bi bi-link-45deg me-1"></i>{$EXTERNAL_BASEURL}/archive/data/2005/01/30/GIS/
        </a>
      </code>
    </div>
    <small class="text-muted">
      <i class="bi bi-info-circle me-1"></i>
      Look for the iaroads_* files. Timestamps are in UTC.
    </small>
  </div>
</div>
EOM;
}
$t->content = $content;
$t->render('single.phtml');
