module profile.data_impl_sqlite;

import slf4d;
import d2sqlite3;
import handy_http_primitives;
import streams.interfaces : InputStream, inputStreamObjectFor;
import streams.types : FileInputStream;

import profile.data;
import profile.model;
import util.sqlite;

const DEFAULT_USERS_DIR = "users";

/// Profile repository that uses an SQLite3 database file for each profile.
class FileSystemProfileRepository : ProfileRepository {
    import std.path;
    import std.file;

    private const string usersDir;
    private const string username;

    this(string usersDir, string username) {
        this.usersDir = usersDir;
        this.username = username;
    }

    this(string username) {
        this(DEFAULT_USERS_DIR, username);
    }

    Optional!Profile findByName(string name) {
        string path = getProfilePath(name);
        if (!exists(path)) return Optional!Profile.empty;
        return Optional!Profile.of(new Profile(name, username));
    }

    Profile createProfile(string name) {
        string path = getProfilePath(name);
        if (exists(path)) throw new HttpStatusException(HttpStatus.BAD_REQUEST, "Profile already exists.");
        if (!exists(getProfilesDir())) mkdir(getProfilesDir());
        ProfileDataSource ds = new SqliteProfileDataSource(path);
        import std.datetime;
        auto propsRepo = ds.getPropertiesRepository();
        propsRepo.setProperty("name", name);
        propsRepo.setProperty("createdAt", Clock.currTime(UTC()).toISOExtString());
        propsRepo.setProperty("user", username);
        return new Profile(name, username);
    }

    Profile[] findAll() {
        string profilesDir = getProfilesDir();
        if (!exists(profilesDir)) return [];
        Profile[] profiles;
        foreach (DirEntry entry; dirEntries(profilesDir, SpanMode.shallow, false)) {
            import std.string : endsWith;
            const suffix = ".sqlite";
            if (endsWith(entry.name, suffix)) {
                string profileName = baseName(entry.name, suffix);
                profiles ~= new Profile(profileName, username);
            }
        }
        import std.algorithm.sorting : sort;
        sort(profiles);
        return profiles;
    }

    void deleteByName(string name) {
        string path = getProfilePath(name);
        if (exists(path)) {
            std.file.remove(path);
        }
    }

    ProfileDataSource getDataSource(in Profile profile) {
        return new SqliteProfileDataSource(getProfilePath(profile.name));
    }

    string getFilesPath(in Profile profile) {
        return buildPath(getProfilesDir(), profile.name ~ "_files");
    }

    Optional!ProfileDownloadData getProfileData(string name) {
        import std.string : toStringz, format;
        import std.datetime;

        string path = getProfilePath(name);
        if (!exists(path)) return Optional!ProfileDownloadData.empty;
        ProfileDownloadData data;
        const now = Clock.currTime(UTC());
        data.filename = format!"%s_%02d-%02d-%02d_%02d-%02d-%02dz.sqlite"(
            name,
            now.year, now.month, now.day,
            now.hour, now.minute, now.second
        );
        data.contentType = "application/vnd.sqlite3";
        data.size = std.file.getSize(path);
        data.inputStream = inputStreamObjectFor(FileInputStream(toStringz(path)));
        return Optional!ProfileDownloadData.of(data);
    }

    private string getProfilesDir() {
        return buildPath(this.usersDir, username, "profiles");
    }

    private string getProfilePath(string name) {
        return buildPath(this.usersDir, username, "profiles", name ~ ".sqlite");
    }

    /**
     * Helper function that applies a given function to ALL profiles of ALL
     * users.
     * Params:
     *   fn = The function to execute against all profiles of all users.
     */
    static void doForAllUserProfiles(void function(Profile, ProfileRepository) fn) {
        import auth.data;
        import auth.data_impl_fs;
        UserRepository userRepo = new FileSystemUserRepository();
        foreach (user; userRepo.findAll()) {
            ProfileRepository profileRepo = new FileSystemProfileRepository(user.username);
            foreach (profile; profileRepo.findAll()) {
                fn(profile, profileRepo);
            }
        }
    }
}

class SqlitePropertiesRepository : PropertiesRepository {
    private Database db;
    this(Database db) {
        this.db = db;
    }

    Optional!string findProperty(string propertyName) {
        return findOne(
            db,
            "SELECT value FROM profile_property WHERE property = ?",
            r => r.peek!string(0),
            propertyName
        );
    }

    void setProperty(string name, string value) {
        if (findProperty(name).isNull) {
            Statement stmt = this.db.prepare("INSERT INTO profile_property (property, value) VALUES (?, ?)");
            stmt.bind(1, name);
            stmt.bind(2, value);
            stmt.execute();
        } else {
            Statement stmt = this.db.prepare("UPDATE profile_property SET value = ? WHERE property = ?");
            stmt.bind(1, value);
            stmt.bind(2, name);
            stmt.execute();
        }
    }

    void deleteProperty(string name) {
        Statement stmt = this.db.prepare("DELETE FROM profile_property WHERE property = ?");
        stmt.bind(1, name);
        stmt.execute();
    }

    ProfileProperty[] findAll() {
        Statement stmt = this.db.prepare("SELECT * FROM profile_property ORDER BY property ASC");
        ResultRange result = stmt.execute();
        ProfileProperty[] props;
        foreach (Row row; result) {
            ProfileProperty prop = ProfileProperty(
                row.peek!string("property"),
                row.peek!string("value")
            );
            props ~= prop;
        }
        return props;
    }

    void deleteAllByPrefix(string prefix) {
        util.sqlite.update(
            db,
            "DELETE FROM profile_property WHERE property LIKE ?",
            prefix ~ "%"
        );
    }
}

