appserver/database/init/02-remove-organizations.sql

-- Migration: Remove organizations (single-tenant architecture)
-- This migration converts the multi-tenant organization structure to single-tenant

-- Step 1: Drop foreign key constraints that reference organizations
ALTER TABLE api_keys DROP CONSTRAINT IF EXISTS api_keys_organization_id_fkey;
ALTER TABLE applications DROP CONSTRAINT IF EXISTS applications_organization_id_fkey;
ALTER TABLE audit_logs DROP CONSTRAINT IF EXISTS audit_logs_organization_id_fkey;

-- Step 2: Drop indexes related to organizations
DROP INDEX IF EXISTS idx_api_keys_org_id;
DROP INDEX IF EXISTS idx_applications_org_id;
DROP INDEX IF EXISTS idx_organization_members_org_id;
DROP INDEX IF EXISTS idx_organization_members_user_id;
DROP INDEX IF EXISTS idx_organizations_slug;
DROP INDEX IF EXISTS idx_audit_logs_org_id;

-- Step 3: Remove organization_id columns from tables
ALTER TABLE applications DROP COLUMN IF EXISTS organization_id;
ALTER TABLE api_keys DROP COLUMN IF EXISTS organization_id;
ALTER TABLE audit_logs DROP COLUMN IF EXISTS organization_id;

-- Step 4: Drop organization-related tables
DROP TABLE IF EXISTS organization_members CASCADE;
DROP TABLE IF EXISTS organizations CASCADE;

-- Step 5: Update dashboard stats view (if any)
-- No views to update in this case

-- Verification queries (commented out - uncomment to verify)
-- SELECT 'Applications' as table_name, COUNT(*) as count FROM applications;
-- SELECT 'API Keys' as table_name, COUNT(*) as count FROM api_keys;
-- SELECT 'Users' as table_name, COUNT(*) as count FROM users;