<?php
declare(strict_types=1);

namespace Instinctor\Services;

use Instinctor\Core\Config;
use Instinctor\Core\DB;
use Instinctor\Core\DirectoryTrades;

if (!defined('ABSPATH')) { exit; }

/**
 * DirectoryService — CRUD + visibility gating for the network directory.
 *
 * The gates ARE the design: Google must never see an empty or thin directory
 * page, not even by accident. Concretely:
 *   - the whole directory is dark until instinctor_directory_public = 1
 *     (public gets a hard 404; admins get a noindexed preview);
 *   - a category page (trade + city) exists only while it holds at least
 *     minListings() live rows;
 *   - a trade hub exists only while it has at least one live category;
 *   - the hub lists only trades that pass the gate above.
 * The sitemap will consume these same gates later, so indexing follows the
 * rendered truth automatically.
 *
 * @package Instinctor
 */
final class DirectoryService
{
    public static function table(): string
    {
        return DB::prefix() . 'instinctor_directory_listings';
    }

    /** Master switch: 0 = dark (build/curate phase), 1 = public. */
    public static function publicEnabled(): bool
    {
        return (bool) Config::get('instinctor_directory_public', 0);
    }

    /** A category page must hold at least this many live listings to exist. */
    public static function minListings(): int
    {
        return max(1, (int) Config::get('instinctor_directory_min_listings', 5));
    }

    /** "Philadelphia" + "PA" -> "philadelphia-pa". No city = no slug (a
     *  region alone must not mint a "-ca" category). */
    public static function citySlug(string $city, string $region): string
    {
        $s = self::slugify($city);
        if ($s === '') return '';
        $r = self::slugify($region);
        return $r !== '' ? $s . '-' . $r : $s;
    }

    public static function slugify(string $raw): string
    {
        $s = strtolower(trim($raw));
        if (function_exists('iconv')) {
            $t = iconv('UTF-8', 'ASCII//TRANSLIT//IGNORE', $s);
            if ($t !== false) $s = $t;
        }
        $s = preg_replace('/[^a-z0-9]+/', '-', $s) ?? '';
        return trim($s, '-');
    }

    // ------------------------------------------------------------------ CRUD

    /**
     * Create ($id = 0) or update a listing. Returns the row id, or 0 when
     * validation fails (empty name/slug, or slug taken by another row —
     * slugs are permanent public URLs, so clashes are refused, not suffixed).
     */
    public static function save(array $data, int $id = 0): int
    {
        $trade = (string) ($data['trade'] ?? '');
        if (!DirectoryTrades::has($trade)) $trade = DirectoryTrades::FALLBACK;

        $name = trim((string) ($data['business_name'] ?? ''));
        if ($name === '') return 0;

        $slug = self::slugify((string) (($data['slug'] ?? '') !== '' ? $data['slug'] : $name));
        if ($slug === '') return 0;

        $clash = (int) DB::get_var(DB::prepare(
            'SELECT id FROM ' . self::table() . ' WHERE slug = %s AND id != %d LIMIT 1',
            $slug, $id
        ));
        if ($clash > 0) return 0;

        $city   = trim((string) ($data['city'] ?? ''));
        $region = strtoupper(trim((string) ($data['region'] ?? '')));

        $row = [
            'owner_id'       => (isset($data['owner_id']) && (int) $data['owner_id'] > 0) ? (int) $data['owner_id'] : null,
            'source'         => in_array($data['source'] ?? '', ['tenant', 'external'], true) ? $data['source'] : 'external',
            'slug'           => $slug,
            'business_name'  => $name,
            'trade'          => $trade,
            'city'           => $city,
            'region'         => $region,
            'country'        => (strtoupper(trim((string) ($data['country'] ?? ''))) ?: 'US'),
            'city_slug'      => self::citySlug($city, $region),
            'blurb'          => trim((string) ($data['blurb'] ??  '')),
            'details'        => trim((string) ($data['details'] ?? '')),
            'story'          => trim((string) ($data['story'] ?? '')),
            'image_rel_path' => trim((string) ($data['image_rel_path'] ?? '')),
            'url'            => trim((string) ($data['url'] ?? '')),
            'review_avg'     => (($data['review_avg'] ?? '') !== '' && $data['review_avg'] !== null) ? (float) $data['review_avg'] : null,
            'review_count'   => (($data['review_count'] ?? '') !== '' && $data['review_count'] !== null) ? (int) $data['review_count'] : null,
            'status'         => in_array($data['status'] ?? '', ['draft', 'live', 'hidden'], true) ? $data['status'] : 'draft',
            'featured'       => !empty($data['featured']) ? 1 : 0,
            // Private delivery contacts for "Contact this business" — curated
            // on the platform screen, NEVER rendered on any public page.
            'notify_email'   => trim((string) ($data['notify_email'] ?? '')),
            'notify_phone'   => trim((string) ($data['notify_phone'] ?? '')),
            'updated_at'     => gmdate('Y-m-d H:i:s'),
        ];

        if ($id > 0) {
            DB::update(self::table(), $row, ['id' => $id]);
            return $id;
        }

        // New listings mint their manage token at birth (0051 backfilled the rest).
        $row['manage_token'] = bin2hex(random_bytes(20));
        if (DB::insert(self::table(), $row) === false) return 0;
        // Fetch by unique slug: insert() return semantics differ per driver.
        return (int) DB::get_var(DB::prepare(
            'SELECT id FROM ' . self::table() . ' WHERE slug = %s', $slug
        ));
    }

