import os
import sqlite3
from datetime import datetime
from flask import Flask, render_template, request, redirect, url_for, session, flash, jsonify
from werkzeug.security import generate_password_hash, check_password_hash
from werkzeug.utils import secure_filename

app = Flask(__name__)
app.secret_key = 'super-secret-key-change-in-production'
app.config['UPLOAD_FOLDER'] = os.path.join('static', 'uploads')
app.config['MAX_CONTENT_LENGTH'] = 16 * 1024 * 1024  # 16MB upload limit

# Ensure upload directory exists
os.makedirs(app.config['UPLOAD_FOLDER'], exist_ok=True)

DATABASE = 'data.db'

def get_db():
    conn = sqlite3.connect(DATABASE)
    conn.row_factory = sqlite3.Row
    return conn

def init_db():
    conn = get_db()
    cursor = conn.cursor()
    
    # Create tables
    cursor.execute('''
    CREATE TABLE IF NOT EXISTS admin (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        username TEXT UNIQUE NOT NULL,
        password TEXT NOT NULL
    )
    ''')
    
    cursor.execute('''
    CREATE TABLE IF NOT EXISTS settings (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        siteName TEXT DEFAULT 'Adilton da Lancha',
        whatsapp TEXT DEFAULT '5511999999999',
        instagram TEXT DEFAULT '@adiltondalancha',
        address TEXT DEFAULT 'Marina, Cais Principal',
        hours TEXT DEFAULT 'Segunda a Domingo, 08:00 - 18:00',
        seoTitle TEXT DEFAULT 'Aluguel de Lanchas | Adilton da Lancha',
        seoDescription TEXT DEFAULT 'Alugue lanchas para passeios inesquecíveis com conforto, segurança e atendimento pelo WhatsApp.',
        logoUrl TEXT
    )
    ''')
    
    cursor.execute('''
    CREATE TABLE IF NOT EXISTS boat (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        capacity INTEGER NOT NULL,
        description TEXT NOT NULL,
        includes TEXT NOT NULL,
        imageUrl TEXT,
        active INTEGER DEFAULT 1,
        createdAt TEXT DEFAULT CURRENT_TIMESTAMP
    )
    ''')
    
    cursor.execute('''
    CREATE TABLE IF NOT EXISTS package (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        price TEXT NOT NULL,
        duration TEXT NOT NULL,
        capacity TEXT,
        description TEXT NOT NULL,
        benefits TEXT NOT NULL,
        active INTEGER DEFAULT 1,
        createdAt TEXT DEFAULT CURRENT_TIMESTAMP
    )
    ''')
    
    cursor.execute('''
    CREATE TABLE IF NOT EXISTS promotion (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        description TEXT NOT NULL,
        oldPrice TEXT NOT NULL,
        newPrice TEXT NOT NULL,
        validUntil TEXT NOT NULL,
        imageUrl TEXT,
        active INTEGER DEFAULT 1,
        createdAt TEXT DEFAULT CURRENT_TIMESTAMP
    )
    ''')
    
    cursor.execute('''
    CREATE TABLE IF NOT EXISTS gallery (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        imageUrl TEXT NOT NULL,
        altText TEXT,
        createdAt TEXT DEFAULT CURRENT_TIMESTAMP
    )
    ''')
    
    # Seed default data if empty
    cursor.execute('SELECT COUNT(*) FROM admin')
    if cursor.fetchone()[0] == 0:
        cursor.execute('INSERT INTO admin (username, password) VALUES (?, ?)', ('admin', generate_password_hash('password123')))
        
    cursor.execute('SELECT COUNT(*) FROM settings')
    if cursor.fetchone()[0] == 0:
        cursor.execute('''
        INSERT INTO settings (siteName, whatsapp, instagram, address, hours, seoTitle, seoDescription)
        VALUES (?, ?, ?, ?, ?, ?, ?)
        ''', (
            'Adilton da Lancha',
            '5511999999999',
            '@adiltondalancha',
            'Marina principal, Cais 4',
            'Todos os dias, 08:00 às 18:00',
            'Aluguel de Lanchas | Adilton da Lancha',
            'Alugue lanchas para passeios inesquecíveis com conforto, segurança e atendimento pelo WhatsApp. Lanchas para até 8 e 10 pessoas.'
        ))
        
    cursor.execute('SELECT COUNT(*) FROM boat')
    if cursor.fetchone()[0] == 0:
        cursor.executemany('''
        INSERT INTO boat (name, capacity, description, includes, active)
        VALUES (?, ?, ?, ?, ?)
        ''', [
            ('Lancha Premium 8 Pessoas', 8, 'Lancha ideal para famílias e pequenos grupos. Muito conforto e estabilidade.', 'Marinheiro, Combustível, Churrasqueira, Som Bluetooth, Cooler', 1),
            ('Lancha Sunset 10 Pessoas', 10, 'Espaçosa e perfeita para festas com amigos. Aproveite o pôr do sol em grande estilo.', 'Marinheiro, Combustível, Churrasqueira, Som Bluetooth, Boias divertidas, Cooler', 1)
        ])
        
    cursor.execute('SELECT COUNT(*) FROM package')
    if cursor.fetchone()[0] == 0:
        cursor.executemany('''
        INSERT INTO package (name, price, duration, capacity, description, benefits, active)
        VALUES (?, ?, ?, ?, ?, ?, ?)
        ''', [
            ('Passeio Econômico', 'A partir de R$ 900', '4 horas', 'Até 8 pessoas', 'Um passeio rápido para conhecer os principais pontos da orla.', 'Roteiro pré-definido, 1 parada para banho', 1),
            ('Passeio Premium', 'A partir de R$ 1.500', '6 horas', 'Até 10 pessoas', 'Aproveite o dia inteiro no mar com roteiro livre e paradas exclusivas.', 'Roteiro livre, Múltiplas paradas, Churrasqueiro a bordo', 1),
            ('Passeio Sunset', 'A partir de R$ 1.200', 'Das 15h às 19h', 'Até 8 pessoas', 'Aprecie o maravilhoso pôr do sol com brinde especial.', 'Horário privilegiado, Fotos incríveis, Brinde surpresa', 1)
        ])
        
    cursor.execute('SELECT COUNT(*) FROM promotion')
    if cursor.fetchone()[0] == 0:
        cursor.execute('''
        INSERT INTO promotion (name, description, oldPrice, newPrice, validUntil, active)
        VALUES (?, ?, ?, ?, ?, ?)
        ''', ('Especial Mês das Noivas', 'Ganhe desconto especial para despedida de solteira!', 'R$ 1.800', 'R$ 1.400', '30/06/2026', 1))
        
    conn.commit()
    conn.close()

