-- Add route generation tables and API usage tracking

-- Generated routes table
CREATE TABLE IF NOT EXISTS generated_routes (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  trip_id UUID REFERENCES trips(id) ON DELETE CASCADE,
  user_id UUID REFERENCES auth.users(id),
  preferences JSONB NOT NULL,
  route_data JSONB NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- API usage tracking table
CREATE TABLE IF NOT EXISTS api_usage (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id UUID REFERENCES auth.users(id),
  api_type TEXT NOT NULL,
  endpoint TEXT NOT NULL,
  request_count INTEGER DEFAULT 1,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Indexes for performance
CREATE INDEX IF NOT EXISTS idx_generated_routes_trip_id ON generated_routes(trip_id);
CREATE INDEX IF NOT EXISTS idx_generated_routes_user_id ON generated_routes(user_id);
CREATE INDEX IF NOT EXISTS idx_api_usage_user_id ON api_usage(user_id);
CREATE INDEX IF NOT EXISTS idx_api_usage_created_at ON api_usage(created_at);

-- RLS Policies for generated_routes
ALTER TABLE generated_routes ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Users can view their own generated routes"
  ON generated_routes FOR SELECT
  TO authenticated
  USING (user_id = auth.uid());

CREATE POLICY "Users can insert their own generated routes"
  ON generated_routes FOR INSERT
  TO authenticated
  WITH CHECK (user_id = auth.uid());

CREATE POLICY "Users can update their own generated routes"
  ON generated_routes FOR UPDATE
  TO authenticated
  USING (user_id = auth.uid())
  WITH CHECK (user_id = auth.uid());

CREATE POLICY "Users can delete their own generated routes"
  ON generated_routes FOR DELETE
  TO authenticated
  USING (user_id = auth.uid());

-- RLS Policies for api_usage
ALTER TABLE api_usage ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Users can view their own API usage"
  ON api_usage FOR SELECT
  TO authenticated
  USING (user_id = auth.uid());

-- Only service_role can insert API usage records
CREATE POLICY "Service role can insert API usage"
  ON api_usage FOR INSERT
  WITH CHECK (auth.role() = 'service_role');
