-- Audit report for user_data_scopes rollout
-- Safe after 032_user_data_scopes_sanitize.sql

CREATE TABLE IF NOT EXISTS user_data_scope_audit_snapshots (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    role ENUM('owner', 'admin', 'manager', 'agent', 'viewer') NOT NULL,
    module_name VARCHAR(80) NOT NULL,
    scope_type ENUM('own', 'team', 'all') NOT NULL,
    total_users INT UNSIGNED NOT NULL DEFAULT 0,
    captured_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user_data_scope_audit_tenant (tenant_id, role, module_name, scope_type, captured_at),
    CONSTRAINT fk_user_data_scope_audit_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO user_data_scope_audit_snapshots (tenant_id, role, module_name, scope_type, total_users)
SELECT
    u.tenant_id,
    u.role,
    s.module_name,
    s.scope_type,
    COUNT(*) AS total_users
FROM user_data_scopes s
INNER JOIN users u
    ON u.id = s.user_id
   AND u.tenant_id = s.tenant_id
GROUP BY u.tenant_id, u.role, s.module_name, s.scope_type;

CREATE OR REPLACE VIEW vw_user_data_scope_audit_current AS
SELECT
    s.tenant_id,
    t.slug AS tenant_slug,
    u.role,
    s.module_name,
    s.scope_type,
    COUNT(*) AS total_users
FROM user_data_scopes s
INNER JOIN users u
    ON u.id = s.user_id
   AND u.tenant_id = s.tenant_id
INNER JOIN tenants t
    ON t.id = s.tenant_id
GROUP BY s.tenant_id, t.slug, u.role, s.module_name, s.scope_type;