# Initialize DB on import/startup
init_db()

# Helper to handle image uploads
def save_file(file):
    if file and file.filename != '':
        filename = secure_filename(f"{int(datetime.now().timestamp())}_{file.filename}")
        file.save(os.path.join(app.config['UPLOAD_FOLDER'], filename))
        return f"/static/uploads/{filename}"
    return None

# Context processor for templates
@app.context_processor
def inject_settings():
    conn = get_db()
    cursor = conn.cursor()
    cursor.execute('SELECT * FROM settings ORDER BY id DESC LIMIT 1')
    settings = cursor.fetchone()
    conn.close()
    
    wpp_link = "#"
    if settings:
        whatsapp = settings['whatsapp'].replace(' ', '').replace('-', '').replace('(', '').replace(')', '')
        wpp_link = f"https://wa.me/{whatsapp}?text=Olá,%20gostaria%20de%20saber%20mais%20sobre%20os%20passeios%20de%20lancha!"
        
    return dict(settings=settings, wppLink=wpp_link)

# --- CLIENT ROUTES ---

@app.route('/')
def home():
    conn = get_db()
    cursor = conn.cursor()
    
    cursor.execute('SELECT * FROM settings ORDER BY id DESC LIMIT 1')
    settings = cursor.fetchone()
    
    cursor.execute('SELECT * FROM boat WHERE active = 1 ORDER BY createdAt DESC')
    boats = cursor.fetchall()
    
    cursor.execute('SELECT * FROM package WHERE active = 1 ORDER BY createdAt ASC')
    packages = cursor.fetchall()
    
    cursor.execute('SELECT * FROM promotion WHERE active = 1 ORDER BY createdAt DESC')
    promotions = cursor.fetchall()
    
    cursor.execute('SELECT * FROM gallery ORDER BY createdAt DESC')
    gallery = cursor.fetchall()
    
    conn.close()
    
    return render_template('home.html', 
                           boats=boats, 
                           packages=packages, 
                           promotions=promotions, 
                           gallery=gallery)

