from flask import Flask, render_template, request, redirect, url_for, session, flash, jsonify, send_from_directory
import sqlite3
import os
from datetime import datetime, timedelta
import json
from functools import wraps
from werkzeug.utils import secure_filename

app = Flask(__name__)
app.secret_key = 'your-secret-key-here'

# Upload folder configuration
UPLOAD_FOLDER = 'uploads'
ALLOWED_EXTENSIONS = {'png', 'jpg', 'jpeg', 'gif', 'pdf', 'doc', 'docx', 'xls', 'xlsx', 'ppt', 'pptx', 'txt'}
app.config['UPLOAD_FOLDER'] = UPLOAD_FOLDER

if not os.path.exists(UPLOAD_FOLDER):
    os.makedirs(UPLOAD_FOLDER)

def allowed_file(filename):
    return '.' in filename and \
           filename.rsplit('.', 1)[1].lower() in ALLOWED_EXTENSIONS

# Database initialization
def init_db():
    conn = sqlite3.connect('projects.db')
    cursor = conn.cursor()
    
    # Create users table
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS users (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            username TEXT UNIQUE NOT NULL,
            full_name TEXT NOT NULL,
            role TEXT NOT NULL DEFAULT 'team',
            whatsapp_number TEXT,
            is_active INTEGER DEFAULT 1,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
        )
    ''')
    
    # Create projects table
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS projects (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            orderNumber TEXT,
            shopeeOrderNumber TEXT,
            fileName TEXT,
            originalFileLink TEXT,
            adminName TEXT,
            teamMember TEXT,
            deadline DATETIME,
            status TEXT DEFAULT 'Belum Dikerjakan',
            notes TEXT,
            fileHasilKerja TEXT,
            jumlahHalaman INTEGER,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
        )
    ''')
    
    # Check if columns exist, if not add them
    cursor.execute("PRAGMA table_info(users)")
    user_columns = [column[1] for column in cursor.fetchall()]
    if 'whatsapp_number' not in user_columns:
        cursor.execute('ALTER TABLE users ADD COLUMN whatsapp_number TEXT')

    cursor.execute("PRAGMA table_info(projects)")
    project_columns = [column[1] for column in cursor.fetchall()]
    if 'jumlahHalaman' not in project_columns:
        cursor.execute('ALTER TABLE projects ADD COLUMN jumlahHalaman INTEGER')
    
    # Insert default admin user if not exists
    cursor.execute('SELECT COUNT(*) FROM users WHERE role = "admin"')
    admin_count = cursor.fetchone()[0]
    
    if admin_count == 0:
        cursor.execute('''
            INSERT INTO users (username, full_name, role, whatsapp_number, is_active)
            VALUES (?, ?, ?, ?, ?)
        ''', ('admin', 'Administrator', 'admin', '628123456789', 1))
        
        # Insert some default team users
        default_users = [
            ('budi', 'Budi Santoso', 'team', '628123456790'),
            ('sari', 'Sari Wulandari', 'team', '628123456791'),
            ('andi', 'Andi Rahman', 'team', '628123456792')
        ]
        
        for username, full_name, role, whatsapp in default_users:
            cursor.execute('''
                INSERT INTO users (username, full_name, role, whatsapp_number, is_active)
                VALUES (?, ?, ?, ?, ?)
            ''', (username, full_name, role, whatsapp, 1))
    
    conn.commit()
    conn.close()

# Helper functions
def get_db_connection():
    conn = sqlite3.connect('projects.db')
    conn.row_factory = sqlite3.Row
    return conn

def get_deadline_class(deadline_str):
    if not deadline_str:
        return 'text-gray-500'
    
    try:
        deadline = datetime.strptime(deadline_str, '%Y-%m-%dT%H:%M')
        now = datetime.now()
        diff = deadline - now
        
        if diff.total_seconds() < 0:  # Past due
            return 'text-red-600 line-through'
        elif diff.total_seconds() < 86400:  # < 24 hours
            return 'text-red-500 font-bold'
        elif diff.total_seconds() < 259200:  # < 3 days
            return 'text-yellow-500 font-semibold'
        else:
            return 'text-green-500'
    except:
        return 'text-gray-500'

# Role decorators
def login_required(f):
    @wraps(f)
    def decorated_function(*args, **kwargs):
        if 'user_id' not in session:
            flash('Silakan login terlebih dahulu!', 'error')
            return redirect(url_for('index'))
        return f(*args, **kwargs)
    return decorated_function

