import pathlib
from typing import Dict
from typing import List
import re


def parse_schema(sql_file: pathlib.Path) -> Dict[str, List[str]]:
    """
    Parses the SQL schema creation script (schemas.sql)
    """
    in_comment = False
    in_table_schema = False
    schema = {}
    table_schema: List[str] = []
    with open(sql_file, "r", encoding="utf-8") as in_fd:
        for line in in_fd:
            line = line.strip().lower()
            if "/*" in line:
                # Start of comment
                in_comment = True
                continue
            elif "*/" in line:
                # End of comment
                in_comment = False
                continue
            elif in_comment:
                # Ignore comments
                continue
            elif line == ");":
                # End of schema
                in_table_schema = False
            elif line.startswith("create table "):
                # Starting a table
                match = re.search(r"create table (\w+) \(", line)
                if match is not None:
                    in_table_schema = True
                    table = match.group(1)
                    table_schema = []
                    schema[table] = table_schema
            elif in_table_schema:
                # Line in the current schema
                col_name = line.split()[0]
                table_schema.append(col_name)

    return schema