-- =====================================================
-- PRODUCTION DEPLOYMENT SCRIPT
-- Date: February 15, 2026
-- Version: 2.2.0
-- Description: Per-Company Permissions Implementation
--              Users can now have different permissions for different companies
-- =====================================================
-- 
-- IMPORTANT: 
-- 1. ⚠️  BACKUP YOUR DATABASE BEFORE RUNNING THIS SCRIPT
-- 2. 📋 Run this script during a maintenance window
-- 3. 🧪 Test in staging environment first
-- 4. 👥 Ensure all users are logged out during deployment
-- 5. ⏱️  Estimated execution time: < 1 minute
--
-- Prerequisites:
--   - production_deployment_2026_02_14.sql must have been run successfully
--   - PostgreSQL 9.5+ (for ON CONFLICT support)
--
-- Usage:
--   psql -U your_db_user -d your_database_name -f production_deployment_2026_02_15.sql
--
-- Rollback (if needed):
--   ALTER TABLE user_companies DROP COLUMN IF EXISTS can_add_edit, DROP COLUMN IF EXISTS can_delete;
--   DROP INDEX IF EXISTS idx_uc_user_company;
--
-- =====================================================

\echo ''
\echo '=============================================='
\echo 'Starting Production Deployment: 2.2.0'
\echo 'Date: February 15, 2026'
\echo '=============================================='
\echo ''

BEGIN;

-- =====================================================
-- SECTION 1: ADD PER-COMPANY PERMISSIONS
-- Purpose: Enable different permissions for users across different companies
--          Example: User A can ADD/EDIT/DELETE in Company A, but only VIEW in Company B
-- =====================================================

\echo 'SECTION 1: Adding per-company permission columns to user_companies table...'

-- 1.1 Add permission columns
DO $$
BEGIN
    -- Add can_add_edit column
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns 
                   WHERE table_name = 'user_companies' AND column_name = 'can_add_edit') THEN
        ALTER TABLE user_companies ADD COLUMN can_add_edit BOOLEAN DEFAULT FALSE;
        RAISE NOTICE '✓ Added can_add_edit column to user_companies';
    ELSE
        RAISE NOTICE '→ can_add_edit column already exists in user_companies';
    END IF;
    
    -- Add can_delete column
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns 
                   WHERE table_name = 'user_companies' AND column_name = 'can_delete') THEN
        ALTER TABLE user_companies ADD COLUMN can_delete BOOLEAN DEFAULT FALSE;
        RAISE NOTICE '✓ Added can_delete column to user_companies';
    ELSE
        RAISE NOTICE '→ can_delete column already exists in user_companies';
    END IF;
END $$;

-- 1.2 Create composite index for faster permission lookups
DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_indexes 
                   WHERE indexname = 'idx_uc_user_company') THEN
        CREATE INDEX idx_uc_user_company ON user_companies(user_id, company_id);
        RAISE NOTICE '✓ Created composite index idx_uc_user_company';
    ELSE
        RAISE NOTICE '→ Index idx_uc_user_company already exists';
    END IF;
END $$;

-- 1.3 Add column comments for documentation
COMMENT ON COLUMN user_companies.can_add_edit IS 'Whether user can add/edit documents in this specific company (company-level permission)';
COMMENT ON COLUMN user_companies.can_delete IS 'Whether user can delete documents in this specific company (company-level permission)';

\echo '✓ Per-company permissions schema updated successfully'
\echo ''

-- =====================================================
-- SECTION 2: DATA MIGRATION (OPTIONAL)
-- Purpose: Migrate existing global permissions to per-company permissions
--          Only run this if you have existing users with data
-- =====================================================

\echo 'SECTION 2: Migrating existing global permissions to per-company level...'

-- 2.1 Update user_companies with permissions from user_permissions table
DO $$
DECLARE
    updated_count INTEGER := 0;
