pihole_adlist_tool

#!/bin/bash

PIHOLE_ADLIST_TOOL_VERSION="2.6.5"

# define path to pihole's databases and temporary database
TEMP_DB="/tmp/temp.db"
PIHOLE_ROOT="/etc/pihole"
PIHOLE_FTL="file:$PIHOLE_ROOT/pihole-FTL.db?mode=ro"
GRAVITY="file:$PIHOLE_ROOT/gravity.db"


## define and initialize variables ##

# variables for various timestamps and dates
DAYS_REQUESTED=30
declare -i TIMESTAMP_REQUESTED
declare -i TIMESTAMP_FIRST_QUERY
declare -i TIMESTAMP_LAST_QUERY
declare -i TIMESTAMP_FIRST_ANALYZED
DATE_REQUESTED=
DATE_FIRST_QUERY=
DATE_FIRST_ANALYZED=
DATE_LAST_QUERY=
declare -i FTL_ID

# variables defining what to show in output
TOP=0
SORT=
SORT_ORDER=
UNIQUE=0
REGEX_MODE=0

# variables for adlist management
declare -a adlist_conf_old_enabled
declare -a adlist_conf_unique_enabled
declare -a adlist_enabled_in_gravity
declare -a adlist_conf_minimal_enabled
declare -i LEFT_DOMAINS

# variables for stats
NUM_DOMAINS_BLOCKED=
HITS_TOTAL=
NUM_ADLISTS=
NUM_ADLISTS_ENABLED=
NUM_GRAVITY_UNIQUE_DOMAINS=
NUM_DOMAINS_BLOCKED_CURRENT=
HITS_TOTAL_CURRENT=
BLACKLIST_GRAVITY=
NUM_TOTAL_UNIQUE_DOMAINS=
BLACKLIST_CNAME=
NUM_DOMAINS_BLOCKED_FUTURE=
NUM_ENABLED_REGEX=
NUM_REGEX=
NUM_ALL_DOMAINS=
NUM_DOMAINS_BLOCKED_BY_REGEX=

# variables for general info
PIHOLE_DNSMASQ_VERSION=
SQLITE_VERSION=

# Docker variables
PIHOLE_DOCKER=
CONTAINER_ID=

# variables for menu selection
AUTOMATIC_MODE=0
declare -i ENABLE_ALL_ADLISTS_FOR_ANALYSIS
declare -i RUN_GRAVITY_NOW
declare -i FURTHER_ACTION

#for text formatting
bold=$(tput bold)
normal=$(tput sgr0)

# variables for RegEx analysis
declare -a all_domains
declare -a all_exact_domains
CURRENT_DOMAIN=
REGEX_ID=

# version message
print_version() {
    echo "Pihole Adlist Tool v${PIHOLE_ADLIST_TOOL_VERSION}"
}

# fetches latest version
fetch_remote_version() {
    local remote_version
    remote_version="$(
        curl --silent https://api.github.com/repos/yubiuser/pihole_adlist_tool/releases/latest |
        grep '"tag_name":' |
        awk -F \" '{print $4}'
    )"
    # returns the latest version tag(eg: 2.4.0).
    echo "$remote_version"
}

# help message
print_help() {
    echo
    echo "  Usage: pihole_adlist_tool [options]

    Options:
        -d [Num]                        Consider the last [Num] days (Default: 30). Enter 0 for all-time analysis.

        -t [Num]                        Show top blocked domains. [Num] defines the number to show.

        -s [total/covered/hits/unique]  Set sorting order to total (total domains), covered (domains covered), hits (hits covered) or
                                        unique (covered unique domains) DESC. (Default sorting: id ASC).

        -u                              Show covered unique domains.

        -a                              Run in 'automatic mode'. No user input is required at all, assuming default choice would be
                                        to leave everything untouched.

        -r                              Analyse RegEx as well. Depending on the amount of domains and RegEx this might take a while.
                                        Please note: Can only be used, if Pi-hole is NOT running in a Docker Container!

        -v                              Display pihole_adlist_tool's version.

        -h                              Show this help dialog.
"
}

#convert timestamp to date

timestamp2date () {
    local DATE
    DATE=$(date -d @"$1" +%d.%m.%Y%t%k:%M:%S)
    echo "$DATE"
}


# calculate timestamps

set_timestamps () {

    TIMESTAMP_FIRST_QUERY=$(sqlite "$PIHOLE_FTL" "SELECT MIN(timestamp) FROM queries;")
    TIMESTAMP_LAST_QUERY=$(sqlite "$PIHOLE_FTL" "SELECT MAX(timestamp) FROM queries;")
    if [ "$DAYS_REQUESTED" = 0 ];
        then
            TIMESTAMP_REQUESTED=$TIMESTAMP_FIRST_QUERY
        else
            TIMESTAMP_REQUESTED=$(date +%s)
            TIMESTAMP_REQUESTED="$TIMESTAMP_REQUESTED-86400*${DAYS_REQUESTED}"
    fi
    TIMESTAMP_FIRST_ANALYZED=$(sqlite "$PIHOLE_FTL" "SELECT min(timestamp) FROM queries WHERE timestamp>=$TIMESTAMP_REQUESTED;")
}

# converts dates

set_dates () {
    DATE_FIRST_QUERY="$(timestamp2date "$TIMESTAMP_FIRST_QUERY")"
    DATE_LAST_QUERY="$(timestamp2date "$TIMESTAMP_LAST_QUERY")"
    DATE_REQUESTED="$(timestamp2date "$TIMESTAMP_REQUESTED")"
    DATE_FIRST_ANALYZED="$(timestamp2date "$TIMESTAMP_FIRST_ANALYZED")"

}

# calculates how many domains from gravity_strip are contained in all enabled adlists in gravity
# this is useful in case users select to enable all adlist initially, but return to their previous adlist configuration
# which might not contain all domains that could have been blocked or only enable adlists with unique covered domains

