/*
 Navicat Premium Dump SQL

 Source Server         : biji数据库
 Source Server Type    : SQLite
 Source Server Version : 3045000 (3.45.0)
 Source Schema         : main

 Target Server Type    : SQLite
 Target Server Version : 3045000 (3.45.0)
 File Encoding         : 65001

 Date: 06/01/2026 20:48:23
*/

PRAGMA foreign_keys = false;

-- ----------------------------
-- Table structure for grouping
-- ----------------------------
DROP TABLE IF EXISTS "grouping";
CREATE TABLE "grouping" (
                            "id" INTEGER NOT NULL DEFAULT 0,
                            "grouping" TEXT NOT NULL,
                            "parentId" INTEGER,
                            "is_deleted" INTEGER DEFAULT 0,
                            "deleted_at" DATETIME,
                            "deleted_by" INTEGER,
                            PRIMARY KEY ("id")
);

PRAGMA foreign_keys = true;



PRAGMA foreign_keys = false;

-- ----------------------------
-- Table structure for image
-- ----------------------------
DROP TABLE IF EXISTS "image";
CREATE TABLE "image" (
                         "id" INTEGER PRIMARY KEY AUTOINCREMENT,
                         "markdown_id" INTEGER,
                         "original_name" TEXT NOT NULL,
                         "stored_name" TEXT NOT NULL,
                         "url" TEXT NOT NULL,
                         "size" INTEGER NOT NULL,
                         "content_type" TEXT NOT NULL,
                         "created_at" DATETIME DEFAULT CURRENT_TIMESTAMP,
                         UNIQUE ("stored_name" ASC)
);

-- ----------------------------
-- Auto increment value for image
-- ----------------------------
UPDATE "sqlite_sequence" SET seq = 25 WHERE name = 'image';

PRAGMA foreign_keys = true;




PRAGMA foreign_keys = false;

-- ----------------------------
-- Table structure for image_name
-- ----------------------------
DROP TABLE IF EXISTS "image_name";
CREATE TABLE "image_name" (
                              "id" INTEGER NOT NULL,
                              "markdown_id" INTEGER NOT NULL,
                              "file_name" text NOT NULL,
                              PRIMARY KEY ("id")
);

PRAGMA foreign_keys = true;





PRAGMA foreign_keys = false;

-- ----------------------------
-- Table structure for markdown_file
-- ----------------------------
DROP TABLE IF EXISTS "markdown_file";
CREATE TABLE "markdown_file" (
                                 "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
                                 "grouping_id" integer NOT NULL DEFAULT 1,
                                 "title" TEXT NOT NULL,
                                 "file_name" TEXT NOT NULL,
                                 "content" TEXT NOT NULL,
                                 "created_at" datetime DEFAULT CURRENT_TIMESTAMP,
                                 "updated_at" datetime DEFAULT CURRENT_TIMESTAMP,
                                 "is_deleted" INTEGER DEFAULT 0,
                                 "deleted_at" DATETIME,
                                 "deleted_by" INTEGER,
                                 "is_private" integer DEFAULT 0
);

-- ----------------------------
-- Auto increment value for markdown_file
-- ----------------------------

PRAGMA foreign_keys = true;



PRAGMA foreign_keys = false;

-- ----------------------------
-- Table structure for registration_codes
-- ----------------------------
DROP TABLE IF EXISTS "registration_codes";
CREATE TABLE "registration_codes" (
                                      "id" integer NOT NULL PRIMARY KEY AUTOINCREMENT,
                                      "code" TEXT NOT NULL,
                                      "expiry_time" TEXT NOT NULL,
                                      "created_by" TEXT,
                                      "created_at" TEXT DEFAULT (datetime('now','localtime'))
);

-- ----------------------------
-- Auto increment value for registration_codes
-- ----------------------------
UPDATE "sqlite_sequence" SET seq = 2 WHERE name = 'registration_codes';

-- ----------------------------
-- Indexes structure for table registration_codes
-- ----------------------------
CREATE UNIQUE INDEX "uk_code"
    ON "registration_codes" (
                             "code" ASC
        );

PRAGMA foreign_keys = true;




PRAGMA foreign_keys = false;

-- ----------------------------
-- Table structure for system_settings
-- ----------------------------
DROP TABLE IF EXISTS "system_settings";
CREATE TABLE "system_settings" (
                                   "setting_key" TEXT NOT NULL,
                                   "setting_value" TEXT,
                                   "description" TEXT,
                                   PRIMARY KEY ("setting_key")
);

PRAGMA foreign_keys = true;



PRAGMA foreign_keys = false;

-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS "user";
CREATE TABLE "user" (
                        "id" INTEGER PRIMARY KEY AUTOINCREMENT,
                        "username" TEXT NOT NULL,
                        "password" TEXT NOT NULL,
                        "email" TEXT,
                        "role" TEXT DEFAULT 'USER',
                        "created_at" DATETIME DEFAULT CURRENT_TIMESTAMP,
                        "updated_at" DATETIME DEFAULT CURRENT_TIMESTAMP,
                        "token" TEXT,
                        "token_enddata" DATETIME,
                        UNIQUE ("username" ASC)
);

-- ----------------------------
-- Auto increment value for user
-- ----------------------------
UPDATE "sqlite_sequence" SET seq = 5 WHERE name = 'user';

PRAGMA foreign_keys = true;