from __future__ import annotations

import sqlite3
from pathlib import Path


def build_fixture_zotero_dir(zotero_dir: Path) -> None:
    storage_dir = zotero_dir / "storage" / "ATTACH01"
    storage_dir.mkdir(parents=True, exist_ok=True)
    (storage_dir / "paper.txt").write_text(
        "This paper studies card pipelines for research writing.",
        encoding="utf-8",
    )

    conn = sqlite3.connect(zotero_dir / "zotero.sqlite")
    cur = conn.cursor()

    cur.executescript(
        """
        create table itemTypes (itemTypeID integer primary key, typeName text, templateItemTypeID int, display int);
        create table items (itemID integer primary key, itemTypeID int, dateAdded text, dateModified text, clientDateModified text, libraryID int, key text, version int, synced int);
        create table fields (fieldID integer primary key, fieldName text, fieldFormatID int);
        create table itemDataValues (valueID integer primary key, value text);
        create table itemData (itemID int, fieldID int, valueID int);
        create table creators (creatorID integer primary key, firstName text, lastName text, fieldMode int);
        create table creatorTypes (creatorTypeID integer primary key, creatorType text);
        create table itemCreators (itemID int, creatorID int, creatorTypeID int, orderIndex int);
        create table tags (tagID integer primary key, name text);
        create table itemTags (itemID int, tagID int, type int);
        create table collections (collectionID integer primary key, collectionName text, parentCollectionID int, clientDateModified text, libraryID int, key text, version int, synced int);
        create table collectionItems (collectionID int, itemID int, orderIndex int);
        create table itemNotes (itemID integer primary key, parentItemID int, note text, title text);
        create table itemAttachments (itemID integer primary key, parentItemID int, linkMode int, contentType text, charsetID int, path text, syncState int, storageModTime int, storageHash text, lastProcessedModificationTime int);
        """
    )

    cur.executemany(
        "insert into itemTypes(itemTypeID, typeName, templateItemTypeID, display) values (?, ?, ?, ?)",
        [
            (1, "journalArticle", None, 1),
            (2, "note", None, 1),
            (3, "attachment", None, 1),
        ],
    )
    cur.executemany(
        "insert into fields(fieldID, fieldName, fieldFormatID) values (?, ?, ?)",
        [
            (1, "title", 1),
            (2, "abstractNote", 1),
            (3, "date", 1),
        ],
    )
    cur.executemany(
        "insert into itemDataValues(valueID, value) values (?, ?)",
        [
            (1, "Card Pipelines for Research Writing"),
            (2, "Merged metadata and notes improve drafting support."),
            (3, "2024"),
        ],
    )
    cur.executemany(
        "insert into items(itemID, itemTypeID, dateAdded, dateModified, clientDateModified, libraryID, key, version, synced) values (?, ?, '', '', '', 1, ?, 1, 1)",
        [
            (1, 1, "PAPER0001"),
            (3, 3, "ATTACH01"),
        ],
    )
    cur.executemany(
        "insert into itemData(itemID, fieldID, valueID) values (?, ?, ?)",
        [
            (1, 1, 1),
            (1, 2, 2),
            (1, 3, 3),
        ],
    )
    cur.execute(
        "insert into creators(creatorID, firstName, lastName, fieldMode) values (1, 'Alice', 'Smith', 0)"
    )
    cur.execute(
        "insert into creatorTypes(creatorTypeID, creatorType) values (1, 'author')"
    )
    cur.execute(
        "insert into itemCreators(itemID, creatorID, creatorTypeID, orderIndex) values (1, 1, 1, 0)"
    )
    cur.executemany(
        "insert into tags(tagID, name) values (?, ?)",
        [
            (1, "llm"),
            (2, "writing"),
        ],
    )
    cur.executemany(
        "insert into itemTags(itemID, tagID, type) values (?, ?, 0)",
        [
            (1, 1),
            (1, 2),
        ],
    )
    cur.executemany(
        "insert into collections(collectionID, collectionName, parentCollectionID, clientDateModified, libraryID, key, version, synced) values (?, ?, ?, '', 1, ?, 1, 1)",
        [
            (1, "Theory", None, "COLL0001"),
            (2, "Drafting", 1, "COLL0002"),
        ],
    )
    cur.execute("insert into collectionItems(collectionID, itemID, orderIndex) values (2, 1, 0)")
    cur.execute(
        "insert into itemNotes(itemID, parentItemID, note, title) values (2, 1, '<div><p>Merged notes matter.</p></div>', 'note')"
    )
    cur.execute(
        "insert into itemAttachments(itemID, parentItemID, linkMode, contentType, charsetID, path, syncState, storageModTime, storageHash, lastProcessedModificationTime) values (3, 1, 1, 'text/plain', null, 'storage:ATTACH01/paper.txt', 0, 0, '', 0)"
    )

    conn.commit()
    conn.close()