#!/bin/bash

# ============================================================
# CONFIGURATION
# ============================================================
# Database
DB_HOST="localhost"
DB_USER="signage"
DB_PASS="signage"
DB_NAME="cinesoc_pb"

# Remote server
REMOTE_USER="signage"
REMOTE_HOST="puzzlebox.cinemeccanica.fr"
REMOTE_DIR="/pbox_media/trailers/"

# Local
LOCAL_DIR="/soc_media/film/"

# Lock file to prevent overlapping runs
LOCK_FILE="/tmp/rsync_sync.lock"

# Log file
LOG_FILE="/var/log/rsync_sync.log"


# ============================================================
# PARSE ARGUMENTS
# ============================================================
VERBOSE=false
VERY_VERBOSE=false

while getopts ":v" opt; do
    case $opt in
        v)
            if [ "$VERBOSE" = true ]; then
                VERY_VERBOSE=true
            fi
            VERBOSE=true
            ;;
        \?) echo "Usage: $0 [-v|-vv]" >&2; exit 1 ;;
    esac
done

# Helper function to log messages
# In verbose mode  : writes to log file AND displays on screen
# In silent mode   : No writing at all
log() {
    local MESSAGE="[$(date '+%Y-%m-%d %H:%M:%S')] $1"
    if [ "$VERBOSE" = true ]; then
        echo "$MESSAGE"
        echo "$MESSAGE" >> "$LOG_FILE"
    fi
}


# ============================================================
# LOCK MECHANISM
# ============================================================
if [ -f "$LOCK_FILE" ]; then
    OLD_PID=$(cat "$LOCK_FILE")
    if ps -p "$OLD_PID" > /dev/null 2>&1; then
        log "Script already running (PID: $OLD_PID). Exiting."
        exit 1
    else
        log "Stale lock file found. Cleaning up."
        rm -f "$LOCK_FILE"
    fi
fi

echo $$ > "$LOCK_FILE"
trap "rm -f '$LOCK_FILE'; exit" INT TERM EXIT


# ============================================================
# PART 1 - QUERY DATABASE
# ============================================================
log "Querying database..."

SQL_QUERY="SELECT path FROM media md WHERE md.id_pbox_trailer IN (SELECT id_trailer_pbox FROM content_playlist_items) OR (md.id_pbox_movie IN (SELECT id_pbox FROM movies WHERE release_date BETWEEN (CURDATE() - INTERVAL 2 MONTH) AND (CURDATE() + INTERVAL 3 MONTH)) AND path REGEXP 'mp4') ;"

FILE_LIST=$(mysql \
    --host="$DB_HOST" \
    --user="$DB_USER" \
    --password="$DB_PASS" \
    --database="$DB_NAME" \
    --batch \
    --skip-column-names \
    --execute="$SQL_QUERY")

if [ "$VERY_VERBOSE" = true ]; then
    log "--- SQL Query ---"
    log "$SQL_QUERY"
    log "--- Query result ---"
    while IFS= read -r LINE; do
        log "  $LINE"
    done <<< "$FILE_LIST"
    log "--- End of query result ---"
fi

if [ -z "$FILE_LIST" ]; then
    log "No files returned by the query. Exiting."
    exit 0
fi

TEMP_FILE=$(mktemp)
echo "$FILE_LIST" > "$TEMP_FILE"

FILE_COUNT=$(wc -l < "$TEMP_FILE")
log "Files to sync: $FILE_COUNT"


# ============================================================
# PART 2 - RSYNC
# ============================================================
log "Starting rsync..."

RSYNC_OPTS=(
    --archive
    --checksum
    --files-from="$TEMP_FILE"
    --no-relative
    --human-readable
    --log-file="$LOG_FILE"
)

if [ "$VERBOSE" = true ]; then
    RSYNC_OPTS+=(
        --progress          # Progress per file
        --stats             # Summary statistics at the end
        --info=progress2    # Overall progress and current transfer speed
    )
fi

rsync "${RSYNC_OPTS[@]}" \
    "${REMOTE_USER}@${REMOTE_HOST}:${REMOTE_DIR}" \
    "${LOCAL_DIR}"

RSYNC_EXIT=$?
if [ $RSYNC_EXIT -ne 0 ]; then
    log "rsync failed with exit code $RSYNC_EXIT."
else
    log "rsync completed successfully."
fi


# ============================================================
# PART 3 - DELETE LOCAL MP4 FILES NOT IN THE LIST
# ============================================================
log "Checking for obsolete local mp4 files..."

DELETED_COUNT=0
DELETED_FILES=()

