import os
import logging
from logging.handlers import RotatingFileHandler
from flask import Flask, request, render_template, redirect, url_for
from flask import Flask, jsonify
from flaskext.mysql import MySQL

app = Flask(__name__)
app.config['MYSQL_DATABASE_HOST'] = 'tr-wp-database.cfqdq6ohjn0p.us-east-1.rds.amazonaws.com'
app.config['MYSQL_DATABASE_USER'] = 'tr_wp_admin'
app.config['MYSQL_DATABASE_PASSWORD'] = 'Wipro123?'
app.config['MYSQL_DATABASE_DB'] = 'happilo'
app.config['MYSQL_DATABASE_PORT'] = 3306
mysql = MySQL(app)

handler = RotatingFileHandler('app.log', maxBytes=10000, backupCount=1)
handler.setLevel(logging.INFO)
app.logger.addHandler(handler)

@app.route('/')

def index():
    try:
        # Connect to the database
        conn = mysql.connect()
        cursor = conn.cursor()

        # Define your SQL query to fetch specific columns
        query = """
                SELECT platform_name, location_name, pincode, brand_name, reseller_name_crawl,
                       web_pid, pdp_page_url, osa_remark, price_rp, price_sp, pdp_title_value,
                       pdp_desc_value, pdp_rating_value, created_on
                FROM rb_pdp
                WHERE pf_id = 1 AND DATE(created_on) = DATE(NOW()) - 1
                """

        # Execute the query
        cursor.execute(query)

        # Fetch all rows
        rows = cursor.fetchall()

        # Convert the data to a list of dictionaries
        data = []
        for row in rows:
            data.append({
                'platform_name': row[0],
                'location_name': row[1],
                'pincode': row[2],
                'brand_name': row[3],
                'reseller_name_crawl': row[4],
                'web_pid': row[5],
                'pdp_page_url': row[6],
                'osa_remark': row[7],
                'price_rp': row[8],
                'price_sp': row[9],
                'pdp_title_value': row[10],
                'pdp_desc_value': row[11],
                'pdp_rating_value': row[12],
                'created_on': row[13].strftime('%Y-%m-%d %H:%M:%S')  # Convert datetime to string
            })

        # Close the cursor and connection
        cursor.close()
        conn.close()
        print(data)
        # Return the data as JSON
        return jsonify(data)

    except Exception as e:
        return jsonify({'error': str(e)}), 500

application = app