domains_blocked_future () {
sqlite $TEMP_DB << EOF
    ATTACH DATABASE "${GRAVITY}?mode=ro" AS gravity_db;
        INSERT INTO info (property, value) Select 'NUM_DOMAINS_BLOCKED_FUTURE', count(distinct gravity_strip.domain) from gravity_strip join gravity_db.gravity on gravity_strip.domain=gravity_db.gravity.domain Join gravity_db.adlist on gravity_db.gravity.adlist_id=gravity_db.adlist.id where enabled=1;
    DETACH DATABASE gravity_db;
.exit
EOF

NUM_DOMAINS_BLOCKED_FUTURE=$(sqlite $TEMP_DB "SELECT value FROM info where property='NUM_DOMAINS_BLOCKED_FUTURE';")

echo
echo "  [i]  Of the ${bold}$NUM_DOMAINS_BLOCKED_CURRENT${normal} domains that would have been blocked by the adlist configuration you chose to analyze,"
echo "       ${bold}$NUM_DOMAINS_BLOCKED_FUTURE${normal} domains are covered by your current adlist configuration."

}

# removes temporary database
remove_temp_database () {
echo
echo -e "  [i]  Removing temporary database..."
if [ "$PIHOLE_DOCKER" = 0 ];
    then
        rm -f $TEMP_DB
    else
        docker exec "$CONTAINER_ID" rm -f $TEMP_DB
fi


echo -e "  [✓]  Temporary database removed\n"
}

# cleanup on trap
cleanup_on_trap () {
echo -e "\n\n  [✗]  ${bold}User-abort detected!${normal}"
remove_temp_database
exit 1
}

# use pihole-FTL's built-in sql shell
# putting this in a function allows to easily change to another sqlite engine one day if necessary
sqlite () {
if [ "$PIHOLE_DOCKER" = 0 ];
    then
        pihole-FTL sqlite3 "$@"
    else
        docker exec -i "$CONTAINER_ID" pihole-FTL sqlite3 "$@"
fi

}

# use declare to save the sqlite function, which will be passed by bash -c to the sudo commands
# https://unix.stackexchange.com/questions/269078/executing-a-bash-script-function-with-sudo
SUDO_SQLITE=$(declare -f sqlite)

trap cleanup_on_trap INT

# getopts flags and assign arguments to variables
while getopts 'd:t:s:uarvh' flag; do
    case "${flag}" in
        d) DAYS_REQUESTED="${OPTARG}" ;;
        t) TOP="${OPTARG}" ;;
        s) SORT="${OPTARG}" ;;
        u) UNIQUE=1 ;;
        a) AUTOMATIC_MODE=1 ;;
        r) REGEX_MODE=1 ;;
        v) print_version
            exit 0 ;;
        h) print_help
            exit 0 ;;
        *) print_help
            exit 1 ;;
    esac
done

### warm-up ######

warm_up() {

# figure out if Pi-hole runs in docker
if command -v pihole &> /dev/null
    then
        PIHOLE_DOCKER=0
    else
        if (command -v docker &> /dev/null) && [ "$(docker ps -q -f status="running" -f name="pihole")" ]
            then
                PIHOLE_DOCKER=1
                CONTAINER_ID=$(docker ps -f "name=pihole" -q)

            else
                echo -e "\n\n  [✗]  ${bold}No Pi-hole installation detected. Exiting.${normal}"
                exit 1
        fi
fi


# get Pi-hole's dnsmasq version
if [ "$PIHOLE_DOCKER" = 0 ];
    then
        PIHOLE_DNSMASQ_VERSION=$(pihole-FTL -vv |awk '/dnsmasq/{getline; print substr($2,9)}')
    else
        PIHOLE_DNSMASQ_VERSION=$(docker exec "$CONTAINER_ID" pihole-FTL -vv |awk '/dnsmasq/{getline; print substr($2,9)}')
fi


# Enforce an up-to-date pihole version. This also guarantees that this script gets all necessary information
if [ "$(printf '%s\n' "2.87" "$PIHOLE_DNSMASQ_VERSION" | sort -V | head -n1)" = "2.87" ]; then :
    else
        echo -e "\n\n  [✗]  ${bold}You're running an old Pi-hole version which is missing important security updates. Please upgrade.${normal}"
        exit 1
fi

# exit if $DAYS_REQUESTED is no digit
case "$DAYS_REQUESTED" in
    ''|*[0-9]*) ;;
    *) print_help
        exit 1 ;;
esac

# exit if $TOP is no digit
case "$TOP" in
    ''|*[0-9]*) ;;
    *) print_help
        exit 1 ;;
esac


echo -e "\n   ++++++++ Info ++++++++\n"

# running Pi-hole in Docker?
if [ "$PIHOLE_DOCKER" = 0 ];
    then
        echo -e "  [i]  PIHOLE_DOCKER: No"
    else
        echo -e "  [i]  PIHOLE_DOCKER: Yes"
fi


# print Pi-hole FTL version and SQLite version
echo -e "  [i]  PIHOLE_DNSMASQ_VERSION: $PIHOLE_DNSMASQ_VERSION"


if [ "$PIHOLE_DOCKER" = 0 ];
    then
        SQLITE_VERSION=$(pihole-FTL sqlite3 --version|awk '{print $1}')
    else
        SQLITE_VERSION=$(docker exec "$CONTAINER_ID" pihole-FTL sqlite3 --version|awk '{print $1}')
fi

echo -e "  [i]  SQLITE_VERSION: $SQLITE_VERSION"

# print number of requested days
if [ "$DAYS_REQUESTED" = 0 ];
    then
        echo -e "  [i]  DAYS_REQUESTED: all time"
    else
        echo -e "  [i]  DAYS_REQUESTED: $DAYS_REQUESTED"
fi

# print number of requested top blocked domains
if [ "$TOP" = 0 ];
    then
        echo -e "  [i]  TOP: Not shown"
    else
        echo -e "  [i]  TOP: $TOP top blocked domains"
fi