def admin_required(f):
    @wraps(f)
    def decorated_function(*args, **kwargs):
        if 'user_id' not in session or session.get('user_role') != 'admin':
            flash('Akses ditolak! Hanya admin yang dapat mengakses halaman ini.', 'error')
            return redirect(url_for('index'))
        return f(*args, **kwargs)
    return decorated_function

def team_required(f):
    @wraps(f)
    def decorated_function(*args, **kwargs):
        if 'user_id' not in session or session.get('user_role') != 'team':
            flash('Akses ditolak! Halaman ini khusus untuk anggota tim.', 'error')
            return redirect(url_for('index'))
        return f(*args, **kwargs)
    return decorated_function

@app.route('/')
def index():
    if 'user_id' in session:
        if session.get('user_role') == 'admin':
            return redirect(url_for('admin_dashboard'))
        else:
            return redirect(url_for('team_dashboard'))
    return render_template('login.html')

@app.route('/login', methods=['POST'])
def login():
    username = request.form.get('username', '').strip().lower()
    
    if not username:
        flash('Username harus diisi!', 'error')
        return redirect(url_for('index'))
    
    conn = get_db_connection()
    user = conn.execute(
        'SELECT * FROM users WHERE username = ? AND is_active = 1', 
        (username,)
    ).fetchone()
    conn.close()
    
    if not user:
        flash('User tidak ditemukan atau tidak aktif!', 'error')
        return redirect(url_for('index'))
    
    session['user_id'] = user['id']
    session['username'] = user['username']
    session['full_name'] = user['full_name']
    session['user_role'] = user['role']
    
    flash(f'Selamat datang, {user["full_name"]}!', 'success')
    
    if user['role'] == 'admin':
        return redirect(url_for('admin_dashboard'))
    else:
        return redirect(url_for('team_dashboard'))

@app.route('/logout')
@login_required
def logout():
    username = session.get('full_name', 'User')
    session.clear()
    flash(f'Sampai jumpa, {username}!', 'success')
    return redirect(url_for('index'))

@app.route('/admin')
@admin_required
def admin_dashboard():
    conn = get_db_connection()
    
    # Get filter parameters
    status_filter = request.args.get('status', '')
    admin_filter = request.args.get('admin', '')
    team_filter = request.args.get('team', '')
    date_filter = request.args.get('date', '')
    
    # Build query with filters
    query = "SELECT * FROM projects WHERE 1=1"
    params = []
    
    if status_filter:
        query += " AND status = ?"
        params.append(status_filter)
    
    if admin_filter:
        query += " AND adminName = ?"
        params.append(admin_filter)
    
    if team_filter:
        query += " AND teamMember LIKE ?"
        params.append(f'%{team_filter}%')
    
    if date_filter:
        query += " AND DATE(deadline) = ?"
        params.append(date_filter)
    
    query += " ORDER BY orderNumber"
    
    projects = conn.execute(query, params).fetchall()
    
    # Get unique values for filter dropdowns
    all_projects = conn.execute("SELECT DISTINCT adminName, teamMember FROM projects").fetchall()
    admin_names = list(set([p['adminName'] for p in all_projects if p['adminName']]))
    team_members = list(set([p['teamMember'] for p in all_projects if p['teamMember']]))
    
    # Get active team users for dropdown
    team_users = conn.execute(
        "SELECT username, full_name FROM users WHERE role = 'team' AND is_active = 1 ORDER BY full_name"
    ).fetchall()
    
    conn.close()
    
    return render_template('admin_dashboard.html', 
                         projects=projects, 
                         admin_names=admin_names,
                         team_members=team_members,
                         team_users=team_users,
                         get_deadline_class=get_deadline_class,
                         current_filters={
                             'status': status_filter,
                             'admin': admin_filter,
                             'team': team_filter,
                             'date': date_filter
                         })

@app.route('/admin/users')
@admin_required
def user_management():
    conn = get_db_connection()
    users = conn.execute(
        "SELECT * FROM users ORDER BY role DESC, full_name ASC"
    ).fetchall()
    conn.close()
    
    return render_template('user_management.html', users=users)

@app.route('/team')
@team_required
def team_dashboard():
    username = session['username']
    conn = get_db_connection()
    
    # Get filter parameters
    date_filter = request.args.get('date', '')
    
    # Build query with filters
    query = "SELECT * FROM projects WHERE teamMember = ?"
    params = [username]
    
    if date_filter:
        query += " AND DATE(deadline) = ?"
        params.append(date_filter)
    
    query += " ORDER BY deadline"
    
    projects = conn.execute(query, params).fetchall()
    conn.close()
    
    return render_template('team_dashboard.html', 
                         projects=projects,
                         get_deadline_class=get_deadline_class,
                         current_date_filter=date_filter)

