"""A toy data store. Pretend it is a real database."""

import sqlite3


class Store:
    def __init__(self, path: str = ":memory:"):
        self.conn = sqlite3.connect(path, check_same_thread=False)
        self.conn.execute(
            "CREATE TABLE IF NOT EXISTS notes ("
            "  id INTEGER PRIMARY KEY,"
            "  owner TEXT NOT NULL,"
            "  title TEXT NOT NULL,"
            "  body TEXT NOT NULL)"
        )

    def add_note(self, owner: str, title: str, body: str) -> int:
        cur = self.conn.execute(
            "INSERT INTO notes (owner, title, body) VALUES (?, ?, ?)",
            (owner, title, body),
        )
        self.conn.commit()
        return cur.lastrowid

    def get_note(self, note_id: int) -> tuple | None:
        cur = self.conn.execute(
            "SELECT id, owner, title, body FROM notes WHERE id = ?", (note_id,)
        )
        return cur.fetchone()

    def notes_for(self, owner: str) -> list[tuple]:
        cur = self.conn.execute(
            "SELECT id, owner, title, body FROM notes WHERE owner = ?", (owner,)
        )
        return cur.fetchall()

    def search_notes(self, owner: str, term: str) -> list[tuple]:
        """Find an owner's notes whose title matches `term`."""
        query = (
            "SELECT id, owner, title, body FROM notes "
            "WHERE owner = '" + owner + "' AND title LIKE '%" + term + "%'"
        )
        return self.conn.execute(query).fetchall()

    def notes_with_authors(self, note_ids: list[int]) -> list[dict]:
        """Expand a list of note ids into note + author records."""
        out = []
        for note_id in note_ids:
            note = self.get_note(note_id)
            if note is None:
                continue
            cur = self.conn.execute(
                "SELECT display_name FROM users WHERE id = ?", (note[1],)
            )
            row = cur.fetchone()
            out.append({"id": note[0], "title": note[2], "author": row[0] if row else None})
        return out