while IFS= read -r -d '' LOCAL_FILE; do
    FILENAME=$(basename "$LOCAL_FILE")

    if ! grep -qxF "$FILENAME" "$TEMP_FILE"; then
        log "Deleting obsolete file: $FILENAME"
        rm -f "$LOCAL_FILE"
        DELETED_FILES+=("$FILENAME")
        ((DELETED_COUNT++))
    fi
done < <(find "$LOCAL_DIR" -maxdepth 1 -name "*.mp4" -print0)

log "Deleted $DELETED_COUNT obsolete mp4 file(s)."


# ============================================================
# PART 4 - COMPARISON BETWEEN DB LIST AND LOCAL FOLDER
# ============================================================
if [ "$VERBOSE" = true ]; then
    log "Comparing database list with local folder content..."

    LOCAL_LIST=$(find "$LOCAL_DIR" -maxdepth 1 -name "*.mp4" -printf "%f\n" | sort)
    DB_LIST=$(sort "$TEMP_FILE")

    MISSING=$(comm -23 <(echo "$DB_LIST") <(echo "$LOCAL_LIST"))
    MISSING_COUNT=$(echo "$MISSING" | grep -c '\S')

    EXTRA=$(comm -13 <(echo "$DB_LIST") <(echo "$LOCAL_LIST"))
    EXTRA_COUNT=$(echo "$EXTRA" | grep -c '\S')

    MATCHED_COUNT=$(comm -12 <(echo "$DB_LIST") <(echo "$LOCAL_LIST") | grep -c '\S')

    log "--- Comparison results ---"
    log "Files in DB list and present locally  : $MATCHED_COUNT"
    log "Files in DB list but missing locally  : $MISSING_COUNT"
    log "Files present locally but not in DB   : $EXTRA_COUNT"

    if [ "$MISSING_COUNT" -gt 0 ]; then
        log "--- Missing files ---"
        while IFS= read -r FILE; do
            [ -n "$FILE" ] && log "  MISSING : $FILE"
        done <<< "$MISSING"
    fi

    if [ "$EXTRA_COUNT" -gt 0 ]; then
        log "--- Extra files (not in DB) ---"
        while IFS= read -r FILE; do
            [ -n "$FILE" ] && log "  EXTRA   : $FILE"
        done <<< "$EXTRA"
    fi

    # -vv exclusive details
    if [ "$VERY_VERBOSE" = true ]; then

        # Newly downloaded files (files that were transferred by rsync)
        log "--- Newly downloaded files ---"
        DOWNLOADED_FILES=$(grep "^>f" "$LOG_FILE" | awk '{print $NF}')
        if [ -n "$DOWNLOADED_FILES" ]; then
            while IFS= read -r FILE; do
                log "  DOWNLOADED : $FILE"
            done <<< "$DOWNLOADED_FILES"
        else
            log "  (none)"
        fi

        # Deleted files
        log "--- Deleted files ---"
        if [ ${#DELETED_FILES[@]} -gt 0 ]; then
            for FILE in "${DELETED_FILES[@]}"; do
                log "  DELETED : $FILE"
            done
        else
            log "  (none)"
        fi

        # Side by side comparison
        log "--- Side by side : DB list vs Local folder ---"
        log "$(printf '%-60s | %-60s' 'DATABASE' 'LOCAL FOLDER')"
        log "$(printf '%-60s-+-%-60s' '------------------------------------------------------------' '------------------------------------------------------------')"

        DB_ARRAY=()
        while IFS= read -r LINE; do
            [ -n "$LINE" ] && DB_ARRAY+=("$LINE")
        done <<< "$DB_LIST"

        LOCAL_ARRAY=()
        while IFS= read -r LINE; do
            [ -n "$LINE" ] && LOCAL_ARRAY+=("$LINE")
        done <<< "$LOCAL_LIST"

        MAX=${#DB_ARRAY[@]}
        [ ${#LOCAL_ARRAY[@]} -gt $MAX ] && MAX=${#LOCAL_ARRAY[@]}

        for ((i=0; i<MAX; i++)); do
            DB_ENTRY=${DB_ARRAY[$i]:-""}
            LOCAL_ENTRY=${LOCAL_ARRAY[$i]:-""}
            log "$(printf '%-60s | %-60s' "$DB_ENTRY" "$LOCAL_ENTRY")"
        done

        log "--- End of side by side ---"
    fi

    log "--- End of comparison ---"
fi

# ============================================================
# CLEANUP
# ============================================================
rm -f "$TEMP_FILE"
log "Done."