-- Security Update Migration
-- This migration adds proper security to the MCP Server ROI database

-- 1. Add user_id columns to all tables for proper user isolation
ALTER TABLE projects ADD COLUMN IF NOT EXISTS user_id UUID;
ALTER TABLE use_cases ADD COLUMN IF NOT EXISTS user_id UUID;
ALTER TABLE projections ADD COLUMN IF NOT EXISTS user_id UUID;
ALTER TABLE simulation_results ADD COLUMN IF NOT EXISTS user_id UUID;
ALTER TABLE actual_metrics ADD COLUMN IF NOT EXISTS user_id UUID;

-- 2. Create indexes on user_id columns for performance
CREATE INDEX IF NOT EXISTS idx_projects_user_id ON projects(user_id);
CREATE INDEX IF NOT EXISTS idx_use_cases_user_id ON use_cases(user_id);
CREATE INDEX IF NOT EXISTS idx_projections_user_id ON projections(user_id);
CREATE INDEX IF NOT EXISTS idx_simulation_results_user_id ON simulation_results(user_id);
CREATE INDEX IF NOT EXISTS idx_actual_metrics_user_id ON actual_metrics(user_id);

-- 3. Revoke dangerous permissions from anon role
REVOKE ALL ON ALL TABLES IN SCHEMA public FROM anon;
REVOKE ALL ON ALL SEQUENCES IN SCHEMA public FROM anon;

-- 4. Grant minimal permissions to anon (read-only on specific views)
-- Note: anon users should only access data through RLS policies
GRANT USAGE ON SCHEMA public TO anon;

-- 5. Grant appropriate permissions to authenticated users
GRANT USAGE ON SCHEMA public TO authenticated;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO authenticated;
GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO authenticated;

-- 6. Enable Row Level Security on all tables
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;
ALTER TABLE use_cases ENABLE ROW LEVEL SECURITY;
ALTER TABLE projections ENABLE ROW LEVEL SECURITY;
ALTER TABLE simulation_results ENABLE ROW LEVEL SECURITY;
ALTER TABLE actual_metrics ENABLE ROW LEVEL SECURITY;

-- 7. Create RLS policies for projects table
CREATE POLICY "Users can view their own projects" 
  ON projects FOR SELECT 
  USING (auth.uid() = user_id);

CREATE POLICY "Users can create their own projects" 
  ON projects FOR INSERT 
  WITH CHECK (auth.uid() = user_id);

CREATE POLICY "Users can update their own projects" 
  ON projects FOR UPDATE 
  USING (auth.uid() = user_id)
  WITH CHECK (auth.uid() = user_id);

CREATE POLICY "Users can delete their own projects" 
  ON projects FOR DELETE 
  USING (auth.uid() = user_id);

-- 8. Create RLS policies for use_cases table
CREATE POLICY "Users can view their own use cases" 
  ON use_cases FOR SELECT 
  USING (auth.uid() = user_id);

CREATE POLICY "Users can create use cases for their projects" 
  ON use_cases FOR INSERT 
  WITH CHECK (
    auth.uid() = user_id AND
    EXISTS (
      SELECT 1 FROM projects 
      WHERE projects.id = use_cases.project_id 
      AND projects.user_id = auth.uid()
    )
  );

CREATE POLICY "Users can update their own use cases" 
  ON use_cases FOR UPDATE 
  USING (auth.uid() = user_id)
  WITH CHECK (auth.uid() = user_id);

CREATE POLICY "Users can delete their own use cases" 
  ON use_cases FOR DELETE 
  USING (auth.uid() = user_id);

-- 9. Create RLS policies for projections table
CREATE POLICY "Users can view their own projections" 
  ON projections FOR SELECT 
  USING (auth.uid() = user_id);

CREATE POLICY "Users can create projections for their projects" 
  ON projections FOR INSERT 
  WITH CHECK (
    auth.uid() = user_id AND
    EXISTS (
      SELECT 1 FROM projects 
      WHERE projects.id = projections.project_id 
      AND projects.user_id = auth.uid()
    )
  );

CREATE POLICY "Users can update their own projections" 
  ON projections FOR UPDATE 
  USING (auth.uid() = user_id)
  WITH CHECK (auth.uid() = user_id);

CREATE POLICY "Users can delete their own projections" 
  ON projections FOR DELETE 
  USING (auth.uid() = user_id);

-- 10. Create RLS policies for simulation_results table
CREATE POLICY "Users can view their own simulation results" 
  ON simulation_results FOR SELECT 
  USING (auth.uid() = user_id);

