nordabiz/database/migrations/035_add_role_system.sql

-- Migration: 035_add_role_system.sql
-- Description: Add hierarchical role system for granular access control
-- Author: Claude Code
-- Date: 2026-02-01
--
-- Role hierarchy:
--   UNAFFILIATED (10) - Firma spoza Izby - tylko publiczne profile
--   MEMBER (20) - Członek Norda bez firmy - pełny dostęp do treści
--   EMPLOYEE (30) - Pracownik firmy członkowskiej - edycja danych firmy
--   MANAGER (40) - Kadra zarządzająca - pełna kontrola firmy + użytkownicy
--   OFFICE_MANAGER (50) - Kierownik biura Norda - panel admina
--   ADMIN (100) - Administrator portalu - pełne prawa
--
-- Company roles:
--   NONE (0) - Brak powiązania z firmą
--   VIEWER (10) - Może przeglądać dashboard firmy
--   EMPLOYEE (20) - Może edytować dane firmy
--   MANAGER (30) - Pełna kontrola + zarządzanie użytkownikami

-- ============================================================
-- STEP 1: Add new columns
-- ============================================================

-- Add role column with default UNAFFILIATED
ALTER TABLE users ADD COLUMN IF NOT EXISTS role VARCHAR(20) DEFAULT 'UNAFFILIATED' NOT NULL;

-- Add company_role column with default NONE
ALTER TABLE users ADD COLUMN IF NOT EXISTS company_role VARCHAR(20) DEFAULT 'NONE' NOT NULL;

-- Add index for role lookups
CREATE INDEX IF NOT EXISTS idx_users_role ON users(role);

-- ============================================================
-- STEP 2: Migrate existing admins
-- ============================================================

UPDATE users
SET role = 'ADMIN'
WHERE is_admin = TRUE AND (role IS NULL OR role = 'UNAFFILIATED');

-- ============================================================
-- STEP 3: Auto-detect MANAGER role from KRS data
-- Users linked to company management (zarząd) get MANAGER role
-- ============================================================

UPDATE users u
SET
    role = 'MANAGER',
    company_role = 'MANAGER'
WHERE
    u.company_id IS NOT NULL
    AND u.role NOT IN ('ADMIN', 'OFFICE_MANAGER')
    AND EXISTS (
        SELECT 1 FROM company_people cp
        WHERE cp.person_id = u.person_id
          AND cp.company_id = u.company_id
          AND cp.role_category = 'zarzad'
    );

-- ============================================================
-- STEP 4: Set EMPLOYEE role for users with company but no KRS link
-- ============================================================

UPDATE users
SET
    role = 'EMPLOYEE',
    company_role = 'EMPLOYEE'
WHERE
    company_id IS NOT NULL
    AND role NOT IN ('ADMIN', 'OFFICE_MANAGER', 'MANAGER');

-- ============================================================
-- STEP 5: Set MEMBER role for Norda members without company
-- ============================================================

UPDATE users
SET role = 'MEMBER'
WHERE
    is_norda_member = TRUE
    AND company_id IS NULL
    AND role = 'UNAFFILIATED';

-- ============================================================
-- STEP 6: Create user_company_permissions table for delegation
-- ============================================================

CREATE TABLE IF NOT EXISTS user_company_permissions (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    company_id INTEGER NOT NULL REFERENCES companies(id) ON DELETE CASCADE,

    -- Content editing permissions
    can_edit_description BOOLEAN DEFAULT TRUE,   -- Opis firmy, historia, wartości
    can_edit_services BOOLEAN DEFAULT TRUE,      -- Usługi, kompetencje, technologie
    can_edit_contacts BOOLEAN DEFAULT FALSE,     -- Email, telefon, adres
    can_edit_social BOOLEAN DEFAULT FALSE,       -- Social media, strona www

    -- Feature permissions
    can_manage_classifieds BOOLEAN DEFAULT TRUE, -- B2B ogłoszenia w imieniu firmy
    can_post_forum BOOLEAN DEFAULT TRUE,         -- Posty na forum w imieniu firmy
    can_view_analytics BOOLEAN DEFAULT FALSE,    -- Statystyki firmy, wyświetlenia

    -- Audit trail
    granted_by_id INTEGER REFERENCES users(id),
    granted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    -- Unique constraint: one permission record per user-company pair
    CONSTRAINT uq_user_company_permissions UNIQUE (user_id, company_id)
);

