import csv
from flask import Flask, render_template, request, redirect, url_for, session, jsonify
from datetime import datetime, timedelta
from collections import defaultdict
from time import time
import sqlite3
import platformdirs
from werkzeug.utils import secure_filename
import os
import pandas as pd
import sys

request_counts = defaultdict(int)

app = Flask(__name__)
app.secret_key = 'zEawjDZeK9Y2vcLbCRq7tRwiiJ81BYCDiSab0N97sPuL7I4bEm'

if not os.path.isfile('dnc_records.db'):
    # Connect to the SQLite database
    conn = sqlite3.connect('dnc_records.db')

    # Create a cursor object
    cursor = conn.cursor()

    # Create the phone_records table
    cursor.execute('''
    CREATE TABLE IF NOT EXISTS phone_records (
        ID INTEGER PRIMARY KEY AUTOINCREMENT,
        Phone TEXT,
        Type TEXT,
        First_name TEXT,
        Middle_name TEXT,
        Last_name TEXT,
        Case_title TEXT,
        Multiple_cases INTEGER,
        Phone_type TEXT,
        Phone_status TEXT,
        Filling_date TEXT,
        Added TEXT
    )
    ''')

    # Commit the changes and close the connection
    conn.commit()
    conn.close()

@app.route('/')
def home():
    if 'logged_in' in session:
        return redirect(url_for('dnc'))
    else:
        return redirect(url_for('login'))

@app.route('/login', methods=['GET', 'POST'])
def login():
    if request.method == 'POST':
        username = request.form['username']
        password = request.form['password']
        if username == 'admin' and password == 'J4ZemfMBjq':
            session['logged_in'] = True
            return redirect(url_for('dnc'))
        else:
            return 'Invalid username or password'
    else:
        return render_template('login.html')

@app.route('/logout')
def logout():
    session.pop('logged_in', None)
    return redirect(url_for('login'))

@app.route('/dnc')
def dnc():
    if 'logged_in' in session:
        return render_template('index.html')
    else:
        return redirect(url_for('login'))

@app.route('/search', methods=['POST'])
def search():
    if 'logged_in' in session:
        phone_number = request.form['phone_number']
        ip = request.form['ip']
        current_minute = int(time() / 60)

        if request_counts[(ip, current_minute)] >= 20:
            return jsonify({'error': 'Too many requests. Please wait a minute.'})

        request_counts[(ip, current_minute)] += 1

        # Connect to the SQLite database
        conn = sqlite3.connect('dnc_records.db')
        cursor = conn.cursor()

        # Execute the SQL query to search for the phone number
        cursor.execute('SELECT * FROM phone_records WHERE Phone = ?', (phone_number,))
        results = cursor.fetchall()
        conn.close()

        if results:
            phone_dicts = [
                {
                    'Phone': result[0],
                    'Type': result[1],
                    'First_name': result[2],
                    'Middle_name': result[3],
                    'Last_name': result[4],
                    'Case_title': result[5],
                    'Multiple_cases': result[6],
                    'Phone_type': result[7],
                    'Phone_status': result[8],
                    'Filling_date': result[9],
                    'Added': result[10]
                } for result in results
            ]
            return jsonify(phone_dicts)
        else:
            return jsonify({'error': 'Phone number not found, Good to go.'})
    else:
        return redirect(url_for('login'))
    
@app.route('/store_ip', methods=['POST'])
def store_ip():
    ip = request.json['ip']
    with open('ips.txt', 'a') as file:
        file.write(ip + '\n')
    return '', 204

