-- Add admin policies for suggestions management
-- Admin email: c4xapps@gmail.com

-- Function to check if user is admin
CREATE OR REPLACE FUNCTION is_admin()
RETURNS BOOLEAN AS $$
BEGIN
  RETURN (
    SELECT email = 'c4xapps@gmail.com'
    FROM auth.users
    WHERE id = auth.uid()
  );
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Policy to allow admin to update any suggestion
CREATE POLICY "Admin can update any suggestion"
  ON suggestions FOR UPDATE
  USING (is_admin());

-- Policy to allow admin to delete any suggestion
CREATE POLICY "Admin can delete any suggestion"
  ON suggestions FOR DELETE
  USING (is_admin());

-- Update existing status check constraint to match new status values
ALTER TABLE suggestions DROP CONSTRAINT IF EXISTS suggestions_status_check;
ALTER TABLE suggestions ADD CONSTRAINT suggestions_status_check 
  CHECK (status IN ('suggested', 'under_review', 'planned', 'in_progress', 'completed', 'rejected'));

COMMENT ON FUNCTION is_admin() IS 'Check if current user is admin (c4xapps@gmail.com)';
COMMENT ON POLICY "Admin can update any suggestion" ON suggestions IS 'Allow admin to update status and other fields of any suggestion';
COMMENT ON POLICY "Admin can delete any suggestion" ON suggestions IS 'Allow admin to delete any suggestion';