path: root/trackers/src/main/java/foundation/e/advancedprivacy/trackers/data/StatsDatabase.kt

/*
 * Copyright (C) 2023 MURENA SAS
 * Copyright (C) 2022 E FOUNDATION
 *
 * This program is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this program.  If not, see <https://www.gnu.org/licenses/>.
 */

package foundation.e.advancedprivacy.trackers.data

import android.content.ContentValues
import android.content.Context
import android.database.Cursor
import android.database.sqlite.SQLiteDatabase
import android.database.sqlite.SQLiteOpenHelper
import android.provider.BaseColumns
import androidx.core.database.getStringOrNull
import foundation.e.advancedprivacy.trackers.data.StatsDatabase.AppTrackerEntry.COLUMN_NAME_APPID
import foundation.e.advancedprivacy.trackers.data.StatsDatabase.AppTrackerEntry.COLUMN_NAME_NUMBER_BLOCKED
import foundation.e.advancedprivacy.trackers.data.StatsDatabase.AppTrackerEntry.COLUMN_NAME_NUMBER_CONTACTED
import foundation.e.advancedprivacy.trackers.data.StatsDatabase.AppTrackerEntry.COLUMN_NAME_TIMESTAMP
import foundation.e.advancedprivacy.trackers.data.StatsDatabase.AppTrackerEntry.COLUMN_NAME_TRACKER
import foundation.e.advancedprivacy.trackers.data.StatsDatabase.AppTrackerEntry.TABLE_NAME
import foundation.e.advancedprivacy.trackers.domain.entities.Tracker
import timber.log.Timber
import java.time.ZonedDateTime
import java.time.format.DateTimeFormatter
import java.time.temporal.ChronoUnit
import java.time.temporal.TemporalUnit
import java.util.concurrent.TimeUnit

