-- FASE 08 - central de notificações
CREATE TABLE IF NOT EXISTS notifications (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    customer_id BIGINT UNSIGNED NULL,
    driver_id BIGINT UNSIGNED NULL,
    order_id BIGINT UNSIGNED NULL,
    channel ENUM('APP','PUSH') NOT NULL DEFAULT 'APP',
    title VARCHAR(160) NOT NULL,
    message VARCHAR(500) NOT NULL,
    event_key VARCHAR(80) NULL,
    read_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY(customer_id) REFERENCES customers(id) ON DELETE CASCADE,
    FOREIGN KEY(driver_id) REFERENCES drivers(id) ON DELETE CASCADE,
    FOREIGN KEY(order_id) REFERENCES orders(id) ON DELETE CASCADE,
    INDEX idx_notify_customer(customer_id,read_at),
    INDEX idx_notify_driver(driver_id,read_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;