# User Management Routes
@app.route('/add_user', methods=['POST'])
@admin_required
def add_user():
    try:
        username = request.form['username'].strip().lower()
        full_name = request.form['full_name'].strip()
        role = request.form['role']
        whatsapp_number = request.form.get('whatsapp_number', '').strip()
        
        if not username or not full_name:
            flash('Username dan nama lengkap harus diisi!', 'error')
            return redirect(url_for('user_management'))
        
        conn = get_db_connection()
        
        # Check if username already exists
        existing = conn.execute(
            'SELECT id FROM users WHERE username = ?', (username,)
        ).fetchone()
        
        if existing:
            flash('Username sudah digunakan!', 'error')
            conn.close()
            return redirect(url_for('user_management'))
        
        conn.execute('''
            INSERT INTO users (username, full_name, role, whatsapp_number, is_active)
            VALUES (?, ?, ?, ?, ?)
        ''', (username, full_name, role, whatsapp_number, 1))
        
        conn.commit()
        conn.close()
        flash(f'User {full_name} berhasil ditambahkan!', 'success')
    except Exception as e:
        flash(f'Error: {str(e)}', 'error')
    
    return redirect(url_for('user_management'))

@app.route('/edit_user/<int:user_id>', methods=['POST'])
@admin_required
def edit_user(user_id):
    try:
        username = request.form['username'].strip().lower()
        full_name = request.form['full_name'].strip()
        role = request.form['role']
        whatsapp_number = request.form.get('whatsapp_number', '').strip()
        is_active = 1 if request.form.get('is_active') == 'on' else 0
        
        if not username or not full_name:
            flash('Username dan nama lengkap harus diisi!', 'error')
            return redirect(url_for('user_management'))
        
        conn = get_db_connection()
        
        # Check if username already exists for other users
        existing = conn.execute(
            'SELECT id FROM users WHERE username = ? AND id != ?', 
            (username, user_id)
        ).fetchone()
        
        if existing:
            flash('Username sudah digunakan oleh user lain!', 'error')
            conn.close()
            return redirect(url_for('user_management'))
        
        conn.execute('''
            UPDATE users SET 
                username = ?, full_name = ?, role = ?, whatsapp_number = ?, is_active = ?,
                updated_at = CURRENT_TIMESTAMP
            WHERE id = ?
        ''', (username, full_name, role, whatsapp_number, is_active, user_id))
        
        conn.commit()
        conn.close()
        flash(f'User {full_name} berhasil diupdate!', 'success')
    except Exception as e:
        flash(f'Error: {str(e)}', 'error')
    
    return redirect(url_for('user_management'))

@app.route('/delete_user/<int:user_id>')
@admin_required
def delete_user(user_id):
    try:
        conn = get_db_connection()
        
        # Check if user exists and not current admin
        user = conn.execute('SELECT * FROM users WHERE id = ?', (user_id,)).fetchone()
        
        if not user:
            flash('User tidak ditemukan!', 'error')
        elif user['id'] == session['user_id']:
            flash('Tidak dapat menghapus user yang sedang login!', 'error')
        else:
            conn.execute('DELETE FROM users WHERE id = ?', (user_id,))
            conn.commit()
            flash(f'User {user["full_name"]} berhasil dihapus!', 'success')
        
        conn.close()
    except Exception as e:
        flash(f'Error: {str(e)}', 'error')
    
    return redirect(url_for('user_management'))

# Project Management Routes
@app.route('/add_project', methods=['POST'])
@admin_required
def add_project():
    try:
        # Handle file upload for originalFile
        original_file_path = ''
        if 'originalFile' in request.files:
            file = request.files['originalFile']
            if file and file.filename != '' and allowed_file(file.filename):
                filename = secure_filename(file.filename)
                unique_filename = f"{datetime.now().strftime('%Y%m%d%H%M%S')}_{filename}"
                file.save(os.path.join(app.config['UPLOAD_FOLDER'], unique_filename))
                original_file_path = unique_filename

        conn = get_db_connection()
        conn.execute('''
            INSERT INTO projects (orderNumber, shopeeOrderNumber, fileName, originalFileLink, 
                                adminName, teamMember, deadline, status, notes, jumlahHalaman)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        ''', (
            request.form['orderNumber'],
            request.form['shopeeOrderNumber'],
            request.form['fileName'],
            original_file_path,
            request.form['adminName'],
            request.form['teamMember'],
            request.form['deadline'],
            request.form['status'],
            request.form['notes'],
            request.form.get('jumlahHalaman', type=int)
        ))
        conn.commit()
        conn.close()
        flash('Proyek berhasil ditambahkan!', 'success')
    except Exception as e:
        flash(f'Error: {str(e)}', 'error')
    
    return redirect(url_for('admin_dashboard'))

