#!/bin/bash

# YouTube Playlist Downloader with yt-dlp and Database
# Downloads videos from multiple playlists, maintains a DB of downloaded videos,
# and automatically skips already-downloaded content

# Color output
RED='\033[0;31m'
GREEN='\033[0;32m'
YELLOW='\033[1;33m'
BLUE='\033[0;34m'
NC='\033[0m'

# Configuration file path
CONFIG_FILE="${XDG_CONFIG_HOME:-$HOME/.config}/youtube_downloader/config.json"
DEFAULT_CONFIG_DIR="$HOME/.youtube_downloader"

# Function to log messages
log_info() {
    echo -e "${GREEN}[INFO]${NC} $1" | tee -a "$LOG_FILE"
}

log_error() {
    echo -e "${RED}[ERROR]${NC} $1" | tee -a "$LOG_FILE"
}

log_warning() {
    echo -e "${YELLOW}[WARNING]${NC} $1" | tee -a "$LOG_FILE"
}

log_debug() {
    echo -e "${BLUE}[DEBUG]${NC} $1" | tee -a "$LOG_FILE"
}

# Load configuration from JSON file
load_config() {
    if [ ! -f "$CONFIG_FILE" ]; then
        log_error "Config file not found: $CONFIG_FILE"
        echo "Please create the config file first. You can copy the example from the script comments."
        exit 1
    fi
    
    # Check if jq is installed
    if ! command -v jq &> /dev/null; then
        log_error "jq is not installed. Please install it first."
        echo "On Ubuntu/Debian: sudo apt-get install jq"
        echo "On macOS: brew install jq"
        exit 1
    fi
    
    # Parse configuration
    DB_DIR=$(jq -r '.general.db_dir' "$CONFIG_FILE" | sed "s|\$HOME|$HOME|g")
    DOWNLOAD_DIR=$(jq -r '.general.temp_download_dir' "$CONFIG_FILE" | sed "s|\$HOME|$HOME|g")
    VIDEO_FORMAT=$(jq -r '.general.video_format' "$CONFIG_FILE")
    OUTPUT_TEMPLATE=$(jq -r '.general.output_template' "$CONFIG_FILE")
    AUDIO_ONLY=$(jq -r '.general.audio_only' "$CONFIG_FILE")
    DOWNLOAD_DELAY=$(jq -r '.general.download_delay' "$CONFIG_FILE")
    
    # Set derived paths
    DB_FILE="$DB_DIR/downloads.db"
    LOG_FILE="$DB_DIR/downloader.log"
    
    log_debug "Configuration loaded from: $CONFIG_FILE"
}

# Get playlist count
get_playlist_count() {
    jq -r '.playlists | length' "$CONFIG_FILE"
}

# Get specific playlist data
get_playlist_data() {
    local index="$1"
    local field="$2"
    jq -r ".playlists[$index].$field" "$CONFIG_FILE"
}

# Get all enabled playlists
get_enabled_playlists() {
    jq -r '.playlists[] | select(.enabled == true) | [.name, .url, .destination] | @tsv' "$CONFIG_FILE"
}

# Initialize database
init_database() {
    # Create directory if it doesn't exist
    mkdir -p "$DB_DIR" || {
        log_error "Failed to create database directory: $DB_DIR"
        exit 1
    }
    
    # Check if sqlite3 is installed
    if ! command -v sqlite3 &> /dev/null; then
        log_error "sqlite3 is not installed. Please install it first."
        echo "On Ubuntu/Debian: sudo apt-get install sqlite3"
        echo "On macOS: brew install sqlite3"
        exit 1
    fi
    
    # Create database and tables if they don't exist
    sqlite3 "$DB_FILE" <<EOF
CREATE TABLE IF NOT EXISTS downloaded_videos (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    video_id TEXT UNIQUE NOT NULL,
    title TEXT NOT NULL,
    url TEXT NOT NULL,
    playlist_name TEXT,
    download_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    file_path TEXT,
    file_size INTEGER,
    status TEXT DEFAULT 'completed'
);

CREATE TABLE IF NOT EXISTS download_sessions (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    playlist_name TEXT NOT NULL,
    playlist_url TEXT NOT NULL,
    session_start TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    session_end TIMESTAMP,
    videos_downloaded INTEGER DEFAULT 0,
    videos_skipped INTEGER DEFAULT 0,
    errors INTEGER DEFAULT 0
);

CREATE INDEX IF NOT EXISTS idx_video_id ON downloaded_videos(video_id);
CREATE INDEX IF NOT EXISTS idx_status ON downloaded_videos(status);
CREATE INDEX IF NOT EXISTS idx_playlist_name ON downloaded_videos(playlist_name);
EOF
    
    log_info "Database initialized at: $DB_FILE"
}

