-- =====================================================
-- PRODUCTION DEPLOYMENT SCRIPT
-- Date: February 14, 2026
-- Version: 2.1.0
-- Description: Complete schema updates for multi-company support, 
--              user permissions, and document head deactivation tracking
-- =====================================================
-- 
-- 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
--
-- Usage:
--   psql -U postgres -d your_database_name -f production_deployment_2026_02_14.sql
--
-- Rollback:
--   See rollback_2026_02_14.sql (if needed)
--
-- =====================================================

\echo ''
\echo '=============================================='
\echo 'Starting Production Deployment: 2.1.0'
\echo 'Date: February 14, 2026'
\echo '=============================================='
\echo ''

BEGIN;

-- =====================================================
-- SECTION 1: MULTI-COMPANY SUPPORT - MASTER TABLES
-- Purpose: Add company_id to all master tables for company-wise data separation
-- =====================================================

\echo 'SECTION 1: Adding company_id to master tables...'

-- 1.1 Sub_type (Document Heads) - Add company_id and is_enabled
DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns 
                   WHERE table_name = 'sub_type' AND column_name = 'company_id') THEN
        ALTER TABLE sub_type ADD COLUMN company_id INTEGER;
        RAISE NOTICE '✓ Added company_id to sub_type';
    ELSE
        RAISE NOTICE '→ company_id already exists in sub_type';
    END IF;
    
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns 
                   WHERE table_name = 'sub_type' AND column_name = 'is_enabled') THEN
        ALTER TABLE sub_type ADD COLUMN is_enabled BOOLEAN DEFAULT TRUE;
        RAISE NOTICE '✓ Added is_enabled to sub_type';
    ELSE
        RAISE NOTICE '→ is_enabled already exists in sub_type';
    END IF;
END $$;

-- 1.2 Authority - Add company_id
DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns 
                   WHERE table_name = 'authority' AND column_name = 'company_id') THEN
        ALTER TABLE authority ADD COLUMN company_id INTEGER;
        RAISE NOTICE '✓ Added company_id to authority';
    ELSE
        RAISE NOTICE '→ company_id already exists in authority';
    END IF;
END $$;

-- 1.3 Documents - Add company_id
DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns 
                   WHERE table_name = 'documents' AND column_name = 'company_id') THEN
        ALTER TABLE documents ADD COLUMN company_id INTEGER;
        RAISE NOTICE '✓ Added company_id to documents';
    ELSE
        RAISE NOTICE '→ company_id already exists in documents';
    END IF;
END $$;

-- 1.4 Mandatory_documents - Add company_id and is_enabled
DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns 
                   WHERE table_name = 'mandatory_documents' AND column_name = 'company_id') THEN
        ALTER TABLE mandatory_documents ADD COLUMN company_id INTEGER;
        RAISE NOTICE '✓ Added company_id to mandatory_documents';
    ELSE
        RAISE NOTICE '→ company_id already exists in mandatory_documents';
    END IF;
    
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns 
                   WHERE table_name = 'mandatory_documents' AND column_name = 'is_enabled') THEN
        ALTER TABLE mandatory_documents ADD COLUMN is_enabled BOOLEAN DEFAULT TRUE;
        RAISE NOTICE '✓ Added is_enabled to mandatory_documents';
    ELSE
        RAISE NOTICE '→ is_enabled already exists in mandatory_documents';
    END IF;
END $$;

-- 1.5 Sub_type_reminders - Add company_id and is_enabled
DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns 
                   WHERE table_name = 'sub_type_reminders' AND column_name = 'company_id') THEN
        ALTER TABLE sub_type_reminders ADD COLUMN company_id INTEGER;
        RAISE NOTICE '✓ Added company_id to sub_type_reminders';
    ELSE
        RAISE NOTICE '→ company_id already exists in sub_type_reminders';
    END IF;
    
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns 
                   WHERE table_name = 'sub_type_reminders' AND column_name = 'is_enabled') THEN
        ALTER TABLE sub_type_reminders ADD COLUMN is_enabled BOOLEAN DEFAULT TRUE;
        RAISE NOTICE '✓ Added is_enabled to sub_type_reminders';
    ELSE
        RAISE NOTICE '→ is_enabled already exists in sub_type_reminders';
    END IF;
