--
-- Baseline migration: the schema as it existed when Prisma was adopted.
--
-- Generated with:
--   prisma migrate diff --from-empty --to-schema prisma/schema.prisma --script
--
-- HAND-CORRECTED, and it matters: Prisma renders `COLLATE utf8mb4_unicode_ci`
-- for MySQL, but every live table is `utf8mb4_general_ci`. Prisma does not
-- model collation and its own `migrate diff` does NOT report the difference, so
-- without this correction a database rebuilt from empty would silently differ
-- from production in string comparison, sorting and FULLTEXT ranking.
-- If you regenerate this file, re-apply the collation fix. There is a test for
-- it in server/tests/schema/migrations.test.js.
--
-- Things Prisma cannot express, which therefore need handling elsewhere:
--   * the DATABASE-level default collation -- set by migrateDatabase.sh when it
--     creates the database, since tables created later without an explicit
--     COLLATE would otherwise inherit the server default
--   * ENGINE=InnoDB (relies on the server default, which is InnoDB)
--   * AUTO_INCREMENT start values (cosmetic; a fresh database starting at 1 is
--     correct)
--   * the COMMENT on note_raw_text.text
--   * row data -- see prisma/seed.sql for the attachment_type lookup rows
--
-- Context carried over from the migration this replaces: scrape_cache.url_hash
-- carries the UNIQUE index rather than `url` because a UNIQUE on
-- varchar(1000) utf8mb4 is 4000 bytes and exceeds InnoDB's 3072-byte key limit;
-- and attachment.scrape_state DEFAULT 2 ("done") is load-bearing -- it keeps
-- pre-existing attachment rows out of the scraper's boot recovery sweep.
--
-- On environments that already had this schema (dev and production), this file
-- is NOT executed -- it is marked applied with `prisma migrate resolve`.
--

