#!/bin/bash

# Create the database
sudo mariadb -e "CREATE DATABASE water_distribution;"

# Create the tables
sudo mariadb -e "CREATE TABLE user (
  id serial PRIMARY KEY,
  name text,
  address text (255),
  phone text (255)
);"

sudo mariadb -e "CREATE TABLE unidade_consumidora (
  id serial PRIMARY KEY,
  consumer_unit_id int NOT NULL,
  month text,
  current_consumption numeric,
  average_consumption numeric,
  mesurement varchar(255),
  user_name text
);"

sudo mariadb -e "CREATE TABLE payment (
  id serial PRIMARY KEY,
  user_id int NOT NULL,
  consumer_unit_id int NOT NULL,
  date_of_payment date,
  amount_paid numeric,
  is_paid boolean
);"

sudo mariadb -e "CREATE TRIGGER trigger_cutting_notice
  AFTER INSERT ON payment
  FOR EACH ROW
  BEGIN
    IF NEW.is_paid = 'false' AND (SELECT MONTH(CURRENT_DATE) - MONTH(NEW.date_of_payment)) >= 3 THEN
      INSERT INTO overdue (user_id, number_of_months_overdue)
      VALUES (NEW.user_id, 3);
    END IF;
  END;"