END $$;

-- 1.6 Document_groups - Add company_id
DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns 
                   WHERE table_name = 'document_groups' AND column_name = 'company_id') THEN
        ALTER TABLE document_groups ADD COLUMN company_id INTEGER;
        RAISE NOTICE '✓ Added company_id to document_groups';
    ELSE
        RAISE NOTICE '→ company_id already exists in document_groups';
    END IF;
END $$;

-- 1.7 Holidays - Add company_id
DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns 
                   WHERE table_name = 'holidays' AND column_name = 'company_id') THEN
        ALTER TABLE holidays ADD COLUMN company_id INTEGER;
        RAISE NOTICE '✓ Added company_id to holidays';
    ELSE
        RAISE NOTICE '→ company_id already exists in holidays';
    END IF;
END $$;

\echo '✓ SECTION 1 Complete: Master tables updated'
\echo ''

-- =====================================================
-- SECTION 2: USER PERMISSIONS & ROLE ACCESS
-- Purpose: Add granular per-user permissions and company-scoped access control
-- =====================================================

\echo 'SECTION 2: Setting up user permissions...'

-- 2.1 Create user_permissions table
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);

\echo '✓ Created user_permissions table'

-- 2.2 Migrate existing users to new permission structure
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)
  AND NOT EXISTS (SELECT 1 FROM user_permissions WHERE user_id = u.id)
ON CONFLICT (user_id) DO NOTHING;

\echo '✓ Migrated existing user permissions'

-- 2.3 Add company_id to user assignment tables
DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns 
                   WHERE table_name = 'user_authorities' AND column_name = 'company_id') THEN
        ALTER TABLE user_authorities ADD COLUMN company_id INTEGER;
        RAISE NOTICE '✓ Added company_id to user_authorities';
    ELSE
        RAISE NOTICE '→ company_id already exists in user_authorities';
    END IF;
END $$;

DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns 
                   WHERE table_name = 'user_document_types' AND column_name = 'company_id') THEN
        ALTER TABLE user_document_types ADD COLUMN company_id INTEGER;
        RAISE NOTICE '✓ Added company_id to user_document_types';
    ELSE
        RAISE NOTICE '→ company_id already exists in user_document_types';
    END IF;
END $$;

\echo '✓ SECTION 2 Complete: User permissions configured'
\echo ''

-- =====================================================
-- SECTION 3: DOCUMENT HEAD DEACTIVATION TRACKING
-- Purpose: Capture audit information when document heads are deactivated
-- =====================================================

\echo 'SECTION 3: Adding deactivation tracking fields...'

-- 3.1 Add deactivation_date column
DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns 
                   WHERE table_name = 'sub_type' AND column_name = 'deactivation_date') THEN
        ALTER TABLE sub_type ADD COLUMN deactivation_date DATE;
        RAISE NOTICE '✓ Added deactivation_date to sub_type';
    ELSE
        RAISE NOTICE '→ deactivation_date already exists in sub_type';
    END IF;
END $$;

-- 3.2 Add deactivation_remarks column
DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns 
                   WHERE table_name = 'sub_type' AND column_name = 'deactivation_remarks') THEN
        ALTER TABLE sub_type ADD COLUMN deactivation_remarks TEXT;
        RAISE NOTICE '✓ Added deactivation_remarks to sub_type';
    ELSE
        RAISE NOTICE '→ deactivation_remarks already exists in sub_type';
    END IF;
END $$;