class StatsDatabase(
    context: Context,
    private val trackersRepository: TrackersRepository
) :
    SQLiteOpenHelper(context, DATABASE_NAME, null, DATABASE_VERSION) {

    companion object {
        const val DATABASE_VERSION = 2
        const val DATABASE_NAME = "TrackerFilterStats.db"
        private const val SQL_CREATE_TABLE = "CREATE TABLE $TABLE_NAME (" +
            "${BaseColumns._ID} INTEGER PRIMARY KEY," +
            "$COLUMN_NAME_TIMESTAMP INTEGER," +
            "$COLUMN_NAME_TRACKER TEXT," +
            "$COLUMN_NAME_NUMBER_CONTACTED INTEGER," +
            "$COLUMN_NAME_NUMBER_BLOCKED INTEGER," +
            "$COLUMN_NAME_APPID TEXT)"

        private const val PROJECTION_NAME_PERIOD = "period"
        private const val PROJECTION_NAME_CONTACTED_SUM = "contactedsum"
        private const val PROJECTION_NAME_BLOCKED_SUM = "blockedsum"
        private const val PROJECTION_NAME_LEAKED_SUM = "leakedsum"
        private const val PROJECTION_NAME_TRACKERS_COUNT = "trackerscount"

        private val MIGRATE_1_2 = listOf(
            "ALTER TABLE $TABLE_NAME ADD COLUMN $COLUMN_NAME_APPID TEXT"
            // "ALTER TABLE $TABLE_NAME DROP COLUMN app_uid"
            // DROP COLUMN is available since sqlite 3.35.0, and sdk29 as 3.22.0, sdk32 as 3.32.2
        )
    }

    object AppTrackerEntry : BaseColumns {
        const val TABLE_NAME = "tracker_filter_stats"
        const val COLUMN_NAME_TIMESTAMP = "timestamp"
        const val COLUMN_NAME_TRACKER = "tracker"
        const val COLUMN_NAME_NUMBER_CONTACTED = "sum_contacted"
        const val COLUMN_NAME_NUMBER_BLOCKED = "sum_blocked"
        const val COLUMN_NAME_APPID = "app_apid"
    }

    private var projection = arrayOf(
        COLUMN_NAME_TIMESTAMP,
        COLUMN_NAME_TRACKER,
        COLUMN_NAME_NUMBER_CONTACTED,
        COLUMN_NAME_NUMBER_BLOCKED,
        COLUMN_NAME_APPID
    )

    private val lock = Any()

    override fun onCreate(db: SQLiteDatabase) {
        db.execSQL(SQL_CREATE_TABLE)
    }

    override fun onUpgrade(db: SQLiteDatabase, oldVersion: Int, newVersion: Int) {
        if (oldVersion == 1 && newVersion == 2) {
            MIGRATE_1_2.forEach(db::execSQL)
        } else {
            Timber.e(
                "Unexpected database versions: oldVersion: $oldVersion ; newVersion: $newVersion"
            )
        }
    }

    private fun getCallsByPeriod(
        periodsCount: Int,
        periodUnit: TemporalUnit,
        sqlitePeriodFormat: String
    ): Map<String, Pair<Int, Int>> {
        synchronized(lock) {
            val minTimestamp = getPeriodStartTs(periodsCount, periodUnit)
            val db = readableDatabase
            val selection = "$COLUMN_NAME_TIMESTAMP >= ?"
            val selectionArg = arrayOf("" + minTimestamp)

            val projection = (
                "$COLUMN_NAME_TIMESTAMP, " +
                    "STRFTIME('$sqlitePeriodFormat', DATETIME($COLUMN_NAME_TIMESTAMP, 'unixepoch', 'localtime')) $PROJECTION_NAME_PERIOD," +
                    "SUM($COLUMN_NAME_NUMBER_CONTACTED) $PROJECTION_NAME_CONTACTED_SUM, " +
                    "SUM($COLUMN_NAME_NUMBER_BLOCKED) $PROJECTION_NAME_BLOCKED_SUM"
                )

            val cursor = db.rawQuery(
                "SELECT $projection FROM $TABLE_NAME WHERE $selection" +
                    " GROUP BY $PROJECTION_NAME_PERIOD" +
                    " ORDER BY $COLUMN_NAME_TIMESTAMP DESC LIMIT $periodsCount",
                selectionArg
            )
            val callsByPeriod = HashMap<String, Pair<Int, Int>>()
            while (cursor.moveToNext()) {
                val contacted = cursor.getInt(PROJECTION_NAME_CONTACTED_SUM)
                val blocked = cursor.getInt(PROJECTION_NAME_BLOCKED_SUM)
                callsByPeriod[cursor.getString(PROJECTION_NAME_PERIOD)] = blocked to contacted - blocked
            }
            cursor.close()
            db.close()
            return callsByPeriod
        }
    }

    private fun callsByPeriodToPeriodsList(
        callsByPeriod: Map<String, Pair<Int, Int>>,
        periodsCount: Int,
        periodUnit: TemporalUnit,
        javaPeriodFormat: String
    ): List<Pair<Int, Int>> {
        var currentDate = ZonedDateTime.now().minus(periodsCount.toLong(), periodUnit)
        val formatter = DateTimeFormatter.ofPattern(javaPeriodFormat)
        val calls = mutableListOf<Pair<Int, Int>>()
        for (i in 0 until periodsCount) {
            currentDate = currentDate.plus(1, periodUnit)
            val currentPeriod = formatter.format(currentDate)
            calls.add(callsByPeriod.getOrDefault(currentPeriod, 0 to 0))
        }
        return calls
    }

    fun getTrackersCallsOnPeriod(
        periodsCount: Int,
        periodUnit: TemporalUnit
    ): List<Pair<Int, Int>> {
        var sqlitePeriodFormat = "%Y%m"
        var javaPeriodFormat = "yyyyMM"
        if (periodUnit === ChronoUnit.MONTHS) {
            sqlitePeriodFormat = "%Y%m"
            javaPeriodFormat = "yyyyMM"
        } else if (periodUnit === ChronoUnit.DAYS) {
            sqlitePeriodFormat = "%Y%m%d"
            javaPeriodFormat = "yyyyMMdd"
        } else if (periodUnit === ChronoUnit.HOURS) {
            sqlitePeriodFormat = "%Y%m%d%H"
            javaPeriodFormat = "yyyyMMddHH"
        }
        val callsByPeriod = getCallsByPeriod(periodsCount, periodUnit, sqlitePeriodFormat)
        return callsByPeriodToPeriodsList(callsByPeriod, periodsCount, periodUnit, javaPeriodFormat)
    }

    fun getActiveTrackersByPeriod(periodsCount: Int, periodUnit: TemporalUnit): Int {
        synchronized(lock) {
            val minTimestamp = getPeriodStartTs(periodsCount, periodUnit)
            val db = writableDatabase
            val selection = "$COLUMN_NAME_TIMESTAMP >= ? AND " +
                "$COLUMN_NAME_NUMBER_CONTACTED > $COLUMN_NAME_NUMBER_BLOCKED"
            val selectionArg = arrayOf("" + minTimestamp)
            val projection =
                "COUNT(DISTINCT $COLUMN_NAME_TRACKER) $PROJECTION_NAME_TRACKERS_COUNT"

            val cursor = db.rawQuery(
                "SELECT $projection FROM $TABLE_NAME WHERE $selection",
                selectionArg
            )
            var count = 0
            if (cursor.moveToNext()) {
                count = cursor.getInt(0)
            }
            cursor.close()
            db.close()
            return count
        }
    }

    fun getContactedTrackersCount(): Int {
        synchronized(lock) {
            val db = readableDatabase
            var query = "SELECT DISTINCT $COLUMN_NAME_TRACKER FROM $TABLE_NAME"

            val cursor = db.rawQuery(query, arrayOf())
            var count = 0
            while (cursor.moveToNext()) {
                trackersRepository.getTracker(cursor.getString(COLUMN_NAME_TRACKER))?.let {
                    count++
                }
            }
            cursor.close()
            db.close()
            return count
        }
    }

    fun getContactedTrackersCountByAppId(): Map<String, Int> {
        synchronized(lock) {
            val db = readableDatabase
            val projection = "$COLUMN_NAME_APPID, $COLUMN_NAME_TRACKER"
            val cursor = db.rawQuery(
                "SELECT DISTINCT $projection FROM $TABLE_NAME", // +
                arrayOf()
            )
            val countByApp = mutableMapOf<String, Int>()
            while (cursor.moveToNext()) {
                trackersRepository.getTracker(cursor.getString(COLUMN_NAME_TRACKER))?.let {
                    val appId = cursor.getString(COLUMN_NAME_APPID)
                    countByApp[appId] = countByApp.getOrDefault(appId, 0) + 1
                }
            }
            cursor.close()
            db.close()
            return countByApp
        }
    }

    fun getCallsByAppIds(periodCount: Int, periodUnit: TemporalUnit): Map<String, Pair<Int, Int>> {
        synchronized(lock) {
            val minTimestamp = getPeriodStartTs(periodCount, periodUnit)
            val db = readableDatabase
            val selection = "$COLUMN_NAME_TIMESTAMP >= ?"
            val selectionArg = arrayOf("" + minTimestamp)
            val projection = "$COLUMN_NAME_APPID, " +
                "SUM($COLUMN_NAME_NUMBER_CONTACTED) $PROJECTION_NAME_CONTACTED_SUM," +
                "SUM($COLUMN_NAME_NUMBER_BLOCKED) $PROJECTION_NAME_BLOCKED_SUM"
            val cursor = db.rawQuery(
                "SELECT $projection FROM $TABLE_NAME" +
                    " WHERE $selection" +
                    " GROUP BY $COLUMN_NAME_APPID",
                selectionArg
            )
            val callsByApp = HashMap<String, Pair<Int, Int>>()
            while (cursor.moveToNext()) {
                val contacted = cursor.getInt(PROJECTION_NAME_CONTACTED_SUM)
                val blocked = cursor.getInt(PROJECTION_NAME_BLOCKED_SUM)
                callsByApp[cursor.getString(COLUMN_NAME_APPID)] = blocked to contacted - blocked
            }
            cursor.close()
            db.close()
            return callsByApp
        }
    }

    fun getCalls(appId: String, periodCount: Int, periodUnit: TemporalUnit): Pair<Int, Int> {
        synchronized(lock) {
            val minTimestamp = getPeriodStartTs(periodCount, periodUnit)
            val db = readableDatabase
            val selection = "$COLUMN_NAME_APPID = ? AND " +
                "$COLUMN_NAME_TIMESTAMP >= ?"
            val selectionArg = arrayOf("" + appId, "" + minTimestamp)
            val projection =
                "SUM($COLUMN_NAME_NUMBER_CONTACTED) $PROJECTION_NAME_CONTACTED_SUM," +
                    "SUM($COLUMN_NAME_NUMBER_BLOCKED) $PROJECTION_NAME_BLOCKED_SUM"
            val cursor = db.rawQuery(
                "SELECT $projection FROM $TABLE_NAME WHERE $selection",
                selectionArg
            )
            var calls: Pair<Int, Int> = 0 to 0
            if (cursor.moveToNext()) {
                val contacted = cursor.getInt(PROJECTION_NAME_CONTACTED_SUM)
                val blocked = cursor.getInt(PROJECTION_NAME_BLOCKED_SUM)
                calls = blocked to contacted - blocked
            }
            cursor.close()
            db.close()
            return calls
        }
    }

    fun getMostLeakedAppId(periodCount: Int, periodUnit: TemporalUnit): String {
        synchronized(lock) {
            val minTimestamp = getPeriodStartTs(periodCount, periodUnit)
            val db = readableDatabase
            val selection = "$COLUMN_NAME_TIMESTAMP >= ?"
            val selectionArg = arrayOf("" + minTimestamp)
            val projection = "$COLUMN_NAME_APPID, " +
                "SUM($COLUMN_NAME_NUMBER_CONTACTED - $COLUMN_NAME_NUMBER_BLOCKED) $PROJECTION_NAME_LEAKED_SUM"
            val cursor = db.rawQuery(
                "SELECT $projection FROM $TABLE_NAME" +
                    " WHERE $selection" +
                    " GROUP BY $COLUMN_NAME_APPID" +
                    " ORDER BY $PROJECTION_NAME_LEAKED_SUM DESC LIMIT 1",
                selectionArg
            )
            var appId = ""
            if (cursor.moveToNext()) {
                appId = cursor.getString(COLUMN_NAME_APPID)
            }
            cursor.close()
            db.close()
            return appId
        }
    }

    fun logAccess(trackerId: String?, appId: String, blocked: Boolean) {
        synchronized(lock) {
            val currentHour = getCurrentHourTs()
            val db = writableDatabase
            val values = ContentValues()
            values.put(COLUMN_NAME_APPID, appId)
            values.put(COLUMN_NAME_TRACKER, trackerId)
            values.put(COLUMN_NAME_TIMESTAMP, currentHour)

            /*String query = "UPDATE product SET "+COLUMN_NAME_NUMBER_CONTACTED+" = "+COLUMN_NAME_NUMBER_CONTACTED+" + 1 ";
        if(blocked)
            query+=COLUMN_NAME_NUMBER_BLOCKED+" = "+COLUMN_NAME_NUMBER_BLOCKED+" + 1 ";
*/
            val selection = "$COLUMN_NAME_TIMESTAMP = ? AND " +
                "$COLUMN_NAME_APPID = ? AND " +
                "$COLUMN_NAME_TRACKER = ? "
            val selectionArg = arrayOf("" + currentHour, "" + appId, trackerId)
            val cursor = db.query(
                TABLE_NAME,
                projection,
                selection,
                selectionArg,
                null,
                null,
                null
            )
            if (cursor.count > 0) {
                cursor.moveToFirst()
                val entry = cursorToEntry(cursor)
                if (blocked) values.put(
                    COLUMN_NAME_NUMBER_BLOCKED,
                    entry.sum_blocked + 1
                ) else values.put(COLUMN_NAME_NUMBER_BLOCKED, entry.sum_blocked)
                values.put(COLUMN_NAME_NUMBER_CONTACTED, entry.sum_contacted + 1)
                db.update(TABLE_NAME, values, selection, selectionArg)

                // db.execSQL(query, new String[]{""+hour, ""+day, ""+month, ""+year, ""+appUid, ""+trackerId});
            } else {
                if (blocked) values.put(
                    COLUMN_NAME_NUMBER_BLOCKED,
                    1
                ) else values.put(COLUMN_NAME_NUMBER_BLOCKED, 0)
                values.put(COLUMN_NAME_NUMBER_CONTACTED, 1)
                db.insert(TABLE_NAME, null, values)
            }
            cursor.close()
            db.close()
        }
    }

    private fun cursorToEntry(cursor: Cursor): StatEntry {
        val entry = StatEntry()
        entry.timestamp = cursor.getLong(COLUMN_NAME_TIMESTAMP)
        entry.appId = cursor.getString(COLUMN_NAME_APPID)
        entry.sum_blocked = cursor.getInt(COLUMN_NAME_NUMBER_BLOCKED)
        entry.sum_contacted = cursor.getInt(COLUMN_NAME_NUMBER_CONTACTED)
        entry.tracker = cursor.getInt(COLUMN_NAME_TRACKER)
        return entry
    }

    fun getTrackers(appIds: List<String>?): List<Tracker> {
        synchronized(lock) {
            val columns = arrayOf(COLUMN_NAME_TRACKER, COLUMN_NAME_APPID)
            var selection: String? = null

            var selectionArg: Array<String>? = null
            appIds?.let { appIds ->
                selection = "$COLUMN_NAME_APPID IN (${appIds.joinToString(", ") { "'$it'" }})"
                selectionArg = arrayOf()
            }

            val db = readableDatabase
            val cursor = db.query(
                true,
                TABLE_NAME,
                columns,
                selection,
                selectionArg,
                null,
                null,
                null,
                null
            )
            val trackers: MutableList<Tracker> = ArrayList()
            while (cursor.moveToNext()) {
                val trackerId = cursor.getString(COLUMN_NAME_TRACKER)
                val tracker = trackersRepository.getTracker(trackerId)
                if (tracker != null) {
                    trackers.add(tracker)
                }
            }
            cursor.close()
            db.close()
            return trackers
        }
    }

    class StatEntry {
        var appId = ""
        var sum_contacted = 0
        var sum_blocked = 0
        var timestamp: Long = 0
        var tracker = 0
    }

    private fun getCurrentHourTs(): Long {
        val hourInMs = TimeUnit.HOURS.toMillis(1L)
        val hourInS = TimeUnit.HOURS.toSeconds(1L)
        return System.currentTimeMillis() / hourInMs * hourInS
    }

    private fun getPeriodStartTs(
        periodsCount: Int,
        periodUnit: TemporalUnit
    ): Long {
        var start = ZonedDateTime.now()
            .minus(periodsCount.toLong(), periodUnit)
            .plus(1, periodUnit)
        var truncatePeriodUnit = periodUnit
        if (periodUnit === ChronoUnit.MONTHS) {
            start = start.withDayOfMonth(1)
            truncatePeriodUnit = ChronoUnit.DAYS
        }
        return start.truncatedTo(truncatePeriodUnit).toEpochSecond()
    }

    private fun Cursor.getInt(columnName: String): Int {
        val columnIndex = getColumnIndex(columnName)
        return if (columnIndex >= 0) getInt(columnIndex) else 0
    }

    private fun Cursor.getLong(columnName: String): Long {
        val columnIndex = getColumnIndex(columnName)
        return if (columnIndex >= 0) getLong(columnIndex) else 0
    }

    private fun Cursor.getString(columnName: String): String {
        val columnIndex = getColumnIndex(columnName)
        return if (columnIndex >= 0) {
            getStringOrNull(columnIndex) ?: ""
        } else ""
    }
}