-- =====================================================
-- PRODUCTION SCHEMA UPDATE SCRIPT
-- Version: 2.0.0 - Company-Wise Masters Implementation
-- Date: February 2026
-- =====================================================
-- 
-- IMPORTANT: 
-- 1. BACKUP YOUR DATABASE BEFORE RUNNING THIS SCRIPT
-- 2. Run this script during maintenance window
-- 3. Test in staging environment first
--
-- Usage:
--   psql -U postgres -d your_database_name -f production_update_v2.0.0.sql
--
-- =====================================================

BEGIN;

-- =====================================================
-- SECTION 1: ADD company_id TO MASTER TABLES
-- =====================================================

-- 1.1 Add company_id to sub_type (document heads)
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;
END $$;

-- 1.2 Add is_enabled to sub_type
DO $$
BEGIN
    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.3 Add company_id to authority
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.4 Add company_id to documents
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.5 Add company_id and is_enabled to mandatory_documents
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;
END $$;

DO $$
BEGIN
    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.6 Add company_id and is_enabled to sub_type_reminders
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;
END $$;

DO $$
BEGIN
    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.7 Add company_id to document_groups
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.8 Add company_id to holidays
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 $$;

-- =====================================================
-- SECTION 2: ADD company_id TO USER ASSIGNMENT TABLES
-- =====================================================

-- 2.1 Add company_id to user_authorities
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 $$;

-- 2.2 Add company_id to user_document_types
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 $$;

-- =====================================================
-- SECTION 3: CREATE INDEXES FOR PERFORMANCE
-- =====================================================

-- 3.1 Index on sub_type.company_id
CREATE INDEX IF NOT EXISTS idx_sub_type_company_id ON sub_type(company_id);

-- 3.2 Index on sub_type.is_enabled
CREATE INDEX IF NOT EXISTS idx_sub_type_is_enabled ON sub_type(is_enabled);

-- 3.3 Index on authority.company_id
CREATE INDEX IF NOT EXISTS idx_authority_company_id ON authority(company_id);

-- 3.4 Index on documents.company_id
CREATE INDEX IF NOT EXISTS idx_documents_company_id ON documents(company_id);

-- 3.5 Index on mandatory_documents.company_id
CREATE INDEX IF NOT EXISTS idx_mandatory_documents_company_id ON mandatory_documents(company_id);

-- 3.6 Index on sub_type_reminders.company_id
CREATE INDEX IF NOT EXISTS idx_sub_type_reminders_company_id ON sub_type_reminders(company_id);

-- 3.7 Index on document_groups.company_id
CREATE INDEX IF NOT EXISTS idx_document_groups_company_id ON document_groups(company_id);

-- 3.8 Index on holidays.company_id
CREATE INDEX IF NOT EXISTS idx_holidays_company_id ON holidays(company_id);

-- 3.9 Composite indexes for user assignment tables
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);

RAISE NOTICE 'Indexes created successfully';

-- =====================================================
-- SECTION 4: ADD FOREIGN KEY CONSTRAINTS (OPTIONAL)
-- =====================================================
-- Uncomment if you want to enforce referential integrity
-- Note: May fail if there's existing data without proper references

/*
ALTER TABLE sub_type 
ADD CONSTRAINT fk_sub_type_company 
FOREIGN KEY (company_id) REFERENCES company(id) ON DELETE SET NULL;

ALTER TABLE authority 
ADD CONSTRAINT fk_authority_company 
FOREIGN KEY (company_id) REFERENCES company(id) ON DELETE SET NULL;

ALTER TABLE documents 
ADD CONSTRAINT fk_documents_company 
FOREIGN KEY (company_id) REFERENCES company(id) ON DELETE SET NULL;

ALTER TABLE mandatory_documents 
ADD CONSTRAINT fk_mandatory_documents_company 
FOREIGN KEY (company_id) REFERENCES company(id) ON DELETE SET NULL;
*/

-- =====================================================
-- SECTION 5: ADD COMMENTS TO DEPRECATED TABLES
-- =====================================================

COMMENT ON TABLE company_document_heads IS 'DEPRECATED v2.0.0: Use sub_type.company_id and sub_type.is_enabled instead';
COMMENT ON TABLE company_mandatory_documents IS 'DEPRECATED v2.0.0: Use mandatory_documents.company_id and mandatory_documents.is_enabled instead';
COMMENT ON TABLE company_authorities IS 'DEPRECATED v2.0.0: Use authority.company_id instead';
COMMENT ON TABLE company_documents IS 'DEPRECATED v2.0.0: Use documents.company_id instead';
COMMENT ON TABLE company_document_groups IS 'DEPRECATED v2.0.0: Use document_groups.company_id instead';
COMMENT ON TABLE company_document_head_reminders IS 'DEPRECATED v2.0.0: Use sub_type_reminders.company_id instead';

RAISE NOTICE 'Deprecated table comments added';

-- =====================================================
-- SECTION 6: VERIFY SCHEMA CHANGES
-- =====================================================

DO $$
DECLARE
    col_count INTEGER;
BEGIN
    -- Count new columns
    SELECT COUNT(*) INTO col_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 col_count >= 9 THEN
        RAISE NOTICE 'VERIFICATION PASSED: All company_id columns added successfully (% tables)', col_count;
    ELSE
        RAISE WARNING 'VERIFICATION WARNING: Expected 9 company_id columns, found %', col_count;
    END IF;
END $$;

COMMIT;

-- =====================================================
-- POST-MIGRATION VERIFICATION QUERIES
-- Run these manually to verify the migration
-- =====================================================

/*
-- Check sub_type structure
SELECT column_name, data_type, is_nullable, column_default
FROM information_schema.columns
WHERE table_name = 'sub_type'
AND column_name IN ('company_id', 'is_enabled')
ORDER BY column_name;

-- Check indexes
SELECT indexname, indexdef 
FROM pg_indexes 
WHERE tablename IN ('sub_type', 'authority', 'documents', 'mandatory_documents')
AND indexname LIKE '%company%';

-- Count records without company_id (for data migration planning)
SELECT 'sub_type' as table_name, COUNT(*) as null_company_id 
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;
*/

-- =====================================================
-- END OF MIGRATION SCRIPT
-- =====================================================

\echo ''
\echo '=============================================='
\echo 'MIGRATION COMPLETED SUCCESSFULLY'
\echo 'Version: 2.0.0 - Company-Wise Masters'
\echo '=============================================='
\echo ''
\echo 'Next Steps:'
\echo '1. Verify indexes: SELECT * FROM pg_indexes WHERE indexname LIKE ''%company%'';'
\echo '2. Check deprecated tables have comments'
\echo '3. Run application test cases'
\echo '4. Monitor application logs for any SQL errors'
\echo ''