-- 3.3 Add column comments
DO $$
BEGIN
    EXECUTE 'COMMENT ON COLUMN sub_type.deactivation_date IS ''Date when document head was deactivated (required when status = 0)''';
    EXECUTE 'COMMENT ON COLUMN sub_type.deactivation_remarks IS ''Reason/remarks for deactivation (required when status = 0, min 10 characters)''';
    RAISE NOTICE '✓ Added column comments for deactivation fields';
END $$;

\echo '✓ SECTION 3 Complete: Deactivation tracking enabled'
\echo ''

-- =====================================================
-- SECTION 4: CREATE PERFORMANCE INDEXES
-- Purpose: Optimize query performance for new columns
-- =====================================================

\echo 'SECTION 4: Creating performance indexes...'

-- 4.1 Indexes on company_id columns
CREATE INDEX IF NOT EXISTS idx_sub_type_company_id ON sub_type(company_id);
CREATE INDEX IF NOT EXISTS idx_sub_type_is_enabled ON sub_type(is_enabled);
CREATE INDEX IF NOT EXISTS idx_authority_company_id ON authority(company_id);
CREATE INDEX IF NOT EXISTS idx_documents_company_id ON documents(company_id);
CREATE INDEX IF NOT EXISTS idx_mandatory_documents_company_id ON mandatory_documents(company_id);
CREATE INDEX IF NOT EXISTS idx_sub_type_reminders_company_id ON sub_type_reminders(company_id);
CREATE INDEX IF NOT EXISTS idx_document_groups_company_id ON document_groups(company_id);
CREATE INDEX IF NOT EXISTS idx_holidays_company_id ON holidays(company_id);

-- 4.2 Composite indexes for user access control
CREATE INDEX IF NOT EXISTS idx_user_authorities_company ON user_authorities(user_id, company_id);
CREATE INDEX IF NOT EXISTS idx_user_document_types_company ON user_document_types(user_id, company_id);

-- 4.3 Index for deactivated document heads
CREATE INDEX IF NOT EXISTS idx_sub_type_status_deactivation ON sub_type(status, deactivation_date) 
WHERE status = '0';

\echo '✓ SECTION 4 Complete: Performance indexes created'
\echo ''

-- =====================================================
-- SECTION 5: UPDATE UNIQUE CONSTRAINTS
-- Purpose: Ensure data integrity with company-scoped uniqueness
-- =====================================================

\echo 'SECTION 5: Updating unique constraints...'

-- 5.1 Update user_authorities unique constraint
DO $$
BEGIN
    -- Drop old constraint if exists
    IF EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'user_authorities_user_id_authority_id_key') THEN
        ALTER TABLE user_authorities DROP CONSTRAINT user_authorities_user_id_authority_id_key;
        RAISE NOTICE '✓ Dropped old user_authorities constraint';
    END IF;
    
    -- Add new constraint with company_id
    IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'user_authorities_user_company_authority_key') THEN
        ALTER TABLE user_authorities ADD CONSTRAINT user_authorities_user_company_authority_key 
            UNIQUE (user_id, company_id, authority_id);
        RAISE NOTICE '✓ Added new user_authorities constraint with company_id';
    ELSE
        RAISE NOTICE '→ user_authorities constraint already exists';
    END IF;
END $$;

-- 5.2 Update user_document_types unique constraint
DO $$
BEGIN
    -- Drop old constraint if exists
    IF EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'user_document_types_user_id_type_id_key') THEN
        ALTER TABLE user_document_types DROP CONSTRAINT user_document_types_user_id_type_id_key;
        RAISE NOTICE '✓ Dropped old user_document_types constraint';
    END IF;
    
    -- Add new constraint with company_id
    IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'user_document_types_user_company_type_key') THEN
        ALTER TABLE user_document_types ADD CONSTRAINT user_document_types_user_company_type_key 
            UNIQUE (user_id, company_id, type_id);
        RAISE NOTICE '✓ Added new user_document_types constraint with company_id';
    ELSE
        RAISE NOTICE '→ user_document_types constraint already exists';
    END IF;
END $$;

\echo '✓ SECTION 5 Complete: Constraints updated'
\echo ''