private const SCHEMA = import("sql/schema.sql");
private const uint SCHEMA_VERSION = 2;
private const SCHEMA_VERSION_PROPERTY = "database-schema-version";

/**
 * An SQLite implementation of the ProfileDataSource that uses a single
 * database connection to initialize various entity data access objects lazily.
 */
class SqliteProfileDataSource : ProfileDataSource {
    import account.data;
    import account.data_impl_sqlite;
    import transaction.data;
    import transaction.data_impl_sqlite;
    import attachment.data;
    import attachment.data_impl_sqlite;
    import analytics.data;
    import analytics.data_impl_sqlite;

    private const string dbPath;
    Database db;

    // Cached versions of all the repositories:
    PropertiesRepository propertiesRepo;
    AttachmentRepository attachmentRepo;
    AccountRepository accountRepo;
    AccountJournalEntryRepository accountJournalEntryRepo;
    AccountValueRecordRepository accountValueRecordRepo;
    TransactionVendorRepository transactionVendorRepo;
    TransactionCategoryRepository transactionCategoryRepo;
    TransactionTagRepository transactionTagRepo;
    TransactionRepository transactionRepo;
    TransactionDraftRepository transactionDraftRepo;
    RecurringTransactionRepository recurringTransactionRepo;
    AnalyticsRepository analyticsRepo;

    this(string path) {
        this.dbPath = path;
        import std.file : exists;
        bool needsInit = !exists(path);
        this.db = Database(path);
        db.run("PRAGMA foreign_keys = ON");
        if (needsInit) {
            infoF!"Initializing database: %s with schema version %d."(dbPath, SCHEMA_VERSION);
            db.run(SCHEMA);
            // Set the schema version property right away:
            import std.conv;
            auto propRepo = new SqlitePropertiesRepository(db);
            propRepo.setProperty(SCHEMA_VERSION_PROPERTY, SCHEMA_VERSION.to!string);
        }
        migrateSchema();
    }

    PropertiesRepository getPropertiesRepository() {
        if (propertiesRepo is null) {
            propertiesRepo = new SqlitePropertiesRepository(db);
        }
        return propertiesRepo;
    }

    AttachmentRepository getAttachmentRepository() {
        if (attachmentRepo is null) {
            attachmentRepo = new SqliteAttachmentRepository(db);
        }
        return attachmentRepo;
    }

    AccountRepository getAccountRepository() {
        if (accountRepo is null) {
            accountRepo = new SqliteAccountRepository(db);
        }
        return accountRepo;
    }

    AccountJournalEntryRepository getAccountJournalEntryRepository() {
        if (accountJournalEntryRepo is null) {
            accountJournalEntryRepo = new SqliteAccountJournalEntryRepository(db);
        }
        return accountJournalEntryRepo;
    }

    AccountValueRecordRepository getAccountValueRecordRepository() {
        if (accountValueRecordRepo is null) {
            accountValueRecordRepo = new SqliteAccountValueRecordRepository(db);
        }
        return accountValueRecordRepo;
    }

    TransactionVendorRepository getTransactionVendorRepository() {
        if (transactionVendorRepo is null) {
            transactionVendorRepo = new SqliteTransactionVendorRepository(db);
        }
        return transactionVendorRepo;
    }

    TransactionCategoryRepository getTransactionCategoryRepository() {
        if (transactionCategoryRepo is null) {
            transactionCategoryRepo = new SqliteTransactionCategoryRepository(db);
        }
        return transactionCategoryRepo;
    }

    TransactionTagRepository getTransactionTagRepository() {
        if (transactionTagRepo is null) {
            transactionTagRepo = new SqliteTransactionTagRepository(db);
        }
        return transactionTagRepo;
    }

    TransactionRepository getTransactionRepository() {
        if (transactionRepo is null) {
            transactionRepo = new SqliteTransactionRepository(db);
        }
        return transactionRepo;
    }

    TransactionDraftRepository getTransactionDraftRepository() {
        if (transactionDraftRepo is null) {
            transactionDraftRepo = new SqliteTransactionDraftRepository(db);
        }
        return transactionDraftRepo;
    }

    RecurringTransactionRepository getRecurringTransactionRepository() {
        if (recurringTransactionRepo is null) {
            recurringTransactionRepo = new SqliteRecurringTransactionRepository(db);
        }
        return recurringTransactionRepo;
    }

    AnalyticsRepository getAnalyticsRepository() {
        if (analyticsRepo is null) {
            analyticsRepo = new SqliteAnalyticsRepository(db);
        }
        return analyticsRepo;
    }

    void doTransaction(void delegate () dg) {
        util.sqlite.doTransaction(db, dg);
    }

    private void migrateSchema() {
        import std.conv;
        PropertiesRepository propsRepo = getPropertiesRepository();
        uint currentVersion;
        try {
            currentVersion = propsRepo.findProperty("database-schema-version")
                .mapIfPresent!(s => s.to!uint).orElse(0);
        } catch (ConvException e) {
            warn("Failed to parse database-schema-version property.", e);
            currentVersion = 0;
        }
        if (currentVersion == SCHEMA_VERSION) return;

        static const migrations = [
            import("sql/migrations/1.sql"),
            import("sql/migrations/2.sql")
        ];
        static if (migrations.length != SCHEMA_VERSION) {
            static assert(false, "Schema version doesn't match the list of defined migrations.");
        }

        while (currentVersion < SCHEMA_VERSION) {
            infoF!"Migrating schema from version %d to %d."(currentVersion, currentVersion + 1);
            db.run(migrations[currentVersion]);
            currentVersion++;
            propsRepo.setProperty("database-schema-version", currentVersion.to!string);
        }
    }
}
