tas_councils/web/index.php

<?php
// web/index.php
declare(strict_types=1);

// ---- DB config from env ----
$DB_HOST = getenv('MYSQL_HOST') ?: 'db';
$DB_NAME = getenv('MYSQL_DATABASE') ?: 'councils';
$DB_USER = getenv('MYSQL_USER') ?: 'root';
$DB_PASS = getenv('MYSQL_PASSWORD') ?: getenv('MYSQL_ROOT_PASSWORD');
$DB_PORT = (int)(getenv('MYSQL_PORT') ?: 3306);

// ---- Connect ----
$dsn = "mysql:host={$DB_HOST};port={$DB_PORT};dbname={$DB_NAME};charset=utf8mb4";
$pdo = new PDO($dsn, $DB_USER, $DB_PASS, [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
]);

// ---- Inputs ----
$q             = trim((string)($_GET['q'] ?? ''));
$councilKeySel = trim((string)($_GET['council_key'] ?? '')); // table name like da_meandervalley
$appTypeSel    = trim((string)($_GET['app_type'] ?? ''));
$includeClosed = isset($_GET['include_closed']);
$sort          = (string)($_GET['sort'] ?? 'close');         // close|council|address|ref

$knownAppTypes = [
    'Residential', 'Commercial', 'Industrial', 'Subdivision',
    'Rural/Agriculture', 'Tourism/Visitor Accommodation',
    'Outbuilding/Shed', 'Change of Use', 'Demolition', 'Signage', 'Other',
];

// ---- Discover da_* tables ----
$allTables = [];
$st = $pdo->query("SHOW TABLES");
while ($row = $st->fetch(PDO::FETCH_NUM)) {
    $t = $row[0];
    if (strpos($t, 'da_') === 0) $allTables[] = $t;
}

// Exclude tables you don't want to UNION
$exclude = ['geo_cache', 'da_plandata', 'da_plan_data', 'da_dorset_stages']; // add more here if needed
$allTables = array_values(array_filter($allTables, fn($t) => !in_array($t, $exclude, true)));

if (!$allTables) {
    http_response_code(200);
    echo "<h1>No da_* tables found</h1>";
    exit;
}

// Helpers
/**
 * Return "`existing_col` AS `alias`" from the first column that exists,
 * otherwise "'' AS `alias`".
 */
function aliasFirstExisting(PDO $pdo, string $table, array $candidates, string $alias): string {
    foreach ($candidates as $c) {
        if (tableHasColumn($pdo, $table, $c)) {
            return "`$c` AS `$alias`";
        }
    }
    return "'' AS `$alias`";
}

/** Abort if $table is not a safe da_* or da_*_stages identifier. */
function validate_table_name(string $table): void {
    if (!preg_match('/\Ada_[a-z0-9_]+\z/', $table)) {
        http_response_code(500);
        exit("Invalid table name");
    }
}
function h($s) { return htmlspecialchars((string)$s, ENT_QUOTES, 'UTF-8'); }
function council_name_from_key(string $k): string {
    $base = preg_replace('/^da_/', '', $k);
    $base = str_replace('_', ' ', $base);
    $map = [
        'flinders_council' => 'Flinders',
        'southernmidlands' => 'Southern Midlands',
    ];
    if (isset($map[$base])) return $map[$base];
    return ucwords($base);
}
function days_left(?string $ymd): ?int {
    if (!$ymd) return null;
    $d = DateTime::createFromFormat('Y-m-d', $ymd);
    if (!$d) return null;
    $today = new DateTime('today');
    return (int)$today->diff($d)->format('%r%a');
}
function tableHasColumn(PDO $pdo, string $table, string $col): bool {
    validate_table_name($table);
    $q = $pdo->prepare("SHOW COLUMNS FROM `{$table}` LIKE ?");
    $q->execute([$col]);
    return (bool)$q->fetch();
}
function fmt_date(?string $ymd, string $fmt = 'd M y'): string {
    if (!$ymd) return '';
    $dt = DateTime::createFromFormat('Y-m-d', $ymd);
    return $dt ? $dt->format($fmt) : '';
}
function tableExists(PDO $pdo, string $table): bool {
    $st = $pdo->prepare("SHOW TABLES LIKE ?");
    $st->execute([$table]);
    return (bool)$st->fetch(PDO::FETCH_NUM);
}

