DB_NAME = 'postgres'
DB_USER = 'postgres'
DB_PASSWORD = 'postgres'
DB_HOST = 'localhost'


app = Flask(__name__)
conn = psycopg2.connect(dbname=DB_NAME, user=DB_USER, password=DB_PASSWORD, host=DB_HOST)
cur = conn.cursor()
cur.execute("CREATE TABLE IF NOT EXISTS region (id INT PRIMARY KEY,name VARCHAR NOT NULL);")
cur.execute("CREATE TABLE IF NOT EXISTS tax_param (id SERIAL PRIMARY KEY, city_id INT REFERENCES region(id) NOT NULL, from_hp_car INT NOT NULL, to_hp_car INT NOT NULL, from_production_year_car INT NOT NULL, to_production_year_car INT NOT NULL, rate NUMERIC NOT NULL);")
cur.execute("CREATE TABLE IF NOT EXISTS auto (id SERIAL PRIMARY KEY, city_id INT REFERENCES region(id) NOT NULL, tax_id INT REFERENCES tax_param(id) NOT NULL, name VARCHAR NOT NULL, horse_power INT NOT NULL, production_year INT NOT NULL, tax NUMERIC NOT NULL);")
conn.commit()


#Задание 1
@app.route('/v1/add/region', methods=['POST'])
def add_region():
    data = request.get_json()
    id = data['id']
    name = data['name']
    cur.execute("SELECT * FROM region WHERE id=(%s)", (id,))
    region_exist = cur.fetchone()
    if region_exist:
        abort(400)
    else:
        cur.execute("INSERT INTO region (id,name) VALUES(%s,%s)", (id, name,))
        conn.commit()
        message = {'message': 'Region added'}
        return jsonify(message)
# Проверка: curl -X POST http://127.0.0.1:5000/v1/add/region -d '{"id": "54", "name": "Novosibirsk"}' -H "Content-type: application/json" -H "Accept: application/json"


#Задание 2
@app.route('/v1/add/tax_param', methods=['POST'])
def add_tax_param():
    data = request.get_json()
    city_id = data['city_id']
    from_hp_car = data['from_hp_car']
    to_hp_car = data['to_hp_car']
    from_production_year_car = data['from_production_year_car']
    to_production_year_car = data['to_production_year_car']
    rate = data['rate']
    cur.execute("SELECT * FROM region WHERE id=(%s)", (city_id,))
    region_exist = cur.fetchone()
    if region_exist:
        cur.execute("INSERT INTO tax_param (city_id,from_hp_car,to_hp_car,from_production_year_car,to_production_year_car,rate) VALUES(%s,%s,%s,%s,%s,%s)", (city_id, from_hp_car, to_hp_car, from_production_year_car, to_production_year_car, rate))
        conn.commit()
        message = {'message': 'Tax_param added'}
        return jsonify(message)
    else:
        abort(400)
# Проверка: curl -X POST http://127.0.0.1:5000/v1/add/tax_param -d '{"city_id": "54", "from_hp_car": "100", "to_hp_car": "200", "from_production_year_car": "2000", "to_production_year_car": "2030", "rate": "5"}' -H "Content-type: application/json" -H "Accept: application/json"


#Задание 3
@app.route('/v1/add/auto', methods=['POST'])
def add_auto():
    data = request.get_json()
    city_id = data['city_id']
    name = data['name']
    horse_power = data['horse_power']
    production_year = data['production_year']
    cur.execute("SELECT * FROM region WHERE id=(%s)", (city_id,))
    region_exist = cur.fetchone()
    if region_exist:
        cur.execute("SELECT id FROM tax_param WHERE from_hp_car<=(%s) AND to_hp_car>=(%s) AND from_production_year_car<=(%s) AND to_production_year_car>=(%s)", (horse_power,horse_power,production_year,production_year,))
        tax_exist = cur.fetchone()
        if tax_exist:
            tax_id = int(tax_exist[0])
            cur.execute("SELECT rate FROM tax_param WHERE id=(%s)", (tax_id,))
            rate = int(cur.fetchone()[0])
            tax = rate*int(horse_power)
            cur.execute("INSERT INTO auto (city_id,tax_id,name,horse_power,production_year,tax) VALUES(%s,%s,%s,%s,%s,%s)", (city_id,tax_id,name,horse_power,production_year,tax))
            conn.commit()
            message = {'message': 'Auto added'}
            return jsonify(message)
        else:
            abort(400)
    else:
        abort(400)
# Проверка: curl -X POST http://127.0.0.1:5000/v1/add/auto -d '{"city_id": "54", "name": "Lada", "horse_power": "150", "production_year": "2010"}' -H "Content-type: application/json" -H "Accept: application/json"


#Задание 4
@app.route('/v1/auto/<id>', methods=['GET'])
def auto(id):
    cur.execute("SELECT * FROM auto WHERE id=(%s)", (int(id),))
    auto = cur.fetchone()
    message = {"Auto": f"{auto}"}
    return jsonify(message)

# Проверка: curl -X GET http://127.0.0.1:5000/v1/auto/1


if __name__ == '__main__':
    app.run()