-- Add tables for AI route generation feature

-- 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
  USING (auth.uid() = user_id);

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

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

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

-- 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
  USING (auth.uid() = user_id);

CREATE POLICY "Users can insert their own API usage"
  ON api_usage FOR INSERT
  WITH CHECK (auth.uid() = user_id);

-- Function to update updated_at timestamp
CREATE OR REPLACE FUNCTION update_generated_routes_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Trigger for updated_at
CREATE TRIGGER update_generated_routes_updated_at
  BEFORE UPDATE ON generated_routes
  FOR EACH ROW
  EXECUTE FUNCTION update_generated_routes_updated_at();

COMMENT ON TABLE generated_routes IS 'Stores AI-generated route recommendations for trips';
COMMENT ON TABLE api_usage IS 'Tracks API usage for rate limiting and analytics';