    public static function delete(int $id): void
    {
        DB::delete(self::table(), ['id' => $id]);
    }

    public static function get(int $id): ?array
    {
        $row = DB::get_row(DB::prepare(
            'SELECT * FROM ' . self::table() . ' WHERE id = %d', $id
        ), 'ARRAY_A');
        return is_array($row) ? $row : null;
    }

    public static function getBySlug(string $slug): ?array
    {
        $row = DB::get_row(DB::prepare(
            'SELECT * FROM ' . self::table() . ' WHERE slug = %s', $slug
        ), 'ARRAY_A');
        return is_array($row) ? $row : null;
    }

    /** Every row, for the admin curation screen. */
    public static function allForAdmin(): array
    {
        return DB::get_results(
            'SELECT * FROM ' . self::table() . ' ORDER BY trade, city_slug, business_name',
            'ARRAY_A'
        );
    }

    // ------------------------------------------------------------- rendering

    /** Every live row (hub + sitemap + future syncs), featured first. */
    public static function allLive(): array
    {
        return DB::get_results(
            'SELECT * FROM ' . self::table() . " WHERE status = 'live' ORDER BY featured DESC, business_name ASC",
            'ARRAY_A'
        );
    }

    /** The hub needs this many live listings before the public sees it. */
    public static function hubMin(): int
    {
        return max(1, (int) Config::get('instinctor_directory_hub_min', 6));
    }

    /**
     * Reframed 2026-07-24 (Sergei): the directory is a flat roster of
     * businesses, Google-style. A business's page is first-class no matter
     * how many neighbors it has, and NOTHING public ever exposes density:
     * no counts, no per-category browse until a category is genuinely full.
     * The hub goes public once the whole directory looks alive.
     */
    public static function hubIsLive(): bool
    {
        if (!self::publicEnabled()) return false;
        $cnt = (int) DB::get_var('SELECT COUNT(*) FROM ' . self::table() . " WHERE status = 'live'");
        return $cnt >= self::hubMin();
    }

    /** Live listings for one category page, featured first, then A-Z. */
    public static function liveListings(string $trade, string $citySlug): array
    {
        return DB::get_results(DB::prepare(
            'SELECT * FROM ' . self::table() .
            " WHERE status = 'live' AND trade = %s AND city_slug = %s
              ORDER BY featured DESC, business_name ASC",
            $trade, $citySlug
        ), 'ARRAY_A');
    }