# --- ADMIN ROUTES ---

def login_required(f):
    import functools
    @functools.wraps(f)
    def decorated_function(*args, **kwargs):
        if 'logged_in' not in session:
            return redirect(url_for('admin_login'))
        return f(*args, **kwargs)
    return decorated_function

@app.route('/admin/login', methods=['GET', 'POST'])
def admin_login():
    if 'logged_in' in session:
        return redirect(url_for('admin_dashboard'))
        
    if request.method == 'POST':
        username = request.form['username']
        password = request.form['password']
        
        conn = get_db()
        cursor = conn.cursor()
        cursor.execute('SELECT * FROM admin WHERE username = ?', (username,))
        user = cursor.fetchone()
        conn.close()
        
        if user and check_password_hash(user['password'], password):
            session['logged_in'] = True
            session['username'] = username
            flash('Login realizado com sucesso!', 'success')
            return redirect(url_for('admin_dashboard'))
        else:
            flash('Usuário ou senha incorretos.', 'error')
            
    return render_template('admin_login.html')

@app.route('/admin/logout')
def admin_logout():
    session.clear()
    flash('Você saiu do painel administrativo.', 'info')
    return redirect(url_for('admin_login'))

@app.route('/admin')
@login_required
def admin_dashboard():
    conn = get_db()
    cursor = conn.cursor()
    
    cursor.execute('SELECT * FROM settings ORDER BY id DESC LIMIT 1')
    settings = cursor.fetchone()
    
    cursor.execute('SELECT * FROM boat ORDER BY createdAt DESC')
    boats = cursor.fetchall()
    
    cursor.execute('SELECT * FROM package ORDER BY createdAt ASC')
    packages = cursor.fetchall()
    
    cursor.execute('SELECT * FROM promotion ORDER BY createdAt DESC')
    promotions = cursor.fetchall()
    
    cursor.execute('SELECT * FROM gallery ORDER BY createdAt DESC')
    gallery = cursor.fetchall()
    
    conn.close()
    
    return render_template('admin_dashboard.html', 
                           settings=settings, 
                           boats=boats, 
                           packages=packages, 
                           promotions=promotions, 
                           gallery=gallery)

# CRUD Boats
@app.route('/admin/boat/add', methods=['POST'])
@login_required
def add_boat():
    name = request.form['name']
    capacity = int(request.form['capacity'] or 0)
    description = request.form['description']
    includes = request.form['includes']
    active = 1 if 'active' in request.form else 0
    
    imageUrl = save_file(request.files.get('image'))
    
    conn = get_db()
    cursor = conn.cursor()
    cursor.execute('''
        INSERT INTO boat (name, capacity, description, includes, imageUrl, active)
        VALUES (?, ?, ?, ?, ?, ?)
    ''', (name, capacity, description, includes, imageUrl, active))
    conn.commit()
    conn.close()
    
    flash('Lancha adicionada com sucesso!', 'success')
    return redirect(url_for('admin_dashboard') + '#boats')

@app.route('/admin/boat/delete/<int:id>', methods=['POST'])
@login_required
def delete_boat(id):
    conn = get_db()
    cursor = conn.cursor()
    cursor.execute('DELETE FROM boat WHERE id = ?', (id,))
    conn.commit()
    conn.close()
    flash('Lancha excluída com sucesso!', 'success')
    return redirect(url_for('admin_dashboard') + '#boats')

# CRUD Packages
@app.route('/admin/package/add', methods=['POST'])
@login_required
def add_package():
    name = request.form['name']
    price = request.form['price']
    duration = request.form['duration']
    capacity = request.form['capacity']
    description = request.form['description']
    benefits = request.form['benefits']
    active = 1 if 'active' in request.form else 0
    
    conn = get_db()
    cursor = conn.cursor()
    cursor.execute('''
        INSERT INTO package (name, price, duration, capacity, description, benefits, active)
        VALUES (?, ?, ?, ?, ?, ?, ?)
    ''', (name, price, duration, capacity, description, benefits, active))
    conn.commit()
    conn.close()
    
    flash('Pacote adicionado com sucesso!', 'success')
    return redirect(url_for('admin_dashboard') + '#packages')