BEGIN
    -- For each user_company record, set permissions based on global user_permissions
    UPDATE user_companies uc
    SET 
        can_add_edit = COALESCE(up.can_add_edit, FALSE),
        can_delete = COALESCE(up.can_delete, FALSE)
    FROM user_permissions up
    WHERE uc.user_id = up.user_id
    AND (uc.can_add_edit IS NULL OR uc.can_delete IS NULL);
    
    GET DIAGNOSTICS updated_count = ROW_COUNT;
    RAISE NOTICE '✓ Migrated permissions for % user-company associations', updated_count;
    
    -- Set default FALSE for any remaining NULL values
    UPDATE user_companies
    SET 
        can_add_edit = COALESCE(can_add_edit, FALSE),
        can_delete = COALESCE(can_delete, FALSE)
    WHERE can_add_edit IS NULL OR can_delete IS NULL;
    
    RAISE NOTICE '✓ Set default permissions for remaining records';
END $$;

\echo '✓ Data migration completed successfully'
\echo ''

-- =====================================================
-- SECTION 3: VALIDATION
-- Purpose: Verify schema changes and data integrity
-- =====================================================

\echo 'SECTION 3: Running validation checks...'

-- 3.1 Verify columns exist
DO $$
DECLARE
    col_count INTEGER;
BEGIN
    SELECT COUNT(*) INTO col_count
    FROM information_schema.columns
    WHERE table_name = 'user_companies' 
    AND column_name IN ('can_add_edit', 'can_delete');
    
    IF col_count = 2 THEN
        RAISE NOTICE '✓ All permission columns verified';
    ELSE
        RAISE EXCEPTION 'Validation failed: Expected 2 columns, found %', col_count;
    END IF;
END $$;

-- 3.2 Verify index exists
DO $$
BEGIN
    IF EXISTS (SELECT 1 FROM pg_indexes WHERE indexname = 'idx_uc_user_company') THEN
        RAISE NOTICE '✓ Composite index verified';
    ELSE
        RAISE EXCEPTION 'Validation failed: Index idx_uc_user_company not found';
    END IF;
END $$;

-- 3.3 Check for NULL values (should be none after migration)
DO $$
DECLARE
    null_count INTEGER;
BEGIN
    SELECT COUNT(*) INTO null_count
    FROM user_companies
    WHERE can_add_edit IS NULL OR can_delete IS NULL;
    
    IF null_count = 0 THEN
        RAISE NOTICE '✓ No NULL values in permission columns';
    ELSE
        RAISE WARNING 'Found % records with NULL permissions (will default to FALSE)', null_count;
    END IF;
END $$;

-- 3.4 Display summary statistics
DO $$
DECLARE
    total_users INTEGER;
    total_associations INTEGER;
    users_with_add_edit INTEGER;
    users_with_delete INTEGER;
BEGIN
    SELECT COUNT(DISTINCT user_id) INTO total_users FROM user_companies;
    SELECT COUNT(*) INTO total_associations FROM user_companies;
    SELECT COUNT(*) INTO users_with_add_edit FROM user_companies WHERE can_add_edit = TRUE;
    SELECT COUNT(*) INTO users_with_delete FROM user_companies WHERE can_delete = TRUE;
    
    RAISE NOTICE '';
    RAISE NOTICE '========================================';
    RAISE NOTICE 'DEPLOYMENT SUMMARY:';
    RAISE NOTICE '========================================';
    RAISE NOTICE 'Total users with company access: %', total_users;
    RAISE NOTICE 'Total user-company associations: %', total_associations;
    RAISE NOTICE 'Associations with ADD/EDIT permission: %', users_with_add_edit;
    RAISE NOTICE 'Associations with DELETE permission: %', users_with_delete;
    RAISE NOTICE '========================================';
    RAISE NOTICE '';
END $$;

\echo ''
\echo '=============================================='
\echo 'Deployment completed successfully!'
\echo 'Version 2.2.0 is now active.'
\echo '=============================================='
\echo ''
\echo 'Next steps:'
\echo '1. Clear application cache (if applicable)'
\echo '2. Verify user permissions in the application'
\echo '3. Test add/edit/delete functionality per company'
\echo '4. Monitor error logs for any issues'
\echo ''

COMMIT;

\echo '✓ Transaction committed successfully'
\echo ''
\echo 'Deployment Notes:'
\echo '- Users can now have different permissions for different companies'
\echo '- Edit existing users to configure per-company permissions'
\echo '- Global permissions (user_permissions table) are still maintained for backward compatibility'
\echo '- User list now filtered by role and company access as per requirements'
\echo ''

