#!/usr/bin/bash
# -*- sh; sh-shell: bash; -*-

# Library with scripts to modify:
# - linphone friends SQLITE3 database
# - table "friends" content
# - linphone contacts in vCard and CSV format
# - avatars

# Script Requires: bash tar xz sed sqlite3 regexp.so
# regexp.so for linux x86 is inside archive https://github.com/nalgeon/sqlean/releases/download/0.21.8/sqlean-linux-x86.zip
# https://hakanu.net/sqlite/2021/04/10/how-to-achieve-regular-expression-based-replace-in-sqlite-regex/
# https://github.com/nalgeon/sqlean/blob/main/docs/regexp.md

# Tested with linphone v5.2.0

SCRIPT_DIR=$( cd -- "$( dirname -- "${BASH_SOURCE[0]}" )" &> /dev/null && pwd )
APPNAME=linphone

# Check if programs are on the system:
if ! [ -x "$(command -v bash)" ]; then
    echo 'Error: bash is not installed.' >&2
    exit 1
fi
if ! [ -x "$(command -v tar)" ]; then
    echo 'Error: tar is not installed.' >&2
    exit 1
fi
if ! [ -x "$(command -v xz)" ]; then
    echo 'Error: xz is not installed.' >&2
    exit 1
fi
if ! [ -x "$(command -v sed)" ]; then
    echo 'Error: sed is not installed.' >&2
    exit 1
fi
if ! [ -x "$(command -v sqlite3)" ]; then
    echo 'Error: sqlite3 is not installed.' >&2
    exit 1
fi
if ! [ -x "$(command -v python3)" ]; then
    echo 'Error: python3 is not installed.' >&2
    exit 1
fi
if [ "$DATABASE" == "" ]; then
    # Check if linphone contact database exists
    if [ ! -f $HOME/.local/share/linphone/friends.db ]; then
        echo 'Error: linphone contacts database not found.'
        echo 'Type this command in terminal:'
        echo
        echo '    linphone'
        echo
        echo 'to create your linphone contact database.'
        exit 1
    else
        DATABASE=$HOME/.local/share/linphone/friends.db
    fi
fi

function linphone-contacts-database-set-empty-fields-to-null {
    DATABASE=$HOME/.local/share/linphone/friends.db
    sqlite3 $DATABASE "UPDATE friends SET ref_key = NULL WHERE ref_key = '';" "UPDATE friends SET vCard_etag = NULL WHERE vCard_etag = '';" "UPDATE friends SET vCard_url = NULL WHERE vCard_url = '';" ".exit"
}

function linphone-contacts-duplicates-show {
    DATABASE=$HOME/.local/share/linphone/friends.db
	echo
	echo "sip_uri           Count"
	echo "======================="
	sqlite3 $DATABASE "SELECT sip_uri, COUNT(*) AS "Count" FROM friends GROUP BY sip_uri HAVING COUNT(*) > 1 ORDER BY "Count" DESC;" ".exit"
	echo
	echo
	echo "id    sip_uri     vCard"
	echo "======================="
    sqlite3 $DATABASE "SELECT id, sip_uri, vCard FROM friends WHERE rowid NOT IN (SELECT MIN(rowid) FROM friends GROUP BY sip_uri);" ".exit"
}

function linphone-contacts-duplicates-remove {
    DATABASE=$HOME/.local/share/linphone/friends.db
    sqlite3 $DATABASE "DELETE FROM friends WHERE EXISTS (SELECT * FROM friends contacts WHERE friends.sip_uri = contacts.sip_uri AND friends.vCard = contacts.vCard AND friends.rowid > contacts.rowid);" ".exit"
}