@app.route('/admin/package/delete/<int:id>', methods=['POST'])
@login_required
def delete_package(id):
    conn = get_db()
    cursor = conn.cursor()
    cursor.execute('DELETE FROM package WHERE id = ?', (id,))
    conn.commit()
    conn.close()
    flash('Pacote excluído com sucesso!', 'success')
    return redirect(url_for('admin_dashboard') + '#packages')

# CRUD Promotions
@app.route('/admin/promotion/add', methods=['POST'])
@login_required
def add_promotion():
    name = request.form['name']
    description = request.form['description']
    oldPrice = request.form['oldPrice']
    newPrice = request.form['newPrice']
    validUntil = request.form['validUntil']
    active = 1 if 'active' in request.form else 0
    
    imageUrl = save_file(request.files.get('image'))
    
    conn = get_db()
    cursor = conn.cursor()
    cursor.execute('''
        INSERT INTO promotion (name, description, oldPrice, newPrice, validUntil, imageUrl, active)
        VALUES (?, ?, ?, ?, ?, ?, ?)
    ''', (name, description, oldPrice, newPrice, validUntil, imageUrl, active))
    conn.commit()
    conn.close()
    
    flash('Promoção adicionada com sucesso!', 'success')
    return redirect(url_for('admin_dashboard') + '#promotions')

@app.route('/admin/promotion/delete/<int:id>', methods=['POST'])
@login_required
def delete_promotion(id):
    conn = get_db()
    cursor = conn.cursor()
    cursor.execute('DELETE FROM promotion WHERE id = ?', (id,))
    conn.commit()
    conn.close()
    flash('Promoção excluída com sucesso!', 'success')
    return redirect(url_for('admin_dashboard') + '#promotions')

# CRUD Gallery
@app.route('/admin/gallery/add', methods=['POST'])
@login_required
def add_gallery():
    altText = request.form['altText']
    imageUrl = save_file(request.files.get('image'))
    
    if not imageUrl:
        flash('Nenhuma imagem selecionada.', 'error')
        return redirect(url_for('admin_dashboard') + '#gallery')
        
    conn = get_db()
    cursor = conn.cursor()
    cursor.execute('INSERT INTO gallery (imageUrl, altText) VALUES (?, ?)', (imageUrl, altText))
    conn.commit()
    conn.close()
    
    flash('Imagem adicionada à galeria!', 'success')
    return redirect(url_for('admin_dashboard') + '#gallery')

@app.route('/admin/gallery/delete/<int:id>', methods=['POST'])
@login_required
def delete_gallery(id):
    conn = get_db()
    cursor = conn.cursor()
    cursor.execute('DELETE FROM gallery WHERE id = ?', (id,))
    conn.commit()
    conn.close()
    flash('Imagem excluída da galeria!', 'success')
    return redirect(url_for('admin_dashboard') + '#gallery')

# Settings Update
@app.route('/admin/settings/update', methods=['POST'])
@login_required
def update_settings():
    siteName = request.form['siteName']
    whatsapp = request.form['whatsapp']
    instagram = request.form['instagram']
    address = request.form['address']
    hours = request.form['hours']
    seoTitle = request.form['seoTitle']
    seoDescription = request.form['seoDescription']
    
    # Handle Logo Upload if present
    logoUrl = None
    if 'logo' in request.files:
        logoUrl = save_file(request.files['logo'])
        
    conn = get_db()
    cursor = conn.cursor()
    
    if logoUrl:
        cursor.execute('''
            UPDATE settings 
            SET siteName=?, whatsapp=?, instagram=?, address=?, hours=?, seoTitle=?, seoDescription=?, logoUrl=?
            WHERE id = (SELECT id FROM settings ORDER BY id DESC LIMIT 1)
        ''', (siteName, whatsapp, instagram, address, hours, seoTitle, seoDescription, logoUrl))
    else:
        cursor.execute('''
            UPDATE settings 
            SET siteName=?, whatsapp=?, instagram=?, address=?, hours=?, seoTitle=?, seoDescription=?
            WHERE id = (SELECT id FROM settings ORDER BY id DESC LIMIT 1)
        ''', (siteName, whatsapp, instagram, address, hours, seoTitle, seoDescription))
        
    conn.commit()
    conn.close()
    
    flash('Configurações atualizadas com sucesso!', 'success')
    return redirect(url_for('admin_dashboard') + '#settings')

if __name__ == '__main__':
    app.run(debug=True, host='0.0.0.0', port=5000)