package app.attestation.server;

import app.attestation.server.AttestationProtocol.DeviceInfo;
import app.attestation.server.attestation.ParsedAttestationRecord;
import com.almworks.sqlite4java.SQLiteConnection;
import com.almworks.sqlite4java.SQLiteException;
import com.almworks.sqlite4java.SQLiteStatement;
import com.github.benmanes.caffeine.cache.Cache;
import com.github.benmanes.caffeine.cache.Caffeine;
import com.google.common.io.BaseEncoding;
import com.google.common.primitives.Bytes;
import com.google.common.util.concurrent.ThreadFactoryBuilder;
import com.google.zxing.BarcodeFormat;
import com.google.zxing.EncodeHintType;
import com.google.zxing.WriterException;
import com.google.zxing.client.j2se.MatrixToImageWriter;
import com.google.zxing.common.BitMatrix;
import com.google.zxing.qrcode.QRCodeWriter;
import com.sun.net.httpserver.HttpExchange;
import com.sun.net.httpserver.HttpHandler;
import com.sun.net.httpserver.HttpServer;
import jakarta.json.JsonArrayBuilder;
import jakarta.json.JsonException;
import jakarta.json.JsonObject;
import jakarta.json.JsonObjectBuilder;
import jakarta.json.JsonReader;
import jakarta.json.JsonWriter;
import jakarta.mail.internet.AddressException;
import jakarta.mail.internet.InternetAddress;
import org.bouncycastle.crypto.generators.SCrypt;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.DataInputStream;
import java.io.EOFException;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.InetSocketAddress;
import java.nio.BufferUnderflowException;
import java.nio.ByteBuffer;
import java.nio.CharBuffer;
import java.nio.charset.CharacterCodingException;
import java.nio.charset.StandardCharsets;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.security.cert.Certificate;
import java.security.GeneralSecurityException;
import java.security.MessageDigest;
import java.security.SecureRandom;
import java.util.Base64;
import java.util.EnumMap;
import java.util.List;
import java.util.Map;
import java.util.concurrent.LinkedBlockingQueue;
import java.util.concurrent.ThreadPoolExecutor;
import java.util.concurrent.TimeUnit;
import java.util.logging.ConsoleHandler;
import java.util.logging.Handler;
import java.util.logging.Level;
import java.util.logging.Logger;
import java.util.zip.DataFormatException;

import static app.attestation.server.AttestationProtocol.fingerprintsNonStock;
import static app.attestation.server.AttestationProtocol.fingerprintsStock;
import static app.attestation.server.AttestationProtocol.fingerprintsStrongBoxNonStock;
import static app.attestation.server.AttestationProtocol.fingerprintsStrongBoxStock;
import static app.attestation.server.SyslogLevel.ALERT;
import static com.almworks.sqlite4java.SQLiteConstants.SQLITE_CONSTRAINT_UNIQUE;

class AttestationServer {
    static final File ATTESTATION_DATABASE = new File("attestation.db");
    static final File SAMPLES_DATABASE = new File("samples.db");
    private static final int MAX_SAMPLE_SIZE = 128 * 1024;

    private static final int DEFAULT_VERIFY_INTERVAL = 6 * 60 * 60;
    private static final int MIN_VERIFY_INTERVAL = 60 * 60;
    private static final int MAX_VERIFY_INTERVAL = 7 * 24 * 70 * 60;
    private static final int DEFAULT_ALERT_DELAY = 48 * 60 * 60;
    private static final int MIN_ALERT_DELAY = 32 * 60 * 60;
    private static final int MAX_ALERT_DELAY = 2 * 7 * 24 * 60 * 60;
    private static final int BUSY_TIMEOUT = 10 * 1000;
    private static final int QR_CODE_PIXEL_SIZE = 300;
    private static final long SESSION_LENGTH = 48 * 60 * 60 * 1000;
    private static final int HISTORY_PER_PAGE = 20;
    private static final long MMAP_SIZE = 1024 * 1024 * 1024;

    static final String DOMAIN = "attestation.app";
    private static final String ORIGIN = "https://" + DOMAIN;