function sqlite3-find-regexp-extension {
    EXTENSION_DIR=sqlean
    EXTENSION_NAME=regexp
    EXTENSION_SUFFIX=so
    # Find the extension location on the system
    test -f "$(type -p /usr/lib/$EXTENSION_DIR/$EXTENSION_NAME.$EXTENSION_SUFFIX)" && LIBDIR=/usr/lib
    test -f "$(type -p /usr/lib64/$EXTENSION_DIR/$EXTENSION_NAME.$EXTENSION_SUFFIX)" && LIBDIR=/usr/lib64
    test -f "$(type -p /usr/lib/x86_64-linux-gnu/$EXTENSION_DIR/$EXTENSION_NAME.$EXTENSION_SUFFIX)" && LIBDIR=/usr/lib/x86_64-linux-gnu
    if [ $LIBDIR == "" ]; then
        echo "Could not find $EXTENSION_NAME.$EXTENSION_SUFFIX"
        echo 'Aborting.'
        exit 1
    fi
    EXTENSION_PATH=$LIBDIR/$EXTENSION_DIR/$EXTENSION_NAME.$EXTENSION_SUFFIX
    if ! [ -f $EXTENSION_PATH ]; then
        echo "Error: $EXTENSION_NAME.$EXTENSION_SUFFIX not found." >&2
        echo "Did you install package $EXTENSION_DIR ?"
        exit 1
    else
        #EXTENSION=$(cat $EXTENSION_PATH | sed 's/\.so//g')
        EXTENSION=$LIBDIR/$EXTENSION_DIR/$EXTENSION_NAME
    fi
    # End sqlite3-find-regexp-extension
}

if [ "$EXTENSION_PATH" == "" ]; then
    sqlite3-find-regexp-extension
fi

function linphone-contacts-avatar-create-svg-icon {
    FILE=$HOME/.local/share/linphone/avatars/avatar.svg
    echo "<svg xmlns=\"http://www.w3.org/2000/svg\" xml:space=\"preserve\" width=\"100\" height=\"100\"><path d=\"M694.454 635.804c52.181-7.045 100.429-26.718 141.5-57.697 89.957-67.852 134.188-178.302 115.893-289.398-15.232-92.503-74.673-174.037-158.215-217.024-41.498-21.353-82.63-31.872-129.787-33.194-50.827-1.424-95.228 8.376-141.39 31.207-29.815 14.745-54.37 32.398-78.583 56.494-105.724 105.214-117.487 272.965-27.515 392.382 51.091 67.811 126.79 110.025 212.597 118.557 12.928 1.285 52.015.493 65.5-1.327z\" style=\"fill:#fdfdfd\" transform=\"matrix(.16669 0 0 .16683 -59.34 -6.4)\"/><path d=\"M628.954 637.13c-85.808-8.531-161.506-50.745-212.597-118.556-89.972-119.417-78.209-287.168 27.515-392.382 24.213-24.096 48.768-41.75 78.582-56.494 46.163-22.83 90.564-32.63 141.391-31.207 37.107 1.04 68.468 7.33 101.577 20.371 69.965 27.56 126.505 79.611 159.512 146.847 13.452 27.401 22.201 54.386 26.913 83 18.295 111.096-25.936 221.546-115.893 289.398-41.071 30.98-89.319 50.652-141.5 57.697-13.485 1.82-52.572 2.612-65.5 1.327zm66.789-25.838c59.688-8.994 112.904-35.762 155.218-78.076 51.962-51.963 80.952-121.797 80.952-195.007 0-131.092-93.097-244.8-221.459-270.486-19.794-3.961-31.624-5.017-55.5-4.955-45.852.12-82.78 8.829-122.5 28.893-83.685 42.272-139.286 121.444-150.672 214.548-1.684 13.773-1.684 50.65 0 64 7.942 62.959 35.162 118.988 79.184 162.993 46.85 46.831 107.631 74.83 174.488 80.377 12.078 1.003 47.337-.335 60.289-2.287z\" style=\"fill:#fb5c01\" transform=\"matrix(.16669 0 0 .16683 -59.34 -6.4)\"/><path d=\"M635.454 613.58c-66.857-5.548-127.638-33.547-174.488-80.378-44.022-44.005-71.242-100.034-79.184-162.993-1.684-13.35-1.684-50.227 0-64 13.424-109.764 88.617-199.5 194.672-232.322 11.57-3.58 29.137-7.35 43.5-9.334 15.533-2.146 52.712-2.44 68-.538 32.857 4.088 61.763 12.763 90.98 27.306 93.657 46.617 152.98 142.356 152.98 246.888 0 136.826-100.108 252.58-236.171 273.083-12.952 1.952-48.211 3.29-60.289 2.287zm66-120.397c39.153-2.492 80.176-9.54 116.954-20.092 6.076-1.743 11.592-3.667 12.258-4.276 4.085-3.726 5.819-37.182 2.717-52.398-9.5-46.594-43.205-84.326-99.033-110.863-6.21-2.952-11.546-5.778-11.856-6.28-.31-.503 1.128-2.871 3.198-5.263 8.847-10.226 14.824-20.18 19.245-32.05 7.146-19.187 7.146-45.317 0-64.505-9.462-25.403-28.743-45.61-52.983-55.525-13.638-5.579-17.272-6.206-36-6.214-15.247-.007-18.53.287-25.5 2.28-67.228 19.218-91.555 100.277-46.281 154.21l5.719 6.813-2.434 1.595c-1.338.877-2.7 1.594-3.024 1.594-.325 0-5.628 2.519-11.785 5.597-60.5 30.248-93.546 72.922-96.338 124.403-.604 11.127.523 24.867 2.724 33.22.92 3.495 1.383 3.845 7.7 5.827 32.222 10.11 83.916 19.48 120.72 21.878 18.04 1.176 75.831 1.206 94 .05z\" style=\"fill:#434343\" transform=\"matrix(.16669 0 0 .16683 -59.34 -6.4)\"/></svg>" > $FILE
    # End linphone-contacts-avatar-create-svg-icon
}

