import os
import sys
from pathlib import Path

from sqlalchemy.dialects import mysql
from sqlalchemy.schema import CreateIndex, CreateTable

sys.path.append(str(Path(__file__).resolve().parents[1]))

from app import create_app
from app.extensions import db


def export_schema(output_path: str | None = None) -> Path:
    app = create_app(os.getenv("FLASK_CONFIG", "development"))
    with app.app_context():
        statements = []
        dialect = mysql.dialect()
        for table in db.metadata.tables.values():
            statements.append(str(CreateTable(table).compile(dialect=dialect)).rstrip() + ";")
            for index in sorted(table.indexes, key=lambda item: item.name or ""):
                statements.append(str(CreateIndex(index).compile(dialect=dialect)).rstrip() + ";")

        destination = Path(output_path or Path(app.root_path).parent / "database" / "mysql_schema.sql")
        destination.parent.mkdir(parents=True, exist_ok=True)
        destination.write_text("\n\n".join(statements) + "\n", encoding="utf-8")
        return destination


if __name__ == "__main__":
    path = export_schema()
    print(f"Schema written to {path}")