@app.route('/edit_project/<int:project_id>', methods=['POST'])
@admin_required
def edit_project(project_id):
    try:
        conn = get_db_connection()
        project = conn.execute('SELECT originalFileLink FROM projects WHERE id = ?', (project_id,)).fetchone()
        
        original_file_path = project['originalFileLink'] if project else ''
        # Handle new file upload
        if 'originalFile' in request.files:
            file = request.files['originalFile']
            if file and file.filename != '' and allowed_file(file.filename):
                # Optionally, delete old file
                if original_file_path and os.path.exists(os.path.join(app.config['UPLOAD_FOLDER'], original_file_path)):
                    try:
                        os.remove(os.path.join(app.config['UPLOAD_FOLDER'], original_file_path))
                    except OSError as e:
                        print(f"Error deleting file {original_file_path}: {e}")

                filename = secure_filename(file.filename)
                unique_filename = f"{datetime.now().strftime('%Y%m%d%H%M%S')}_{filename}"
                file.save(os.path.join(app.config['UPLOAD_FOLDER'], unique_filename))
                original_file_path = unique_filename

        conn.execute('''
            UPDATE projects SET 
                orderNumber = ?, shopeeOrderNumber = ?, fileName = ?, 
                originalFileLink = ?, adminName = ?, teamMember = ?, 
                deadline = ?, status = ?, notes = ?, jumlahHalaman = ?, 
                updated_at = CURRENT_TIMESTAMP
            WHERE id = ?
        ''', (
            request.form['orderNumber'],
            request.form['shopeeOrderNumber'],
            request.form['fileName'],
            original_file_path,
            request.form['adminName'],
            request.form['teamMember'],
            request.form['deadline'],
            request.form['status'],
            request.form['notes'],
            request.form.get('jumlahHalaman', type=int),
            project_id
        ))
        conn.commit()
        conn.close()
        flash('Proyek berhasil diupdate!', 'success')
    except Exception as e:
        flash(f'Error: {str(e)}', 'error')
    
    return redirect(url_for('admin_dashboard'))

@app.route('/delete_project/<int:project_id>')
@admin_required
def delete_project(project_id):
    try:
        conn = get_db_connection()
        # Also delete associated files
        project = conn.execute('SELECT originalFileLink, fileHasilKerja FROM projects WHERE id = ?', (project_id,)).fetchone()
        if project:
            for file_path in [project['originalFileLink'], project['fileHasilKerja']]:
                if file_path and os.path.exists(os.path.join(app.config['UPLOAD_FOLDER'], file_path)):
                    try:
                        os.remove(os.path.join(app.config['UPLOAD_FOLDER'], file_path))
                    except OSError as e:
                        print(f"Error deleting file {file_path}: {e}")
        
        conn.execute('DELETE FROM projects WHERE id = ?', (project_id,))
        conn.commit()
        conn.close()
        flash('Proyek berhasil dihapus!', 'success')
    except Exception as e:
        flash(f'Error: {str(e)}', 'error')
    
    return redirect(url_for('admin_dashboard'))

@app.route('/update_status', methods=['POST'])
@login_required
def update_status():
    try:
        project_id = request.json['project_id']
        new_status = request.json['status']
        
        conn = get_db_connection()
        
        # Check if user has permission to update this project
        if session['user_role'] == 'team':
            project = conn.execute(
                'SELECT teamMember FROM projects WHERE id = ?', 
                (project_id,)
            ).fetchone()
            
            if not project or project['teamMember'] != session['username']:
                conn.close()
                return jsonify({'success': False, 'message': 'Tidak ada izin untuk mengupdate proyek ini'})
        
        conn.execute('''
            UPDATE projects SET status = ?, updated_at = CURRENT_TIMESTAMP 
            WHERE id = ?
        ''', (new_status, project_id))
        conn.commit()
        conn.close()
        
        return jsonify({'success': True, 'message': 'Status berhasil diupdate!'})
    except Exception as e:
        return jsonify({'success': False, 'message': str(e)})