function linphone-contacts-avatar-remove-all-from-contacts {
    # Remove avatar from all contacts
    # https://hakanu.net/sqlite/2021/04/10/how-to-achieve-regular-expression-based-replace-in-sqlite-regex/
    # https://github.com/nalgeon/sqlean/blob/main/docs/regexp.md
    DATABASE=$HOME/.local/share/linphone/friends.db
    # Usage: SELECT REGEXP_REPLACE('this year is 2021', '[0-9]+', '2050');
    # sqlite3 $DATABASE "SELECT load_extension('./regexp.so');" "UPDATE friends SET vCard = REGEXP_REPLACE(vCard, 'PHOTO:.*' || CHAR(10), '') WHERE id = 12;" ".exit"
    # sqlite3 $DATABASE "SELECT load_extension('./regexp.so');" "UPDATE friends SET vCard = REGEXP_REPLACE(vCard, 'PHOTO:.*' || CHAR(10), '');" ".exit"
    sqlite3 $DATABASE "SELECT load_extension('$EXTENSION');" "UPDATE friends SET vCard = REGEXP_REPLACE(vCard, 'PHOTO:.*' || CHAR(10), '');" ".exit"
    # End function linphone-contacts-avatar-remove-all-from-contacts
}

function linphone-contacts-avatar-remove-default-from-contacts {
    # Remove default avatar from all contacts
    DATABASE=$HOME/.local/share/linphone/friends.db
    sqlite3 $DATABASE "UPDATE friends SET vCard = REPLACE(vCard, 'PHOTO:linphone-desktop:/avatar.svg' || CHAR(10), '');" ".exit"
    # End function linphone-contacts-avatar-remove-default-from-contacts
}

function linphone-contacts-avatar-reset-to-default {
    # Set a default avatar on all contacts
    # https://hakanu.net/sqlite/2021/04/10/how-to-achieve-regular-expression-based-replace-in-sqlite-regex/
    # https://github.com/nalgeon/sqlean/blob/main/docs/regexp.md
    DATABASE=$HOME/.local/share/linphone/friends.db
    # Usage: SELECT REGEXP_REPLACE('this year is 2021', '[0-9]+', '2050');
    # sqlite3 $DATABASE "SELECT load_extension('./regexp.so');" "UPDATE friends SET vCard = REGEXP_REPLACE(vCard, 'PHOTO:.*', 'PHOTO:linphone-desktop:/avatar.svg');" ".exit"
    sqlite3 $DATABASE "SELECT load_extension('$EXTENSION');" "UPDATE friends SET vCard = REGEXP_REPLACE(vCard, 'PHOTO:.*', 'PHOTO:linphone-desktop:/avatar.svg');" ".exit"
    # End function linphone-contacts-avatar-reset-to-default
}

