87 lines
3.9 KiB
PHP
87 lines
3.9 KiB
PHP
<?php
|
|
declare(strict_types=1);
|
|
|
|
require_once __DIR__ . '/config.php';
|
|
|
|
function directory_categories(): array
|
|
{
|
|
return ['Restaurants', 'Health & Wellness', 'Home Services', 'Professional Services', 'Retail', 'Arts & Entertainment'];
|
|
}
|
|
|
|
function directory_regions(): array
|
|
{
|
|
return ['CA', 'NY', 'TX', 'FL', 'IL', 'WA'];
|
|
}
|
|
|
|
function ensure_directory_schema(): void
|
|
{
|
|
static $ready = false;
|
|
if ($ready) {
|
|
return;
|
|
}
|
|
|
|
$pdo = db();
|
|
$pdo->exec("CREATE TABLE IF NOT EXISTS directory_listings (
|
|
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
business_name VARCHAR(160) NOT NULL,
|
|
category VARCHAR(80) NOT NULL,
|
|
description TEXT NOT NULL,
|
|
address VARCHAR(180) NOT NULL,
|
|
city VARCHAR(90) NOT NULL,
|
|
region VARCHAR(90) NOT NULL,
|
|
postal_code VARCHAR(24) DEFAULT NULL,
|
|
phone VARCHAR(40) DEFAULT NULL,
|
|
email VARCHAR(160) NOT NULL,
|
|
website VARCHAR(220) DEFAULT NULL,
|
|
hours VARCHAR(220) DEFAULT NULL,
|
|
owner_name VARCHAR(140) NOT NULL,
|
|
status ENUM('pending','approved') NOT NULL DEFAULT 'pending',
|
|
is_claimable TINYINT(1) NOT NULL DEFAULT 1,
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
INDEX idx_directory_category (category),
|
|
INDEX idx_directory_location (city, region),
|
|
INDEX idx_directory_status (status),
|
|
FULLTEXT INDEX ft_directory_search (business_name, category, description, address, city, region)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
|
|
|
|
$count = (int) $pdo->query('SELECT COUNT(*) FROM directory_listings')->fetchColumn();
|
|
if ($count === 0) {
|
|
$seed = [
|
|
['Northside Coffee Bar', 'Restaurants', 'Independent coffee shop with espresso, pastries, remote-work seating, and weekend brunch.', '118 Market Street', 'San Francisco', 'CA', '94103', '(415) 555-0188', 'hello@northside.example', 'https://example.com/northside', 'Mon-Fri 7:00 AM - 5:00 PM; Sat-Sun 8:00 AM - 3:00 PM', 'Sample Owner', 'approved'],
|
|
['Harbor Wellness Studio', 'Health & Wellness', 'Appointment-based wellness studio offering massage therapy, mobility coaching, and recovery plans.', '42 Harbor Avenue', 'Seattle', 'WA', '98101', '(206) 555-0142', 'bookings@harborwellness.example', 'https://example.com/harbor-wellness', 'Tue-Sat 9:00 AM - 6:00 PM', 'Sample Owner', 'approved'],
|
|
['BrightFix Home Pros', 'Home Services', 'Licensed local repair team for small plumbing, electrical, and maintenance jobs.', '880 Oak Lane', 'Austin', 'TX', '78701', '(512) 555-0164', 'service@brightfix.example', 'https://example.com/brightfix', 'Mon-Sat 8:00 AM - 6:00 PM', 'Sample Owner', 'approved'],
|
|
];
|
|
$stmt = $pdo->prepare('INSERT INTO directory_listings (business_name, category, description, address, city, region, postal_code, phone, email, website, hours, owner_name, status) VALUES (:business_name, :category, :description, :address, :city, :region, :postal_code, :phone, :email, :website, :hours, :owner_name, :status)');
|
|
foreach ($seed as $item) {
|
|
$stmt->execute([
|
|
':business_name' => $item[0],
|
|
':category' => $item[1],
|
|
':description' => $item[2],
|
|
':address' => $item[3],
|
|
':city' => $item[4],
|
|
':region' => $item[5],
|
|
':postal_code' => $item[6],
|
|
':phone' => $item[7],
|
|
':email' => $item[8],
|
|
':website' => $item[9],
|
|
':hours' => $item[10],
|
|
':owner_name' => $item[11],
|
|
':status' => $item[12],
|
|
]);
|
|
}
|
|
}
|
|
|
|
$ready = true;
|
|
}
|
|
|
|
function h(?string $value): string
|
|
{
|
|
return htmlspecialchars((string) $value, ENT_QUOTES, 'UTF-8');
|
|
}
|
|
|
|
function listing_status_label(string $status): string
|
|
{
|
|
return $status === 'approved' ? 'Live' : 'Pending review';
|
|
}
|