# Check if video is already downloaded
is_video_downloaded() {
    local video_id="$1"
    local result=$(sqlite3 "$DB_FILE" "SELECT COUNT(*) FROM downloaded_videos WHERE video_id='$video_id' AND status='completed';")
    [ "$result" -gt 0 ]
}

# Add video to database
add_to_database() {
    local video_id="$1"
    local title="$2"
    local url="$3"
    local playlist_name="$4"
    local file_path="$5"
    local file_size="$6"
    
    # Escape single quotes in title
    title="${title//\'/\'\'}"
    
    sqlite3 "$DB_FILE" <<EOF
INSERT OR REPLACE INTO downloaded_videos (video_id, title, url, playlist_name, file_path, file_size, status)
VALUES ('$video_id', '$title', '$url', '$playlist_name', '$file_path', $file_size, 'completed');
EOF
    
    log_debug "Added to database: $title (ID: $video_id)"
}

# Get list of all videos in playlist with their IDs
get_playlist_videos() {
    local playlist_url="$1"
    yt-dlp --quiet --no-warnings --print "%(id)s|%(title)s|%(webpage_url)s" \
        --flat-playlist "$playlist_url" 2>/dev/null
}

# Start a new download session
start_session() {
    local playlist_name="$1"
    local playlist_url="$2"
    
    local session_id=$(sqlite3 "$DB_FILE" \
        "INSERT INTO download_sessions (playlist_name, playlist_url) VALUES ('$playlist_name', '$playlist_url'); SELECT last_insert_rowid();")
    echo "$session_id"
}

# Update session with results
end_session() {
    local session_id="$1"
    local downloaded="$2"
    local skipped="$3"
    local errors="$4"
    
    sqlite3 "$DB_FILE" <<EOF
UPDATE download_sessions
SET session_end=CURRENT_TIMESTAMP, 
    videos_downloaded=$downloaded,
    videos_skipped=$skipped,
    errors=$errors
WHERE id=$session_id;
EOF
}

# Get database statistics
show_stats() {
    log_info "===== Database Statistics ====="
    
    local total=$(sqlite3 "$DB_FILE" "SELECT COUNT(*) FROM downloaded_videos WHERE status='completed';")
    local total_size=$(sqlite3 "$DB_FILE" "SELECT SUM(file_size) FROM downloaded_videos WHERE status='completed';")
    local last_download=$(sqlite3 "$DB_FILE" "SELECT download_date FROM downloaded_videos WHERE status='completed' ORDER BY download_date DESC LIMIT 1;")
    local total_sessions=$(sqlite3 "$DB_FILE" "SELECT COUNT(*) FROM download_sessions;")
    
    echo "Total videos downloaded: $total"
    echo "Total storage used: $(numfmt --to=iec-i --suffix=B $total_size 2>/dev/null || echo "$total_size bytes")"
    echo "Last download: $last_download"
    echo "Total download sessions: $total_sessions"
    echo ""
    
    # Show statistics per playlist
    log_info "===== Per-Playlist Statistics ====="
    sqlite3 -header -column "$DB_FILE" \
        "SELECT playlist_name, COUNT(*) as videos, ROUND(SUM(file_size)/1024/1024, 2) as size_mb FROM downloaded_videos WHERE status='completed' GROUP BY playlist_name;"
}

# List recently downloaded videos
list_recent() {
    local limit="${1:-10}"
    log_info "===== Last $limit Downloads ====="
    
    sqlite3 -header -column "$DB_FILE" \
        "SELECT playlist_name, title, download_date FROM downloaded_videos WHERE status='completed' ORDER BY download_date DESC LIMIT $limit;"
}

# Validate inputs
validate_setup() {
    local destination_dir="$1"
    
    if [ ! -d "$destination_dir" ]; then
        log_warning "Destination directory does not exist. Creating: $destination_dir"
        mkdir -p "$destination_dir" || {
            log_error "Failed to create destination directory: $destination_dir"
            return 1
        }
    fi
    
    mkdir -p "$DOWNLOAD_DIR" || {
        log_error "Failed to create download directory: $DOWNLOAD_DIR"
        return 1
    }
    
    return 0
}

