-- =====================================================
-- USER PERMISSIONS MIGRATION
-- Version: 1.0
-- Date: 2026-02-01
-- Purpose: Add per-user permission flags for granular access control
-- =====================================================

-- =====================================================
-- 1. CREATE USER PERMISSIONS TABLE
-- Stores per-user permission flags (View/Add-Edit/Delete)
-- =====================================================
CREATE TABLE IF NOT EXISTS user_permissions (
    id SERIAL PRIMARY KEY,
    user_id INT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    can_view BOOLEAN DEFAULT TRUE,
    can_add_edit BOOLEAN DEFAULT FALSE,
    can_delete BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(user_id)
);

CREATE INDEX IF NOT EXISTS idx_up_user ON user_permissions(user_id);

-- =====================================================
-- 2. MIGRATE EXISTING USERS TO NEW PERMISSION STRUCTURE
-- Super Admin (role_id=1): Full access
-- Admin (role_id=2): Full access (existing behavior preserved)
-- User (role_id=3): View only
-- =====================================================

-- Insert permissions for existing users based on their role
-- Note: Handles both string and integer role_id values
INSERT INTO user_permissions (user_id, can_view, can_add_edit, can_delete)
SELECT 
    u.id,
    TRUE,  -- All users can view
    CASE 
        WHEN u.role_id::text IN ('1', '2') THEN TRUE  -- Super Admin and Admin can add/edit
        ELSE FALSE  -- Users cannot add/edit by default
    END,
    CASE 
        WHEN u.role_id::text = '1' THEN TRUE  -- Only Super Admin can delete by default
        WHEN u.role_id::text = '2' THEN TRUE  -- Admin can delete (existing behavior)
        ELSE FALSE  -- Users cannot delete
    END
FROM users u
WHERE u.status::text = '1' OR u.status IS NULL
ON CONFLICT (user_id) DO NOTHING;

-- =====================================================
-- 3. ADD TRIGGER TO AUTO-CREATE PERMISSIONS FOR NEW USERS
-- =====================================================
-- NOTE: This trigger is DISABLED because the application handles permission saving
-- The PHP code in user.php controller saves permissions correctly
-- We keep this function for reference but don't use the trigger

CREATE OR REPLACE FUNCTION create_user_permissions()
RETURNS TRIGGER AS $$
BEGIN
    -- Only create default permissions if none exist
    -- This is a fallback - normally the application saves permissions
    INSERT INTO user_permissions (user_id, can_view, can_add_edit, can_delete)
    VALUES (
        NEW.id,
        TRUE,
        FALSE,  -- Default to no edit permission
        FALSE   -- Default to no delete permission
    )
    ON CONFLICT (user_id) DO NOTHING;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- DROP the trigger - PHP application handles permission saving
DROP TRIGGER IF EXISTS trigger_create_user_permissions ON users;

-- NOTE: We intentionally do NOT create the trigger anymore
-- The PHP code in user.php controller saves permissions with the correct values
-- selected by the admin during user creation

-- =====================================================
-- 4. MIGRATE USER AUTHORITIES FROM CSV TO NORMALIZED TABLE
-- (if not already done)
-- =====================================================
INSERT INTO user_authorities (user_id, authority_id)
SELECT 
    u.id,
    unnest(string_to_array(u.user_authority, ','))::int
FROM users u
WHERE u.user_authority IS NOT NULL 
  AND u.user_authority != ''
  AND u.user_authority != 'NULL'
  AND u.role_id::text = '3'  -- Only for User role
ON CONFLICT (user_id, authority_id) DO NOTHING;


