-- =====================================================
-- Company-wise Masters Implementation
-- Add company_id to all master and user assignment tables
-- =====================================================

-- 1. AUTHORITY - Add company_id
ALTER TABLE authority ADD COLUMN IF NOT EXISTS company_id INTEGER REFERENCES company(id) ON DELETE CASCADE;
CREATE INDEX IF NOT EXISTS idx_authority_company ON authority(company_id);

-- 2. DOCUMENTS - Add company_id
ALTER TABLE documents ADD COLUMN IF NOT EXISTS company_id INTEGER REFERENCES company(id) ON DELETE CASCADE;
CREATE INDEX IF NOT EXISTS idx_documents_company ON documents(company_id);

-- 3. SUB_TYPE (Document Head) - Add company_id
ALTER TABLE sub_type ADD COLUMN IF NOT EXISTS company_id INTEGER REFERENCES company(id) ON DELETE CASCADE;
CREATE INDEX IF NOT EXISTS idx_sub_type_company ON sub_type(company_id);

-- 4. DOCUMENT_GROUPS - Add company_id
ALTER TABLE document_groups ADD COLUMN IF NOT EXISTS company_id INTEGER REFERENCES company(id) ON DELETE CASCADE;
CREATE INDEX IF NOT EXISTS idx_document_groups_company ON document_groups(company_id);

-- 5. HOLIDAYS - Add company_id
ALTER TABLE holidays ADD COLUMN IF NOT EXISTS company_id INTEGER REFERENCES company(id) ON DELETE CASCADE;
CREATE INDEX IF NOT EXISTS idx_holidays_company ON holidays(company_id);

-- 6. MANDATORY_DOCUMENTS - Add company_id
ALTER TABLE mandatory_documents ADD COLUMN IF NOT EXISTS company_id INTEGER REFERENCES company(id) ON DELETE CASCADE;
CREATE INDEX IF NOT EXISTS idx_mandatory_documents_company ON mandatory_documents(company_id);

-- 7. USER_AUTHORITIES - Add company_id for company-scoped authority access
ALTER TABLE user_authorities ADD COLUMN IF NOT EXISTS company_id INTEGER REFERENCES company(id) ON DELETE CASCADE;
CREATE INDEX IF NOT EXISTS idx_ua_company ON user_authorities(company_id);

-- Drop old unique constraint if exists and create new one with company_id
ALTER TABLE user_authorities DROP CONSTRAINT IF EXISTS user_authorities_user_id_authority_id_key;
ALTER TABLE user_authorities ADD CONSTRAINT user_authorities_user_company_authority_key 
    UNIQUE (user_id, company_id, authority_id);

-- 8. USER_DOCUMENT_TYPES - Add company_id for company-scoped document head access
ALTER TABLE user_document_types ADD COLUMN IF NOT EXISTS company_id INTEGER REFERENCES company(id) ON DELETE CASCADE;
CREATE INDEX IF NOT EXISTS idx_udt_company ON user_document_types(company_id);

-- Drop old unique constraint if exists and create new one with company_id
ALTER TABLE user_document_types DROP CONSTRAINT IF EXISTS user_document_types_user_id_type_id_key;
ALTER TABLE user_document_types ADD CONSTRAINT user_document_types_user_company_type_key 
    UNIQUE (user_id, company_id, type_id);

-- =====================================================
-- Note: The following bridge tables are now DEPRECATED
-- Keep them for backward compatibility but don't use in new code:
-- - company_authorities
-- - company_documents  
-- - company_document_heads
-- - company_mandatory_documents
-- - company_document_groups
-- =====================================================

-- Add comments to mark deprecated tables
COMMENT ON TABLE company_authorities IS 'DEPRECATED: Use authority.company_id instead';
COMMENT ON TABLE company_documents IS 'DEPRECATED: Use documents.company_id instead';
COMMENT ON TABLE company_document_heads IS 'DEPRECATED: Use sub_type.company_id instead';
COMMENT ON TABLE company_mandatory_documents IS 'DEPRECATED: Use mandatory_documents.company_id instead';
COMMENT ON TABLE company_document_groups IS 'DEPRECATED: Use document_groups.company_id instead';

