<?php
// ============================================================
//  MediCore — Smart Top Status Bar: shared schema + notification
//  helpers.
//  File: includes/notify.php
//  Require this from includes/header.php and from any API file
//  that needs to raise a notification (purchases, low stock, etc).
// ============================================================

// ── Auto-migration: create all topbar-related tables ───────────
// Follows the same defensive information_schema pattern already
// used by ensure_soft_delete() in includes/db.php — safe to call
// on every request, cached per-request via a static flag so the
// actual CREATE TABLE IF NOT EXISTS statements only really cost
// anything the very first time they run against a fresh database.
function ensure_topbar_tables(): void {
    static $done = false;
    if ($done) return;
    $done = true;

    // Reached from both header.php (every page load, app-wide) and
    // api/status.php (every health-check ping) — the two most-hit
    // entry points in the app — so this is where the purchase_items.exp
    // index gets created on an existing/older database.
    ensure_perf_indexes();

    try {
        // ── Branches: minimal — this system is currently single-
        // location, but push-message "branch-wise targeting" needs
        // something to target. One default branch is seeded so
        // everything works today and is ready to expand later.
        db_run("CREATE TABLE IF NOT EXISTS branches (
            id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            name       VARCHAR(100) NOT NULL,
            is_active  TINYINT(1)   DEFAULT 1,
            created_at TIMESTAMP    DEFAULT CURRENT_TIMESTAMP
        ) ENGINE=InnoDB");
        db_run("INSERT IGNORE INTO branches (id, name) VALUES (1, 'Main Branch')");

        $hasBranchCol = db_val(
            "SELECT COUNT(*) FROM information_schema.COLUMNS
              WHERE TABLE_SCHEMA = DATABASE()
                AND TABLE_NAME   = 'users'
                AND COLUMN_NAME  = 'branch_id'"
        );
        if (!$hasBranchCol) {
            db_run("ALTER TABLE users ADD COLUMN branch_id INT UNSIGNED DEFAULT 1");
        }

        // ── Notifications (one row per targeted user — keeps
        // is_read a simple column instead of needing a join table)
        db_run("CREATE TABLE IF NOT EXISTS notifications (
            id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            user_id    INT UNSIGNED NOT NULL,
            type       VARCHAR(40)  NOT NULL,
            icon       VARCHAR(10)  DEFAULT '🔔',
            title      VARCHAR(200) NOT NULL,
            message    VARCHAR(500) DEFAULT '',
            link       VARCHAR(255) DEFAULT '',
            is_read    TINYINT(1)   DEFAULT 0,
            read_at    DATETIME     DEFAULT NULL,
            created_at TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
            INDEX idx_notif_user_read (user_id, is_read),
            INDEX idx_notif_type_created (type, created_at)
        ) ENGINE=InnoDB");

        // ── Push messages (admin/manager broadcasts) ────────────
        db_run("CREATE TABLE IF NOT EXISTS push_messages (
            id               INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            title            VARCHAR(200) NOT NULL,
            message          TEXT,
            sender_id        INT UNSIGNED DEFAULT NULL,
            target_type      ENUM('all','branch','user') DEFAULT 'all',
            target_branch_id INT UNSIGNED DEFAULT NULL,
            target_user_id   INT UNSIGNED DEFAULT NULL,
            created_at       TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        ) ENGINE=InnoDB");

        db_run("CREATE TABLE IF NOT EXISTS push_message_recipients (
            id               INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            push_message_id  INT UNSIGNED NOT NULL,
            user_id          INT UNSIGNED NOT NULL,
            is_read          TINYINT(1) DEFAULT 0,
            read_at          DATETIME   DEFAULT NULL,
            UNIQUE KEY uniq_msg_user (push_message_id, user_id),
            INDEX idx_pmr_user_read (user_id, is_read)
        ) ENGINE=InnoDB");

        // ── Tasks ────────────────────────────────────────────────
        db_run("CREATE TABLE IF NOT EXISTS tasks (
            id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            title        VARCHAR(200) NOT NULL,
            description  VARCHAR(500) DEFAULT '',
            assigned_to  INT UNSIGNED DEFAULT NULL,
            priority     ENUM('high','medium','low') DEFAULT 'medium',
            due_date     DATE DEFAULT NULL,
            status       ENUM('pending','in_progress','completed') DEFAULT 'pending',
            created_by   INT UNSIGNED DEFAULT NULL,
            created_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            completed_at DATETIME  DEFAULT NULL,
            INDEX idx_task_assigned (assigned_to, status)
        ) ENGINE=InnoDB");

        // ── System events (server-down / recovered / sync errors) ─
        db_run("CREATE TABLE IF NOT EXISTS system_events (
            id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            event_type VARCHAR(40)  NOT NULL,
            message    VARCHAR(500) DEFAULT '',
            created_at TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
            INDEX idx_evt_type_created (event_type, created_at)
        ) ENGINE=InnoDB");

        // ── One-time backfill: fix notifications saved BEFORE
        // create_notification() started wrapping links with base_url().
        // Those rows still hold bare relative paths like
        // 'customer/list.php', which 404 when clicked from any page
        // that isn't at the site root (e.g. clicking from
        // recycle/index.php resolved to .../recycle/customer/list.php).
        //
        // This used to be gated only by the $done flag above, which is
        // per-PHP-process — meaning it re-ran on literally every single
        // page load and every status.php ping across the whole app,
        // each time doing an unindexed full scan of the notifications
        // table (no index exists or should exist on `link`; the LIKE
        // patterns start with a wildcard so an index couldn't help
        // anyway). As notifications accumulate over weeks/months this
        // got slower on every single page navigation for every user.
        // Gated behind a settings flag instead — same one-time-ever
        // pattern as cleanup_duplicate_system_notifications() below —
        // so it actually runs once, ever, then never touches the table
        // again.
        if ((get_settings()['topbar_links_fixed_v1'] ?? '') !== '1') {
            $brokenLinks = db_rows(
                "SELECT id, link FROM notifications
                  WHERE link <> '' AND link NOT LIKE '/%' AND link NOT LIKE 'http%'"
            );
            foreach ($brokenLinks as $row) {
                db_run(
                    "UPDATE notifications SET link = ? WHERE id = ?",
                    [base_url($row['link']), $row['id']]
                );
            }
            save_setting('topbar_links_fixed_v1', '1');
        }

    } catch (Throwable $e) {
        error_log('ensure_topbar_tables failed: ' . $e->getMessage());
    }
}

