-- =============================================================================
-- user_business_access: which businesses (under a tenant) a user may access.
-- Replaces per-module READ/WRITE grants for tenant managers/users with a simple
-- per-business on/off grant. SUPER_ADMIN / TENANT_ADMIN are not restricted.
-- Deploy: mysql -u root -p db_abos_v0.1 < db/user_business_access.sql
-- =============================================================================
USE db_abos_v0.1;

CREATE TABLE IF NOT EXISTS user_business_access (
    id          BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id   INT NOT NULL,
    user_id     INT NOT NULL,
    business_id INT NOT NULL,
    is_deleted  TINYINT(1) NOT NULL DEFAULT 0,
    created_at  TIMESTAMP  DEFAULT CURRENT_TIMESTAMP,
    created_by  INT NULL,
    updated_at  TIMESTAMP  DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_uba_user_business (user_id, business_id),
    KEY idx_uba_user (user_id),
    KEY idx_uba_tenant (tenant_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;