function linphone-contacts-avatar-set-on-contacts-where-missing {
    # Set a default avatar on all contacts
    DATABASE=$HOME/.local/share/linphone/friends.db
    contactswithnoavatar=$(sqlite3 $DATABASE "SELECT id FROM friends WHERE vCard NOT LIKE '%PHOTO:linphone-desktop:%';" | tr -d '\r')
    for id in $contactswithnoavatar
    do
        sqlite3 $DATABASE "UPDATE friends SET vCard = REPLACE(vCard, 'END:VCARD', 'PHOTO:linphone-desktop:/avatar.svg' || CHAR(10) || 'END:VCARD') WHERE id = $id;"
    done
    # End function linphone-contacts-avatar-set-on-contacts-where-missing
}

function linphone-contacts-database-compact {
    DATABASE=$HOME/.local/share/linphone/friends.db
    sqlite3 $DATABASE "VACUUM;" ".exit"
    sqlite3 $DATABASE "DROP TABLE new_ids;" ".exit"
    FIRSTROW=$(sqlite3 $DATABASE "SELECT id FROM friends ORDER BY ROWID ASC LIMIT 1;"  ".exit")
    echo "Table friends starts at row with id = $FIRSTROW"
    NUMBER_OF_ROWS=$(sqlite3 $DATABASE "SELECT COUNT(*) FROM friends;"  ".exit")
    echo "Table friends has $NUMBER_OF_ROWS rows"
    # Compact database and renumber ids from start START_AT_ROW
    START_AT_ROW=1
    # START_AT_ROW=$(( FIRSTROW + NUMBER_OF_ROWS ))
    #if [ $FIRSTROW -ne $START_AT_ROW ]; then
    echo "Renumbering table rows with field 'id' from $START_AT_ROW."
    START_AT_ROW=$(( $START_AT_ROW - 1 ))
    sqlite3 $DATABASE "CREATE TABLE new_ids(i INTEGER PRIMARY KEY, old_id INTEGER);" ".exit"
    # Insert a dummy record to ensure that the next new record starts at 100, then insert all the IDs of the Categories table that you want to change:
    sqlite3 $DATABASE "INSERT INTO new_ids VALUES($START_AT_ROW, NULL);" ".exit"
    sqlite3 $DATABASE "INSERT INTO new_ids SELECT NULL, id FROM friends ORDER BY sip_uri ASC;" ".exit"
    sqlite3 $DATABASE "DELETE FROM new_ids WHERE i = $START_AT_ROW;" ".exit"
    sqlite3 $DATABASE "UPDATE friends SET id = (SELECT i FROM new_ids WHERE old_id = friends.id) WHERE id IN (SELECT old_id FROM new_ids); DROP TABLE new_ids;" ".exit"
    sqlite3 $DATABASE "UPDATE SQLITE_SEQUENCE SET SEQ=0 WHERE NAME='friends';"
    echo "Renumbered all table rows field 'id'."
    #else
    #    echo "No table renumbering needed."
    #fi
    # End function linphone-contacts-database-compact
}

function linphone-contacts-export-vcard-file {
    DATABASE=$HOME/.local/share/linphone/friends.db
    sqlite3 $DATABASE "SELECT vCard FROM friends;" | tr -d '\r' | sed '/^[[:space:]]*$/d' > contacts_linphone_$(date -I).vcf
    echo "Exported contacts in vCard format"    
    # End function linphone-contacts-export-vcards-file
}

