USE food_delivery;

CREATE TABLE IF NOT EXISTS activity_logs (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id     INT UNSIGNED  DEFAULT NULL,
    user_name   VARCHAR(100)  DEFAULT NULL,          -- snapshot (user may be deleted later)
    role        VARCHAR(50)   DEFAULT NULL,
    action      VARCHAR(50)   NOT NULL,              -- created, updated, deleted, login, logout, toggled, etc.
    module      VARCHAR(50)   NOT NULL,              -- category, menu_item, order, voucher, user, etc.
    target_id   INT UNSIGNED  DEFAULT NULL,          -- affected record id
    target_name VARCHAR(200)  DEFAULT NULL,          -- human-readable label of affected record
    description TEXT          DEFAULT NULL,          -- full detail message
    ip_address  VARCHAR(45)   DEFAULT NULL,
    user_agent  VARCHAR(255)  DEFAULT NULL,
    created_at  TIMESTAMP     DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_module    (module),
    INDEX idx_user_id   (user_id),
    INDEX idx_created_at(created_at),
    INDEX idx_action    (action)
) ENGINE=InnoDB;