from flask import Flask, render_template, request, redirect, url_for, flash
from flask import jsonify
import sqlite3
from datetime import datetime
import os

app = Flask(__name__)
app.secret_key = 'your_secret_key_here'  # Change this in production


# Database initialization
def init_db():
    conn = sqlite3.connect('rwh_database.db')
    cursor = conn.cursor()

    cursor.execute('''
        CREATE TABLE IF NOT EXISTS rwh_users (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            phone TEXT,
            email TEXT,
            address TEXT,
            location TEXT,
            property_type TEXT NOT NULL,
            roof_area REAL,
            system_type TEXT NOT NULL,
            tank_capacity REAL,
            notes TEXT,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    ''')

    conn.commit()
    conn.close()


# Database helper functions
def get_db_connection():
    conn = sqlite3.connect('rwh_database.db')
    conn.row_factory = sqlite3.Row
    return conn


def get_all_users():
    conn = get_db_connection()
    users = conn.execute('SELECT * FROM rwh_users ORDER BY created_at DESC').fetchall()
    conn.close()
    return users


def get_user_by_id(user_id):
    conn = get_db_connection()
    user = conn.execute('SELECT * FROM rwh_users WHERE id = ?', (user_id,)).fetchone()
    conn.close()
    return user


def add_user(data):
    conn = get_db_connection()
    cursor = conn.cursor()

    cursor.execute('''
        INSERT INTO rwh_users (name, phone, email, address, location, property_type, 
                              roof_area, system_type, tank_capacity, notes)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    ''', (
        data['name'], data['phone'], data['email'], data['address'],
        data['location'], data['property_type'], data['roof_area'],
        data['system_type'], data['tank_capacity'], data['notes']
    ))

    conn.commit()
    user_id = cursor.lastrowid
    conn.close()
    return user_id


def update_user(user_id, data):
    conn = get_db_connection()
    cursor = conn.cursor()

    cursor.execute('''
        UPDATE rwh_users 
        SET name=?, phone=?, email=?, address=?, location=?, property_type=?, 
            roof_area=?, system_type=?, tank_capacity=?, notes=?, 
            updated_at=CURRENT_TIMESTAMP
        WHERE id=?
    ''', (
        data['name'], data['phone'], data['email'], data['address'],
        data['location'], data['property_type'], data['roof_area'],
        data['system_type'], data['tank_capacity'], data['notes'], user_id
    ))

    conn.commit()
    conn.close()


def delete_user(user_id):
    conn = get_db_connection()
    conn.execute('DELETE FROM rwh_users WHERE id = ?', (user_id,))
    conn.commit()
    conn.close()


# Routes
@app.route('/')
def index():
    users = get_all_users()
    return render_template('index.html', users=users)


@app.route('/add_profile', methods=['GET', 'POST'])
def add_profile():
    if request.method == 'GET':
        # visiting /add_profile in the browser will redirect to the form on index
        return redirect(url_for('index'))

    # POST: collect form fields and save to DB
    data = {
        'name': request.form.get('name', '').strip(),
        'phone': request.form.get('phone', '').strip(),
        'email': request.form.get('email', '').strip(),
        'address': request.form.get('address', '').strip(),
        'location': request.form.get('location', '').strip(),
        'property_type': request.form.get('property_type', '').strip(),
        'roof_area': float(request.form.get('roof_area')) if request.form.get('roof_area') else None,
        'system_type': request.form.get('system_type', '').strip(),
        'tank_capacity': float(request.form.get('tank_capacity')) if request.form.get('tank_capacity') else None,
        'notes': request.form.get('notes', '').strip()
    }

    try:
        add_user(data)
        flash('Profile added', 'success')
    except Exception as e:
        flash(f'Error adding profile: {e}', 'error')

    return redirect(url_for('index'))


@app.route('/profile/<int:user_id>')
def view_profile(user_id):
    user = get_user_by_id(user_id)
    if not user:
        flash('Profile not found', 'warning')
        return redirect(url_for('index'))
    return render_template('view_profile.html', user=user)


@app.route('/edit/<int:user_id>', methods=['GET', 'POST'])
def edit_profile(user_id):
    user = get_user_by_id(user_id)
    if not user:
        flash('Profile not found!', 'error')
        return redirect(url_for('index'))

    if request.method == 'POST':
        data = {
            'name': request.form['name'],
            'phone': request.form['phone'],
            'email': request.form['email'],
            'address': request.form['address'],
            'location': request.form['location'],
            'property_type': request.form['property_type'],
            'roof_area': float(request.form['roof_area']) if request.form['roof_area'] else None,
            'system_type': request.form['system_type'],
            'tank_capacity': float(request.form['tank_capacity']) if request.form['tank_capacity'] else None,
            'notes': request.form['notes']
        }

        try:
            update_user(user_id, data)
            flash('Profile updated successfully!', 'success')
            return redirect(url_for('view_profile', user_id=user_id))
        except Exception as e:
            flash(f'Error updating profile: {str(e)}', 'error')

    return render_template('edit_profile.html', user=user)


@app.route('/delete/<int:user_id>', methods=['POST'])
def delete_profile(user_id):
    try:
        delete_user(user_id)
        flash('Profile deleted successfully!', 'success')
    except Exception as e:
        flash(f'Error deleting profile: {str(e)}', 'error')

    return redirect(url_for('index'))


@app.route('/api/users')
def api_users():
    users = get_all_users()
    return jsonify([dict(user) for user in users])


# add small compatibility routes so /add and /profiles links work
@app.route('/add')
def add_redirect():
    # If you want a dedicated add page, render it here.
    # For now redirect to index which contains the form.
    return redirect(url_for('index'))


@app.route('/profiles')
def profiles():
    users = get_all_users()
    return render_template('profiles.html', users=users)


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