-- CreateTable
CREATE TABLE `attachment` (
    `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
    `note_id` INTEGER UNSIGNED NULL,
    `user_id` INTEGER UNSIGNED NOT NULL,
    `attachment_type` INTEGER UNSIGNED NULL,
    `text` MEDIUMTEXT NULL,
    `url` VARCHAR(1000) NULL DEFAULT '',
    `last_indexed` INTEGER NULL,
    `file_location` VARCHAR(500) NULL,
    `visible` TINYINT NULL DEFAULT 1,
    `scrape_state` TINYINT NOT NULL DEFAULT 2,
    `scrape_attempts` TINYINT NOT NULL DEFAULT 0,
    `enqueued_at` INTEGER NULL,

    INDEX `attachment_scrape_state`(`scrape_state`, `enqueued_at`),
    INDEX `attachment_type`(`attachment_type`),
    INDEX `note_id`(`note_id`),
    INDEX `user_id`(`user_id`),
    FULLTEXT INDEX `attachment_text_index`(`text`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

-- CreateTable
CREATE TABLE `attachment_type` (
    `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
    `mime_type` VARCHAR(500) NULL,
    `extension` VARCHAR(10) NULL,
    `name` VARCHAR(50) NULL,

    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

-- CreateTable
CREATE TABLE `note` (
    `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_id` INTEGER UNSIGNED NULL,
    `note_raw_text_id` INTEGER NULL,
    `opened` INTEGER NULL,
    `created` INTEGER NULL,
    `pinned` INTEGER NULL DEFAULT 0,
    `archived` INTEGER NULL DEFAULT 0,
    `trashed` TINYINT NULL DEFAULT 0,
    `color` VARCHAR(500) NULL,
    `shared` INTEGER NULL DEFAULT 0,
    `share_user_id` INTEGER NULL,
    `quick_note` INTEGER NULL DEFAULT 0,
    `indexed` TINYINT NULL DEFAULT 0,
    `snippet` VARCHAR(1300) NULL,
    `snippet_salt` VARCHAR(1300) NULL,
    `encrypted_share_password_key` VARCHAR(1300) NULL,

    INDEX `user_note`(`user_id`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

-- CreateTable
CREATE TABLE `note_raw_text` (
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `text` LONGTEXT NULL,
    `updated` BIGINT NULL,
    `salt` VARCHAR(1300) NULL,
    `last_decrypted_date` INTEGER NULL,
    `decrypt_attempts_count` INTEGER NULL DEFAULT 0,

    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

-- CreateTable
CREATE TABLE `note_tag` (
    `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
    `note_id` INTEGER UNSIGNED NULL,
    `tag_id` INTEGER UNSIGNED NULL,
    `user_id` INTEGER UNSIGNED NULL,

    INDEX `note_id`(`note_id`),
    INDEX `tag_id`(`tag_id`),
    INDEX `user_id`(`user_id`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

-- CreateTable
CREATE TABLE `scrape_cache` (
    `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
    `url_hash` CHAR(64) NOT NULL,
    `url` VARCHAR(1000) NOT NULL DEFAULT '',
    `canonical_url` VARCHAR(1000) NULL,
    `status` SMALLINT NOT NULL DEFAULT 0,
    `title` VARCHAR(512) NULL,
    `site_name` VARCHAR(255) NULL,
    `description` MEDIUMTEXT NULL,
    `keywords` VARCHAR(512) NULL,
    `search_text` MEDIUMTEXT NULL,
    `image_url` VARCHAR(1000) NULL,
    `file_location` VARCHAR(500) NULL,
    `etag` VARCHAR(255) NULL,
    `last_modified` VARCHAR(128) NULL,
    `content_type` VARCHAR(128) NULL,
    `fetch_state` VARCHAR(20) NOT NULL DEFAULT 'pending',
    `failure_count` INTEGER NOT NULL DEFAULT 0,
    `last_error` VARCHAR(255) NULL,
    `fetched_at` INTEGER NULL,
    `expires_at` INTEGER NULL,

    UNIQUE INDEX `scrape_cache_url_hash`(`url_hash`),
    INDEX `scrape_cache_expires_at`(`expires_at`),
    INDEX `scrape_cache_fetch_state`(`fetch_state`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

-- CreateTable
CREATE TABLE `tag` (
    `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
    `text` VARCHAR(400) NULL,
    `hash` INTEGER NULL,

    INDEX `hash`(`hash`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

-- CreateTable
CREATE TABLE `user` (
    `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
    `username` VARCHAR(150) NOT NULL DEFAULT '',
    `password` TEXT NOT NULL,
    `salt` VARCHAR(500) NOT NULL DEFAULT '',
    `iterations` INTEGER NOT NULL,
    `last_login` DATETIME(0) NULL,
    `created` DATETIME(0) NULL,
    `two_fa_secret` VARCHAR(50) NULL,
    `two_fa_enabled` TINYINT NULL DEFAULT 0,
    `two_fa_every_login` TINYINT NULL DEFAULT 0,
    `pushkey` VARCHAR(100) NULL,

    UNIQUE INDEX `id_UNIQUE`(`id`),
    UNIQUE INDEX `username_UNIQUE`(`username`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

-- CreateTable
CREATE TABLE `user_active_session` (
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `salt` VARCHAR(100) NULL,
    `encrypted_master_password` VARCHAR(500) NULL,
    `created` INTEGER NULL,
    `uses` INTEGER NULL,
    `user_hash` VARCHAR(500) NULL,
    `active` TINYINT NULL DEFAULT 1,
    `session_id` VARCHAR(100) NULL,
    `last_used` INTEGER NULL,

    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

-- CreateTable
CREATE TABLE `user_encrypted_search_index` (
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `user_id` INTEGER NULL,
    `salt` VARCHAR(100) NULL,
    `index` LONGTEXT NULL,
    `last_update` INTEGER NULL,

    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

-- CreateTable
CREATE TABLE `user_key` (
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `user_id` INTEGER NULL,
    `salt` VARCHAR(1300) NULL,
    `key` VARCHAR(5000) NULL,
    `created` INTEGER NULL,
    `public_key` VARCHAR(5000) NULL,
    `private_key_encrypted` VARCHAR(5000) NULL,

    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;