import sqlite3
from flask import Flask, request, jsonify
from flask_cors import CORS
import datetime


app = Flask(__name__)
CORS(app)

DATABASE = 'orders.db'

def get_db():
    connect = sqlite3.connect(DATABASE)
    connect.row_factory = sqlite3.Row
    return connect

def init_db():
    with app.app_context():
        db = get_db()
        with open('schema.sql', 'r') as f:
            db.executescript(f.read())
        db.commit()

@app.route("/orders", methods=['GET'])
def get_orders():
    db = get_db()
    orders = db.execute('SELECT * FROM orders ORDER BY id DESC').fetchall()
    return jsonify([dict(order) for order in orders])

@app.route("/orders", methods=['POST'])
def add_order():
    order = request.json
    db = get_db()
    
    product = db.execute('SELECT * FROM products WHERE name = ?', (order['order_detail'],)).fetchone()
    if product is None:
        return jsonify({'error': 'Product not found'}), 404
    
    if product['stock'] < order['quantity']:
        return jsonify({'error': 'Out of stock'}), 400

    new_stock = product['stock'] - order['quantity']
    db.execute('UPDATE products SET stock = ? WHERE name = ?', (new_stock, order['order_detail']))

    db.execute(
        'INSERT INTO orders (time, name, order_detail, status, quantity, price) VALUES (?, ?, ?, ?, ?, ?)',
        (order['time'], order['name'], order['order_detail'], order['status'], order['quantity'], order['price'])
    )
    db.commit()
    return jsonify(order), 201

@app.route("/orders/<int:id>", methods=['PUT'])
def update_order(id):
    order = request.json
    db = get_db()


    current_order = db.execute('SELECT * FROM orders WHERE id = ?', (id,)).fetchone()
    if not current_order:
        return jsonify({'error': 'Order not found'}), 404

    if current_order['order_detail'] != order['order_detail'] or current_order['quantity'] != order['quantity']:

        product = db.execute('SELECT * FROM products WHERE name = ?', (current_order['order_detail'],)).fetchone()
        new_stock = product['stock'] + current_order['quantity']
        db.execute('UPDATE products SET stock = ? WHERE name = ?', (new_stock, current_order['order_detail']))

        new_product = db.execute('SELECT * FROM products WHERE name = ?', (order['order_detail'],)).fetchone()
        if new_product['stock'] < order['quantity']:
            return jsonify({'error': 'Out of stock'}), 400
        
        new_stock = new_product['stock'] - order['quantity']
        db.execute('UPDATE products SET stock = ? WHERE name = ?', (new_stock, order['order_detail']))

    db.execute(
        'UPDATE orders SET name = ?, order_detail = ?, status = ?, quantity = ?, price = ? WHERE id = ?',
        (order['name'], order['order_detail'], order['status'], order['quantity'], order['price'], id)
    )
    db.commit()
    return jsonify(order), 200

@app.route("/orders/<int:id>", methods=['DELETE'])
def delete_order(id):
    db = get_db()

    current_order = db.execute('SELECT * FROM orders WHERE id = ?', (id,)).fetchone()
    if not current_order:
        return jsonify({'error': 'Order not found'}), 404

    product = db.execute('SELECT * FROM products WHERE name = ?', (current_order['order_detail'],)).fetchone()
    new_stock = product['stock'] + current_order['quantity']
    db.execute('UPDATE products SET stock = ? WHERE name = ?', (new_stock, current_order['order_detail']))

    db.execute('DELETE FROM orders WHERE id = ?', (id,))
    db.commit()
    return '', 204

@app.route("/products", methods=['GET'])
def get_products():
    db = get_db()
    products = db.execute('SELECT * FROM products').fetchall()
    return jsonify([dict(product) for product in products])

@app.route("/products", methods=['POST'])
def add_product():
    product = request.json
    db = get_db()
    db.execute('INSERT INTO products (name, stock, price) VALUES (?, ?, ?)',
               (product['name'], product['stock'], product['price']))
    db.commit()
    return jsonify(product), 201

@app.route("/products/<int:id>", methods=['DELETE'])
def delete_product(id):
    db = get_db()
    db.execute('DELETE FROM products WHERE id = ?', (id,))
    db.commit()
    return '', 204

if __name__ == "__main__":
    init_db()
    app.run(debug=True)