@app.route('/submit_work', methods=['POST'])
@team_required
def submit_work():
    try:
        project_id = request.form['project_id']

        if 'fileHasilKerja' not in request.files:
            flash('File hasil kerja tidak ditemukan!', 'error')
            return redirect(url_for('team_dashboard'))
        
        file = request.files['fileHasilKerja']
        
        if file.filename == '':
            flash('Tidak ada file yang dipilih!', 'error')
            return redirect(url_for('team_dashboard'))

        if file and allowed_file(file.filename):
            conn = get_db_connection()
            project = conn.execute(
                'SELECT teamMember, fileHasilKerja FROM projects WHERE id = ?', (project_id,)
            ).fetchone()
            
            if not project or project['teamMember'] != session['username']:
                flash('Tidak ada izin untuk mengupdate proyek ini!', 'error')
                conn.close()
                return redirect(url_for('team_dashboard'))

            # Optionally delete old submitted file
            old_file = project['fileHasilKerja']
            if old_file and os.path.exists(os.path.join(app.config['UPLOAD_FOLDER'], old_file)):
                 try:
                    os.remove(os.path.join(app.config['UPLOAD_FOLDER'], old_file))
                 except OSError as e:
                    print(f"Error deleting file {old_file}: {e}")

            filename = secure_filename(file.filename)
            unique_filename = f"{datetime.now().strftime('%Y%m%d%H%M%S')}_{filename}"
            file.save(os.path.join(app.config['UPLOAD_FOLDER'], unique_filename))
            file_hasil_path = unique_filename

            conn.execute('''
                UPDATE projects SET 
                    fileHasilKerja = ?, 
                    status = 'Selesai', 
                    updated_at = CURRENT_TIMESTAMP 
                WHERE id = ?
            ''', (file_hasil_path, project_id))
            conn.commit()
            conn.close()
            
            flash('Hasil kerja berhasil diserahkan!', 'success')
        else:
            flash('Tipe file tidak diizinkan!', 'error')

    except Exception as e:
        flash(f'Error: {str(e)}', 'error')
    
    return redirect(url_for('team_dashboard'))

# New route to serve uploaded files
@app.route('/uploads/<path:filename>')
@login_required
def serve_upload(filename):
    return send_from_directory(app.config['UPLOAD_FOLDER'], filename)

@app.route('/get_project/<int:project_id>')
@admin_required
def get_project(project_id):
    conn = get_db_connection()
    project = conn.execute('SELECT * FROM projects WHERE id = ?', (project_id,)).fetchone()
    conn.close()
    
    if project:
        return jsonify(dict(project))
    else:
        return jsonify({'error': 'Project not found'})

@app.route('/get_user/<int:user_id>')
@admin_required
def get_user(user_id):
    conn = get_db_connection()
    user = conn.execute('SELECT * FROM users WHERE id = ?', (user_id,)).fetchone()
    conn.close()
    
    if user:
        return jsonify(dict(user))
    else:
        return jsonify({'error': 'User not found'})

@app.route('/get_whatsapp_info/<project_id>')
@login_required
def get_whatsapp_info(project_id):
    conn = get_db_connection()
    
    # Get project info
    project = conn.execute('SELECT * FROM projects WHERE id = ?', (project_id,)).fetchone()
    if not project:
        conn.close()
        return jsonify({'error': 'Project not found'})
    
    # Get admin WhatsApp (for team notifications)
    admin_wa = conn.execute(
        'SELECT whatsapp_number, full_name FROM users WHERE role = "admin" AND is_active = 1 LIMIT 1'
    ).fetchone()
    
    # Get team member WhatsApp (for admin notifications)
    team_wa = conn.execute(
        'SELECT whatsapp_number, full_name FROM users WHERE username = ? AND is_active = 1', 
        (project['teamMember'],)
    ).fetchone()
    
    conn.close()
    
    result = {
        'project': dict(project),
        'admin_whatsapp': dict(admin_wa) if admin_wa else None,
        'team_whatsapp': dict(team_wa) if team_wa else None
    }
    
    return jsonify(result)

# Error Handlers
@app.errorhandler(404)
def not_found_error(error):
    return render_template('error.html', 
                         error_code=404, 
                         error_message='Halaman tidak ditemukan'), 404

@app.errorhandler(500)
def internal_error(error):
    return render_template('error.html', 
                         error_code=500, 
                         error_message='Terjadi kesalahan internal server'), 500

@app.errorhandler(403)
def forbidden_error(error):
    return render_template('error.html', 
                         error_code=403, 
                         error_message='Akses ditolak'), 403

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