Abel9436/PySQLEngine ? reverse-engineered prompt

Build me a tiny SQL database engine in pure Python, with a simple command line REPL and a Python API so I can create tables, insert rows, and run queries against a local database file.

I want it to support common SQL like CREATE TABLE, INSERT, SELECT, UPDATE, DELETE, DROP TABLE, and CREATE INDEX, plus basic joins, filters, sorting, grouping, aggregates, and subqueries. It should also be able to show the query plan for a statement in a readable way.

Make it actually store data on disk, not just in memory, and include a basic index so lookups can be faster. If you can, add transactions with snapshot style reads, rollback, and crash recovery with a simple write ahead log. I also want a few tests so I can tell the core pieces are working. Keep it educational and clean, since the goal is to understand how a database works under the hood.