M-Project/backend/migrations/012_simplify_permissions.sql

-- 권한 시스템 단순화
-- admin/user 구조로 변경하고 페이지별 접근 권한으로 변경

-- 1. 기존 복잡한 권한 테이블 삭제하고 단순한 페이지 권한 테이블로 변경
DROP TABLE IF EXISTS user_permissions CASCADE;

-- 2. 페이지별 접근 권한 테이블 생성
CREATE TABLE user_page_permissions (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    page_name VARCHAR(50) NOT NULL,
    can_access BOOLEAN DEFAULT FALSE,
    granted_by_id INTEGER REFERENCES users(id),
    granted_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    notes TEXT,
    UNIQUE(user_id, page_name)
);

-- 3. 인덱스 생성
CREATE INDEX IF NOT EXISTS idx_user_page_permissions_user_id ON user_page_permissions(user_id);
CREATE INDEX IF NOT EXISTS idx_user_page_permissions_page_name ON user_page_permissions(page_name);

-- 4. 기존 복잡한 함수들 삭제
DROP FUNCTION IF EXISTS check_user_permission(INTEGER, VARCHAR);
DROP FUNCTION IF EXISTS grant_user_permission(INTEGER, VARCHAR, INTEGER, TEXT);
DROP FUNCTION IF EXISTS revoke_user_permission(INTEGER, VARCHAR, INTEGER, TEXT);

-- 5. 단순한 페이지 접근 권한 체크 함수
CREATE OR REPLACE FUNCTION check_page_access(p_user_id INTEGER, p_page_name VARCHAR)
RETURNS BOOLEAN AS $$
DECLARE
    user_role userrole;
    has_access BOOLEAN := FALSE;
BEGIN
    -- 사용자 역할 가져오기
    SELECT role INTO user_role FROM users WHERE id = p_user_id AND is_active = TRUE;
    
    IF user_role IS NULL THEN
        RETURN FALSE;
    END IF;
    
    -- admin은 모든 페이지 접근 가능
    IF user_role = 'admin' THEN
        RETURN TRUE;
    END IF;
    
    -- 일반 사용자는 개별 페이지 권한 확인
    SELECT can_access INTO has_access 
    FROM user_page_permissions 
    WHERE user_id = p_user_id 
      AND page_name = p_page_name;
    
    -- 권한이 설정되지 않은 경우 기본값 (부적합 등록/조회만 허용)
    IF has_access IS NULL THEN
        CASE p_page_name
            WHEN 'issues_create' THEN has_access := TRUE;
            WHEN 'issues_view' THEN has_access := TRUE;
            ELSE has_access := FALSE;
        END CASE;
    END IF;
    
    RETURN COALESCE(has_access, FALSE);
END;
$$ LANGUAGE plpgsql;

-- 6. 페이지 권한 부여 함수
CREATE OR REPLACE FUNCTION grant_page_access(
    p_user_id INTEGER, 
    p_page_name VARCHAR, 
    p_can_access BOOLEAN,
    p_granted_by_id INTEGER,
    p_notes TEXT DEFAULT NULL
)
RETURNS BOOLEAN AS $$
BEGIN
    INSERT INTO user_page_permissions (user_id, page_name, can_access, granted_by_id, notes)
    VALUES (p_user_id, p_page_name, p_can_access, p_granted_by_id, p_notes)
    ON CONFLICT (user_id, page_name) 
    DO UPDATE SET 
        can_access = p_can_access,
        granted_by_id = p_granted_by_id,
        granted_at = NOW(),
        notes = p_notes;
    
    RETURN TRUE;
END;
$$ LANGUAGE plpgsql;

-- 7. 사용자 페이지 권한 조회 뷰
CREATE OR REPLACE VIEW user_page_access_view AS
SELECT 
    u.id as user_id,
    u.username,
    u.full_name,
    u.role,
    upp.page_name,
    upp.can_access,
    upp.granted_at,
    granted_by.username as granted_by_username,
    upp.notes
FROM users u
LEFT JOIN user_page_permissions upp ON u.id = upp.user_id
LEFT JOIN users granted_by ON upp.granted_by_id = granted_by.id
WHERE u.is_active = TRUE
ORDER BY u.username, upp.page_name;

-- 8. 기존 super_admin, manager 역할을 admin으로 변경
UPDATE users SET role = 'admin' WHERE role IN ('super_admin', 'manager');

-- 9. 기존 뷰 삭제
DROP VIEW IF EXISTS user_permissions_view;