// ── Create a notification for one or more users ────────────────
// $target: 'all' | 'admins' (admin+manager) | 'warehouse_staff'
//          (admin+manager+warehouse) | array of user ids
function create_notification(
    $target,
    string $type,
    string $icon,
    string $title,
    string $message = '',
    string $link = ''
): void {
    ensure_topbar_tables();
    try {
        $userIds = resolve_notification_targets($target);
        if (!$userIds) return;

        // Callers pass app-root-relative paths (e.g. 'product/list.php').
        // Store them as proper absolute links via base_url() so clicking a
        // notification works from ANY page depth — previously the raw
        // relative path was stored as-is and the browser resolved it
        // against the CURRENT page's URL, so e.g. clicking a notification
        // while on recycle/index.php sent the user to
        // .../recycle/product/list.php (404) instead of .../product/list.php.
        if ($link !== '' && !preg_match('#^(https?:)?//#i', $link) && !str_starts_with($link, '/')) {
            $link = base_url($link);
        }

        foreach ($userIds as $uid) {
            db_run(
                "INSERT INTO notifications (user_id, type, icon, title, message, link)
                 VALUES (?,?,?,?,?,?)",
                [$uid, $type, $icon, $title, $message, $link]
            );
        }
    } catch (Throwable $e) {
        error_log('create_notification failed: ' . $e->getMessage());
    }
}

function resolve_notification_targets($target): array {
    if (is_array($target)) {
        return array_values(array_unique(array_map('intval', $target)));
    }
    switch ($target) {
        case 'all':
            $rows = db_rows("SELECT id FROM users WHERE is_active = 1");
            break;
        case 'warehouse_staff':
            $rows = db_rows("SELECT id FROM users WHERE is_active = 1 AND role IN ('admin','manager','warehouse')");
            break;
        case 'admins':
        default:
            $rows = db_rows("SELECT id FROM users WHERE is_active = 1 AND role IN ('admin','manager')");
            break;
    }
    return array_map(fn($r) => (int)$r['id'], $rows);
}

// ── Log a system event (server down/recovered/sync errors) ─────
function log_system_event(string $type, string $message = ''): void {
    ensure_topbar_tables();
    try {
        db_run(
            "INSERT INTO system_events (event_type, message) VALUES (?, ?)",
            [$type, $message]
        );
    } catch (Throwable $e) {
        error_log('log_system_event failed: ' . $e->getMessage());
    }
}

