from flask import Flask, request, render_template, redirect, url_for
import sqlite3, datetime, pandas as pd, os

DB = os.path.join(os.path.dirname(__file__), 'alert.db')
app = Flask(__name__)

def init_db():
    with sqlite3.connect(DB) as conn:
        conn.execute("""CREATE TABLE IF NOT EXISTS alerts(
            id INTEGER PRIMARY KEY,
            code TEXT,
            price REAL,
            change REAL,
            ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )""")

@app.route('/post_alert', methods=['POST'])
def post_alert():
    data = request.get_json(force=True)
    with sqlite3.connect(DB) as conn:
        conn.execute("INSERT INTO alerts(code,price,change) VALUES(?,?,?)",
                     (data['code'], data['price'], data['change']))
    return {'status':'ok'}

@app.route('/')
def index():
    init_db()
    df = pd.read_sql('SELECT * FROM alerts ORDER BY ts DESC LIMIT 100', sqlite3.connect(DB))
    return render_template('index.html', table=df.to_html(classes='table table-sm', index=False))

if __name__ == '__main__':   # ← ローカルテスト用
    init_db()
    app.run(debug=True, host='0.0.0.0')