    private static final long POST_START_DELAY_MS = 1000;

    private static final Logger logger = Logger.getLogger(AttestationServer.class.getName());

    // This should be moved to a table in the database so that it can be modified dynamically
    // without modifying the source code.
    private static final String[] emailBlacklistPatterns = {
        "(contact|security|webmaster)@(attestation.app|grapheneos.org|seamlessupdate.app)"
    };

    private static final Cache<ByteBuffer, Boolean> pendingChallenges = Caffeine.newBuilder()
            .expireAfterWrite(1, TimeUnit.MINUTES)
            .maximumSize(1000000)
            .build();

    static SQLiteConnection open(final File db) throws SQLiteException {
        final SQLiteConnection conn = new SQLiteConnection(db);
        conn.open();
        try {
            conn.setBusyTimeout(BUSY_TIMEOUT);
            conn.exec("PRAGMA foreign_keys = ON");
            conn.exec("PRAGMA journal_mode = WAL");
            conn.exec("PRAGMA trusted_schema = OFF");
            conn.exec("PRAGMA mmap_size = " + MMAP_SIZE);
        } catch (final Exception e) {
            conn.dispose();
            throw e;
        }
        return conn;
    }

    private static final ThreadLocal<SQLiteConnection> localAttestationConn = new ThreadLocal<>();

    static SQLiteConnection getLocalAttestationConn() throws SQLiteException {
        SQLiteConnection conn = localAttestationConn.get();
        if (conn != null) {
            return conn;
        }
        conn = open(ATTESTATION_DATABASE);
        localAttestationConn.set(conn);
        return conn;
    }

    static void rollbackIfNeeded(final SQLiteConnection conn) throws SQLiteException {
        if (!conn.getAutoCommit()) {
            conn.exec("ROLLBACK");
        }
    }

    private static int getUserVersion(final SQLiteConnection conn) throws SQLiteException {
        final SQLiteStatement pragmaUserVersion = conn.prepare("PRAGMA user_version");
        try {
            pragmaUserVersion.step();
            int userVersion = pragmaUserVersion.columnInt(0);
            logger.info("Existing schema version: " + userVersion);
            return userVersion;
        } finally {
            pragmaUserVersion.dispose();
        }
    }

    private static final String CREATE_SAMPLES_TABLE = """
            CREATE TABLE IF NOT EXISTS Samples (
                sample BLOB NOT NULL,
                time INTEGER NOT NULL
            ) STRICT""";

    private static void setupSamplesDatabase() throws SQLiteException {
        final SQLiteConnection conn = open(SAMPLES_DATABASE);
        try {
            final SQLiteStatement selectCreated = conn.prepare(
                    "SELECT 1 FROM sqlite_master WHERE type='table' AND name='Samples'");
            if (!selectCreated.step()) {
                conn.exec("PRAGMA user_version = 1");
            }
            selectCreated.dispose();

            int userVersion = getUserVersion(conn);

            conn.exec(CREATE_SAMPLES_TABLE);

            if (userVersion < 1) {
                logger.log(ALERT, SAMPLES_DATABASE + " database schemas older than version 1 are no longer " +
                        "supported. Use an older AttestationServer revision to upgrade.");
                System.exit(1);
            }

            logger.info("Finished database setup for " + SAMPLES_DATABASE);
        } finally {
            conn.dispose();
        }
    }