@app.route('/report_dnc', methods=['POST'])
def report_dnc():
    if 'popup_logged_in' not in session:
        return jsonify({'status': 'error', 'message': 'Please login on the original rev portal report dnc page for access.'})
    if 'logged_in' in session:
        data = request.form.to_dict()
        try:
            # Extract required fields from the request form data
            phone = data['phone']
            type = data['type']
            first_name = data['first_name']
            middle_name = data['middle_name']
            last_name = data['last_name']
            case_title = data['case_title']
            multiple_cases = int(data['multiple_cases'])
            phone_type = data['phone_type']
            phone_status = data['phone_status']
            filling_date = data['filling_date']
            added = data['added']

            # Write to CSV file
            with open('data/dnc_records.csv', 'a', newline='') as csvfile:
                fieldnames = ['Phone', 'Type', 'First_name', 'Middle_name', 'Last_name',
                              'Case_title', 'Multiple_cases', 'Phone_type', 'Phone_status',
                              'Filling_date', 'Added']
                writer = csv.DictWriter(csvfile, fieldnames=fieldnames)

                # Write new record to CSV file
                writer.writerow({
                    'Phone': phone,
                    'Type': type,
                    'First_name': first_name,
                    'Middle_name': middle_name,
                    'Last_name': last_name,
                    'Case_title': case_title,
                    'Multiple_cases': multiple_cases,
                    'Phone_type': phone_type,
                    'Phone_status': phone_status,
                    'Filling_date': filling_date,
                    'Added': added
                })

            # Add record to SQLite database
            conn = sqlite3.connect('dnc_records.db')
            cursor = conn.cursor()
            cursor.execute('''
                INSERT OR IGNORE INTO phone_records (
                    Phone, Type, First_name, Middle_name, Last_name, Case_title,
                    Multiple_cases, Phone_type, Phone_status, Filling_date, Added
                ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
                ''', (
                    phone, type, first_name, middle_name,
                    last_name, case_title, multiple_cases,
                    phone_type, phone_status, filling_date,
                    added
                ))
            conn.commit()
            conn.close()

            return jsonify({'message': 'Record added successfully'}), 201
        except KeyError as e:
            return jsonify({'error': f'Missing required field: {str(e)}'}), 400
    else:
        return redirect(url_for('login'))

@app.route('/report', methods=['GET'])
def report_dnc_page():
    if 'logged_in' in session:
        return render_template('report.html')
    else:
        return redirect(url_for('login'))

@app.route('/bulk_upload', methods=['POST'])
def bulk_upload():
    if 'popup_logged_in' not in session:
        return jsonify({'status': 'error', 'message': 'Please login on the original rev portal report dnc page for access.'})
    if 'logged_in' in session:
        if 'file' not in request.files:
            return jsonify({'error': 'No file provided'}), 400

        file = request.files['file']
        filename = secure_filename(file.filename)

        # Ensure the /temp directory exists
        temp_dir = os.path.join(os.getcwd(), 'temp')
        if not os.path.exists(temp_dir):
            os.makedirs(temp_dir)

        filepath = os.path.join(temp_dir, filename)
        file.save(filepath)

        try:
            # Determine if the file is CSV or Excel
            if filename.endswith('.csv'):
                temp_csv_filepath = filepath
            elif filename.endswith(('.xls', '.xlsx')):
                temp_csv_filepath = os.path.join(temp_dir, filename.rsplit('.', 1)[0] + '.csv')
                df = pd.read_excel(filepath)
                df.to_csv(temp_csv_filepath, index=False)
            else:
                return jsonify({'error': 'Unsupported file format'}), 400

            # Read the uploaded CSV file
            with open(temp_csv_filepath, 'r', newline='') as csvfile:
                reader = csv.DictReader(csvfile)
                new_rows = [row for row in reader]

            # Connect to the SQLite database
            conn = sqlite3.connect('dnc_records.db')
            cursor = conn.cursor()

            # Append the new data to the existing data in the SQLite database
            for row in new_rows:
                cursor.execute('''
                    INSERT OR IGNORE INTO phone_records (
                        Phone, Type, First_name, Middle_name, Last_name, Case_title,
                        Multiple_cases, Phone_type, Phone_status, Filling_date, Added
                    ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
                    ''', (
                        row.get('Phone', ''),
                        row.get('Type', ''),
                        row.get('First_name', ''),
                        row.get('Middle_name', ''),
                        row.get('Last_name', ''),
                        row.get('Case_title', ''),
                        row.get('Multiple_cases', ''),
                        row.get('Phone_type', ''),
                        row.get('Phone_status', ''),
                        row.get('Filling_date', ''),
                        row.get('Added', '')
                    ))

            conn.commit()
            conn.close()

            # Write the new records to the dnc_records.csv file
            with open('data/dnc_records.csv', 'a', newline='') as csvfile:
                fieldnames = ['Phone', 'Type', 'First_name', 'Middle_name', 'Last_name',
                              'Case_title', 'Multiple_cases', 'Phone_type', 'Phone_status',
                              'Filling_date', 'Added']
                writer = csv.DictWriter(csvfile, fieldnames=fieldnames)

                # Write new records to CSV file
                for row in new_rows:
                    writer.writerow({
                        'Phone': row.get('Phone', ''),
                        'Type': row.get('Type', ''),
                        'First_name': row.get('First_name', ''),
                        'Middle_name': row.get('Middle_name', ''),
                        'Last_name': row.get('Last_name', ''),
                        'Case_title': row.get('Case_title', ''),
                        'Multiple_cases': row.get('Multiple_cases', ''),
                        'Phone_type': row.get('Phone_type', ''),
                        'Phone_status': row.get('Phone_status', ''),
                        'Filling_date': row.get('Filling_date', ''),
                        'Added': row.get('Added', '')
                    })

            return jsonify({'message': 'Records added successfully'}), 201
        finally:
            # Delete the temporary files
            if os.path.exists(filepath):
                os.remove(filepath)
            if os.path.exists(temp_csv_filepath) and temp_csv_filepath != filepath:
                os.remove(temp_csv_filepath)
    else:
        return redirect(url_for('login'))