    /**
     * Categories (trade + city) that meet the listing minimum.
     * $min = null uses the configured public minimum; the admin preview
     * passes 1 so curators can see a category forming before it goes live.
     *
     * @return array<int,array{trade:string,city_slug:string,city:string,region:string,cnt:string}>
     */
    public static function liveCategories(?string $trade = null, ?int $min = null): array
    {
        $min = $min ?? self::minListings();
        $sql = 'SELECT trade, city_slug, city, region, COUNT(*) AS cnt FROM ' . self::table()
             . " WHERE status = 'live'"
             . ($trade !== null ? DB::prepare(' AND trade = %s', $trade) : '')
             . ' GROUP BY trade, city_slug, city, region'
             . DB::prepare(' HAVING COUNT(*) >= %d', $min)
             . ' ORDER BY trade ASC, cnt DESC, city_slug ASC';
        return DB::get_results($sql, 'ARRAY_A');
    }

    /** Trade slugs with at least one qualifying category, in registry order. */
    public static function liveTrades(?int $min = null): array
    {
        $have = [];
        foreach (self::liveCategories(null, $min) as $c) {
            $have[(string) $c['trade']] = true;
        }
        $out = [];
        foreach (array_keys(DirectoryTrades::all()) as $slug) {
            if (isset($have[$slug])) $out[] = $slug;
        }
        return $out;
    }

    public static function categoryIsLive(string $trade, string $citySlug, ?int $min = null): bool
    {
        $min = $min ?? self::minListings();
        $cnt = (int) DB::get_var(DB::prepare(
            'SELECT COUNT(*) FROM ' . self::table() .
            " WHERE status = 'live' AND trade = %s AND city_slug = %s",
            $trade, $citySlug
        ));
        return $cnt >= $min;
    }

    /**
     * Pro flip -> membership sync (LOCKED rule: directory membership is
     * Pro-gated for tenants). First upgrade creates a DRAFT listing seeded
     * from the owner's BusinessProfile (trade + blurb quality get curated
     * on the platform before it flips live; FALLBACK trade is a staging
     * value, never a live one). Re-upgrade restores a hidden listing
     * (downgrade never deletes: the profile URL is permanent). Downgrade
     * hides a live listing. Best-effort by design: any miss is fixable on
     * the curation screen.
     */
    public static function syncTenantListing(int $ownerId, bool $pro): void
    {
        if ($ownerId <= 0) return;
        $row = DB::get_row(DB::prepare(
            'SELECT * FROM ' . self::table() . ' WHERE owner_id = %d ORDER BY id LIMIT 1', $ownerId
        ), 'ARRAY_A');

        if (!$pro) {
            if (is_array($row) && $row['status'] === 'live') {
                DB::update(self::table(), ['status' => 'hidden', 'updated_at' => gmdate('Y-m-d H:i:s')], ['id' => (int) $row['id']]);
            }
            return;
        }

        if (is_array($row)) {
            if ($row['status'] === 'hidden') {
                DB::update(self::table(), ['status' => 'live', 'updated_at' => gmdate('Y-m-d H:i:s')], ['id' => (int) $row['id']]);
            }
            return;
        }

        $bp     = \Instinctor\Services\BusinessProfile::all($ownerId);
        $name   = trim((string) ($bp['business_name'] ?? ''));
        $pagesT = DB::prefix() . 'instinctor_pages';
        $slug   = strtolower(trim((string) DB::get_var(DB::prepare(
            "SELECT slug FROM {$pagesT} WHERE owner_id = %d AND status = 'published' AND slug <> '' ORDER BY id ASC LIMIT 1",
            $ownerId
        ))));
        if ($name === '' || $slug === '') return;   // nothing usable yet; curated by hand

        // BusinessProfile city is owner-typed prose ("Austin, TX"): split best-effort.
        $cityRaw = trim((string) ($bp['city'] ?? ''));
        $city    = $cityRaw;
        $region  = '';
        if (strpos($cityRaw, ',') !== false) {
            [$city, $region] = array_map('trim', explode(',', $cityRaw, 2));
        }

        self::save([
            'owner_id'      => $ownerId,
            'source'        => 'tenant',
            'slug'          => $slug,
            'business_name' => $name,
            'trade'         => DirectoryTrades::FALLBACK,
            'city'          => $city,
            'region'        => $region,
            'blurb'         => trim((string) ($bp['tagline'] ?? '')),
            'url'           => home_url('/' . $slug),
            'status'        => 'draft',
            'notify_email'  => trim((string) ($bp['email'] ?? '')),
            'notify_phone'  => trim((string) (($bp['notify_phone'] ?? '') ?: ($bp['phone'] ?? ''))),
        ]);
    }