# set sort order for adlist table based on $SORT
case "$SORT" in
    total)      SORT_ORDER="total_domains DESC"
                echo -e "  [i]  SORT_ORDER: total_domains DESC";;
    domains)    SORT_ORDER="domains_covered DESC"
                echo -e "  [i]  SORT_ORDER: domains_covered DESC";;
    hits)       SORT_ORDER="hits_covered DESC"
                echo -e "  [i]  SORT_ORDER: hits_covered DESC";;
    unique)     SORT_ORDER="unique_domains_covered DESC"
                echo -e "  [i]  SORT_ORDER: unique_domains_covered DESC";;
    *)          SORT_ORDER="id ASC"
                echo -e "  [i]  SORT_ORDER: id ASC";;
esac

# print if unique covered domains should be shown
if [ "$UNIQUE" -eq 1 ];
    then
        echo -e "  [i]  UNIQUE: Shown"
    else
        echo -e "  [i]  UNIQUE: Not shown"
fi

# print if RegEx should be analysed as well
if [ "$REGEX_MODE" -eq 1 ];
    then
        # do not RegEx when running in docker container:
        if [ "$PIHOLE_DOCKER" = 1 ]; then
            REGEX_MODE=0
            echo -e "  [i]  REGEX_MODE: Disabled due to running Pi-hole in Docker Container"
        else
            echo -e "  [i]  REGEX_MODE: Enabled"
        fi
    else
        echo -e "  [i]  REGEX_MODE: Disabled"
fi

# verify if there is an update available
remote_version=$(fetch_remote_version)
if [ -n "$remote_version" ] && [ "$remote_version" != "$PIHOLE_ADLIST_TOOL_VERSION" ]; then
    echo -e "  [i]  There is an update available: $remote_version"
fi

echo -e "\n   ++++++++++++++++++++++\n\n"
}



#### Program starts #####


echo
echo
echo "  ${bold}*** Pihole Adlist Tool $PIHOLE_ADLIST_TOOL_VERSION ***${normal}"
echo

warm_up



# calculate various timestamps, converts them to dates
set_timestamps
set_dates

# get FTL_ID based on $TIMESTAMP_REQUESTED
FTL_ID=$(sqlite "$PIHOLE_FTL" "SELECT MIN(id) FROM queries WHERE timestamp>=$TIMESTAMP_REQUESTED;")



# Print timestamp warnings
if [ "$TIMESTAMP_REQUESTED" -gt "$TIMESTAMP_LAST_QUERY" ];
    then
        echo
        echo "  [i]  ${bold}Warning:${normal} You requested to analyze the last ${DAYS_REQUESTED} day(s) (starting from $DATE_REQUESTED),"
        echo "       but last query is from $DATE_LAST_QUERY"
        echo "  [i]  Nothing to do here. Exiting "
        echo
        exit 0
fi

if [ "$TIMESTAMP_REQUESTED" -lt "$TIMESTAMP_FIRST_QUERY" ];
    then
        echo
        echo -e "  [i]  ${bold}Warning:${normal} You requested to analyze the last ${DAYS_REQUESTED} days (starting from $DATE_REQUESTED),"
        echo -e "       but oldest query is from $DATE_FIRST_QUERY. Using this instead\n"
        echo
fi


# save old adlist_configuration
mapfile -t adlist_conf_old_enabled < <(sqlite $GRAVITY "select id from adlist where enabled=1;")

echo "  Would you like to analyze your current adlist configuration or first enable all adlists (current can be restored later)?"
echo
echo "  1)  Current adlist configuration"
echo "  2)  Enable all adlists (runs pihole -g)"
echo

if [ "$AUTOMATIC_MODE" -eq 1 ];
    then
        ENABLE_ALL_ADLISTS_FOR_ANALYSIS=1
        echo "  [i]  Running in automatic mode, keeping current adlist configuration."
fi

while [[ $ENABLE_ALL_ADLISTS_FOR_ANALYSIS != [12] ]]; do
    read -r -p "  Please select: " ENABLE_ALL_ADLISTS_FOR_ANALYSIS
done
echo
echo

if [ "$ENABLE_ALL_ADLISTS_FOR_ANALYSIS" -eq 1 ]; then
    if [ "$AUTOMATIC_MODE" -eq 0 ];
        then
            echo "  [i]  Keeping current adlist configuration"
    fi



    # check if a mismatch between enabled adlists and data in gravity exists, offer to run gravity
    # https://stackoverflow.com/a/28161520
    mapfile -t adlist_enabled_in_gravity < <(sqlite $GRAVITY "select distinct adlist_id from gravity;")
    if [ -n "$(echo "${adlist_conf_old_enabled[@]}" "${adlist_enabled_in_gravity[@]}" | tr ' ' '\n' |sort |uniq -u)" ]; then

        echo
        echo "  [i]  There is a mismatch between your enabled adlists and the data found in the gravity database."
        echo "       You have ${bold}${#adlist_conf_old_enabled[@]}${normal} adlists enabled, but data from ${bold}${#adlist_enabled_in_gravity[@]}${normal} (partially different) adlists in your gravity database."
        echo "       You're likely disabled/enabled adlist without running gravity afterwards."
        echo "       It's highly recommended to run gravity now to solve the differences, otherwise this tool will analyze the available data."
        echo
        echo
        echo "  Would you like to run gravity now?"
        echo
        echo "  1)  Yes"
        echo "  2)  No"
        echo
        if [ "$AUTOMATIC_MODE" -eq 1 ];
            then
                RUN_GRAVITY_NOW=2
                echo "  [i]  Running in automatic mode, not running gravity."
        fi

        while [[ $RUN_GRAVITY_NOW != [12] ]]; do
            read -r -p "  Please select: " RUN_GRAVITY_NOW
        done
        if [ "$RUN_GRAVITY_NOW" -eq 1 ]; then
                echo
                echo
                echo "  [i]  Starting gravity"
                echo
                if [ "$PIHOLE_DOCKER" = 0 ];
                    then
                        pihole -g
                    else
                        docker exec "$CONTAINER_ID" pihole -g
                fi

                echo
                echo "  [✓]  Gravity update finished"
                echo
                echo
                echo
            else
                echo
                echo
                if [ "$AUTOMATIC_MODE" -eq 0 ];
                    then
                        echo "  [i]  Not running gravity, keeping mismatch between enabled adlists and the data found in the gravity database. "
                fi

        fi
    fi
