"""
Pure SQL tests that don't depend on nancy's Python code
"""
import pytest

import datetime
import os
import sqlite3
from typing import Iterator


@pytest.fixture
def temp_db() -> Iterator[sqlite3.Cursor]:
    """Create an in-memory database that follow's the nancy schema"""
    with sqlite3.connect(":memory:") as conn:
        cur = conn.cursor()

        from nancy import db

        db.init_schema(cur)

        cur.execute(
            'INSERT INTO local_metadata VALUES ("store_uuid", ?)',
            ("78dc0b93-0e22-45ee-ae90-49d3575dd70f",),
        )

        yield cur


@pytest.fixture
def insert_machine(temp_db: sqlite3.Cursor) -> sqlite3.Cursor:
    cur = temp_db
    cur.executemany(
        "INSERT INTO machine VALUES " "(?, ?, ?, ?, ?, ?)",
        [
            (
                "e3b0c44298fc1c149afbf4c8996fb92427ae41e4649b934ca495991b7852b855",  # sha256
                "a5d97c08a15c4db69f5fded523a1bfe3",  # machine_id TEXT
                "lucky",  # hostname TEXT, -- platform.node(): 'lucky'
                "Linux",  # system TEXT, -- platform.system(): 'Linux'
                "x86_64",  # cpu_type TEXT, -- platform.machine():
                "i386",  # processor TEXT, -- platform.processor():
            ),
            (
                "2c26b46b68ffc68ff99b453c1d30413413422d706483bfa0f98a5e886266e7ae",  # sha256
                "b5d97c08a15c4db69f5fded523a1bfe3",  # machine_id TEXT
                "a100",  # hostname TEXT, -- platform.node(): 'lucky'
                "Linux",  # system TEXT, -- platform.system(): 'Linux'
                "x86_64",  # cpu_type TEXT, -- platform.machine():
                "i386",  # processor TEXT, -- platform.processor():
            ),
        ],
    )
    return cur


def test_insert_machine(insert_machine: sqlite3.Cursor) -> None:
    cur = insert_machine
    cur.execute("SELECT * FROM machine")
    machines = cur.fetchall()
    assert len(machines) == 2


@pytest.fixture
def insert_user(insert_machine: sqlite3.Cursor) -> sqlite3.Cursor:
    cur = insert_machine
    cur.executemany(
        "INSERT INTO user VALUES (?, ?, ?, ?, ?)",
        [
            (  # jacob@lucky
                "f2ee9db3526d36b4346980f12a49cb404b924cf784dbd3d5db8a362e363a8070",  # sha256
                "jacob",  # username TEXT NOT NULL,
                101,  # userid INTEGER,
                "Jacob Hinkle",  # fullname TEXT,
                "e3b0c44298fc1c149afbf4c8996fb92427ae41e4649b934ca495991b7852b855",  # machine
            ),
            (  # jacob@a100
                "3219c598f4e6e84bea3d0c295f0d2c35f3ce01747f4839fb698b32646b037d3c",  # sha256
                "jacob",  # username TEXT NOT NULL,
                10301,  # userid INTEGER,
                "Jacob Hinkle",  # fullname TEXT,
                "2c26b46b68ffc68ff99b453c1d30413413422d706483bfa0f98a5e886266e7ae",  # machine
            ),
            (  # bob@a100
                "99389acaef44ed99c16bcaf1bff0c261b35ad110f0544092f711a1354e616f61",  # sha256
                "bob",  # username TEXT NOT NULL,
                2035,  # userid INTEGER,
                "Just Bob",  # fullname TEXT,
                "2c26b46b68ffc68ff99b453c1d30413413422d706483bfa0f98a5e886266e7ae",  # machine
            ),
        ],
    )
    return cur


def test_insert_user(insert_user: sqlite3.Cursor) -> None:
    cur = insert_user
    cur.execute("SELECT * FROM user")
    users = cur.fetchall()
    assert len(users) == 3


def test_invalid_user_machine(insert_user: sqlite3.Cursor) -> None:
    cur = insert_user
    with pytest.raises(sqlite3.IntegrityError):
        # should fail foreign key constraint
        cur.execute(
            "INSERT INTO user VALUES " "(?, ?, ?, ?, ?)",
            (
                None,  # id INTEGER PRIMARY KEY NOT NULL,
                "bozo",  # username TEXT NOT NULL,
                100,  # userid INTEGER,
                "Bozo the Clown",  # fullname TEXT,
                3,  # machine INTEGER NOT NULL,
            ),
        )
    with pytest.raises(sqlite3.IntegrityError):
        # should fail uniqueness constraint
        cur.execute(
            "INSERT INTO user VALUES " "(?, ?, ?, ?, ?)",
            (
                None,  # id INTEGER PRIMARY KEY NOT NULL,
                "jacob",  # username TEXT NOT NULL,
                101,  # userid INTEGER,
                "Jacob Hinkle",  # fullname TEXT,
                1,  # machine INTEGER NOT NULL,
            ),
        )


