-- Meteoblue API Cache Database
CREATE TABLE IF NOT EXISTS meteoblue_cache (
    id INT AUTO_INCREMENT PRIMARY KEY,
    location_name VARCHAR(100) NOT NULL,
    latitude DECIMAL(10, 6) NOT NULL,
    longitude DECIMAL(10, 6) NOT NULL,
    altitude INT NOT NULL,
    api_packages TEXT NOT NULL,
    request_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    data_timestamp TIMESTAMP NOT NULL,
    raw_json LONGTEXT NOT NULL,
    metadata JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_location (location_name),
    INDEX idx_coordinates (latitude, longitude),
    INDEX idx_timestamps (data_timestamp, request_timestamp),
    UNIQUE KEY unique_location_time (location_name, data_timestamp)
);

-- Parsed weather data table for easier querying
CREATE TABLE IF NOT EXISTS weather_data (
    id INT AUTO_INCREMENT PRIMARY KEY,
    cache_id INT NOT NULL,
    location_name VARCHAR(100) NOT NULL,
    data_type ENUM('current', 'hourly', 'daily') NOT NULL,
    timestamp DATETIME NOT NULL,
    temperature DECIMAL(5, 2),
    temperature_min DECIMAL(5, 2),
    temperature_max DECIMAL(5, 2),
    felttemperature DECIMAL(5, 2),
    humidity INT,
    windspeed DECIMAL(5, 2),
    winddirection INT,
    precipitation DECIMAL(5, 2),
    cloudcover INT,
    visibility INT,
    uvindex INT,
    pictocode INT,
    pressure DECIMAL(7, 2),
    ghi_instant DECIMAL(8, 2),
    dni_instant DECIMAL(8, 2),
    dif_instant DECIMAL(8, 2),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (cache_id) REFERENCES meteoblue_cache(id) ON DELETE CASCADE,
    INDEX idx_location_type (location_name, data_type),
    INDEX idx_timestamp (timestamp),
    INDEX idx_cache_id (cache_id)
);

-- API usage tracking
CREATE TABLE IF NOT EXISTS api_usage (
    id INT AUTO_INCREMENT PRIMARY KEY,
    location_name VARCHAR(100) NOT NULL,
    api_endpoint TEXT NOT NULL,
    request_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    response_size INT,
    cache_hit BOOLEAN DEFAULT FALSE,
    error_message TEXT,
    http_status INT,
    INDEX idx_location_time (location_name, request_timestamp),
    INDEX idx_cache_hit (cache_hit)
);

-- Settings for cache behavior
CREATE TABLE IF NOT EXISTS cache_settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    setting_key VARCHAR(100) NOT NULL UNIQUE,
    setting_value TEXT NOT NULL,
    description TEXT,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Insert default cache settings
INSERT IGNORE INTO cache_settings (setting_key, setting_value, description) VALUES
('cache_duration_minutes', '60', 'How long to cache API responses in minutes'),
('max_cache_entries', '1000', 'Maximum number of cache entries to keep'),
('enable_cache', '1', 'Enable/disable caching system'),
('auto_cleanup', '1', 'Automatically clean old cache entries');