-- =====================================================
-- SECTION 6: MARK DEPRECATED TABLES
-- Purpose: Document legacy tables that should no longer be used
-- =====================================================

\echo 'SECTION 6: Marking deprecated tables...'

-- Mark bridge tables as deprecated
DO $$
BEGIN
    EXECUTE 'COMMENT ON TABLE company_document_heads IS ''DEPRECATED v2.1.0: Use sub_type.company_id and sub_type.is_enabled instead''';
    EXECUTE 'COMMENT ON TABLE company_mandatory_documents IS ''DEPRECATED v2.1.0: Use mandatory_documents.company_id and mandatory_documents.is_enabled instead''';
    EXECUTE 'COMMENT ON TABLE company_authorities IS ''DEPRECATED v2.1.0: Use authority.company_id instead''';
    EXECUTE 'COMMENT ON TABLE company_documents IS ''DEPRECATED v2.1.0: Use documents.company_id instead''';
    EXECUTE 'COMMENT ON TABLE company_document_groups IS ''DEPRECATED v2.1.0: Use document_groups.company_id instead''';
    EXECUTE 'COMMENT ON TABLE company_document_head_reminders IS ''DEPRECATED v2.1.0: Use sub_type_reminders.company_id instead''';
    RAISE NOTICE '✓ Marked bridge tables as deprecated';
EXCEPTION 
    WHEN undefined_table THEN
        RAISE NOTICE '→ Some bridge tables do not exist (this is OK)';
END $$;

\echo '✓ SECTION 6 Complete: Deprecated tables marked'
\echo ''

-- =====================================================
-- SECTION 7: DATA INTEGRITY VERIFICATION
-- Purpose: Verify all schema changes were applied successfully
-- =====================================================

\echo 'SECTION 7: Verifying schema changes...'

DO $$
DECLARE
    company_id_count INTEGER;
    is_enabled_count INTEGER;
    deactivation_count INTEGER;
    permission_count INTEGER;
BEGIN
    -- Verify company_id columns
    SELECT COUNT(*) INTO company_id_count
    FROM information_schema.columns
    WHERE table_schema = 'public'
    AND column_name = 'company_id'
    AND table_name IN ('sub_type', 'authority', 'documents', 'mandatory_documents', 
                       'sub_type_reminders', 'document_groups', 'holidays',
                       'user_authorities', 'user_document_types');
    
    IF company_id_count >= 9 THEN
        RAISE NOTICE '✓ VERIFICATION PASSED: % company_id columns found', company_id_count;
    ELSE
        RAISE WARNING '⚠ VERIFICATION WARNING: Expected 9 company_id columns, found %', company_id_count;
    END IF;
    
    -- Verify is_enabled columns
    SELECT COUNT(*) INTO is_enabled_count
    FROM information_schema.columns
    WHERE table_schema = 'public'
    AND column_name = 'is_enabled'
    AND table_name IN ('sub_type', 'mandatory_documents', 'sub_type_reminders');
    
    IF is_enabled_count >= 3 THEN
        RAISE NOTICE '✓ VERIFICATION PASSED: % is_enabled columns found', is_enabled_count;
    ELSE
        RAISE WARNING '⚠ VERIFICATION WARNING: Expected 3 is_enabled columns, found %', is_enabled_count;
    END IF;
    
    -- Verify deactivation columns
    SELECT COUNT(*) INTO deactivation_count
    FROM information_schema.columns
    WHERE table_schema = 'public'
    AND table_name = 'sub_type'
    AND column_name IN ('deactivation_date', 'deactivation_remarks');
    
    IF deactivation_count = 2 THEN
        RAISE NOTICE '✓ VERIFICATION PASSED: Deactivation tracking columns found';
    ELSE
        RAISE WARNING '⚠ VERIFICATION WARNING: Expected 2 deactivation columns, found %', deactivation_count;
    END IF;
    
    -- Verify user_permissions table
    SELECT COUNT(*) INTO permission_count
    FROM information_schema.tables
    WHERE table_schema = 'public'
    AND table_name = 'user_permissions';
    
    IF permission_count = 1 THEN
        RAISE NOTICE '✓ VERIFICATION PASSED: user_permissions table exists';
    ELSE
        RAISE WARNING '⚠ VERIFICATION WARNING: user_permissions table not found';
    END IF;
    
    RAISE NOTICE '';
    RAISE NOTICE '============================================';
    RAISE NOTICE '✓ SCHEMA VERIFICATION COMPLETE';
    RAISE NOTICE '============================================';
