Database Migrations

# Install Supabase CLI
npm install -g supabase

# Link to project
supabase link --project-ref your-project-ref

# Push migrations
supabase db push

-- Core indexes for enterprise isolation
CREATE INDEX IF NOT EXISTS idx_bras_enterprise 
ON business_risk_assessments(enterprise_id);

CREATE INDEX IF NOT EXISTS idx_legal_entities_enterprise 
ON legal_entities(enterprise_id);

-- BRA query optimization
CREATE INDEX IF NOT EXISTS idx_bra_risk_scenarios_bra 
ON bra_risk_scenarios(bra_id);

CREATE INDEX IF NOT EXISTS idx_bra_risk_ratings_bra 
ON bra_risk_ratings(bra_id);

CREATE INDEX IF NOT EXISTS idx_bra_risk_ratings_scenario 
ON bra_risk_ratings(bra_risk_scenario_id);

-- Control query optimization
CREATE INDEX IF NOT EXISTS idx_sub_controls_key_control 
ON sub_controls(key_control_id);

-- Action query optimization
CREATE INDEX IF NOT EXISTS idx_mitigation_actions_status 
ON mitigation_actions(status);

CREATE INDEX IF NOT EXISTS idx_mitigation_actions_due_date 
ON mitigation_actions(due_date);

-- Audit log optimization
CREATE INDEX IF NOT EXISTS idx_audit_log_enterprise 
ON audit_log(enterprise_id);

CREATE INDEX IF NOT EXISTS idx_audit_log_created 
ON audit_log(created_at);

-- Enable RLS
ALTER TABLE enterprises ENABLE ROW LEVEL SECURITY;
ALTER TABLE enterprise_users ENABLE ROW LEVEL SECURITY;
ALTER TABLE business_risk_assessments ENABLE ROW LEVEL SECURITY;
ALTER TABLE bra_risk_scenarios ENABLE ROW LEVEL SECURITY;
ALTER TABLE bra_risk_ratings ENABLE ROW LEVEL SECURITY;
ALTER TABLE mitigation_actions ENABLE ROW LEVEL SECURITY;
ALTER TABLE legal_entities ENABLE ROW LEVEL SECURITY;
ALTER TABLE business_units ENABLE ROW LEVEL SECURITY;
ALTER TABLE products ENABLE ROW LEVEL SECURITY;
ALTER TABLE risk_scenarios ENABLE ROW LEVEL SECURITY;
ALTER TABLE key_controls ENABLE ROW LEVEL SECURITY;
ALTER TABLE sub_controls ENABLE ROW LEVEL SECURITY;
ALTER TABLE audit_log ENABLE ROW LEVEL SECURITY;

-- Example policy: Users can only access their enterprise data
CREATE POLICY "enterprise_isolation" ON business_risk_assessments
FOR ALL
USING (
    enterprise_id IN (
        SELECT enterprise_id 
        FROM enterprise_users 
        WHERE user_id = auth.uid()
    )
);

-- Automatic timestamp updates
CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Apply to tables
CREATE TRIGGER update_bras_updated_at
BEFORE UPDATE ON business_risk_assessments
FOR EACH ROW EXECUTE FUNCTION update_updated_at();

CREATE TRIGGER update_legal_entities_updated_at
BEFORE UPDATE ON legal_entities
FOR EACH ROW EXECUTE FUNCTION update_updated_at();

-- Add triggers to all tables with updated_at column

-- Automatic risk score calculation
CREATE OR REPLACE FUNCTION calculate_risk_score()
RETURNS TRIGGER AS $$
BEGIN
    NEW.risk_score = NEW.impact_score * NEW.likelihood_score;
    NEW.risk_level = CASE
        WHEN NEW.risk_score <= 4 THEN 'Low'
        WHEN NEW.risk_score <= 9 THEN 'Medium'
        WHEN NEW.risk_score <= 15 THEN 'High'
        ELSE 'Very High'
    END;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER calculate_rating_score
BEFORE INSERT OR UPDATE ON bra_risk_ratings
FOR EACH ROW EXECUTE FUNCTION calculate_risk_score();

SELECT 
    schemaname,
    tablename,
    indexname,
    indexdef
FROM pg_indexes
WHERE schemaname = 'public'
ORDER BY tablename, indexname;

SELECT 
    tablename,
    rowsecurity
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY tablename;

SELECT 
    trigger_name,
    event_manipulation,
    event_object_table,
    action_statement
FROM information_schema.triggers
WHERE trigger_schema = 'public';

-- Rollback: Remove index
DROP INDEX IF EXISTS idx_bras_enterprise;

-- Rollback: Remove trigger
DROP TRIGGER IF EXISTS update_bras_updated_at ON business_risk_assessments;

-- Rollback: Disable RLS (caution!)
ALTER TABLE business_risk_assessments DISABLE ROW LEVEL SECURITY;