    // ------------------------------------------------------- member portal

    /** The listing's private manage-portal URL (possession = identity). */
    public static function manageUrl(array $listing): string
    {
        $tok = (string) ($listing['manage_token'] ?? '');
        if ($tok === '') return '';
        return home_url('/directory/manage/' . $tok);
    }

    public static function getByManageToken(string $token): ?array
    {
        if (!preg_match('/^[a-f0-9]{40}$/', $token)) return null;
        $row = DB::get_row(DB::prepare(
            'SELECT * FROM ' . self::table() . ' WHERE manage_token = %s', $token
        ), 'ARRAY_A');
        return is_array($row) ? $row : null;
    }

    /**
     * The owner's self-serve slice: blurb + where messages are delivered.
     * Everything else (name, trade, city, slug, url, status) stays curated
     * so a member can never break their public URL or the roster's shape.
     * Returns 'ok' or 'invalid'.
     */
    public static function memberUpdate(int $id, array $data): string
    {
        $blurb = trim((string) ($data['blurb'] ?? ''));
        if (mb_strlen($blurb) > 600) $blurb = mb_substr($blurb, 0, 600);

        $email = trim((string) ($data['notify_email'] ?? ''));
        if ($email !== '' && !filter_var($email, FILTER_VALIDATE_EMAIL)) return 'invalid';

        $phone = trim((string) ($data['notify_phone'] ?? ''));
        if (mb_strlen($phone) > 40) return 'invalid';

        DB::update(self::table(), [
            'blurb'        => $blurb,
            'notify_email' => $email,
            'notify_phone' => $phone,
            'updated_at'   => gmdate('Y-m-d H:i:s'),
        ], ['id' => $id]);
        return 'ok';
    }

    /**
     * The manage-portal URL for THIS site's own listing, or '' when the site
     * has none (the dashboard tile simply does not exist then). Two paths:
     * on instinctor.com the tenant's listing row by owner id; on a client
     * standalone a config key set once at provisioning, because the registry
     * lives on instinctor.com and the install cannot look it up. Hidden
     * listings resolve to '': a downgraded membership does not advertise
     * itself on the dashboard.
     */
    public static function memberManageUrl(): string
    {
        if (\Instinctor\Core\Plugin::isInstinctorApp()) {
            $ownerId = (int) (\Instinctor\Core\Tenant::currentOwnerId() ?? 0);
            if ($ownerId <= 0) return '';
            $tok = (string) DB::get_var(DB::prepare(
                'SELECT manage_token FROM ' . self::table() .
                " WHERE owner_id = %d AND status != 'hidden' ORDER BY id LIMIT 1",
                $ownerId
            ));
        } else {
            $tok = (string) Config::get('instinctor_directory_manage_token', '');
        }
        if (!preg_match('/^[a-f0-9]{40}$/', $tok)) return '';
        return 'https://instinctor.com/directory/manage/' . $tok;
    }

    // -------------------------------------------------------------- messages

