"""Script to create the SQL schema in Azure SQL Database."""

import os

import pyodbc

schema_creation = """
CREATE TABLE Addresses (
    ID INT PRIMARY KEY IDENTITY(1,1),
    StreetName NVARCHAR(100),
    HouseNumber NVARCHAR(10),
    City NVARCHAR(50),
    PostalCode NVARCHAR(10),
    Longitude FLOAT,
    Latitude FLOAT
);

CREATE TABLE Meters (
    ID INT PRIMARY KEY IDENTITY(1,1),
    Signature NVARCHAR(11),
    MeterType NVARCHAR(3),
    AddressID INT,
    FOREIGN KEY (AddressID) REFERENCES Addresses(ID)
);

CREATE TABLE Customers (
    ID INT PRIMARY KEY IDENTITY(1,1),
    FirstName NVARCHAR(100),
    LastName NVARCHAR(100),
    GasMeterID INT,
    EltMeterID INT,
    FOREIGN KEY (GasMeterID) REFERENCES Meters(ID),
    FOREIGN KEY (EltMeterID) REFERENCES Meters(ID)
);

CREATE TABLE Readings (
    ID INT PRIMARY KEY IDENTITY(1,1),
    CustomerID INT,
    MeterID INT,
    ReadingDate DATE,
    ReadingValue INT,
    FOREIGN KEY (CustomerID) REFERENCES Customers(ID),
    FOREIGN KEY (MeterID) REFERENCES Meters(ID)
);
"""


def create_tables(cursor: pyodbc.Cursor, schema: str) -> None:
    """Create database tables based on the provided schema.

    This function takes a database schema as a string and executes each table
    creation statement separately. It handles cases where tables already exist
    and reports this information.

    Parameters
    ----------
    cursor : pyodbc.Cursor
        A pyodbc cursor object used to execute SQL commands.
    schema : str
        A string containing SQL statements for table creation, separated by
        semicolons.

    Returns
    -------
    None
        This function doesn't return any value.

    Raises
    ------
    pyodbc.ProgrammingError
        If there's an error in executing the SQL statements, other than
        "table already exists".

    Notes
    -----
    - The function assumes the existence of a global 'conn' object for
      committing changes to the database.
    - It prints a message if a table already exists instead of raising an error.
    """
    for table_creation in schema.split(";"):
        if table_creation.strip():
            try:
                cursor.execute(table_creation)
                conn.commit()
            except pyodbc.ProgrammingError as e:
                if "There is already an object named" in str(e):
                    print(f"Table already exists: {table_creation.split('(')[0].split()[-1]}")
                else:
                    raise


if __name__ == "__main__":

    # Connect to the database
    connection_string = os.environ.get("AZURE_SQL_CONNECTION_STRING")

    try:
        conn = pyodbc.connect(connection_string)
        cursor = conn.cursor()
        print("Connected to Azure SQL Database successfully!")

        # Example query
        create_tables(cursor, schema_creation)

    except pyodbc.Error as e:
        print(f"Error connecting to Azure SQL Database: {e}")
    finally:
        if "cursor" in locals():
            cursor.close()
        if "conn" in locals():
            conn.close()