-- Create the agval_aws_db database if it doesn't exist (optional)
CREATE DATABASE agval_aws_db;

CREATE USER agvalawsuser_105x WITH PASSWORD 'au7109wkBMsyaokQ__51--!';


-- Connect to the agval_aws_db database
\connect agval_aws_db;


-- After creating the tables, grant permissions

GRANT ALL PRIVILEGES ON DATABASE agval_aws_db TO agvalawsuser_105x;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO agvalawsuser_105x;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO agvalawsuser_105x;



-- Create the weather_data table
CREATE TABLE IF NOT EXISTS weather_data (
  id SERIAL PRIMARY KEY,
  station_id VARCHAR(100) NOT NULL,
  timestamp TIMESTAMP NOT NULL,
  duration INTEGER NOT NULL,
  temperature NUMERIC,
  precipitation NUMERIC,
  humidity NUMERIC,
  windspeed NUMERIC,
  winddirection_degrees NUMERIC,
  radiation NUMERIC,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Create an index on the station_id column for faster searches (optional, but recommended)
CREATE INDEX IF NOT EXISTS station_id_index ON weather_data (station_id);

-- Create an index on the timestamp column for faster time-based searches (optional, but recommended)
CREATE INDEX IF NOT EXISTS timestamp_index ON weather_data (timestamp);



CREATE TABLE weather_stations (
  id SERIAL PRIMARY KEY,
  station_id VARCHAR(100) NOT NULL,  
  name VARCHAR(100),
  location VARCHAR(200),
  latitude FLOAT,
  longitude FLOAT,
  elevation FLOAT,
  start_date DATE,
  end_date DATE,
  station_status VARCHAR(20),
  data_sources TEXT,
  network_membership VARCHAR(100),
  contact_person VARCHAR(100),
  contact_email VARCHAR(100),
  contact_phone VARCHAR(20),
  installation_date DATE,
  sensor_specifications TEXT,
  data_collection_interval VARCHAR(50),
  sensor_heights TEXT,
  instrumentation_setup TEXT,
  data_transmission_method VARCHAR(100),
  data_processing_steps TEXT,
  calibration_schedule TEXT,
  data_access_formats TEXT,
  station_history TEXT,
  data_quality VARCHAR(50),
  equipment_calibration VARCHAR(50),
  maintenance_history TEXT,
  data_availability VARCHAR(50),
  temperature BOOLEAN,
  precipitation BOOLEAN,
  humidity BOOLEAN,
  wind_speed BOOLEAN,
  solar_radiation BOOLEAN,
  air_pressure BOOLEAN,
  cloud_cover BOOLEAN,
  evapotranspiration BOOLEAN,
  snow_depth BOOLEAN,
  visibility BOOLEAN,
  dew_point BOOLEAN,
  frost BOOLEAN,
  hail BOOLEAN
);