# Main download function for a single playlist
download_playlist() {
    local playlist_name="$1"
    local playlist_url="$2"
    local destination_dir="$3"
    
    log_info "=========================================="
    log_info "Starting download for: $playlist_name"
    log_info "Playlist URL: $playlist_url"
    log_info "Destination: $destination_dir"
    log_info "=========================================="
    
    # Validate setup
    if ! validate_setup "$destination_dir"; then
        return 1
    fi
    
    local session_id=$(start_session "$playlist_name" "$playlist_url")
    log_debug "Session ID: $session_id"
    
    local download_count=0
    local skip_count=0
    local error_count=0
    
    # Get all videos in the playlist
    local playlist_data=$(get_playlist_videos "$playlist_url")
    
    if [ -z "$playlist_data" ]; then
        log_error "Failed to fetch playlist. Check URL and internet connection."
        end_session "$session_id" 0 0 1
        return 1
    fi
    
    # Process each video
    while IFS='|' read -r video_id title video_url; do
        [ -z "$video_id" ] && continue
        
        if is_video_downloaded "$video_id"; then
            log_info "Skipping (already downloaded): $title"
            skip_count=$((skip_count + 1))
            continue
        fi
        
        log_info "Downloading: $title (ID: $video_id)"
        
        # Download the video
        local temp_file="$DOWNLOAD_DIR/${title}.%(ext)s"
        
        if yt-dlp \
            -f "$VIDEO_FORMAT" \
            -o "$temp_file" \
            --quiet \
            --no-warnings \
            "$video_url" 2>/dev/null; then
            
            # Find the actual downloaded file
            local downloaded_file=$(find "$DOWNLOAD_DIR" -name "${title}.*" -type f -printf '%T@ %p\n' | sort -rn | head -1 | cut -d' ' -f2-)
            
            if [ -f "$downloaded_file" ]; then
                local file_size=$(stat -f%z "$downloaded_file" 2>/dev/null || stat -c%s "$downloaded_file" 2>/dev/null)
                local file_name=$(basename "$downloaded_file")
                
                # Move to destination
                if mv "$downloaded_file" "$destination_dir/" 2>/dev/null; then
                    log_info "Moved to destination: $file_name"
                    
                    # Add to database
                    add_to_database "$video_id" "$title" "$video_url" "$playlist_name" "$destination_dir/$file_name" "$file_size"
                    
                    download_count=$((download_count + 1))
                else
                    log_error "Failed to move: $file_name"
                    error_count=$((error_count + 1))
                fi
            else
                log_error "Downloaded file not found"
                error_count=$((error_count + 1))
            fi
        else
            log_error "Download failed: $title"
            error_count=$((error_count + 1))
        fi
        
        # Small delay between downloads to avoid rate limiting
        sleep "$DOWNLOAD_DELAY"
        
    done <<< "$playlist_data"
    
    # Cleanup
    rm -rf "$DOWNLOAD_DIR" 2>/dev/null
    
    # End session
    end_session "$session_id" "$download_count" "$skip_count" "$error_count"
    
    # Summary
    echo ""
    log_info "===== Download Summary for: $playlist_name ====="
    echo "New videos downloaded: $download_count"
    echo "Videos skipped (already have): $skip_count"
    echo "Errors: $error_count"
    echo ""
}

# Download from all enabled playlists
download_all_playlists() {
    log_info "Starting downloads from all enabled playlists..."
    
    local total_downloaded=0
    local total_skipped=0
    local total_errors=0
    
    get_enabled_playlists | while IFS=$'\t' read -r playlist_name playlist_url destination_dir; do
        if download_playlist "$playlist_name" "$playlist_url" "$destination_dir"; then
            # Note: We can't increment variables in subshells, so we'll just log per-playlist
            :
        fi
    done
    
    log_info "All playlist downloads completed!"
}

# Download from a specific playlist
download_specific_playlist() {
    local playlist_name="$1"
    
    local count=$(get_playlist_count)
    local found=false
    
    for ((i=0; i<count; i++)); do
        local name=$(get_playlist_data "$i" "name")
        if [ "$name" = "$playlist_name" ]; then
            local url=$(get_playlist_data "$i" "url")
            local destination=$(get_playlist_data "$i" "destination")
            local enabled=$(get_playlist_data "$i" "enabled")
            
            if [ "$enabled" != "true" ]; then
                log_warning "Playlist '$playlist_name' is disabled in config"
                return 1
            fi
            
            download_playlist "$name" "$url" "$destination"
            found=true
            break
        fi
    done
    
    if [ "$found" = false ]; then
        log_error "Playlist not found: $playlist_name"
        return 1
    fi
}

# List all playlists
list_playlists() {
    log_info "===== Configured Playlists ====="
    
    local count=$(get_playlist_count)
    
    for ((i=0; i<count; i++)); do
        local name=$(get_playlist_data "$i" "name")
        local url=$(get_playlist_data "$i" "url")
        local destination=$(get_playlist_data "$i" "destination")
        local enabled=$(get_playlist_data "$i" "enabled")
        
        local status="${GREEN}enabled${NC}"
        [ "$enabled" != "true" ] && status="${RED}disabled${NC}"
        
        echo ""
        echo -e "Name: $name (${status})"
        echo "URL: $url"
        echo "Destination: $destination"
    done
}

# Display help
show_help() {
    cat << EOF
YouTube Playlist Downloader with Database

Usage: $0 [COMMAND] [OPTIONS]

Commands:
  run [PLAYLIST_NAME]    Download new videos from specified playlist, or all if not specified