import Database from "better-sqlite3";
import fs from "fs";
import path from "path";
import env from "../../enviroment/env";

const dbPath = env.sqliteDBPath as string;

fs.mkdirSync(path.dirname(dbPath), { recursive: true });

const db = new Database(dbPath);

db.pragma("journal_mode = WAL");
db.pragma("foreign_keys = ON");

db.exec(`
  CREATE TABLE IF NOT EXISTS files (
    id TEXT PRIMARY KEY,
    filename TEXT NOT NULL,
    length INTEGER NOT NULL,
    chunkSize INTEGER,
    uploadDate TEXT NOT NULL,
    owner TEXT NOT NULL,
    parent TEXT NOT NULL,
    parentList TEXT NOT NULL,
    hasThumbnail INTEGER NOT NULL DEFAULT 0,
    isVideo INTEGER NOT NULL DEFAULT 0,
    thumbnailID TEXT,
    size INTEGER NOT NULL,
    iv BLOB NOT NULL,
    linkType TEXT,
    link TEXT,
    filePath TEXT,
    s3ID TEXT,
    personalFile INTEGER,
    trashed INTEGER,
    processingFile INTEGER
  );

  CREATE INDEX IF NOT EXISTS idx_files_owner_parent_filename ON files(owner, parent, filename COLLATE NOCASE);
  CREATE INDEX IF NOT EXISTS idx_files_owner_parent_upload ON files(owner, parent, uploadDate);
  CREATE INDEX IF NOT EXISTS idx_files_trashed ON files(trashed);
  CREATE INDEX IF NOT EXISTS idx_files_hasThumbnail ON files(hasThumbnail);
  CREATE INDEX IF NOT EXISTS idx_files_parentList ON files(parentList);

  CREATE TABLE IF NOT EXISTS folders (
    id TEXT PRIMARY KEY,
    name TEXT NOT NULL,
    parent TEXT NOT NULL,
    owner TEXT NOT NULL,
    parentList TEXT NOT NULL,
    personalFolder INTEGER,
    trashed INTEGER,
    createdAt TEXT NOT NULL,
    updatedAt TEXT NOT NULL
  );

  CREATE INDEX IF NOT EXISTS idx_folders_owner ON folders(owner);
  CREATE INDEX IF NOT EXISTS idx_folders_parent ON folders(parent);
  CREATE INDEX IF NOT EXISTS idx_folders_trashed ON folders(trashed);
  CREATE INDEX IF NOT EXISTS idx_folders_name ON folders(name COLLATE NOCASE);
  CREATE INDEX IF NOT EXISTS idx_folders_createdAt ON folders(createdAt);
  CREATE INDEX IF NOT EXISTS idx_folders_parentList ON folders(parentList);

  CREATE TABLE IF NOT EXISTS users (
    id TEXT PRIMARY KEY,
    email TEXT NOT NULL UNIQUE,
    password TEXT NOT NULL,
    privateKey TEXT,
    publicKey TEXT,
    emailVerified INTEGER,
    emailToken TEXT,
    passwordResetToken TEXT,
    passwordLastModified INTEGER,
    createdAt TEXT NOT NULL,
    updatedAt TEXT NOT NULL
  );

  CREATE TABLE IF NOT EXISTS user_tokens (
    user_id TEXT NOT NULL,
    token TEXT NOT NULL,
    is_temp INTEGER NOT NULL DEFAULT 0,
    uuid TEXT,
    time INTEGER
  );

  CREATE INDEX IF NOT EXISTS idx_user_tokens_user ON user_tokens(user_id, is_temp);

  CREATE TABLE IF NOT EXISTS thumbnails (
    id TEXT PRIMARY KEY,
    name TEXT NOT NULL,
    owner TEXT NOT NULL,
    data BLOB,
    path TEXT,
    iv BLOB,
    s3ID TEXT,
    personalFile INTEGER,
    createdAt TEXT NOT NULL,
    updatedAt TEXT NOT NULL
  );

  CREATE INDEX IF NOT EXISTS idx_thumbnails_owner ON thumbnails(owner);
`);

export default db;