-- Migration: Create weather_api_data table
CREATE TABLE IF NOT EXISTS weather_api_data (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER NOT NULL,
    location_id INTEGER NOT NULL,
    api_type TEXT NOT NULL DEFAULT 'openweathermap',
    data TEXT NOT NULL, -- JSON data from the API
    fetched_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY(location_id) REFERENCES locations(id) ON DELETE CASCADE
);
CREATE INDEX IF NOT EXISTS idx_weather_api_data_user_id ON weather_api_data(user_id);
CREATE INDEX IF NOT EXISTS idx_weather_api_data_location_id ON weather_api_data(location_id);
CREATE INDEX IF NOT EXISTS idx_weather_api_data_fetched_at ON weather_api_data(fetched_at);
CREATE INDEX IF NOT EXISTS idx_weather_api_data_api_type ON weather_api_data(api_type);