CREATE POLICY "Users can create simulation results for their projections" 
  ON simulation_results FOR INSERT 
  WITH CHECK (
    auth.uid() = user_id AND
    EXISTS (
      SELECT 1 FROM projections 
      WHERE projections.id = simulation_results.projection_id 
      AND projections.user_id = auth.uid()
    )
  );

CREATE POLICY "Users can delete their own simulation results" 
  ON simulation_results FOR DELETE 
  USING (auth.uid() = user_id);

-- 11. Create RLS policies for actual_metrics table
CREATE POLICY "Users can view their own metrics" 
  ON actual_metrics FOR SELECT 
  USING (auth.uid() = user_id);

CREATE POLICY "Users can create metrics for their projections" 
  ON actual_metrics FOR INSERT 
  WITH CHECK (
    auth.uid() = user_id AND
    EXISTS (
      SELECT 1 FROM projections 
      WHERE projections.id = actual_metrics.projection_id 
      AND projections.user_id = auth.uid()
    )
  );

CREATE POLICY "Users can update their own metrics" 
  ON actual_metrics FOR UPDATE 
  USING (auth.uid() = user_id)
  WITH CHECK (auth.uid() = user_id);

CREATE POLICY "Users can delete their own metrics" 
  ON actual_metrics FOR DELETE 
  USING (auth.uid() = user_id);

-- 12. Update views to include user_id filtering
DROP VIEW IF EXISTS active_projects_summary;
CREATE VIEW active_projects_summary AS
SELECT 
  p.*,
  latest_proj.id as latest_projection_id,
  latest_proj.calculations->>'five_year_roi' as latest_roi,
  latest_proj.calculations->>'payback_period_months' as latest_payback_months,
  latest_proj.created_at as projection_date,
  COUNT(DISTINCT uc.id) as use_case_count
FROM projects p
LEFT JOIN LATERAL (
  SELECT * FROM projections 
  WHERE project_id = p.id 
  ORDER BY created_at DESC 
  LIMIT 1
) latest_proj ON true
LEFT JOIN use_cases uc ON uc.project_id = p.id
WHERE p.status = 'active'
  AND p.user_id = auth.uid()  -- Add user filtering
GROUP BY p.id, latest_proj.id, latest_proj.calculations, latest_proj.created_at;

DROP VIEW IF EXISTS project_performance;
CREATE VIEW project_performance AS
SELECT 
  p.id as project_id,
  p.project_name,
  p.client_name,
  proj.id as projection_id,
  proj.calculations->>'five_year_roi' as projected_roi,
  proj.calculations->>'payback_period_months' as projected_payback,
  am.period,
  am.metrics->>'cost_savings' as actual_cost_savings,
  am.metrics->>'user_adoption' as adoption_rate,
  am.tracked_at
FROM projects p
JOIN projections proj ON proj.project_id = p.id
LEFT JOIN actual_metrics am ON am.projection_id = proj.id
WHERE p.user_id = auth.uid()  -- Add user filtering
ORDER BY p.id, am.period;

-- 13. Create a function to automatically set user_id on insert
CREATE OR REPLACE FUNCTION set_user_id()
RETURNS TRIGGER AS $$
BEGIN
  NEW.user_id = auth.uid();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- 14. Create triggers to automatically set user_id
CREATE TRIGGER set_projects_user_id
  BEFORE INSERT ON projects
  FOR EACH ROW
  EXECUTE FUNCTION set_user_id();

CREATE TRIGGER set_use_cases_user_id
  BEFORE INSERT ON use_cases
  FOR EACH ROW
  EXECUTE FUNCTION set_user_id();

CREATE TRIGGER set_projections_user_id
  BEFORE INSERT ON projections
  FOR EACH ROW
  EXECUTE FUNCTION set_user_id();

CREATE TRIGGER set_simulation_results_user_id
  BEFORE INSERT ON simulation_results
  FOR EACH ROW
  EXECUTE FUNCTION set_user_id();

CREATE TRIGGER set_actual_metrics_user_id
  BEFORE INSERT ON actual_metrics
  FOR EACH ROW
  EXECUTE FUNCTION set_user_id();

-- 15. Add comments explaining the security model
COMMENT ON COLUMN projects.user_id IS 'UUID of the user who owns this project';
COMMENT ON COLUMN use_cases.user_id IS 'UUID of the user who owns this use case';
COMMENT ON COLUMN projections.user_id IS 'UUID of the user who owns this projection';
COMMENT ON COLUMN simulation_results.user_id IS 'UUID of the user who owns this simulation result';
COMMENT ON COLUMN actual_metrics.user_id IS 'UUID of the user who owns this metric';