    private static final String CREATE_ATTESTATION_TABLES = """
            CREATE TABLE IF NOT EXISTS Configuration (
                key TEXT PRIMARY KEY NOT NULL,
                value ANY NOT NULL
            ) STRICT;

            CREATE TABLE IF NOT EXISTS Accounts (
                userId INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
                username TEXT NOT NULL COLLATE NOCASE UNIQUE,
                passwordHash BLOB NOT NULL,
                passwordSalt BLOB NOT NULL,
                subscribeKey BLOB NOT NULL,
                creationTime INTEGER NOT NULL,
                loginTime INTEGER NOT NULL,
                verifyInterval INTEGER NOT NULL,
                alertDelay INTEGER NOT NULL
            ) STRICT;

            CREATE TABLE IF NOT EXISTS EmailAddresses (
                userId INTEGER NOT NULL REFERENCES Accounts (userId) ON DELETE CASCADE,
                address TEXT NOT NULL,
                PRIMARY KEY (userId, address)
            ) STRICT;

            CREATE TABLE IF NOT EXISTS Sessions (
                sessionId INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
                userId INTEGER NOT NULL REFERENCES Accounts (userId) ON DELETE CASCADE,
                token BLOB NOT NULL,
                expiryTime INTEGER NOT NULL
            ) STRICT;

            CREATE TABLE IF NOT EXISTS Devices (
                fingerprint BLOB NOT NULL PRIMARY KEY,
                pinnedCertificates BLOB NOT NULL,
                attestKey INTEGER NOT NULL CHECK (attestKey in (0, 1)),
                pinnedVerifiedBootKey BLOB NOT NULL,
                verifiedBootHash BLOB,
                pinnedOsVersion INTEGER NOT NULL,
                pinnedOsPatchLevel INTEGER NOT NULL,
                pinnedVendorPatchLevel INTEGER,
                pinnedBootPatchLevel INTEGER,
                pinnedAppVersion INTEGER NOT NULL,
                pinnedAppVariant INTEGER NOT NULL CHECK (pinnedAppVariant in (0, 1, 2)),
                pinnedSecurityLevel INTEGER NOT NULL,
                userProfileSecure INTEGER NOT NULL CHECK (userProfileSecure in (0, 1)),
                enrolledBiometrics INTEGER NOT NULL CHECK (enrolledBiometrics in (0, 1)),
                accessibility INTEGER NOT NULL CHECK (accessibility in (0, 1)),
                deviceAdmin INTEGER NOT NULL CHECK (deviceAdmin in (0, 1, 2)),
                adbEnabled INTEGER NOT NULL CHECK (adbEnabled in (0, 1)),
                addUsersWhenLocked INTEGER NOT NULL CHECK (addUsersWhenLocked in (0, 1)),
                oemUnlockAllowed INTEGER NOT NULL CHECK (oemUnlockAllowed in (0, 1)),
                systemUser INTEGER NOT NULL CHECK (systemUser in (0, 1)),
                autoRebootSeconds INTEGER CHECK (autoRebootSeconds == -2 OR autoRebootSeconds == 0 OR autoRebootSeconds >= 20),
                portSecurityMode INTEGER CHECK (portSecurityMode == -2 OR portSecurityMode >= 0),
                userCount INTEGER CHECK (userCount == -2 OR userCount >= 1),
                oemUnlockAllowed2 INTEGER CHECK (oemUnlockAllowed2 == -2 OR oemUnlockAllowed2 in (0, 1)),
                verifiedTimeFirst INTEGER NOT NULL,
                verifiedTimeLast INTEGER NOT NULL,
                expiredTimeLast INTEGER,
                failureTimeLast INTEGER,
                failureAlertTime INTEGER,
                userId INTEGER NOT NULL REFERENCES Accounts (userId) ON DELETE CASCADE,
                deletionTime INTEGER
            ) STRICT;

            CREATE TABLE IF NOT EXISTS Attestations (
                id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
                fingerprint BLOB NOT NULL REFERENCES Devices (fingerprint) ON DELETE CASCADE,
                time INTEGER NOT NULL,
                strong INTEGER NOT NULL CHECK (strong in (0, 1)),
                osVersion INTEGER NOT NULL,
                osPatchLevel INTEGER NOT NULL,
                vendorPatchLevel INTEGER,
                bootPatchLevel INTEGER,
                verifiedBootHash BLOB,
                appVersion INTEGER NOT NULL,
                userProfileSecure INTEGER NOT NULL CHECK (userProfileSecure in (0, 1)),
                enrolledBiometrics INTEGER NOT NULL CHECK (enrolledBiometrics in (0, 1)),
                accessibility INTEGER NOT NULL CHECK (accessibility in (0, 1)),
                deviceAdmin INTEGER NOT NULL CHECK (deviceAdmin in (0, 1, 2)),
                adbEnabled INTEGER NOT NULL CHECK (adbEnabled in (0, 1)),
                addUsersWhenLocked INTEGER NOT NULL CHECK (addUsersWhenLocked in (0, 1)),
                oemUnlockAllowed INTEGER NOT NULL CHECK (oemUnlockAllowed in (0, 1)),
                systemUser INTEGER NOT NULL CHECK (systemUser in (0, 1)),
                autoRebootSeconds INTEGER CHECK (autoRebootSeconds == -2 OR autoRebootSeconds == 0 OR autoRebootSeconds >= 20),
                portSecurityMode INTEGER CHECK (portSecurityMode == -2 OR portSecurityMode >= 0),
                userCount INTEGER CHECK (userCount == -2 OR userCount >= 1),
                oemUnlockAllowed2 INTEGER CHECK (oemUnlockAllowed2 == -2 OR oemUnlockAllowed2 in (0, 1))
            ) STRICT""";