-- Indexes for lookups
CREATE INDEX IF NOT EXISTS idx_user_company_permissions_user ON user_company_permissions(user_id);
CREATE INDEX IF NOT EXISTS idx_user_company_permissions_company ON user_company_permissions(company_id);

-- Grant permissions to application user
GRANT ALL ON TABLE user_company_permissions TO nordabiz_app;
GRANT USAGE, SELECT ON SEQUENCE user_company_permissions_id_seq TO nordabiz_app;

-- ============================================================
-- STEP 7: Create default permissions for existing EMPLOYEE users
-- ============================================================

INSERT INTO user_company_permissions (user_id, company_id, can_edit_description, can_edit_services)
SELECT id, company_id, TRUE, TRUE
FROM users
WHERE role = 'EMPLOYEE' AND company_id IS NOT NULL
ON CONFLICT (user_id, company_id) DO NOTHING;

-- ============================================================
-- STEP 8: Verify migration results
-- ============================================================

-- Log migration statistics (view in PostgreSQL logs)
DO $$
DECLARE
    admin_count INTEGER;
    office_mgr_count INTEGER;
    manager_count INTEGER;
    employee_count INTEGER;
    member_count INTEGER;
    unaffiliated_count INTEGER;
    permissions_count INTEGER;
BEGIN
    SELECT COUNT(*) INTO admin_count FROM users WHERE role = 'ADMIN';
    SELECT COUNT(*) INTO office_mgr_count FROM users WHERE role = 'OFFICE_MANAGER';
    SELECT COUNT(*) INTO manager_count FROM users WHERE role = 'MANAGER';
    SELECT COUNT(*) INTO employee_count FROM users WHERE role = 'EMPLOYEE';
    SELECT COUNT(*) INTO member_count FROM users WHERE role = 'MEMBER';
    SELECT COUNT(*) INTO unaffiliated_count FROM users WHERE role = 'UNAFFILIATED';
    SELECT COUNT(*) INTO permissions_count FROM user_company_permissions;

    RAISE NOTICE 'Role migration complete:';
    RAISE NOTICE '  ADMIN: %', admin_count;
    RAISE NOTICE '  OFFICE_MANAGER: %', office_mgr_count;
    RAISE NOTICE '  MANAGER: %', manager_count;
    RAISE NOTICE '  EMPLOYEE: %', employee_count;
    RAISE NOTICE '  MEMBER: %', member_count;
    RAISE NOTICE '  UNAFFILIATED: %', unaffiliated_count;
    RAISE NOTICE '  Delegated permissions records: %', permissions_count;
END $$;

-- ============================================================
-- VERIFICATION QUERIES (run manually to verify)
-- ============================================================

-- Check for any users with NULL role (should be 0)
-- SELECT COUNT(*) FROM users WHERE role IS NULL;

-- View role distribution
-- SELECT role, COUNT(*) as count FROM users GROUP BY role ORDER BY count DESC;

-- View users with company but no company_role
-- SELECT id, email, company_id, role, company_role
-- FROM users
-- WHERE company_id IS NOT NULL AND company_role = 'NONE';

-- Check KRS-linked managers
-- SELECT u.id, u.email, u.role, u.company_role, c.name as company_name
-- FROM users u
-- JOIN companies c ON u.company_id = c.id
-- WHERE u.role = 'MANAGER'
-- LIMIT 10;