    public static function messagesTable(): string
    {
        return DB::prefix() . 'instinctor_directory_messages';
    }

    /** Messages accepted from one IP per hour before the brake (cheap spam gate). */
    private const MSG_IP_HOURLY_CAP = 5;

    /**
     * Store and relay a "Contact this business" message. The member's notify
     * contacts stay private: the sender never learns where it went. Returns
     * 'ok', 'throttled', or 'invalid'. The row is stored BEFORE delivery is
     * attempted, so a relay outage never loses a message: the platform can
     * re-send anything whose delivery flags stayed 0.
     */
    public static function submitMessage(array $listing, string $name, string $email, string $business, string $message, string $ip, ?int $senderListingId = null): string
    {
        $name     = trim($name);
        $business = trim($business);
        $email    = trim($email);
        $message  = trim($message);
        if ($name === '' || $message === '' || !filter_var($email, FILTER_VALIDATE_EMAIL)) return 'invalid';
        if (mb_strlen($name) > 120)      $name     = mb_substr($name, 0, 120);
        if (mb_strlen($business) > 191)  $business = mb_substr($business, 0, 191);
        if (mb_strlen($message) > 4000)  $message  = mb_substr($message, 0, 4000);

        $recent = (int) DB::get_var(DB::prepare(
            'SELECT COUNT(*) FROM ' . self::messagesTable() .
            ' WHERE ip = %s AND created_at > UTC_TIMESTAMP() - INTERVAL 1 HOUR', $ip
        ));
        if ($recent >= self::MSG_IP_HOURLY_CAP) return 'throttled';

        if (DB::insert(self::messagesTable(), [
            'listing_id'        => (int) $listing['id'],
            'sender_listing_id' => ($senderListingId !== null && $senderListingId > 0) ? $senderListingId : null,
            'sender_name'       => $name,
            'sender_business'   => $business,
            'sender_email'      => $email,
            'message'           => $message,
            'ip'                => $ip,
        ]) === false) {
            return 'invalid';
        }
        $msgId = (int) DB::insert_id();

        $flags = self::deliverMessage($listing, [
            'sender_name'       => $name,
            'sender_business'   => $business,
            'sender_email'      => $email,
            'message'           => $message,
            'sender_listing_id' => $senderListingId,
        ]);
        if ($flags && $msgId > 0) {
            DB::update(self::messagesTable(), $flags, ['id' => $msgId]);
        }
        return 'ok';
    }

    /**
     * The relay itself (email + SMS to the listing's private notify
     * contacts). Shared by first delivery and platform re-send; returns
     * the delivery flags that succeeded.
     */
    private static function deliverMessage(array $listing, array $m): array
    {
        $flags    = [];
        $name     = (string) ($m['sender_name'] ?? '');
        $business = (string) ($m['sender_business'] ?? '');
        $email    = (string) ($m['sender_email'] ?? '');

        $to = trim((string) ($listing['notify_email'] ?? ''));
        if ($to !== '') {
            $manage = self::manageUrl($listing);
            $body = "New message from your Instinctor directory page.\n\n"
                  . 'From: ' . $name . ($business !== '' && $business !== $name ? ' (' . $business . ')' : '') . "\n"
                  . 'Email: ' . $email . "\n"
                  . (!empty($m['sender_listing_id']) ? "Sent by a fellow Instinctor directory member.\n" : '')
                  . "\n" . (string) ($m['message'] ?? '') . "\n\n"
                  . "Reply directly to this email to answer.\n"
                  . ($manage !== '' ? 'Manage your listing and see all messages: ' . $manage . "\n" : '');
            if (\Instinctor\Core\Mailer::send(
                $to,
                'New message for ' . (string) $listing['business_name'] . ' via Instinctor',
                $body,
                ['reply_to' => $email, 'from_name' => 'Instinctor Directory']
            )) {
                $flags['email_sent'] = 1;
            }
        }

        $phone = trim((string) ($listing['notify_phone'] ?? ''));
        if ($phone !== '') {
            if (\Instinctor\Core\Sms::send(
                $phone,
                'Instinctor: ' . $name . ' sent a message to ' . (string) $listing['business_name']
                . ' from your directory page. Reply by email: ' . $email
            )) {
                $flags['sms_sent'] = 1;
            }
        }
        return $flags;
    }

