-- Migration 002: Create sessions table
-- Requirements: 3.2, 3.7
-- Design: Data Models -> 3. SESSIONS

CREATE TABLE IF NOT EXISTS sessions (
    id              CHAR(64)     NOT NULL,
    user_id         BIGINT UNSIGNED NOT NULL,
    csrf_token      CHAR(64)     NOT NULL,
    ip_address      VARCHAR(45)  NULL,
    user_agent      VARCHAR(512) NULL,
    expires_at      DATETIME(3)  NOT NULL,
    created_at      DATETIME(3)  NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    PRIMARY KEY (id),
    KEY idx_sessions_user (user_id),
    KEY idx_sessions_expiry (expires_at),
    CONSTRAINT fk_sessions_user FOREIGN KEY (user_id)
        REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;