function linphone-contacts-export-csv-file {
    # Create an archive bundle with avatars
    DATABASE=$HOME/.local/share/linphone/friends.db
    sqlite3 -header -csv $DATABASE "SELECT friend_list_id, sip_uri, subscribe_policy, send_subscribe, ref_key, vCard, vCard_etag, vCard_url, presence_received FROM friends ORDER BY sip_uri;"  ".exit" > contacts_linphone_$(date -I).csv
    echo "Exported contacts in CSV format"
    # End linphone-contacts-export-csv-file
}

function linphone-contacts-export-archive-tar-xz {
    # Create an archive bundle with avatars
    DATABASE=$HOME/.local/share/linphone/friends.db
    defaultavatar=avatar.svg
    FILENAME=contacts_linphone_$(date -I)
    RESET_AVATAR=false
    ADD_MISSING_AVATAR=true
    ARCHIVE_DATABASE=false
    echo "Show columns in table:"
    echo
    sqlite3 $DATABASE "PRAGMA table_info(friends);" ".exit"
    sleep 2
    echo
    echo "Export all those columns without the first 'id' column:"
    echo
    sleep 2
    echo
    echo "Header with first line of data from table friends:"
    echo
    sqlite3 --header $DATABASE "SELECT * FROM friends ORDER BY ROWID ASC LIMIT 1;"
    echo
    sleep 2
    echo "Export all those columns without the first 'id' column:"
    echo
    sleep 2
    linphone-contacts-avatar-create-svg-icon
    linphone-contacts-database-compact
    if $RESET_AVATAR; then
        cp $HOME/.local/share/linphone/friends.db $HOME/.local/share/linphone/friends.bak.db
        linphone-contacts-avatar-reset-to-default
        echo "Reset all avatars to default avatar"
    fi
    if $ADD_MISSING_AVATAR; then
        linphone-contacts-avatar-set-on-contacts-where-missing
        echo "Added default avatar to all contacts missing an avatar"
    fi
    sqlite3 -header -csv $DATABASE "SELECT friend_list_id, sip_uri, subscribe_policy, send_subscribe, ref_key, vCard, vCard_etag, vCard_url, presence_received FROM friends ORDER BY sip_uri;" > $FILENAME.csv
    echo "Exported contacts in CSV format"
    sqlite3 $DATABASE "SELECT vCard FROM friends;" | tr -d '\r' | sed '/^[[:space:]]*$/d' > contacts_linphone_$(date -I).vcf
    echo "Exported contacts in vCard format" 
    if $RESET_AVATAR; then
        echo "Reset all avatars to default avatar: $defaultavatar"
        echo "If the exported files contain wrong PHOTO: data, they need to be query replaced with:"
        echo
        echo "sed -i 's/PHOTO:linphone-desktop:\/.*/PHOTO:linphone-desktop:\/avatar.svg/g' $FILENAME.csv"
        echo "or"
        echo "sed -i \"s|PHOTO:linphone-desktop:\/.*|PHOTO:linphone-desktop:\/$defaultavatar|g\" $FILENAME.csv"
        sed -i "s|PHOTO:linphone-desktop:\/.*|PHOTO:linphone-desktop:\/$defaultavatar|g" $FILENAME.csv
        echo "sed -i \"s|PHOTO:linphone-desktop:\/.*|PHOTO:linphone-desktop:\/$defaultavatar|g\" $FILENAME.vcf"
        echo "or"
        echo "sed -i \"s|PHOTO:linphone-desktop:\/.*|PHOTO:linphone-desktop:\/$defaultavatar|g\" $FILENAME.vcf"
        sed -i "s|PHOTO:linphone-desktop:\/.*|PHOTO:linphone-desktop:\/$defaultavatar|g" $FILENAME.vcf
        echo
    fi
    if $ARCHIVE_DATABASE; then
        tar -c --file=$FILENAME.tar -C $HOME .local/share/linphone/friends.db
        echo "Added friends.db to archive"
    fi
    tar -r --file=$FILENAME.tar --transform='flags=r;s|^|.local/share/linphone/|' $FILENAME.csv
    echo "Added $FILENAME.csv to archive"
    tar -r --file=$FILENAME.tar --transform='flags=r;s|^|.local/share/linphone/|' $FILENAME.vcf
    echo "Added $FILENAME.vcf to archive"
    if $RESET_AVATAR; then
        tar -r --file=$FILENAME.tar -C $HOME .local/share/linphone/avatars/$defaultavatar
        echo "Added $defaultavatar to archive"
    else
        # Archive all avatars including unused ones
        # tar -r --file=$FILENAME.tar -C $HOME .local/share/linphone/avatars
        echo "Archive currently used unique avatars:"
        avatars=$(grep "PHOTO:linphone-desktop:" $FILENAME.csv | sed 's/PHOTO:linphone-desktop:\///g' | tr -d '\r' | sort | uniq)
        for avatar in $avatars
        do
            tar -r --file=$FILENAME.tar -C $HOME .local/share/linphone/avatars/$avatar
            echo "Added $avatar to archive"
        done
        xz -f $FILENAME.tar
        echo "Compressed tar archive into $FILENAME.tar.xz"
        echo "Full path to archive: $(cd "$(dirname "$FILENAME.tar.xz")"; pwd -P)/$(basename "$FILENAME.tar.xz")"
        
    fi
    if $RESET_AVATAR; then
        # Restore the database
        cp $HOME/.local/share/linphone/friends.bak.db $HOME/.local/share/linphone/friends.db
    fi
    # Clean up
    rm $FILENAME.csv
    rm $FILENAME.vcf
    # End linphone-contacts-export-archive-tar-xz
}