/** Return the first non-empty from an array of possible keys */
function firstField(array $row, array $candidates, $default = null) {
    foreach ($candidates as $k) {
        if (array_key_exists($k, $row) && $row[$k] !== null && $row[$k] !== '') {
            return $row[$k];
        }
    }
    return $default;
}

// Build council options
$councilOptions = array_map(fn($t) => ['key' => $t, 'label' => council_name_from_key($t)], $allTables);
usort($councilOptions, fn($a, $b) => strcasecmp($a['label'], $b['label']));
$validKeys = array_column($councilOptions, 'key');
if ($councilKeySel !== '' && !in_array($councilKeySel, $validKeys, true)) {
    $councilKeySel = '';
}

// Apply table filter
$tables = $councilKeySel ? [$councilKeySel] : $allTables;

// ---- Prefetch *_stages into [$council_key][$council_reference] ----
$stagesByRef = [];
foreach ($tables as $t) {
    $stageT = $t . '_stages';
    if (!tableExists($pdo, $stageT)) continue;
    validate_table_name($stageT);

    // Pull everything (small tables) – adjust/WHERE if needed
    $st2 = $pdo->query("SELECT * FROM `{$stageT}`");
    while ($r = $st2->fetch()) {
        $ref = $r['council_reference'] ?? null;
        if (!$ref) continue;

        // Normalise column names from varying scrapers
        $milestone = firstField($r, ['milestone']);
        $stageDesc = firstField($r, ['stage_desc','stage','stage_description']);
        $opened    = firstField($r, ['opened','open_date']);
        $target    = firstField($r, ['target_date','target']);
        $completed = firstField($r, ['completed','completed_date']);
        $status    = firstField($r, ['status','stage_status']);

        $stagesByRef[$t][$ref][] = [
            'milestone' => $milestone,
            'stage'     => $stageDesc,
            'opened'    => $opened,
            'target'    => $target,
            'completed' => $completed,
            'status'    => $status,
        ];
    }

    // Optional: sort stages by opened date (NULLs last)
    if (!empty($stagesByRef[$t])) {
        foreach ($stagesByRef[$t] as &$lst) {
            usort($lst, function($a,$b){
                $A = $a['opened'] ?: '9999-12-31';
                $B = $b['opened'] ?: '9999-12-31';
                return strcmp($A,$B);
            });
        }
        unset($lst);
    }
}


// Build UNION with per-table optional columns
$selects = [];
foreach ($tables as $t) {
    // description can be named differently across scrapers
    $descriptionExpr = aliasFirstExisting(
        $pdo,
        $t,
        ['description','proposal','application_details','work_description','details','brief_description','desc'],
        'description'
    );

    $cols = [
        "'{$t}' AS council_key",
        $descriptionExpr,
        "date_received",
        "date_received_raw",
        "on_notice_to",
        "on_notice_to_raw",
        "address",
        "council_reference",
        "property_id",
    ];

    // optional columns
    $cols[] = tableHasColumn($pdo, $t, 'address_std')        ? "address_std"                                   : "'' AS address_std";
    $cols[] = tableHasColumn($pdo, $t, 'applicant')           ? "applicant"                                     : "'' AS applicant";
    $cols[] = tableHasColumn($pdo, $t, 'owner')               ? "owner"                                         : "'' AS owner";
    $cols[] = tableHasColumn($pdo, $t, 'title_reference')     ? "title_reference"                               : "'' AS title_reference";
    $cols[] = tableHasColumn($pdo, $t, 'document_url')        ? "COALESCE(document_url,'') AS document_url"     : "'' AS document_url";
    $cols[] = tableHasColumn($pdo, $t, 'local_document_url')  ? "COALESCE(local_document_url,'') AS local_document_url" : "'' AS local_document_url";
    $cols[] = tableHasColumn($pdo, $t, 'documents_json')      ? "documents_json"                                : "NULL AS documents_json";
    $cols[] = tableHasColumn($pdo, $t, 'application_type')    ? "application_type"                              : "NULL AS application_type";

    validate_table_name($t);
    $selects[] = "SELECT ".implode(", ", $cols)." FROM `{$t}`";
}
$sql = "SELECT * FROM (".implode(" UNION ALL ", $selects).") AS x";


