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

app = Flask(__name__)
CORS(app)

# SQLite DB 초기화
def init_db():
    conn = sqlite3.connect('fuel_data.db')
    cursor = conn.cursor()
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS fuel_records (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            year TEXT,
            month TEXT,
            date TEXT,
            fuel_amount REAL,
            current_km REAL,
            driven_distance REAL
        )
    ''')
    conn.commit()
    conn.close()

init_db()

@app.route('/')
def index():
    return render_template('index.html')

@app.route('/api/oil_price')
def oil_price():
    url = "http://www.opinet.co.kr/api/detailById.do"
    params = {"code": "F240808241", "id": "A0017598", "out": "json"}

    response = requests.get(url, params=params)
    if response.status_code == 200:
        data = response.json()
        price_value = None
        for oil in data['RESULT']['OIL']:
            for oil_price in oil['OIL_PRICE']:
                if oil_price['PRODCD'] == 'D047':
                    price_value = oil_price['PRICE']
                    break
        return jsonify({"PRICE": price_value})
    else:
        return jsonify({"PRICE": "- 원(경유)"})

@app.route('/api/fuel_records', methods=['GET'])
def get_fuel_records():
    conn = sqlite3.connect('fuel_data.db')
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM fuel_records ORDER BY year DESC, month DESC, date DESC")
    records = cursor.fetchall()
    conn.close()

    data = []
    total_driven_distance = 0

    for idx, row in enumerate(records):
        total_driven_distance += row[5]
        data.append({
            "order": len(records) - idx,
            "year": row[1],
            "month": row[2],
            "date": row[3],
            "fuelAmount": row[4],
            "currentKm": row[5],
            "drivenDistance": row[6],
            "fuelEfficiency": row[6] / row[4] if row[4] != 0 else 0  # 연비 계산
        })

    return jsonify({"records": data, "totalKm": total_driven_distance})

@app.route('/api/year_options', methods=['GET'])
def year_options():
    conn = sqlite3.connect('fuel_data.db')
    cursor = conn.cursor()
    cursor.execute("SELECT DISTINCT year, month FROM fuel_records ORDER BY year DESC, month DESC")
    year_months = cursor.fetchall()
    conn.close()

    unique_year_months = sorted(set([f"{year[0]}년 {year[1]}월" for year in year_months]))
    return jsonify({"year_months": unique_year_months})

@app.route('/api/add_fuel_record', methods=['POST'])
def add_fuel_record():
    data = request.json
    conn = sqlite3.connect('fuel_data.db')
    cursor = conn.cursor()
    cursor.execute('''
        INSERT INTO fuel_records (year, month, date, fuel_amount, current_km, driven_distance)
        VALUES (?, ?, ?, ?, ?, ?)
    ''', (data['year'], data['month'], data['date'], data['fuelAmount'], data['currentKm'], data['drivenDistance']))
    conn.commit()
    conn.close()

    return jsonify({"status": "success"})

if __name__ == '__main__':
    app.run('0.0.0.0', port=5000, debug=True)