@app.route('/popup_login', methods=['POST'])
def popup_login():
    username = request.form['username']
    password = request.form['password']
    if username == 'rv13_lab' and password == '4Hv5soRWKP3oH0d':
        session['popup_logged_in'] = True
        return jsonify({'status': 'success'})
    else:
        return jsonify({'status': 'error', 'message': 'Invalid username or password'})

@app.route('/quit', methods=['GET'])
def quit():
    if 'logged_in' in session:
        return render_template('quit.html')
    else:
        return redirect(url_for('login'))

@app.route('/quit_server', methods=['POST'])
def quit_server():
    username = request.form['username']
    password = request.form['password']

    if username == 'Rev_Q' and password == 'revsaidquitt':
        try:
            # Delete the dnc_records.db file
            os.remove('dnc_records.db')
            print('dnc_records.db file deleted successfully')
        except OSError as e:
            print("Error: %s - %s." % ("dnc_records.db", e.strerror))

        # Terminate the Python process
        os._exit(0)

    else:
        return 'Invalid username or password', 401
    
def create_and_populate_db():
    db_file = 'dnc_records.db'
    
    # Check if the database file already exists
    db_exists = os.path.exists(db_file)
    
    # Connect to the SQLite database
    conn = sqlite3.connect(db_file)
    
    # Create a cursor object
    cursor = conn.cursor()
    
    # Create table if it does not exist
    cursor.execute('''
    CREATE TABLE IF NOT EXISTS phone_records (
        Phone TEXT PRIMARY KEY,
        Type TEXT,
        First_name TEXT,
        Middle_name TEXT,
        Last_name TEXT,
        Case_title TEXT,
        Multiple_cases TEXT,
        Phone_type TEXT,
        Phone_status TEXT,
        Filling_date TEXT,
        Added TEXT
    )
    ''')
    
    # Check if the table is empty
    cursor.execute('SELECT COUNT(*) FROM phone_records')
    count = cursor.fetchone()[0]
    
    if count == 0:
        # Populate the database with the phone number records if the table is empty
        with open('data/dnc_records.csv', 'r') as file:
            reader = csv.DictReader(file)
            
            # Insert the phone number records into the database
            for row in reader:
                cursor.execute('''
                INSERT OR IGNORE INTO phone_records (
                    Phone, Type, First_name, Middle_name, Last_name, Case_title,
                    Multiple_cases, Phone_type, Phone_status, Filling_date, Added
                ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
                ''', (
                    row['Phone'], row['Type'], row['First_name'], row['Middle_name'],
                    row['Last_name'], row['Case_title'], row['Multiple_cases'],
                    row['Phone_type'], row['Phone_status'], row['Filling_date'],
                    row['Added']
                ))
    
    # Commit the changes and close the connection
    conn.commit()
    conn.close()

if __name__ == '__main__':
    create_and_populate_db()
    app.run(port=5005, host='0.0.0.0')