    /** Platform re-send for a stored message whose delivery failed. */
    public static function resendMessage(int $msgId): bool
    {
        $m = DB::get_row(DB::prepare(
            'SELECT * FROM ' . self::messagesTable() . ' WHERE id = %d', $msgId
        ), 'ARRAY_A');
        if (!is_array($m)) return false;
        $listing = self::get((int) $m['listing_id']);
        if (!is_array($listing)) return false;
        $flags = self::deliverMessage($listing, $m);
        if ($flags) {
            DB::update(self::messagesTable(), $flags, ['id' => $msgId]);
        }
        return (bool) $flags;
    }

    /** New secret for a leaked manage link. Old link (and any member-install
     *  config key holding it) stops working immediately. */
    public static function rotateManageToken(int $id): string
    {
        $tok = bin2hex(random_bytes(20));
        DB::update(self::table(), ['manage_token' => $tok, 'updated_at' => gmdate('Y-m-d H:i:s')], ['id' => $id]);
        return $tok;
    }

    // ------------------------------------------------------------- analytics

    public static function viewsTable(): string
    {
        return DB::prefix() . 'instinctor_directory_views';
    }

    /** One JS-verified profile view (aggregated per day; nothing personal). */
    public static function recordView(int $listingId): void
    {
        if ($listingId <= 0) return;
        DB::query(DB::prepare(
            'INSERT INTO ' . self::viewsTable() . ' (listing_id, day, views) VALUES (%d, CURDATE(), 1)
             ON DUPLICATE KEY UPDATE views = views + 1',
            $listingId
        ));
    }

    /**
     * 30-day views + message counts per listing id, for the platform table
     * and the member portal.
     *
     * @return array<int,array{views:int,messages:int}>
     */
    public static function stats30(): array
    {
        $out = [];
        foreach (DB::get_results(
            'SELECT listing_id, SUM(views) v FROM ' . self::viewsTable() .
            ' WHERE day > CURDATE() - INTERVAL 30 DAY GROUP BY listing_id', 'ARRAY_A'
        ) as $r) {
            $out[(int) $r['listing_id']]['views'] = (int) $r['v'];
        }
        foreach (DB::get_results(
            'SELECT listing_id, COUNT(*) c FROM ' . self::messagesTable() .
            ' WHERE created_at > UTC_TIMESTAMP() - INTERVAL 30 DAY GROUP BY listing_id', 'ARRAY_A'
        ) as $r) {
            $out[(int) $r['listing_id']]['messages'] = (int) $r['c'];
        }
        foreach ($out as $id => $s) {
            $out[$id] += ['views' => 0, 'messages' => 0];
        }
        return $out;
    }

    /** Newest-first messages for the platform screen (all, or one listing's). */
    public static function messagesForAdmin(int $listingId = 0, int $limit = 200): array
    {
        $sql = 'SELECT m.*, l.business_name, l.slug,'
             . ' sl.business_name AS sender_listing_name, sl.slug AS sender_listing_slug'
             . ' FROM ' . self::messagesTable() . ' m'
             . ' LEFT JOIN ' . self::table() . ' l ON l.id = m.listing_id'
             . ' LEFT JOIN ' . self::table() . ' sl ON sl.id = m.sender_listing_id'
             . ($listingId > 0 ? DB::prepare(' WHERE m.listing_id = %d', $listingId) : '')
             . ' ORDER BY m.id DESC'
             . DB::prepare(' LIMIT %d', max(1, $limit));
        return DB::get_results($sql, 'ARRAY_A');
    }
}