// Where
$where = [];
$params = [];
if (!$includeClosed) {
    // show items that are still open today or later, or unknown close date
    $where[] = "(x.on_notice_to IS NULL OR x.on_notice_to >= CURDATE())";
}
if ($q !== '') {
    $where[] = "(x.address LIKE ? OR x.description LIKE ? OR x.council_reference LIKE ? OR x.address_std LIKE ?)";
    $like = "%{$q}%";
    array_push($params, $like, $like, $like, $like);
}
if ($appTypeSel !== '' && in_array($appTypeSel, $knownAppTypes, true)) {
    $where[] = "x.application_type = ?";
    $params[] = $appTypeSel;
}
if ($where) $sql .= " WHERE ".implode(" AND ", $where);

// Sort
// Sort
$dir = strtolower((string)($_GET['dir'] ?? 'desc')) === 'asc' ? 'ASC' : 'DESC';

switch ($sort) {
    case 'council':
        // within a council, order by date
        $order = "x.council_key ASC, x.date_received $dir, x.address ASC";
        break;
    case 'address':
        $order = "x.address $dir";
        break;
    case 'ref':
        $order = "x.council_reference $dir";
        break;
    case 'close':
    default:
        // primary sort by close date
        $order = "(x.on_notice_to IS NULL) ASC, x.on_notice_to $dir, x.council_key ASC, x.address ASC";
}
$sql .= " ORDER BY {$order}";

// Query
$st = $pdo->prepare($sql);
$st->execute($params);
$rows = $st->fetchAll();