fi


if [ "$ENABLE_ALL_ADLISTS_FOR_ANALYSIS" -eq 2 ]; then

    echo "  [i]  Enabling all adlists...."
    if [ "$PIHOLE_DOCKER" = 0 ];
        then
            sudo bash -c "PIHOLE_DOCKER=0; $SUDO_SQLITE; sqlite $GRAVITY 'UPDATE adlist SET enabled=1;'"
        else
            sqlite $GRAVITY "UPDATE adlist SET enabled=1;"
    fi

    echo
    echo
    echo "  [i]  Starting gravity"
    echo
    if [ "$PIHOLE_DOCKER" = 0 ];
        then
            pihole -g
        else
            docker exec "$CONTAINER_ID" pihole -g
    fi
    echo
    echo "  [✓]  Gravity update finished"
    echo
    echo
    echo
fi

echo
echo
echo "  [i]  Calculating....."
echo "  [i]  This might take some time - please be patient."
#
#
#

# Database manipulation
# To analyze the data this tool creates a temporary database using data provided by pihole-ftl.db and gravity.db
# timeout is set to 5000 ms in which sqlite tries to open an locked database
#
#
# table blocked_domains contains all domains that would have been blocked
# table adlist contauns the info about all adlists and how many domains, hits, unique domains it contains
# table gravity_strip is a stripped version of the gravity database's gravity table which contains only domains that have been visited
# table blacklist_gravity contains all domains that are on the personal blacklist and also in gravity_strip
# table unique_domains contains all domains from gravity_strip that are found just on one adlist
# table cname contains all domains that have been blocked during deep CNAME inspection (not the requested domain, but the actual blocked domain)
# table blacklist_cname contains all domains that are on the personal blacklist and also in cname
# table info contains various pre-calculated information


# create $TEMP_DB
sqlite $TEMP_DB << EOF
    create table blocked_domains (domain TEXT UNIQUE,hits INTEGER);
    create table adlist (id INTEGER, enabled INTEGER, address TEXT, total_domains INTEGER, domains_covered INTEGER, hits_covered INTEGER, unique_domains_covered INTEGER);
    create table gravity_strip (domain TEXT,adlist_id INTEGER);
    create table blacklist_gravity(domain TEXT, hits INTEGER);
    create table unique_domains(domain TEXT, adlist_id INTEGER);
    create table cname(additional_info TEXT,hits INTEGER);
    create table blacklist_cname(domain TEXT, hits INTEGER);
    create table info (property TEXT, value INTEGER);
.exit
EOF



# get all data from $PIHOLE_FTL and $GRAVITY

# 1.) select all domains from pihole-ftl.db that that are also found in gravity.db. Depending on -d n this is limited to the last n days
# 2.) copies id, enable, address, number from gravity.adlist to table adlist
# 3.) strip gravity's gravity table to domains that have been visited (are in blocked_domains table)
# 4.) select all domains that are on the blacklist and also found in gravity_strip
# 5.) update blacklist_gravity with the number of hits for each domain (must be done before CNAME handling, as this adds hits to domains found during CNAME instection)

# CNAME handling

# 6.) table cname selects all domains from pihole-ftl.db (additional_info) that that are also found in gravity.db and have status=9.
#   (status=9 == "Domain contained in gravity database & Blocked during deep CNAME inspection". This is just being cautious, because "additional_info" might contain other domains in the future for purposes different than CNAME inspection)
# 7.) add blocked domains (found by deep CNAME inspection) to gravity_strip
# 8.) add domain and hits found during cname analysis to blocked_domains; if domain is already on the list, only update the hit counter
#    (this is the critical step - "upsert" function was introduced first in sqlite with 3.24)
# 9.) select all domains that are on the blacklist and also found during deep CNAME inspection
# 10.) update blacklist_cname with the number of hits for each domain


sqlite -cmd ".timeout 5000" $TEMP_DB << EOF
    ATTACH DATABASE "${PIHOLE_FTL}" AS pihole_ftl_db;
    ATTACH DATABASE "${GRAVITY}?mode=ro" AS gravity_db;

    INSERT INTO blocked_domains(domain, hits) SELECT domain, COUNT(domain) FROM pihole_ftl_db.queries WHERE EXISTS (select 1 from gravity_db.gravity where gravity.domain=queries.domain) AND id>=${FTL_ID} GROUP BY domain ORDER BY COUNT(domain) DESC;

    INSERT INTO adlist (id, enabled, address, total_domains) SELECT id, enabled, address, number FROM gravity_db.adlist ORDER BY adlist.id;

    INSERT INTO gravity_strip(domain,adlist_id) SELECT gravity_db.gravity.domain, gravity_db.gravity.adlist_id FROM gravity JOIN blocked_domains ON blocked_domains.domain = gravity.domain;

    INSERT INTO blacklist_gravity(domain) SELECT gravity_strip.domain FROM gravity_strip JOIN gravity_db.domainlist on gravity_strip.domain=gravity_db.domainlist.domain WHERE type==1 GROUP BY gravity_strip.domain;

    UPDATE blacklist_gravity SET hits=(SELECT blocked_domains.hits FROM blocked_domains WHERE blocked_domains.domain=blacklist_gravity.domain);

    INSERT INTO cname(additional_info, hits) SELECT additional_info, COUNT(domain) FROM pihole_ftl_db.queries WHERE EXISTS (select 1 from gravity_db.gravity where gravity.domain=queries.additional_info) AND id>=${FTL_ID} AND status=9 GROUP BY additional_info ORDER BY COUNT(additional_info) DESC;

    INSERT OR IGNORE INTO gravity_strip(domain,adlist_id) SELECT gravity_db.gravity.domain, gravity_db.gravity.adlist_id FROM gravity JOIN cname ON cname.additional_info = gravity.domain;

    INSERT INTO blocked_domains (domain, hits) SELECT additional_info,hits FROM cname  WHERE true ON CONFLICT(domain) DO UPDATE SET hits=hits+(SELECT hits FROM cname);

    INSERT INTO blacklist_cname(domain) SELECT cname.additional_info FROM cname JOIN gravity_db.domainlist on cname.additional_info=gravity_db.domainlist.domain WHERE type==1 GROUP BY cname.additional_info;

    UPDATE blacklist_cname SET hits=(SELECT cname.hits FROM cname WHERE cname.additional_info=blacklist_cname.domain);

    DETACH DATABASE gravity_db;
    DETACH DATABASE pihole_ftl_db;
