-- ============================================================
-- migration_notifications.sql
-- ============================================================
-- FIX (30 Jul 2026): the `notifications` table was referenced all over
-- the code (bell #2 in includes/header.php, modules/notifications/ajax.php,
-- api/v1/index.php's notify() helper) but was NEVER actually created in
-- config/database.sql. Every notify() call has been silently failing
-- (caught by try/catch) since the table didn't exist — that's why the
-- bell notification icon never showed anything for anyone, web or mobile.
--
-- UPDATE (30 Jul 2026, later): added `user_id` + made `employee_id`
-- nullable so a notification can target a login account directly, not
-- just an employee record. This is needed so superadmin logins with no
-- linked employee can still receive notifications (they get every
-- notification created anywhere in the system — see notifyAllSuperadmins()
-- in includes/functions.php).
--
-- Run this once against your existing database:
--   mysql -u <user> -p <database_name> < migration_notifications.sql
-- If you already ran an earlier version of this file, just run the
-- ALTER TABLE block at the bottom (CREATE TABLE IF NOT EXISTS is a no-op
-- if the table already exists).
-- ============================================================

CREATE TABLE IF NOT EXISTS notifications (
    id            INT AUTO_INCREMENT PRIMARY KEY,
    employee_id   INT NULL,
    user_id       INT NULL,
    title         VARCHAR(150) NOT NULL,
    message       VARCHAR(500) NOT NULL,
    type          VARCHAR(30) DEFAULT 'general',
    ref_id        INT NULL,
    is_read       TINYINT(1) DEFAULT 0,
    created_at    DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE INDEX idx_notifications_emp_read  ON notifications(employee_id, is_read);
CREATE INDEX idx_notifications_user_read ON notifications(user_id, is_read);
CREATE INDEX idx_notifications_created   ON notifications(created_at);

-- If you already had this table from the first version of this file
-- (no user_id column, employee_id NOT NULL), run these two lines:
-- ALTER TABLE notifications MODIFY employee_id INT NULL;
-- ALTER TABLE notifications ADD COLUMN user_id INT NULL AFTER employee_id,
--   ADD FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE;
-- ALTER TABLE notifications ADD INDEX idx_notifications_user_read (user_id, is_read);