END $$;

COMMIT;

-- =====================================================
-- POST-DEPLOYMENT VERIFICATION QUERIES
-- Run these manually after deployment to verify success
-- =====================================================

\echo ''
\echo '=============================================='
\echo '✓ DEPLOYMENT COMPLETED SUCCESSFULLY'
\echo 'Version: 2.1.0'
\echo 'Date: February 14, 2026'
\echo '=============================================='
\echo ''
\echo 'POST-DEPLOYMENT CHECKLIST:'
\echo '1. ✓ Run verification queries below'
\echo '2. ✓ Clear application cache'
\echo '3. ✓ Test user login and permissions'
\echo '4. ✓ Verify document head deactivation workflow'
\echo '5. ✓ Check dashboard tiles display correctly'
\echo '6. ✓ Monitor error logs for 24 hours'
\echo ''
\echo 'VERIFICATION QUERIES:'
\echo '-------------------------------------------'
\echo 'SELECT * FROM pg_indexes WHERE indexname LIKE ''%company%'';'
\echo 'SELECT COUNT(*) FROM user_permissions;'
\echo 'SELECT column_name FROM information_schema.columns WHERE table_name = ''sub_type'' AND column_name LIKE ''%deactivation%'';'
\echo ''

/*
-- =====================================================
-- MANUAL VERIFICATION QUERIES (Run after deployment)
-- =====================================================

-- 1. Check all new columns are present
SELECT table_name, column_name, data_type, is_nullable, column_default
FROM information_schema.columns
WHERE table_schema = 'public'
AND column_name IN ('company_id', 'is_enabled', 'deactivation_date', 'deactivation_remarks')
ORDER BY table_name, column_name;

-- 2. Check indexes were created
SELECT schemaname, tablename, indexname, indexdef
FROM pg_indexes
WHERE schemaname = 'public'
AND (indexname LIKE '%company%' OR indexname LIKE '%deactivation%')
ORDER BY tablename, indexname;

-- 3. Verify user_permissions table structure
SELECT column_name, data_type, is_nullable, column_default
FROM information_schema.columns
WHERE table_name = 'user_permissions'
ORDER BY ordinal_position;

-- 4. Count records without company_id (for future data migration)
SELECT 'sub_type' as table_name, COUNT(*) as records_without_company 
FROM sub_type WHERE company_id IS NULL
UNION ALL
SELECT 'authority', COUNT(*) FROM authority WHERE company_id IS NULL
UNION ALL
SELECT 'documents', COUNT(*) FROM documents WHERE company_id IS NULL
UNION ALL
SELECT 'mandatory_documents', COUNT(*) FROM mandatory_documents WHERE company_id IS NULL;

-- 5. Verify user permissions were migrated
SELECT 
    u.id, 
    u.user_name, 
    u.role_id,
    up.can_view,
    up.can_add_edit,
    up.can_delete
FROM users u
LEFT JOIN user_permissions up ON u.id = up.user_id
WHERE u.status = '1'
ORDER BY u.role_id, u.user_name;

-- 6. Check deprecated table comments
SELECT tablename, 
       obj_description(schemaname||'.'||tablename::regclass, 'pg_class') as comment
FROM pg_tables
WHERE schemaname = 'public'
AND tablename LIKE 'company_%'
ORDER BY tablename;
*/

-- =====================================================
-- END OF PRODUCTION DEPLOYMENT SCRIPT
-- =====================================================