@pytest.fixture
def insert_store(insert_machine: sqlite3.Cursor) -> sqlite3.Cursor:
    import uuid

    cur = insert_machine
    cur.executemany(
        "INSERT INTO store VALUES " "(?)",
        [
            ("63c2a84a-524f-46ad-b512-b99e80f7385b",),
            ("3be35ff6-6a7b-49cf-ba3e-c610b781b54e",),
            ("b8f685d8-2322-403d-a59c-fb8301229788",),
        ],
    )
    return cur


@pytest.fixture
def insert_directories(insert_store: sqlite3.Cursor) -> sqlite3.Cursor:
    cur = insert_store
    cur.executemany(
        "INSERT INTO filedir VALUES (?, ?, ?, ?)",
        [
            (
                "71e75275-0847-4e47-9df5-1f2bbe01da91",  # uuid
                "63c2a84a-524f-46ad-b512-b99e80f7385b",  # store
                ".",  # filename
                None,  # parent
            ),
            (
                "6051a251-d38a-4d8c-ba57-ac74023ec2f4",  # uuid
                "63c2a84a-524f-46ad-b512-b99e80f7385b",  # store
                "foo",  # filename
                "71e75275-0847-4e47-9df5-1f2bbe01da91",  # parent
            ),
            (
                "5f74b5f2-eac8-4a86-90f6-4f7c7981564d",  # uuid
                "3be35ff6-6a7b-49cf-ba3e-c610b781b54e",  # store
                ".",  # filename
                None,  # parent
            ),
        ],
    )
    cur.executemany(
        "INSERT INTO filedir_version VALUES " "(?, ?, ?, ?, ?, ?, ?, ?, ?)",
        [
            (
                "baad81ef-7cc6-48df-973c-e9f32ceda19a",  # uuid
                "71e75275-0847-4e47-9df5-1f2bbe01da91",  # filedir
                datetime.datetime.now().timestamp(),
                "DIR",  # filetype TEXT, -- One of 'LNK', 'DIR', 'REG', etc. See store.FSEntry.from_path for details
                False,  # deleted BOOL NOT NULL,  -- set True when recording a deleted file
                "drwxrwxr-x",  # perms TEXT, -- stat.filemode(os.stat(path).st_mode): '-rw-rw-r--'
                None,  # symlink_target TEXT, -- if this is a symlink, this is the (read but not fully resolved) target. I.e. this is the "content" of the symlink.
                "a84ed33864d06615a87bc8da5258d841163f1e7969367ecd07b041ae1a18febd",  # sha256 TEXT,
                None,  # source_task INTEGER,
            ),
            (
                "c4037a74-88cd-4a1d-b158-4ebebb25f10d",  # uuid
                "71e75275-0847-4e47-9df5-1f2bbe01da91",  # filedir
                datetime.datetime.now().timestamp(),
                "DIR",  # filetype TEXT, -- One of 'LNK', 'DIR', 'REG', etc. See store.FSEntry.from_path for details
                False,  # deleted BOOL NOT NULL,  -- set True when recording a deleted file
                "drwxrwxr-x",  # perms TEXT, -- stat.filemode(os.stat(path).st_mode): '-rw-rw-r--'
                None,  # symlink_target TEXT, -- if this is a symlink, this is the (read but not fully resolved) target. I.e. this is the "content" of the symlink.
                "a84ed33864d06615a87bc8da5258d841163f1e7969367ecd07b041ae1a18febd",  # sha256 TEXT,
                None,  # source_task INTEGER,
            ),
            (
                "dcfbe086-3c4b-43b8-a550-974610b86267",  # uuid
                "5f74b5f2-eac8-4a86-90f6-4f7c7981564d",  # filedir
                datetime.datetime.now().timestamp(),
                "DIR",  # filetype TEXT, -- One of 'LNK', 'DIR', 'REG', etc. See store.FSEntry.from_path for details
                False,  # deleted BOOL NOT NULL,  -- set True when recording a deleted file
                "drwxrwxr-x",  # perms TEXT, -- stat.filemode(os.stat(path).st_mode): '-rw-rw-r--'
                None,  # symlink_target TEXT, -- if this is a symlink, this is the (read but not fully resolved) target. I.e. this is the "content" of the symlink.
                "a84ed33864d06615a87bc8da5258d841163f1e7969367ecd07b041ae1a18febd",  # sha256 TEXT,
                None,  # source_task INTEGER,
            ),
        ],
    )
    return cur


def test_crossstore_directory_insert(insert_directories: sqlite3.Cursor) -> None:
    cur = insert_directories
    with pytest.raises(sqlite3.IntegrityError):
        # declaring directory as belonging to store 2, but parent's store is 1
        cur.execute(
            "INSERT INTO filedir VALUES (?, ?, ?, ?)",
            (
                "b9774e78-2646-476e-b4a8-f6df0b10ba8e",  # uuid
                "3be35ff6-6a7b-49cf-ba3e-c610b781b54e",  # store
                "some_dir",  # filename
                "baad81ef-7cc6-48df-973c-e9f32ceda19a",  # parent
            ),
        )
        for row in cur.connection.iterdump():
            print(row)
        cur.execute("SELECT * FROM filedir")
        print(cur.fetchall())