.exit
EOF


# finish database work in $TEMP_DB
#
#
# 1.) select all domains that are only once in gravity_strip (covered unique domains)
# 2.) counts how many domains an adlist would have covered if used alone
# 3.) counts how many hits an adlist would have covered if used alone
# 4.) counts the number of unique domains covered by each adlist
# 5-11.) Calculate some statistics

sqlite $TEMP_DB << EOF
    INSERT INTO unique_domains(domain, adlist_id) SELECT domain, adlist_id FROM gravity_strip GROUP BY domain HAVING COUNT(domain)==1 order by adlist_id asc;
    UPDATE adlist SET domains_covered=(select count(domain) FROM gravity_strip WHERE id== adlist_id GROUP BY adlist_id);
    UPDATE adlist SET hits_covered=(SELECT SUM(blocked_domains.hits) FROM gravity_strip JOIN blocked_domains ON gravity_strip.domain == blocked_domains.domain WHERE id== adlist_id Group by adlist_id);
    UPDATE adlist SET unique_domains_covered=(SELECT COUNT(domain) FROM unique_domains WHERE adlist_id==id GROUP BY adlist_id);

    INSERT INTO info (property, value) Select 'NUM_ADLISTS', COUNT(id) FROM adlist;
    INSERT INTO info (property, value) Select 'NUM_ADLISTS_ENABLED', COUNT(id) FROM adlist WHERE enabled==1;
    INSERT INTO info (property, value) Select 'NUM_DOMAINS_BLOCKED_CURRENT', COUNT(domain) FROM blocked_domains;
    INSERT INTO info (property, value) Select 'HITS_TOTAL_CURRENT', SUM(hits) FROM blocked_domains;
    INSERT INTO info (property, value) Select 'BLACKLIST_GRAVITY', COUNT(*) FROM blacklist_gravity;
    INSERT INTO info (property, value) Select 'NUM_TOTAL_UNIQUE_DOMAINS', COUNT(*) FROM unique_domains;
    INSERT INTO info (property, value) Select 'BLACKLIST_CNAME', COUNT(*) FROM blacklist_cname;
.exit
EOF


# get some statistics
# the number of domains blocked and hits is the sum of enties with status 1 or 9
read -r NUM_DOMAINS_BLOCKED HITS_TOTAL <<<"$(sqlite -separator " " "$PIHOLE_FTL" "SELECT COUNT(DISTINCT domain),count(domain) FROM queries WHERE id>=${FTL_ID} AND status in (1,9);")"

NUM_ADLISTS=$(sqlite $TEMP_DB "SELECT value FROM info where property='NUM_ADLISTS';")
NUM_ADLISTS_ENABLED=$(sqlite $TEMP_DB "SELECT value FROM info where property='NUM_ADLISTS_ENABLED';")
NUM_DOMAINS_BLOCKED_CURRENT=$(sqlite $TEMP_DB "SELECT value FROM info where property='NUM_DOMAINS_BLOCKED_CURRENT';")
HITS_TOTAL_CURRENT=$(sqlite $TEMP_DB "SELECT value FROM info where property='HITS_TOTAL_CURRENT';")
BLACKLIST_GRAVITY=$(sqlite $TEMP_DB "SELECT value FROM info where property='BLACKLIST_GRAVITY';")
NUM_TOTAL_UNIQUE_DOMAINS=$(sqlite $TEMP_DB "SELECT value FROM info where property='NUM_TOTAL_UNIQUE_DOMAINS';")
BLACKLIST_CNAME=$(sqlite $TEMP_DB "SELECT value FROM info where property='BLACKLIST_CNAME';")
NUM_GRAVITY_UNIQUE_DOMAINS=$(sqlite $GRAVITY "SELECT value FROM info WHERE property == 'gravity_count';")


echo
echo "  [i]  You have ${bold}$NUM_ADLISTS adlists${normal} configured ($NUM_ADLISTS_ENABLED enabled)"
echo "  [i]  Your gravity.db contains ${bold}$NUM_GRAVITY_UNIQUE_DOMAINS unique domains${normal}"
echo "  [i]  Since $DATE_FIRST_ANALYZED ${bold}$NUM_DOMAINS_BLOCKED different domains${normal} from your adlists have been blocked ${bold}$HITS_TOTAL times${normal} in total"
echo "       (blocked directly by gravity or during deep CNAME inspection)"
echo "  [i]  Using your current adlist configuration ${bold}$NUM_DOMAINS_BLOCKED_CURRENT domains${normal} would have been blocked ${bold}$HITS_TOTAL_CURRENT times${normal}"

echo
echo
echo


if [ "$BLACKLIST_GRAVITY" -ne 0 ]; then
    echo
    echo
    echo "  [i]  ${bold}You hit a special case${normal}"
    echo "       Your personal blacklist contains at least one domain that is also on an adlist"
    echo "       and has been requested in the selected time period. If it was blocked by gravity,"
    echo "       it got a special status ('blocked by blacklist' instead of 'blocked by gravity')"
    echo "       and is NOT counted on the above number of blocked domains/hits. As the domain is on an"
    echo "       adlist, the number of potentially blocked domains/hits is therefore higher."
    echo
    echo
    sqlite -column -header $TEMP_DB "SELECT * FROM blacklist_gravity"
    echo
    echo "  [i]  Use 'pihole -q DOMAIN' to see which adlist(s) contains the requested domain"
    echo
    echo
    echo
fi

