-- Create activity_status table for n8n integration
CREATE TABLE IF NOT EXISTS activity_status (
  id INTEGER PRIMARY KEY DEFAULT 1,
  activity_type VARCHAR(50),
  activity_details VARCHAR(255),
  activity_project VARCHAR(255),
  activity_language VARCHAR(50),
  activity_repo VARCHAR(500),
  music_playing BOOLEAN DEFAULT FALSE,
  music_track VARCHAR(255),
  music_artist VARCHAR(255),
  music_album VARCHAR(255),
  music_platform VARCHAR(50),
  music_progress INTEGER,
  music_album_art VARCHAR(500),
  watching_title VARCHAR(255),
  watching_platform VARCHAR(50),
  watching_type VARCHAR(50),
  gaming_game VARCHAR(255),
  gaming_platform VARCHAR(50),
  gaming_status VARCHAR(50),
  status_mood VARCHAR(50),
  status_message VARCHAR(500),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Insert default row
INSERT INTO activity_status (id, updated_at)
VALUES (1, NOW())
ON CONFLICT (id) DO NOTHING;

-- Create function to automatically update updated_at
CREATE OR REPLACE FUNCTION update_activity_status_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Create trigger for automatic timestamp updates
DROP TRIGGER IF EXISTS activity_status_updated_at ON activity_status;
CREATE TRIGGER activity_status_updated_at
  BEFORE UPDATE ON activity_status
  FOR EACH ROW
  EXECUTE FUNCTION update_activity_status_updated_at();

-- Add helpful comment
COMMENT ON TABLE activity_status IS 'Stores real-time activity status from n8n workflows (coding, music, gaming, etc.)';