CREATE TABLE IF NOT EXISTS vehicle_open_user (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(64) NOT NULL,
    display_name VARCHAR(96) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    status VARCHAR(16) NOT NULL DEFAULT 'enabled',
    valid_from DATETIME(3) NOT NULL,
    valid_to DATETIME(3) NULL,
    failed_login_count INT UNSIGNED NOT NULL DEFAULT 0,
    locked_until DATETIME(3) NULL,
    password_changed_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    last_login_at DATETIME(3) NULL,
    created_by VARCHAR(96) NOT NULL,
    updated_by VARCHAR(96) NOT NULL,
    created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
    UNIQUE KEY uk_vehicle_open_user_username (username),
    INDEX idx_vehicle_open_user_status_validity (status, valid_from, valid_to),
    CONSTRAINT chk_vehicle_open_user_status CHECK (status IN ('enabled', 'disabled')),
    CONSTRAINT chk_vehicle_open_user_validity CHECK (valid_to IS NULL OR valid_to > valid_from)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS vehicle_open_user_app (
    user_id BIGINT UNSIGNED NOT NULL,
    app_id BIGINT UNSIGNED NOT NULL,
    role VARCHAR(16) NOT NULL,
    granted_by VARCHAR(96) NOT NULL,
    created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
    PRIMARY KEY (user_id, app_id),
    INDEX idx_vehicle_open_user_app_app (app_id, role, user_id),
    CONSTRAINT fk_vehicle_open_user_app_user FOREIGN KEY (user_id) REFERENCES vehicle_open_user(id) ON DELETE CASCADE,
    CONSTRAINT fk_vehicle_open_user_app_app FOREIGN KEY (app_id) REFERENCES vehicle_open_app(id) ON DELETE CASCADE,
    CONSTRAINT chk_vehicle_open_user_app_role CHECK (role IN ('owner', 'developer', 'viewer'))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS vehicle_open_user_session (
    id CHAR(32) NOT NULL PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    token_hash BINARY(32) NOT NULL,
    issued_at DATETIME(3) NOT NULL,
    expires_at DATETIME(3) NOT NULL,
    last_seen_at DATETIME(3) NOT NULL,
    revoked_at DATETIME(3) NULL,
    remote_addr VARCHAR(96) NOT NULL DEFAULT '',
    user_agent VARCHAR(255) NOT NULL DEFAULT '',
    UNIQUE KEY uk_vehicle_open_user_session_token (token_hash),
    INDEX idx_vehicle_open_user_session_user (user_id, expires_at),
    INDEX idx_vehicle_open_user_session_expiry (expires_at, revoked_at),
    CONSTRAINT fk_vehicle_open_user_session_user FOREIGN KEY (user_id) REFERENCES vehicle_open_user(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS vehicle_open_user_audit (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NULL,
    actor VARCHAR(96) NOT NULL,
    action VARCHAR(64) NOT NULL,
    result VARCHAR(24) NOT NULL,
    detail_json JSON NULL,
    remote_addr VARCHAR(96) NOT NULL DEFAULT '',
    created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    INDEX idx_vehicle_open_user_audit_user_time (user_id, created_at),
    INDEX idx_vehicle_open_user_audit_action_time (action, created_at),
    CONSTRAINT fk_vehicle_open_user_audit_user FOREIGN KEY (user_id) REFERENCES vehicle_open_user(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;