if [ "$BLACKLIST_CNAME" -ne 0 ]; then
    echo
    echo
    echo "  [i]  ${bold}You hit a special case${normal}"
    echo "       Your personal blacklist contains at least one domain that is also on an adlist"
    echo "       and has been blocked in the selected time period by deep CNAME inspection. It got a special"
    echo "       status ('blocked by blacklist during deep CNAME inspection' instead of 'blocked by gravity')"
    echo "       and is NOT counted on the above number of blocked domains/hits. As the domain is on an adlist,"
    echo "       the number of potentially blocked domains/hits is therefore higher."
    echo
    echo
    sqlite -column -header $TEMP_DB "SELECT * FROM blacklist_cname"
    echo
    echo
    echo
fi



echo
echo
# prints n top potentially blocked domains based on -t argument
if [ "$TOP" = 0 ]; then :
    else
        echo
        echo "  [i]  ${bold}Top blocked adlist domains${normal}"
        echo "       Those would have been the ${bold}$TOP top blocked adlist domains${normal} since $DATE_FIRST_ANALYZED"
        echo "       using your current adlist configuration"
        echo
        sqlite -column -header $TEMP_DB "SELECT domain, hits FROM blocked_domains LIMIT ${TOP};"
        echo
        echo
        echo
        echo
fi

echo
echo

echo "  [i]  ${bold}Adlist coverage${normal}"
echo
echo

# prints the adlist table, sorting depends on -s argument
sqlite -column -header $TEMP_DB "SELECT id, enabled, total_domains, domains_covered, hits_covered, unique_domains_covered, address FROM adlist ORDER BY ${SORT_ORDER};"

echo
echo
echo
echo "  [i]  Domains from disabled adlists are not stored in gravity's database."
echo "       If you want to include them in the analysis, run this script again and select 'Enable all adlists'"
echo "       As the same domains usually appears on more than one adlist the sum of covered domains from this table is greater "
echo "       than the number of calculated blocked domains shown above"
echo
echo
echo "  [i]  In total your adlists contain ${bold}$NUM_TOTAL_UNIQUE_DOMAINS visited (covered) unique domains${normal} - meaning those domains are contained only in a single adlist. "

echo
echo


if [ "$ENABLE_ALL_ADLISTS_FOR_ANALYSIS" -eq 1 ];
    then
        echo "  Would you like to ... "
        echo
        echo "  1)  Keep your current adlist configuration"
        echo "  2)  Enable only adlists with covered unique domains"
        echo "  3)  Enable the minimal number of adlists that cover all domains that would have been blocked"
        echo

        if [ "$AUTOMATIC_MODE" -eq 1 ];
            then
                FURTHER_ACTION=1
                echo "  [i]  Running in automatic mode, keeping current adlist configuration."
        fi

        while [[ $FURTHER_ACTION != [123] ]]; do
            read -r -p "  Please select: " FURTHER_ACTION
        done
        if [ "$FURTHER_ACTION" -eq 1 ] && [ "$AUTOMATIC_MODE" -eq 0 ];
            then
                echo
                echo "  [i]  Keeping current adlist configuration"
        fi

    else
        echo "  Would you like to ..."
        echo
        echo "  1)  Keep all adlists enabled"
        echo "  2)  Enable only adlists with covered unique domains"
        echo "  3)  Enable the minimal number of adlists that cover all domains that would have been blocked"
        echo "  4)  Restore previous adlist configuration"
        echo
        while [[ $FURTHER_ACTION != [1234] ]]; do
            read -r -p "  Please select: " FURTHER_ACTION
        done
        if [ "$FURTHER_ACTION" -eq 1 ]; then
            echo
            echo "  [i] Keeping all adlists enabled"
        fi
fi

if [ "$FURTHER_ACTION" -eq 2 ]; then

    echo
    echo "  [i]  Enabling adlists with covered unique domains...."
    if [ "$PIHOLE_DOCKER" = 0 ];
        then
            sudo bash -c "PIHOLE_DOCKER=0; $SUDO_SQLITE; sqlite $GRAVITY 'UPDATE adlist SET enabled=0;'"
        else
            sqlite $GRAVITY "UPDATE adlist SET enabled=0;"
    fi

    mapfile -t adlist_conf_unique_enabled < <(sqlite $TEMP_DB "select id from adlist where unique_domains_covered IS NOT NULL;")
    for adlist_id in "${adlist_conf_unique_enabled[@]}"; do

        if [ "$PIHOLE_DOCKER" = 0 ];
            then
                sudo bash -c "PIHOLE_DOCKER=0; $SUDO_SQLITE; sqlite $GRAVITY 'UPDATE adlist SET enabled=1 where id=$adlist_id;'"
            else
                sqlite $GRAVITY "UPDATE adlist SET enabled=1 where id=$adlist_id;"
        fi
    done

    if [ "$PIHOLE_DOCKER" = 0 ];
        then
            pihole restartdns reload-lists
        else
            docker exec "$CONTAINER_ID" pihole restartdns reload-lists
    fi

    echo
    echo "  [✓]  Adlists with covered unique domains enabled"
    echo
    domains_blocked_future
fi