// Build type breakdown from results (no extra query)
$typeCount = [];
foreach ($rows as $r) {
    $t = trim((string)($r['application_type'] ?? ''));
    if ($t !== '') $typeCount[$t] = ($typeCount[$t] ?? 0) + 1;
}
arsort($typeCount);
$classifiedCount = array_sum($typeCount);
?>
<!doctype html>
<html lang="en">
    <head>
        <meta charset="utf-8">
        <meta name="viewport" content="width=device-width, initial-scale=1">
        <title>Advertised DAs</title>
        <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.8/dist/css/bootstrap.min.css" rel="stylesheet">
        <style>
            body { padding: 24px; }
            header form .form-control, header form select { min-width: 220px; }
            .badge-council { background:#6c757d; }
            .badge-open    { background:#198754; }
            .badge-today  { background:#ffc107; }
            .badge-closed  { background:#dc3545; }
            .badge-unknown  { background:#ced4da; }
            .badge-apptype  { background:#0d6efd; }
            .muted { color:#6c757d; }
            .accordion-button .meta { margin-left:auto; display:flex; gap:.5rem; align-items:center; }
            .nowrap { white-space:nowrap; }
        </style>
    </head>
    <body>
        <header class="container mb-3">
            <h1 class="h3 mb-3">Currently Advertised Applications</h1>
            <a class="btn btn-outline-secondary btn-sm" href="councils.php">Council status</a>
            <form method="get" class="row g-2 align-items-center">
                <div class="col-auto">
                    <input type="text" name="q" class="form-control" placeholder="Search address, ref, description" value="<?=h($q)?>">
                </div>
                <div class="col-auto">
                    <select name="council_key" class="form-select">
                        <option value="">All councils</option>
                        <?php foreach ($councilOptions as $opt): ?>
                        <option value="<?= h($opt['key']) ?>" <?= $opt['key']===$councilKeySel ? 'selected' : '' ?>>
                            <?= h($opt['label']) ?>
                        </option>
                        <?php endforeach; ?>
                    </select>
                </div>
                <div class="col-auto">
                    <select name="app_type" class="form-select">
                        <option value="">All types</option>
                        <?php foreach ($knownAppTypes as $at): ?>
                        <option value="<?= h($at) ?>" <?= $at === $appTypeSel ? 'selected' : '' ?>><?= h($at) ?></option>
                        <?php endforeach; ?>
                    </select>
                </div>
                <div class="col-auto form-check">
                    <input class="form-check-input" type="checkbox" name="include_closed" value="1" id="incClosed" <?= $includeClosed ? 'checked' : '' ?>>
                    <label class="form-check-label" for="incClosed">include closed</label>
                </div>
                <div class="col-auto">
                    <select name="sort" class="form-select">
                        <option value="close"   <?= $sort==='close'?'selected':'' ?>>sort by close date</option>
                        <option value="council" <?= $sort==='council'?'selected':'' ?>>sort by council</option>
                        <option value="address" <?= $sort==='address'?'selected':'' ?>>sort by address</option>
                        <option value="ref"     <?= $sort==='ref'?'selected':'' ?>>sort by reference</option>
                    </select>
                </div>
                <div class="col-auto">
                    <select name="dir" class="form-select">
                        <option value="desc" <?= (($_GET['dir'] ?? '')==='desc') ? 'selected' : '' ?>>newest first</option>
                        <option value="asc"  <?= (($_GET['dir'] ?? '')!=='desc') ? 'selected' : '' ?>>oldest first</option>
                    </select>
                </div>
                <div class="col-auto">
                    <button type="submit" class="btn btn-primary">Apply</button>
                </div>
            </form>
            <div class="muted mt-2"><?= count($rows) ?> item(s)<?php if ($classifiedCount > 0): ?>, <?= $classifiedCount ?> classified<?php endif; ?></div>
            <?php if (!empty($typeCount)): ?>
            <div class="mt-2 d-flex flex-wrap gap-1 align-items-center">
                <?php
                // Build URL preserving current filters but swapping app_type
                $baseParams = array_filter([
                    'q'             => $q,
                    'council_key'   => $councilKeySel,
                    'sort'          => $sort !== 'close' ? $sort : null,
                    'dir'           => $dir !== 'DESC' ? strtolower($dir) : null,
                    'include_closed'=> $includeClosed ? '1' : null,
                ]);
                ?>
                <?php if ($appTypeSel !== ''): ?>
                <a class="btn btn-sm btn-outline-secondary" href="?<?= h(http_build_query($baseParams)) ?>">All types</a>
                <?php endif; ?>
                <?php foreach ($typeCount as $type => $n):
                    $linkParams = $baseParams + ['app_type' => $type];
                    $active = ($appTypeSel === $type);
                ?>
                <a class="btn btn-sm <?= $active ? 'btn-primary' : 'btn-outline-primary' ?>"
                   href="?<?= h(http_build_query($linkParams)) ?>">
                   <?= h($type) ?> <span class="badge <?= $active ? 'text-bg-light' : 'text-bg-primary' ?>"><?= $n ?></span>
                </a>
                <?php endforeach; ?>
            </div>
            <?php endif; ?>
        </header>

        <div class="container">
            <div class="accordion" id="advertisedDA">
                <?php foreach ($rows as $i => $r):
                           $uid         = 'acc' . $i;
                           $cname       = council_name_from_key($r['council_key']);
                           $days        = days_left($r['on_notice_to']);
                           $closeLabel  = $r['on_notice_to'] ? h($r['on_notice_to']) : h($r['on_notice_to_raw']);
                           $statusBadge = '';
                           if ($r['on_notice_to']) {
                               if ($days !== null && $days < 0) {
                                   $statusBadge = '<span class="badge badge-closed">closed</span>';
                               } elseif ($days === 0) {
                                   $statusBadge = '<span class="badge badge-today">today</span>';
                               } elseif ($days > 0) {
                                   $statusBadge = '<span class="badge badge-open">due '.$days.'d</span>';
                               }
                           } else {
                               $statusBadge = '<span class="badge badge-unknown">unknown</span>';
                           }

                           $appType = trim((string)($r['application_type'] ?? ''));

                           // prefer local file, fallback to council URL
                           $docLocal = trim((string)($r['local_document_url'] ?? ''));
                           $docWeb   = trim((string)($r['document_url'] ?? ''));
                           $docHref  = $docLocal !== '' ? $docLocal : $docWeb;

                           // multi-document list (e.g. Launceston)
                           $docList = [];
                           $rawJson = $r['documents_json'] ?? '';
                           if ($rawJson !== '' && $rawJson !== null) {
                               $decoded = json_decode($rawJson, true);
                               if (is_array($decoded)) {
                                   foreach ($decoded as $d) {
                                       $href = trim((string)($d['local_url'] ?? ''));
                                       if ($href === '') $href = trim((string)($d['url'] ?? ''));
                                       $name = trim((string)($d['name'] ?? ''));
                                       if ($name === '') $name = 'Document';
                                       if ($href !== '') $docList[] = ['href' => $href, 'name' => $name];
                                   }
                               }
                           }
                ?>
                <div class="accordion-item">
                    <h2 class="accordion-header" id="<?= $uid ?>-head">
                        <button class="accordion-button collapsed py-2" type="button"
                                data-bs-toggle="collapse"
                                data-bs-target="#<?= $uid ?>-body"
                                aria-expanded="false"
                                aria-controls="<?= $uid ?>-body">
                            <div class="col-2 align-self-start"><?= h($r['council_reference'] ?: '[no ref]') ?></div>
                            <div class="col align-self-center"><?= h(($r['address_std'] ?: $r['address']) ?: '[no address]') ?></div>
                            <div class="col align-self-emd meta ">
                                <div class="col"><span class="badge badge-council"><?= h($cname) ?></span></div>
                                <?php if ($closeLabel): ?>
                                <div class="col"><span class="badge text-bg-light border nowrap">Close <?= $closeLabel ?></span></div>
                                <?php endif; ?>
                                <div class="col me-1"><?= $statusBadge ?></div>
                                <?php if ($appType !== ''): ?>
                                <div class="col"><span class="badge badge-apptype"><?= h($appType) ?></span></div>
                                <?php endif; ?>
                            </div>
                        </button>
                    </h2>
                    <div id="<?= $uid ?>-body" class="accordion-collapse collapse" aria-labelledby="<?= $uid ?>-head" data-bs-parent="#advertisedDA">
                        <div class="accordion-body">
                            <div class="row gy-2">
                                <div class="col-md-3">
                                    <label class="form-label">Reference</label>
                                    <input type="text" class="form-control form-control-sm" value="<?= h($r['council_reference']) ?>" disabled readonly>
                                </div>
                                <div class="col-md-3">
                                    <label class="form-label">Title</label>
                                    <input type="text" class="form-control form-control-sm" value="<?= h($r['title_reference'] ?? '') ?>" disabled readonly>
                                </div>
                                <div class="col-md-3">
                                    <label class="form-label">Property ID</label>
                                    <input type="text" class="form-control form-control-sm" value="<?= h($r['property_id'] ?? '') ?>" disabled readonly>
                                </div>
                                <div class="col-md-3">
                                    <label class="form-label">Council</label>
                                    <input type="text" class="form-control form-control-sm" value="<?= h($cname) ?>" disabled readonly>
                                </div>
                                <div class="col-6">
                                    <label class="form-label">Owner</label>
                                    <input type="text" class="form-control form-control-sm" value="<?= h($r['owner'] ?? '') ?>" disabled readonly>
                                </div>
                                <div class="col-6">
                                    <label class="form-label">Applicant</label>
                                    <input type="text" class="form-control form-control-sm" value="<?= h($r['applicant'] ?? '') ?>" disabled readonly>
                                </div>
                                <div class="col-12">
                                    <label class="form-label">Address</label>
                                    <input type="text" class="form-control form-control-sm" value="<?= h(($r['address_std'] ?: $r['address']) ?? '') ?>" disabled readonly>
                                </div>
                                <div class="col-12">
                                    <label class="form-label">Description</label>
                                    <textarea class="form-control form-control-sm" rows="2" disabled readonly><?= h($r['description']) ?></textarea>
                                </div>
                                <div class="col-md-4">
                                    <label class="form-label">Received date</label>
                                    <input type="text" class="form-control form-control-sm" value="<?= h(fmt_date($r['date_received']) ?: ($r['date_received_raw'] ?? '')) ?>" disabled readonly>
                                </div>
                                <div class="col-md-4">
                                    <label class="form-label">Close date</label>
                                    <input type="text" class="form-control form-control-sm" value="<?= h(fmt_date($r['on_notice_to']) ?: ($r['on_notice_to_raw'] ?? '')) ?>" disabled readonly>
                                </div>
                                <div class="col-md-4">
                                    <label class="form-label">Days remaining</label>
                                    <input type="text" class="form-control form-control-sm" value="<?= is_null($days) ? '' : $days ?>" disabled readonly>
                                </div>
                                <?php if ($appType !== ''): ?>
                                <div class="col-md-4">
                                    <label class="form-label">Application type</label>
                                    <input type="text" class="form-control form-control-sm" value="<?= h($appType) ?>" disabled readonly>
                                </div>
                                <?php endif; ?>
                                <div class="col-12">
                                    <?php if (!empty($docList)): ?>
                                    <div class="d-flex flex-wrap gap-2">
                                        <?php foreach ($docList as $doc): ?>
                                        <a class="btn btn-sm btn-outline-secondary" href="<?= h($doc['href']) ?>" target="_blank" rel="noopener"><?= h($doc['name']) ?></a>
                                        <?php endforeach; ?>
                                    </div>
                                    <?php elseif ($docHref !== ''): ?>
                                    <a class="btn btn-outline-secondary" href="<?= h($docHref) ?>" target="_blank" rel="noopener">Open document</a>
                                    <?php else: ?>
                                    <span class="text-muted">No document link</span>
                                    <?php endif; ?>
                                </div>
                            </div>

                            <?php
                            $refKey   = (string)$r['council_reference'];
                            $cKey     = (string)$r['council_key'];
                            $hasStages = !empty($stagesByRef[$cKey][$refKey]);
                            $stageRows = $hasStages ? $stagesByRef[$cKey][$refKey] : [];
                            ?>

                            <?php if ($hasStages): ?>
                            <div class="col-12 mt-3">
                                <h6 class="mb-2">Stages</h6>
                                <div class="table-responsive">
                                    <table class="table table-sm table-bordered align-middle mb-0">
                                        <thead class="table-light">
                                            <tr>
                                                <th style="width:18%">Milestone</th>
                                                <th>Stage</th>
                                                <th style="width:12%">Opened</th>
                                                <th style="width:12%">Target</th>
                                                <th style="width:12%">Completed</th>
                                                <th style="width:14%">Status</th>
                                            </tr>
                                        </thead>
                                        <tbody>
                                            <?php foreach ($stageRows as $sr): ?>
                                            <tr>
                                                <td><?= h($sr['milestone'] ?? '') ?></td>
                                                <td><?= h($sr['stage'] ?? '') ?></td>
                                                <td class="nowrap"><?= h(fmt_date($sr['opened'] ?? null)) ?></td>
                                                <td class="nowrap"><?= h(fmt_date($sr['target'] ?? null)) ?></td>
                                                <td class="nowrap"><?= h(fmt_date($sr['completed'] ?? null)) ?></td>
                                                <td><?= h($sr['status'] ?? '') ?></td>
                                            </tr>
                                            <?php endforeach; ?>
                                        </tbody>
                                    </table>
                                </div>
                            </div>
                            <?php endif; ?>

                        </div>
                    </div>
                </div>
                <?php endforeach; ?>
            </div>
        </div>

        <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.8/dist/js/bootstrap.bundle.min.js"></script>
    </body>
</html>