@pytest.fixture
def insert_files(insert_directories: sqlite3.Cursor) -> sqlite3.Cursor:
    cur = insert_directories
    cur.execute("SELECT COUNT(*) FROM filedir")
    (nprev,) = cur.fetchone()
    cur.executemany(
        "INSERT INTO filedir VALUES " "(?, ?, ?, ?, ?)",
        [
            (
                None,  # id INTEGER PRIMARY KEY NOT NULL,
                1,  # store INTEGER NOT NULL,
                "example.csv",  # filename TEXT,  -- only a filename, not a path
                1,  # parent INTEGER REFERENCES filedir ON UPDATE CASCADE,
                False,  # frozen BOOL NOT NULL,
            ),
            (
                None,  # id INTEGER PRIMARY KEY NOT NULL,
                1,  # store INTEGER NOT NULL,
                "plots.png",  # filename TEXT,  -- only a filename, not a path
                2,  # parent INTEGER REFERENCES filedir ON UPDATE CASCADE,
                False,  # frozen BOOL NOT NULL,
            ),
        ],
    )
    cur.executemany(
        "INSERT INTO filedir_version VALUES " "(?, ?, ?, ?, ?, ?, ?, ?, ?)",
        [
            (
                None,  # id INTEGER PRIMARY KEY NOT NULL,
                nprev
                + 1,  # INTEGER REFERENCES filedir ON UPDATE CASCADE, -- parent filedir entry
                datetime.datetime.now().timestamp(),
                "REG",  # filetype TEXT, -- One of 'LNK', 'DIR', 'REG', etc. See store.FSEntry.from_path for details
                False,  # deleted BOOL NOT NULL,  -- set True when recording a deleted file
                "drwxrwxr-x",  # unfrozen_perms TEXT, -- stat.filemode(os.stat(path).st_mode): '-rw-rw-r--'
                None,  # symlink_target TEXT, -- if this is a symlink, this is the (read but not fully resolved) target. I.e. this is the "content" of the symlink.
                "a84ed33864d06615a87bc8da5258d841163f1e7969367ecd07b041ae1a18febd",  # sha256 TEXT,
                None,  # source_task INTEGER,
            ),
            (  # second version of first file
                None,  # id INTEGER PRIMARY KEY NOT NULL,
                nprev
                + 1,  # INTEGER REFERENCES filedir ON UPDATE CASCADE, -- parent filedir entry
                datetime.datetime.now().timestamp(),
                "REG",  # filetype TEXT, -- One of 'LNK', 'DIR', 'REG', etc. See store.FSEntry.from_path for details
                False,  # deleted BOOL NOT NULL,  -- set True when recording a deleted file
                "drwxr-xr-x",  # unfrozen_perms TEXT, -- stat.filemode(os.stat(path).st_mode): '-rw-rw-r--'
                None,  # symlink_target TEXT, -- if this is a symlink, this is the (read but not fully resolved) target. I.e. this is the "content" of the symlink.
                "a94ed33864d06615a87bc8da5258d841163f1e7969367ecd07b041ae1a18febd",  # sha256 TEXT,
                None,  # source_task INTEGER,
            ),
            (
                None,  # id INTEGER PRIMARY KEY NOT NULL,
                nprev
                + 2,  # INTEGER REFERENCES filedir ON UPDATE CASCADE, -- parent filedir entry
                datetime.datetime.now().timestamp(),
                "REG",  # filetype TEXT, -- One of 'LNK', 'DIR', 'REG', etc. See store.FSEntry.from_path for details
                False,  # deleted BOOL NOT NULL,  -- set True when recording a deleted file
                "drwxr-xr-x",  # unfrozen_perms TEXT, -- stat.filemode(os.stat(path).st_mode): '-rw-rw-r--'
                None,  # symlink_target TEXT, -- if this is a symlink, this is the (read but not fully resolved) target. I.e. this is the "content" of the symlink.
                "a94ed33864d06615a87bc8da5258d841163f1e7969367ecd07b041ae1a18febd",  # sha256 TEXT,
                None,  # source_task INTEGER,
            ),
        ],
    )
    return cur


# TODO: This test is disabled until triggers are added to check for these types
# of constraints. These became much more complicated to check when I added
# filedir_version.
def disabled_test_nondir_parent_directory_insert(insert_files: sqlite3.Cursor) -> None:
    cur = insert_files
    with pytest.raises(sqlite3.IntegrityError):
        # declaring parent as 5, but 5 is a file (plots.png)
        cur.execute(
            "INSERT INTO filedir VALUES " "(?, ?, ?, ?, ?)",
            (
                None,  # id INTEGER PRIMARY KEY NOT NULL,
                1,  # store INTEGER NOT NULL,
                "some_filedir.txt",  # filename TEXT,  -- only a filename, not a path
                5,  # parent INTEGER REFERENCES filedir ON UPDATE CASCADE,
                False,  # frozen BOOL NOT NULL,
            ),
        )
        for row in cur.connection.iterdump():
            print(row)
        cur.execute("SELECT * FROM filedir")
        print(cur.fetchall())