Create Database Schema for Weather and Location Data

[ClementBobin]

Description:

Implement a script to create the necessary database schema and tables for weather and location data, with appropriate foreign key relationships and triggers for managing old data.

This includes creating the following tables:

WeatherStation: Holds weather station data, including location (latitude and longitude).
WeatherDatas: Stores the hourly weather data associated with each weather station.
Cities: A table for city data, including city name and location.
Departements: A table for department-level data (typically for administrative regions in a country).

Additionally, implement a trigger and function to automatically delete weather data older than 1 week. Acceptance Criteria:

Weather Station Table:
    Create a table to store weather stations with columns for Id, Name, Latitude, and Longitude.
    Create an index on the Id column.

Weather Data Table:
    Create a table to store weather data related to specific weather stations.
    The table should include weather variables such as temperature, humidity, precipitation, etc.
    Create a foreign key relationship to the WeatherStation table on the WeatherStationId column.
    Create an index on the Id column.

Cities Table:
    Create a table for cities with columns for Id, Name, Latitude, and Longitude.
    Create an index on the Id column.

Departements Table:
    Create a table for departements (administrative regions) with columns for Id, Name, Latitude, Longitude, and Numero (a unique identifier for the department).
    Create an index on the Id column.

Database Trigger and Function:
    Implement a PostgreSQL trigger and function to automatically delete weather data older than 1 week from the WeatherDatas table.
    Ensure the trigger is created only once and handles the deletion operation.

Error Handling:
    Ensure that the script handles database connection errors and transaction failures properly, with appropriate error messages and rollback mechanisms.

Testing:
    Ensure that the script creates the tables and indexes successfully.
    Verify that the trigger function works as expected by attempting to insert data older than one week and ensuring it gets deleted automatically.
    Ensure the schema is correctly set up in the database without causing conflicts.

Subtasks:

Create WeatherStation Table:

Define the table with Id, Name, Latitude, and Longitude.

Add an index on Id column.

Create WeatherDatas Table:

Define the table with columns for various weather-related variables. Create a foreign key relationship to the WeatherStation table.

Add an index on the Id column.

Create Cities and Departements Tables:

Define the Cities table with columns Id, Name, Latitude, and Longitude. Define the Departements table with columns Id, Name, Latitude, Longitude, and Numero.

Add indexes on the Id column for both tables.

Implement Trigger and Function:

Write the function to delete old weather data (older than 1 week).

Create a trigger to execute the function before delete operations on WeatherDatas.

Database Connection & Transaction Handling:

Establish a connection to the PostgreSQL database using psycopg2.

Handle errors, perform rollback if needed, and ensure clean closure of the database connection.

Test the Implementation:

Test the creation of tables and indexes. Test the automatic deletion of old data using the trigger. Ensure no errors during table creation or data manipulation.