-- ResellBD Step 14: in-site notifications. Apply ONLY to the NEW ResellBD database after migrations 001-007.
SET NAMES utf8mb4;
CREATE TABLE IF NOT EXISTS notifications (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
 user_id BIGINT UNSIGNED NOT NULL,
 type VARCHAR(40) NOT NULL DEFAULT 'general',
 title VARCHAR(190) NOT NULL,
 message VARCHAR(1000) NOT NULL,
 target_url VARCHAR(500) NULL,
 related_type VARCHAR(40) NULL,
 related_id BIGINT UNSIGNED NULL,
 is_read TINYINT(1) NOT NULL DEFAULT 0,
 read_at DATETIME NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 PRIMARY KEY(id), KEY idx_notifications_user_read_date(user_id,is_read,created_at), KEY idx_notifications_related(related_type,related_id),
 CONSTRAINT fk_notifications_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;