if [ "$FURTHER_ACTION" -eq 3 ]; then
    echo
    echo "  [i]  Enabling minimum number of adlists that cover all domains that would have been blocked...."
    if [ "$PIHOLE_DOCKER" = 0 ];
        then
            sudo bash -c "PIHOLE_DOCKER=0; $SUDO_SQLITE; sqlite $GRAVITY 'UPDATE adlist SET enabled=0;'"
        else
            sqlite $GRAVITY "UPDATE adlist SET enabled=0;"
    fi


    # get all adlist_ids with unique domains (same as $adlist_conf_unique_enabled)
    # create a copy of gravity_strip where domains can be removed from (gravity_strip is used later again)
    # delete all domains from gravity_dup that are also found on an adlist in the array with the unique domains
    # repeat until gravity_dup is empty
    # get the adlist_id for which there are the most remaining domains on gravity_dup
    # add this adlist_id to the array
    # remove all domains from gravity_dup that are also contained in that adlist
    # count how many domains are still on gravity_dup

    mapfile -t adlist_conf_minimal_enabled < <(sqlite $TEMP_DB "select id from adlist where unique_domains_covered IS NOT NULL;")

    sqlite $TEMP_DB "CREATE TABLE gravity_dup AS SELECT * FROM gravity_strip"

    for adlist_id in "${adlist_conf_minimal_enabled[@]}"; do
        sqlite $TEMP_DB "DELETE FROM gravity_dup WHERE domain IN (SELECT domain from gravity_dup where adlist_id=$adlist_id);"
    done

    LEFT_DOMAINS=$(sqlite $TEMP_DB "SELECT COUNT (domain) from gravity_dup;")

    while [[ ${LEFT_DOMAINS} != 0 ]]; do
        current_id=$(sqlite $TEMP_DB "Select adlist_id from gravity_dup group by adlist_id order by count (domain) desc, adlist_id asc limit 1;");

        adlist_conf_minimal_enabled[${#adlist_conf_minimal_enabled[@]}]="$current_id"
        sqlite $TEMP_DB "DELETE FROM gravity_dup WHERE domain IN (SELECT domain from gravity_dup where adlist_id=$current_id);"
        LEFT_DOMAINS=$(sqlite $TEMP_DB "SELECT COUNT (domain) from gravity_dup;")
    done

    echo
    echo "  [i]  Enabling adlists with id" "${adlist_conf_minimal_enabled[@]}"

    for adlist_id in "${adlist_conf_minimal_enabled[@]}"; do

        if [ "$PIHOLE_DOCKER" = 0 ];
            then
                sudo bash -c "PIHOLE_DOCKER=0; $SUDO_SQLITE; sqlite $GRAVITY 'UPDATE adlist SET enabled=1 where id=$adlist_id;'"
            else
                sqlite $GRAVITY "UPDATE adlist SET enabled=1 where id=$adlist_id;"
        fi
    done

    if [ "$PIHOLE_DOCKER" = 0 ];
        then
            pihole restartdns reload-lists
        else
            docker exec "$CONTAINER_ID" pihole restartdns reload-lists
    fi

    echo
    echo "  [✓]  Minimal number of adlists that cover all domains (that would have been blocked) enabled"
    echo
    domains_blocked_future
fi


if [ "$FURTHER_ACTION" -eq 4 ]; then

    echo
    echo "  [i]  Restoring previous adlist configuration...."

    if [ "$PIHOLE_DOCKER" = 0 ];
            then
                sudo bash -c "PIHOLE_DOCKER=0; $SUDO_SQLITE; sqlite $GRAVITY 'UPDATE adlist SET enabled=0;'"
            else
                sqlite $GRAVITY "UPDATE adlist SET enabled=0;"
    fi

    for adlist_id in "${adlist_conf_old_enabled[@]}"; do

        if [ "$PIHOLE_DOCKER" = 0 ];
            then
                sudo bash -c "PIHOLE_DOCKER=0; $SUDO_SQLITE; sqlite $GRAVITY 'UPDATE adlist SET enabled=1 where id=$adlist_id;'"
            else
                sqlite $GRAVITY "UPDATE adlist SET enabled=1 where id=$adlist_id;"
        fi

    done

    if [ "$PIHOLE_DOCKER" = 0 ];
        then
            pihole restartdns reload-lists
        else
            docker exec "$CONTAINER_ID" pihole restartdns reload-lists
    fi
    echo
    echo "  [✓]  Previous adlist configuration restored"
    echo
    domains_blocked_future
fi



if [ "$UNIQUE" = 1 ];
    then
        echo
        echo
        echo "  [i]  ${bold}Covered unique domains${normal}"
        echo
        sqlite -column -header $TEMP_DB "SELECT domain, adlist_id, address FROM unique_domains JOIN adlist WHERE adlist_id=id;"
        echo
        echo
fi

# analyse RegEx
if [ "$REGEX_MODE" -eq 1 ];
    then
        echo
        echo
        echo "  [i]  Analysing RegEx ....."
        echo "  [i]  This might take some time (minutes!) - please be patient."
        echo
        echo


# table all_regex contains all RegEx from gravity.db
# table regex_black contains all blacklisted RegEx from gravity.db
# table all_domains contains all domains (in the selected time period) from the pihole-FTL.db (including domains from CNAME inspection)
# table domain_by_regex contains all domains and the blocking RegEx
# table domainlist_regex contains all exact domains that are on the personal black/whitelist and covered by RegEx as well

# 1.) copy blacklisted RegEx info from gravity database
# 2.) copy distinct domains from pihole-FTL.db
# 3.) add distinct domains from pihole-FTL.db found in additional_info columen coming from CNAME inspection (status 9,10,11)
# 4.) Save some statistics


sqlite -cmd ".timeout 5000" $TEMP_DB << EOF
            ATTACH DATABASE "${PIHOLE_FTL}" AS pihole_ftl_db;
            ATTACH DATABASE "${GRAVITY}?mode=ro" AS gravity_db;

            CREATE table all_regex (id TEXT UNIQUE, regex TEXT);
            CREATE table regex_black (id TEXT UNIQUE, regex TEXT, enabled INTEGER, domains_covered INTEGER);
            CREATE table all_domains(domain TEXT UNIQUE);
            CREATE table domain_by_regex(domain TEXT, regex_id INTEGER);
            CREATE table domainlist_regex(domain TEXT, regex_id INTEGER, regex TEXT);

            INSERT INTO all_regex(id, regex) SELECT id, domain FROM gravity_db.domainlist WHERE type in (2,3);
            INSERT INTO regex_black(id, regex,enabled) SELECT id, domain, enabled FROM gravity_db.domainlist WHERE type =3;
            INSERT INTO all_domains(domain) SELECT distinct domain FROM pihole_ftl_db.queries WHERE id>=${FTL_ID};
            INSERT OR IGNORE INTO all_domains(domain) SELECT distinct additional_info FROM pihole_ftl_db.queries WHERE status in (9,10,11) AND id>=${FTL_ID};

            INSERT INTO info (property, value) Select 'NUM_ALL_DOMAINS', COUNT(*) FROM all_domains;
            INSERT INTO info (property, value) Select 'NUM_REGEX', COUNT(*) FROM regex_black;
            INSERT INTO info (property, value) Select 'NUM_ENABLED_REGEX', COUNT(id) FROM regex_black WHERE enabled=1;

            DETACH DATABASE gravity_db;
            DETACH DATABASE pihole_ftl_db;
.exit
EOF

# Regex vs. GRAVITY
# copy all domains from table all_domais in array all_domains
# iterate over each domain in all_domains
# for each domain check if it is covered by a RegEx (using pihole-FTL regex-test)
# if the test returns regex_ids, save them in domain_by_regex table
# NOTE: pihole-FTL regex-test will test also against RegEx whitelist BUT this is still much faster than to create a second loop to check against each RegEx blacklist individually


        mapfile -t all_domains < <(sqlite $TEMP_DB "SELECT domain FROM all_domains")

        for CURRENT_DOMAIN in "${all_domains[@]}"; do
                pihole-FTL regex-test "$CURRENT_DOMAIN" |grep -E -o "DB ID [0-9]*"|awk '{print $3}' | while read -r REGEX_ID; do
                    sqlite $TEMP_DB "INSERT INTO domain_by_regex(domain, regex_id) VALUES ('$CURRENT_DOMAIN',$REGEX_ID);"
            done
        done

# count for each RegEx_id how many domains are in domain_by_regex and store it in table regex_black

        sqlite $TEMP_DB "UPDATE regex_black SET domains_covered=(SELECT COUNT(regex_id) from domain_by_regex WHERE id=regex_id GROUP BY regex_id );"


# Regex vs domainlist
# copy all exact black/whitelisted domains from gravity's domainlist (type=1 or type=0)
# iterate over each domain
# for each domain check if it is covered by a RegEx (using pihole-FTL regex-test)
# if the test returns regex_ids, save them in domainlist_regex

        mapfile -t all_exact_domains < <(sqlite $GRAVITY "SELECT domain FROM domainlist WHERE type in (0,1);")

        for CURRENT_DOMAIN in "${all_exact_domains[@]}"; do
            pihole-FTL regex-test "$CURRENT_DOMAIN" |grep -E -o "DB ID [0-9]*"|awk '{print $3}' | while read -r REGEX_ID; do
                sqlite $TEMP_DB "INSERT INTO domainlist_regex(domain, regex_id) VALUES ('$CURRENT_DOMAIN',$REGEX_ID);"
            done
        done

        sqlite $TEMP_DB "UPDATE domainlist_regex SET regex=(SELECT regex from all_regex WHERE id=regex_id);"

# get stats
        # the number of different domains that would have been blocked by RegEx with the current RegEx configuration
        sqlite $TEMP_DB "INSERT INTO info (property, value) Select 'NUM_DOMAINS_BLOCKED_BY_REGEX', COUNT (distinct domain) FROM domain_by_regex JOIN regex_black ON regex_id=id where enabled=1 ;"

        # the number of domainlist domains covered by RegEx
        sqlite $TEMP_DB "INSERT INTO info (property, value) Select 'DOMAINLIST_REGEX', COUNT(distinct domain) FROM domainlist_regex;"

        NUM_ALL_DOMAINS=$(sqlite $TEMP_DB "SELECT value FROM info where property='NUM_ALL_DOMAINS';")
        NUM_REGEX=$(sqlite $TEMP_DB "SELECT value FROM info where property='NUM_REGEX';")
        NUM_ENABLED_REGEX=$(sqlite $TEMP_DB  "SELECT value FROM info WHERE property ='NUM_ENABLED_REGEX';")
        NUM_DOMAINS_BLOCKED_BY_REGEX=$(sqlite $TEMP_DB  "SELECT value FROM info WHERE property ='NUM_DOMAINS_BLOCKED_BY_REGEX';")
        NUM_DOMAINLIST_REGEX=$(sqlite $TEMP_DB  "SELECT value FROM info WHERE property ='DOMAINLIST_REGEX';")

        echo
        echo
        echo
        echo "  [i]  ${bold}Blacklist RegEx coverage${normal}"
        # prints the RegEx table
        echo
        echo
        sqlite -column -header $TEMP_DB "SELECT id, enabled, domains_covered, regex FROM regex_black;"
        echo
        echo
        echo "  [i]  Since $DATE_FIRST_ANALYZED you have been visiting ${bold}$NUM_ALL_DOMAINS different domains${normal}."
        echo "       You have ${bold}$NUM_REGEX blacklist RegEx${normal} configured ($NUM_ENABLED_REGEX enabled)"
        echo "       With your enabled blacklist RegEx you would have covered ${bold}$NUM_DOMAINS_BLOCKED_BY_REGEX different domains${normal}."
        echo
        echo "  [i]  Please note: the internal Pi-hole RegEx test used here only checks domains against ${bold}enabled RegEx${normal}."
        echo "       Therefore, currently disabled RegEx will always have 0 domains covered."


        if [ "$NUM_DOMAINLIST_REGEX" -ne 0 ]; then
            echo
            echo
            echo "  [i]  ${bold}You hit a special case${normal}"
            echo "       Your personal domainlist (black- and whitelist) contains at least one domain that is also covered by at least"
            echo "       one of your RegEx (black- and whitelist)"
            echo
            echo
            sqlite -column -header $TEMP_DB "SELECT * FROM domainlist_regex"
            echo
            echo
            echo "       ${bold}$NUM_DOMAINLIST_REGEX distinct domain(s)${normal} from your domainlist are covered by your RegEx."

            echo "       Please note that this tool does not take into account if your domains and RegEx do match"
            echo "       regarding their black/whitelist status. E.g. a whitelisted domain that is covered by a blacklist RegEx"
            echo "       is counted and reported here as well."
            echo
            echo "  [i]  Use 'pihole-FTL regex-test DOMAIN' to get more information about that specific domain vs the RegEx filters"
            echo
            echo
        fi
fi


remove_temp_database