function linphone-contacts-remove-all {
    # Delete all records in database
    DATABASE=$HOME/.local/share/linphone/friends.db
    sqlite3 $DATABASE "DELETE FROM friends;" \
            "UPDATE SQLITE_SEQUENCE SET SEQ=0 WHERE NAME='friends';" \
            ".exit"
    # End linphone-contacts-remove-all
}

function linphone-contacts-import-csv-file {
    csvfile=$1
    if [ -f "$csvfile" ]; then
        echo "$csvfile exists."
        echo "Importing $csvfile into linphone database."
        python3 $SCRIPT_DIR/linphone-contacts-import-csv.py "$csvfile"
        echo
        echo "Restart $APPNAME to see imported contacts."
    else
        echo "CSV file not found. Please check file name."
    fi
    # End linphone-contacts-import-csv-file
}

function linphone-contacts-import-vcard-file {
    vcardfile=$1
    if [ -f "$vcardfile" ]; then
        echo "Importing $vcardfile into linphone contacts database."
        python3 $SCRIPT_DIR/linphone-contacts-import-vcard.py "$vcardfile"
        echo
        echo "Restart $APPNAME to see imported contacts."
    else
        echo "vCard file not found. Please check file name."
    fi
    # End linphone-contacts-import-vcard-file
}

function linphone-contacts-import-archive-tar-xz {
    # Import archived contacts into linphone database
    archive=$1
    echo "archive=$archive"
    XDG_DATA_HOME=$HOME/.local/share
    APPNAME=linphone
    DATABASEDIR=$XDG_DATA_HOME/$APPNAME
    echo "DATABASEDIR=$DATABASEDIR"
    DATABASE=$DATABASEDIR/friends.db
    BASEFILENAME=$(basename $archive .xz)
    echo "BASEFILENAME=$BASEFILENAME"
    importfile="$DATABASEDIR/${BASEFILENAME%.*}.csv"
    echo "importfile=$importfile"
    # Extract archive
    tar -xf $archive -C $HOME
    if [ -f "$importfile" ]; then
        # Python basename: Path(archive).stem
        echo "$importfile exists."
        echo "Importing $importfile into linphone database."
        python3 $SCRIPT_DIR/linphone-contacts-import-csv.py "$importfile"
        echo
        echo "Restart $APPNAME to see imported contacts."
    else
        echo "importfile was not found. Please check file name."
    fi
    # End linphone-contacts-import-archive-tar-xz
}
# End of linphone_contacts_import_export_functions.sh
