#! /usr/bin/env python
# -*- coding: utf-8 -*-
# vim:fenc=utf-8
#
# Copyright © 2024 StrayWarrior <i@straywarrior.com>

from flask import Flask, request, render_template
from flaskext.mysql import MySQL
from datetime import datetime, timedelta

app = Flask(__name__)

app.config['MYSQL_DATABASE_HOST'] = 'rm-bp17q95335a99272b.mysql.rds.aliyuncs.com'
app.config['MYSQL_DATABASE_USER'] = 'crawler'
app.config['MYSQL_DATABASE_PASSWORD'] = 'crawler123456@'
app.config['MYSQL_DATABASE_DB'] = 'growth'

mysql = MySQL()
mysql.init_app(app)

@app.route('/', methods=['GET', 'POST'])
def index():
    if request.method == 'POST':
        staff_id = request.form['staff_id']
        video_id = request.form['video_id']

        if video_id:
            condition = "b.miniprogram_video_id = %s"
            if staff_id:
                condition += " AND a.carrier_id = %s"
            query = f"""
	            SELECT a.carrier_id, a.remark, b.miniprogram_video_id, b.send_time,
                       DATEDIFF(NOW(), b.send_time) as days
                FROM we_com_staff a JOIN we_com_message_attachment b
                ON a.id = b.staff_id
                WHERE {condition} ORDER BY send_time DESC
            """
            cursor = mysql.get_db().cursor()
            if staff_id:
                cursor.execute(query, (video_id, staff_id))
            else:
                cursor.execute(query, (video_id))
            result = cursor.fetchall()
            cursor.close()
        elif staff_id:
            query = """
	            SELECT a.carrier_id, a.remark, b.miniprogram_video_id, b.send_time,
                       DATEDIFF(NOW(), b.send_time) as days
                FROM we_com_staff a JOIN we_com_message_attachment b
                ON a.id = b.staff_id
                WHERE a.carrier_id = %s ORDER BY send_time DESC
            """
            cursor = mysql.get_db().cursor()
            cursor.execute(query, (staff_id,))
            result = cursor.fetchall()
            cursor.close()
        else:
            return render_template('index.html', results=[], form=request.form)

        return render_template('index.html', results=result, form=request.form)
    return render_template('index.html', results=[], form={'staff_id': '', 'video_id': ''})

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