// ── One-time cleanup of duplicates created by the race condition
// fixed above (see the comment inside generate_system_notifications()).
// Removes any already-inserted duplicate system notification — same
// user, same type, same day — keeping only the earliest row per
// group. Gated by a settings flag so the DELETE actually runs only
// once, ever, no matter how many times this function is called.
function cleanup_duplicate_system_notifications(): void {
    static $doneThisRequest = false;
    if ($doneThisRequest) return;
    $doneThisRequest = true;

    $cfg = get_settings();
    if (($cfg['notif_dupes_cleaned_v1'] ?? '') === '1') return;

    try {
        db_run(
            "DELETE n1 FROM notifications n1
             INNER JOIN notifications n2
                     ON n1.user_id = n2.user_id
                    AND n1.type    = n2.type
                    AND DATE(n1.created_at) = DATE(n2.created_at)
                    AND n1.id > n2.id
             WHERE n1.type IN ('low_stock', 'expiry', 'due_reminder')"
        );
    } catch (Throwable $e) {
        error_log('cleanup_duplicate_system_notifications failed: ' . $e->getMessage());
    }
    save_setting('notif_dupes_cleaned_v1', '1');
}

// ── Periodic sweep: generate Low Stock / Expiry / Due Collection
// notifications from live data. Rate-limited via the settings
// table so this only actually queries + inserts once per hour,
// no matter how often it's called from api/status.php's ping.
function generate_system_notifications(): void {
    ensure_topbar_tables();
    cleanup_duplicate_system_notifications();
    try {
        // Atomically claim the sweep instead of "read last_notif_sweep,
        // then separately write a fresh one" — that older check-then-set
        // version had a race window: every open terminal/tab polls
        // api/status.php every few seconds, so two pings landing within
        // milliseconds of each other could both read the same stale
        // last_notif_sweep before either had written a new one, both
        // pass the "not swept within the last hour" check, and both go
        // on to generate/insert the SAME day's Low Stock / Expiry
        // notifications — duplicating them for every recipient (visible
        // as the same alert appearing twice in the topbar ticker). This
        // single INSERT ... ON DUPLICATE KEY UPDATE makes the database
        // itself the lock: MySQL reports 0 affected rows when an
        // existing row is "updated" to the same value it already had,
        // so exactly one concurrent caller ever sees a non-zero result
        // and proceeds — the same atomic-claim pattern used to fix the
        // oversell race in api/sales.php.
        $now = date('Y-m-d H:i:s');
        $claimed = db_run(
            "INSERT INTO settings (setting_key, setting_value)
             VALUES ('last_notif_sweep', ?)
             ON DUPLICATE KEY UPDATE
               setting_value = IF(setting_value < DATE_SUB(?, INTERVAL 1 HOUR), VALUES(setting_value), setting_value)",
            [$now, $now]
        );
        if ($claimed === 0) {
            return; // another concurrent request already claimed this sweep
        }

        // Low stock
        $lowStockCount = (int) db_val(
            "SELECT COUNT(*) FROM products
              WHERE is_active = 1 AND is_deleted = 0
                AND low_stock_alert > 0 AND stock_pcs < low_stock_alert"
        );
        if ($lowStockCount > 0 && !notif_exists_today('low_stock')) {
            create_notification('warehouse_staff', 'low_stock', '⚠',
                'Low Stock Alert',
                "$lowStockCount product(s) have fallen below their alert level.",
                'product/smart-requisition.php'
            );
        }

        // Expiry (expired or expiring this month — mirrors header.php's badge logic)
        $expiringCount = (int) db_val(
            "SELECT COUNT(DISTINCT p.id)
               FROM products p
               JOIN purchase_items pi ON pi.product_id = p.id
              WHERE pi.exp != '' AND pi.exp < DATE_FORMAT(NOW(),'%Y-%m')"
        );
        if ($expiringCount > 0 && !notif_exists_today('expiry')) {
            create_notification('warehouse_staff', 'expiry', '⏰',
                'Expiry Alert',
                "$expiringCount product batch(es) are expired or expiring soon.",
                'product/expired.php'
            );
        }

        // Due collection
        $dueCount = (int) db_val(
            "SELECT COUNT(*) FROM customers WHERE id > 1 AND is_deleted = 0 AND due > 0"
        );
        if ($dueCount > 0 && !notif_exists_today('due_reminder')) {
            create_notification('admins', 'due_reminder', '💰',
                'Due Collection Reminder',
                "$dueCount customer(s) currently have an outstanding due balance.",
                'customer/list.php'
            );
        }
    } catch (Throwable $e) {
        error_log('generate_system_notifications failed: ' . $e->getMessage());
    }
}

function notif_exists_today(string $type): bool {
    return (bool) db_val(
        "SELECT COUNT(*) FROM notifications
          WHERE type = ? AND DATE(created_at) = CURDATE()",
        [$type]
    );
}
