from flask import request, jsonify, send_from_directory, flash, redirect, url_for, session
from api import api_bp
from utils.common import get_db_connection, clean_dataframe_data
import pandas as pd
from werkzeug.utils import secure_filename
 
@api_bp.route('/files', methods=['GET'])
def get_files():
    conn = get_db_connection()
    cur = conn.cursor()

    try:
        cur.execute('SELECT * FROM uploaded_files WHERE user_id = %s', (session['user_id'],))
        files = cur.fetchall()
        return jsonify(files), 200
    except Exception as e:
        return jsonify({"error": f"파일 목록을 가져오는 중 오류가 발생했습니다: {str(e)}"}), 500
    finally:
        cur.close()
        conn.close()

@api_bp.route('/download/<filename>', methods=['GET'])
def download_file(filename):
    try:
        return send_from_directory('uploads', filename, as_attachment=True)
    except Exception as e:
        return jsonify({"error": f"파일 다운로드 중 오류가 발생했습니다: {str(e)}"}), 500
    
    

@api_bp.route('/update_file', methods=['POST'])
def update_file():
    file_id = request.form.get('file_id')
    bridge_name = request.form.get('bridge_name')
    structure_type = request.form.get('structure_type', '')
    span_count = request.form.get('span_count', 0)
    length = request.form.get('length', 0)
    width = request.form.get('width', 0)
    expansion_joint_location = request.form.get('expansion_joint_location', '')
    
    # 수치 값 변환 및 검증
    try:
        span_count = int(span_count) if span_count else 0
        length = float(length) if length else 0
        width = float(width) if width else 0
    except (ValueError, TypeError):
        flash('입력된 수치 값에 오류가 있습니다.', 'error')
        return redirect(url_for('index'))
        
    # 찾아보자
    if 'file' in request.files:
        file = request.files['file']
        if file.filename != '':
            try:
                # 파일 읽기
                df = pd.read_excel(file)
                
                # DataFrame 데이터 정리 및 trim 처리
                df = clean_dataframe_data(df)
                
                # 데이터베이스에 저장
                conn = get_db_connection()
                cur = conn.cursor()
                
                # 파일 데이터를 JSON으로 변환
                file_data = df.to_json(orient='records', force_ascii=False)
                
                # file_id가 있으면 업데이트, 없으면 새로 생성
                if file_id and file_id.strip():
                    # 기존 파일 업데이트
                    cur.execute('''
                        UPDATE uploaded_files
                        SET bridge_name = %s, file_data = %s, original_filename = %s,
                            structure_type = %s, span_count = %s, length = %s, width = %s,
                            expansion_joint_location = %s
                        WHERE id = %s
                    ''', (bridge_name, file_data, file.filename, structure_type, span_count, 
                          length, width, expansion_joint_location, file_id))
                else:
                    # 새 파일 생성
                    cur.execute('''
                        INSERT INTO uploaded_files 
                        (user_id, filename, original_filename, file_data, bridge_name,
                         structure_type, span_count, length, width, expansion_joint_location) 
                        VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
                        ON CONFLICT (user_id, filename) 
                        DO UPDATE SET file_data = EXCLUDED.file_data, bridge_name = EXCLUDED.bridge_name,
                            structure_type = EXCLUDED.structure_type, span_count = EXCLUDED.span_count,
                            length = EXCLUDED.length, width = EXCLUDED.width,
                            expansion_joint_location = EXCLUDED.expansion_joint_location
                    ''', (
                        session['user_id'],
                        secure_filename(file.filename),
                        file.filename,
                        file_data,
                        bridge_name,
                        structure_type,
                        span_count,
                        length,
                        width,
                        expansion_joint_location
                    ))
                
                conn.commit()
                cur.close()
                conn.close()
                
                flash('파일이 성공적으로 업로드되었습니다.', 'success')
                return redirect(url_for('index'))
                
            except Exception as e:
                flash(f'파일 처리 중 오류가 발생했습니다: {str(e)}', 'error')
                return redirect(url_for('index'))
    else:
        # 파일 없이 교량 정보만 업데이트하는 경우
        if file_id and file_id.strip():
            conn = get_db_connection()
            cur = conn.cursor()
            
            # 교량 정보 업데이트
            cur.execute('''
                UPDATE uploaded_files
                SET bridge_name = %s, structure_type = %s, span_count = %s, 
                    length = %s, width = %s, expansion_joint_location = %s
                WHERE id = %s
            ''', (bridge_name, structure_type, span_count, length, width, 
                  expansion_joint_location, file_id))
            
            conn.commit()
            cur.close()
            conn.close()
            
            flash('교량 정보가 성공적으로 업데이트되었습니다.', 'success')
        else:
            flash('필요한 정보를 입력해주세요.', 'error')
            
    return redirect(url_for('index'))

@api_bp.route('/update_bridge_info', methods=['POST'])
def update_bridge_info():
    file_id = request.form.get('file_id')
    bridge_name = request.form.get('bridge_name')
    structure_type = request.form.get('structure_type', '')
    span_count = request.form.get('span_count', 0)
    length = request.form.get('length', 0)
    width = request.form.get('width', 0)
    expansion_joint_location = request.form.get('expansion_joint_location', '')

    # 수치 값 변환 및 검증
    try:
        span_count = int(span_count) if span_count else 0
        length = float(length) if length else 0
        width = float(width) if width else 0
    except (ValueError, TypeError):
        return jsonify({'success': False, 'error': '입력된 수치 값에 오류가 있습니다.'}), 400

    if file_id and file_id.strip():
        conn = get_db_connection()
        cur = conn.cursor()
        cur.execute('''
            UPDATE uploaded_files
            SET bridge_name = %s, structure_type = %s, span_count = %s, 
                length = %s, width = %s, expansion_joint_location = %s
            WHERE filename = %s
        ''', (bridge_name, structure_type, span_count, length, width, 
              expansion_joint_location, file_id))
        conn.commit()
        cur.close()
        conn.close()
        return jsonify({'success': True, 'message': '교량 정보가 성공적으로 업데이트되었습니다.'}), 200
    else:
        return jsonify({'success': False, 'error': '필요한 정보를 입력해주세요.'}), 400