    private static final String CREATE_ATTESTATION_INDICES = """
            CREATE INDEX IF NOT EXISTS Accounts_loginTime
            ON Accounts (loginTime);

            CREATE INDEX IF NOT EXISTS Sessions_expiryTime
            ON Sessions (expiryTime);
            CREATE INDEX IF NOT EXISTS Sessions_userId
            ON Sessions (userId);

            CREATE INDEX IF NOT EXISTS Devices_userId_verifiedTimeFirst
            ON Devices (userId, verifiedTimeFirst);
            CREATE INDEX IF NOT EXISTS Devices_userId_verifiedTimeLast_deletionTimeNull
            ON Devices (userId, verifiedTimeLast) WHERE deletionTime IS NULL;
            CREATE INDEX IF NOT EXISTS Devices_deletionTime
            ON Devices (deletionTime) WHERE deletionTime IS NOT NULL;
            CREATE INDEX IF NOT EXISTS Devices_verifiedTimeLast_deletionTimeNull
            ON Devices (verifiedTimeLast) WHERE deletionTime IS NULL;

            CREATE INDEX IF NOT EXISTS Attestations_fingerprint_id
            ON Attestations (fingerprint, id)""";

    private static void setupAttestationDatabase() throws DataFormatException, GeneralSecurityException, IOException, SQLiteException {
        final SQLiteConnection conn = open(ATTESTATION_DATABASE);
        try {
            final SQLiteStatement selectCreated = conn.prepare(
                    "SELECT 1 FROM sqlite_master WHERE type='table' AND name='Configuration'");
            if (!selectCreated.step()) {
                conn.exec("PRAGMA user_version = 15");
            }
            selectCreated.dispose();

            int userVersion = getUserVersion(conn);

            conn.exec(CREATE_ATTESTATION_TABLES);
            conn.exec(CREATE_ATTESTATION_INDICES);

            if (userVersion < 11) {
                logger.log(ALERT, ATTESTATION_DATABASE + " database schemas older than version 11 are no longer " +
                        "supported. Use an older AttestationServer revision to upgrade.");
                System.exit(1);
            }

            int targetUserVersion;

            // remove denyNewUsb column from Devices
            targetUserVersion = 12;
            if (userVersion < targetUserVersion) {
                conn.exec("PRAGMA foreign_keys = OFF");
                conn.exec("BEGIN IMMEDIATE TRANSACTION");

                conn.exec("ALTER TABLE Devices RENAME TO OldDevices");
                conn.exec("ALTER TABLE Attestations RENAME TO OldAttestations");

                conn.exec(CREATE_ATTESTATION_TABLES);

                conn.exec("""
                        INSERT INTO Devices (
                            fingerprint,
                            pinnedCertificates,
                            attestKey,
                            pinnedVerifiedBootKey,
                            verifiedBootHash,
                            pinnedOsVersion,
                            pinnedOsPatchLevel,
                            pinnedVendorPatchLevel,
                            pinnedBootPatchLevel,
                            pinnedAppVersion,
                            pinnedAppVariant,
                            pinnedSecurityLevel,
                            userProfileSecure,
                            enrolledBiometrics,
                            accessibility,
                            deviceAdmin,
                            adbEnabled,
                            addUsersWhenLocked,
                            oemUnlockAllowed,
                            systemUser,
                            verifiedTimeFirst,
                            verifiedTimeLast,
                            expiredTimeLast,
                            failureTimeLast,
                            userId,
                            deletionTime)
                        SELECT
                            fingerprint,
                            pinnedCertificates,
                            attestKey,
                            pinnedVerifiedBootKey,
                            verifiedBootHash,
                            pinnedOsVersion,
                            pinnedOsPatchLevel,
                            pinnedVendorPatchLevel,
                            pinnedBootPatchLevel,
                            pinnedAppVersion,
                            pinnedAppVariant,
                            pinnedSecurityLevel,
                            userProfileSecure,
                            enrolledBiometrics,
                            accessibility,
                            deviceAdmin,
                            adbEnabled,
                            addUsersWhenLocked,
                            oemUnlockAllowed,
                            systemUser,
                            verifiedTimeFirst,
                            verifiedTimeLast,
                            expiredTimeLast,
                            failureTimeLast,
                            userId,
                            deletionTime
                        FROM OldDevices""");

                conn.exec("""
                        INSERT INTO Attestations (
                            id,
                            fingerprint,
                            time,
                            strong,
                            osVersion,
                            osPatchLevel,
                            vendorPatchLevel,
                            bootPatchLevel,
                            verifiedBootHash,
                            appVersion,
                            userProfileSecure,
                            enrolledBiometrics,
                            accessibility,
                            deviceAdmin,
                            adbEnabled,
                            addUsersWhenLocked,
                            oemUnlockAllowed,
                            systemUser
                        ) SELECT
                            id,
                            fingerprint,
                            time,
                            strong,
                            osVersion,
                            osPatchLevel,
                            vendorPatchLevel,
                            bootPatchLevel,
                            verifiedBootHash,
                            appVersion,
                            userProfileSecure,
                            enrolledBiometrics,
                            accessibility,
                            deviceAdmin,
                            adbEnabled,
                            addUsersWhenLocked,
                            oemUnlockAllowed,
                            systemUser
                        FROM OldAttestations""");

                conn.exec("DROP TABLE OldDevices");
                conn.exec("DROP TABLE OldAttestations");

                conn.exec(CREATE_ATTESTATION_INDICES);
                conn.exec("PRAGMA user_version = " + targetUserVersion);
                conn.exec("COMMIT TRANSACTION");
                userVersion = targetUserVersion;
                conn.exec("PRAGMA foreign_keys = ON");
                logger.info("Migrated to schema version: " + userVersion);
            }

            // update DEFLATE dictionary from 2 to 4
            targetUserVersion = 13;
            if (userVersion < targetUserVersion) {
                conn.exec("BEGIN IMMEDIATE TRANSACTION");

                final SQLiteStatement select = conn.prepare(
                        "SELECT pinnedCertificates, fingerprint FROM Devices");
                final SQLiteStatement update = conn.prepare(
                        "UPDATE Devices SET pinnedCertificates = ? where fingerprint = ?");
                while (select.step()) {
                    final Certificate[] chain = AttestationProtocol.decodeChain(AttestationProtocol.DEFLATE_DICTIONARY_2, select.columnBlob(0));
                    update.bind(1, AttestationProtocol.encodeChain(AttestationProtocol.DEFLATE_DICTIONARY_4, chain));
                    update.bind(2, select.columnBlob(1));
                    update.step();
                    update.reset();
                }
                select.dispose();
                update.dispose();

                conn.exec("PRAGMA user_version = " + targetUserVersion);
                conn.exec("COMMIT TRANSACTION");
                userVersion = targetUserVersion;
                logger.info("Migrated to schema version: " + userVersion);
            }

            // add failureAlertTime column to Devices
            targetUserVersion = 14;
            if (userVersion < targetUserVersion) {
                conn.exec("PRAGMA foreign_keys = OFF");
                conn.exec("BEGIN IMMEDIATE TRANSACTION");

                conn.exec("ALTER TABLE Devices RENAME TO OldDevices");
                conn.exec("ALTER TABLE Attestations RENAME TO OldAttestations");

                conn.exec(CREATE_ATTESTATION_TABLES);

                conn.exec("""
                        INSERT INTO Devices (
                            fingerprint,
                            pinnedCertificates,
                            attestKey,
                            pinnedVerifiedBootKey,
                            verifiedBootHash,
                            pinnedOsVersion,
                            pinnedOsPatchLevel,
                            pinnedVendorPatchLevel,
                            pinnedBootPatchLevel,
                            pinnedAppVersion,
                            pinnedAppVariant,
                            pinnedSecurityLevel,
                            userProfileSecure,
                            enrolledBiometrics,
                            accessibility,
                            deviceAdmin,
                            adbEnabled,
                            addUsersWhenLocked,
                            oemUnlockAllowed,
                            systemUser,
                            verifiedTimeFirst,
                            verifiedTimeLast,
                            expiredTimeLast,
                            failureTimeLast,
                            failureAlertTime,
                            userId,
                            deletionTime)
                        SELECT
                            fingerprint,
                            pinnedCertificates,
                            attestKey,
                            pinnedVerifiedBootKey,
                            verifiedBootHash,
                            pinnedOsVersion,
                            pinnedOsPatchLevel,
                            pinnedVendorPatchLevel,
                            pinnedBootPatchLevel,
                            pinnedAppVersion,
                            pinnedAppVariant,
                            pinnedSecurityLevel,
                            userProfileSecure,
                            enrolledBiometrics,
                            accessibility,
                            deviceAdmin,
                            adbEnabled,
                            addUsersWhenLocked,
                            oemUnlockAllowed,
                            systemUser,
                            verifiedTimeFirst,
                            verifiedTimeLast,
                            expiredTimeLast,
                            failureTimeLast,
                            NULL,
                            userId,
                            deletionTime
                        FROM OldDevices""");

                conn.exec("""
                        INSERT INTO Attestations (
                            id,
                            fingerprint,
                            time,
                            strong,
                            osVersion,
                            osPatchLevel,
                            vendorPatchLevel,
                            bootPatchLevel,
                            verifiedBootHash,
                            appVersion,
                            userProfileSecure,
                            enrolledBiometrics,
                            accessibility,
                            deviceAdmin,
                            adbEnabled,
                            addUsersWhenLocked,
                            oemUnlockAllowed,
                            systemUser
                        ) SELECT
                            id,
                            fingerprint,
                            time,
                            strong,
                            osVersion,
                            osPatchLevel,
                            vendorPatchLevel,
                            bootPatchLevel,
                            verifiedBootHash,
                            appVersion,
                            userProfileSecure,
                            enrolledBiometrics,
                            accessibility,
                            deviceAdmin,
                            adbEnabled,
                            addUsersWhenLocked,
                            oemUnlockAllowed,
                            systemUser
                        FROM OldAttestations""");

                conn.exec("DROP TABLE OldDevices");
                conn.exec("DROP TABLE OldAttestations");

                conn.exec(CREATE_ATTESTATION_INDICES);
                conn.exec("PRAGMA user_version = " + targetUserVersion);
                conn.exec("COMMIT TRANSACTION");
                userVersion = targetUserVersion;
                conn.exec("PRAGMA foreign_keys = ON");
                logger.info("Migrated to schema version: " + userVersion);
            }

            // add autoRebootSeconds, portSecurityMode, userCount, oemUnlockAllowed2 columns
            // to Devices, acquired from extended SecurityStateManager#getGlobalSecurityState API
            targetUserVersion = 15;
            if (userVersion < targetUserVersion) {
                conn.exec("PRAGMA foreign_keys = OFF");
                conn.exec("BEGIN IMMEDIATE TRANSACTION");

                conn.exec("ALTER TABLE Devices RENAME TO OldDevices");
                conn.exec("ALTER TABLE Attestations RENAME TO OldAttestations");

                conn.exec(CREATE_ATTESTATION_TABLES);

                conn.exec("""
                        INSERT INTO Devices (
                            fingerprint,
                            pinnedCertificates,
                            attestKey,
                            pinnedVerifiedBootKey,
                            verifiedBootHash,
                            pinnedOsVersion,
                            pinnedOsPatchLevel,
                            pinnedVendorPatchLevel,
                            pinnedBootPatchLevel,
                            pinnedAppVersion,
                            pinnedAppVariant,
                            pinnedSecurityLevel,
                            userProfileSecure,
                            enrolledBiometrics,
                            accessibility,
                            deviceAdmin,
                            adbEnabled,
                            addUsersWhenLocked,
                            oemUnlockAllowed,
                            systemUser,
                            autoRebootSeconds,
                            portSecurityMode,
                            userCount,
                            oemUnlockAllowed2,
                            verifiedTimeFirst,
                            verifiedTimeLast,
                            expiredTimeLast,
                            failureTimeLast,
                            failureAlertTime,
                            userId,
                            deletionTime)
                        SELECT
                            fingerprint,
                            pinnedCertificates,
                            attestKey,
                            pinnedVerifiedBootKey,
                            verifiedBootHash,
                            pinnedOsVersion,
                            pinnedOsPatchLevel,
                            pinnedVendorPatchLevel,
                            pinnedBootPatchLevel,
                            pinnedAppVersion,
                            pinnedAppVariant,
                            pinnedSecurityLevel,
                            userProfileSecure,
                            enrolledBiometrics,
                            accessibility,
                            deviceAdmin,
                            adbEnabled,
                            addUsersWhenLocked,
                            oemUnlockAllowed,
                            systemUser,
                            -1,
                            -1,
                            -1,
                            -1,
                            verifiedTimeFirst,
                            verifiedTimeLast,
                            expiredTimeLast,
                            failureTimeLast,
                            failureAlertTime,
                            userId,
                            deletionTime
                        FROM OldDevices""");

                conn.exec("""
                        INSERT INTO Attestations (
                            id,
                            fingerprint,
                            time,
                            strong,
                            osVersion,
                            osPatchLevel,
                            vendorPatchLevel,
                            bootPatchLevel,
                            verifiedBootHash,
                            appVersion,
                            userProfileSecure,
                            enrolledBiometrics,
                            accessibility,
                            deviceAdmin,
                            adbEnabled,
                            addUsersWhenLocked,
                            oemUnlockAllowed,
                            systemUser,
                            autoRebootSeconds,
                            portSecurityMode,
                            userCount,
                            oemUnlockAllowed2
                        ) SELECT
                            id,
                            fingerprint,
                            time,
                            strong,
                            osVersion,
                            osPatchLevel,
                            vendorPatchLevel,
                            bootPatchLevel,
                            verifiedBootHash,
                            appVersion,
                            userProfileSecure,
                            enrolledBiometrics,
                            accessibility,
                            deviceAdmin,
                            adbEnabled,
                            addUsersWhenLocked,
                            oemUnlockAllowed,
                            systemUser,
                            -1,
                            -1,
                            -1,
                            -1
                        FROM OldAttestations""");

                conn.exec("DROP TABLE OldDevices");
                conn.exec("DROP TABLE OldAttestations");

                conn.exec(CREATE_ATTESTATION_INDICES);
                conn.exec("PRAGMA user_version = " + targetUserVersion);
                conn.exec("COMMIT TRANSACTION");
                userVersion = targetUserVersion;
                conn.exec("PRAGMA foreign_keys = ON");
                logger.info("Migrated to schema version: " + userVersion);
            }

            // modify autoRebootSeconds, portSecurityMode, userCount, oemUnlockAllowed2
            // columns persistable values
            targetUserVersion = 16;
            if (userVersion < targetUserVersion) {
                conn.exec("PRAGMA foreign_keys = OFF");
                conn.exec("BEGIN IMMEDIATE TRANSACTION");

                conn.exec("ALTER TABLE Devices RENAME TO OldDevices");
                conn.exec("ALTER TABLE Attestations RENAME TO OldAttestations");

                conn.exec(CREATE_ATTESTATION_TABLES);

                conn.exec("""
                        INSERT INTO Devices (
                            fingerprint,
                            pinnedCertificates,
