import pandas as pd
from flask import Flask, request, render_template, send_file, jsonify, send_from_directory, redirect, url_for, session, flash
import os
import re
import random
from difflib import get_close_matches
from docx import Document
from docx.shared import Inches, Pt
from docx.enum.text import WD_ALIGN_PARAGRAPH
from docx.enum.table import WD_ALIGN_VERTICAL
import io
import numpy as np
from collections import defaultdict
import json
import math
from static.data.damage_solutions import damage_solutions
from utils.common import COMPONENT_ORDER, normalize_component, remove_special_characters, sort_components, get_db_connection, clean_dataframe_data
from utils.evaluation import *
from utils.pivot_detail_view_park import *
from datetime import datetime
import psycopg2
from werkzeug.security import generate_password_hash, check_password_hash
from werkzeug.utils import secure_filename

app = Flask(__name__)
app.secret_key = 'your_secret_key_here'  # Change this to a secure secret key

# Jinja2 필터 함수들
@app.template_filter('evaluate_crack')
def evaluate_crack(value):
    if value == '-' or pd.isna(value):
        return 'a'
    value = float(value)
    if value >= 1.0:
        return 'e'
    elif value >= 0.5:
        return 'd'
    elif value >= 0.3:
        return 'c'
    elif value >= 0.1:
        return 'b'
    return 'a'

@app.template_filter('evaluate_crack_ratio')
def evaluate_crack_ratio(value):
    if value == '-' or pd.isna(value):
        return 'a'
    value = float(value)
    if value >= 20:
        return 'e'
    elif value >= 15:
        return 'd'
    elif value >= 10:
        return 'c'
    elif value >= 5:
        return 'b'
    return 'a'

@app.template_filter('evaluate_leak')
def evaluate_leak(value):
    if value == '-' or pd.isna(value):
        return 'a'
    value = float(value)
    if value >= 20:
        return 'd'
    elif value >= 10:
        return 'c'
    elif value > 0:
        return 'b'
    return 'a'

@app.template_filter('evaluate_surface')
def evaluate_surface(value):
    if value == '-' or pd.isna(value):
        return 'a'
    value = float(value)
    if value >= 20:
        return 'e'
    elif value >= 10:
        return 'd'
    elif value >= 5:
        return 'c'
    elif value > 0:
        return 'b'
    return 'a'

@app.template_filter('evaluate_rebar')
def evaluate_rebar(value):
    if value == '-' or pd.isna(value):
        return 'a'
    value = float(value)
    if value >= 3:
        return 'e'
    elif value >= 2:
        return 'd'
    elif value >= 1:
        return 'c'
    elif value > 0:
        return 'b'
    return 'a'

# Database configuration
 
def init_db():
    conn = get_db_connection()
    cur = conn.cursor()
    
    # Create users table if it doesn't exist
    cur.execute('''
        CREATE TABLE IF NOT EXISTS users (
            id SERIAL PRIMARY KEY,
            username VARCHAR(50) UNIQUE NOT NULL,
            password VARCHAR(255) NOT NULL,
            email VARCHAR(100) UNIQUE NOT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    ''')
    
    # Create uploaded_files table
    cur.execute('''
        CREATE TABLE IF NOT EXISTS uploaded_files (
            id SERIAL PRIMARY KEY,
            user_id INTEGER REFERENCES users(id),
            filename VARCHAR(255) NOT NULL,
            original_filename VARCHAR(255) NOT NULL,
            upload_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            file_data JSONB NOT NULL,
            bridge_name VARCHAR(255), -- 교량명
            length NUMERIC,          -- 연장
            width NUMERIC,           -- 폭
            structure_type VARCHAR(255), -- 구조형식
            span_count INTEGER,      -- 경간수
            expansion_joint_location TEXT, -- 신축이음위치
            UNIQUE(user_id, filename)
        )
    ''')
    cur.execute('''
    CREATE TABLE IF NOT EXISTS file_damage_details (
        id SERIAL PRIMARY KEY,
        user_id INTEGER REFERENCES users(id),
        username TEXT NOT NULL,
        filename VARCHAR(255) NOT NULL,
        component_name TEXT NOT NULL,       -- 부재명
        damage_description TEXT NOT NULL,   -- 손상내용
        unit TEXT,
        damage_quantity NUMERIC,
        repair_quantity NUMERIC,
        count INTEGER,
        repair_method TEXT,
        priority TEXT,
        unit_price NUMERIC,
        estimated_cost NUMERIC,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        UNIQUE(user_id, filename, component_name, damage_description)
    );
''')
    # Insert sample user if not exists
    cur.execute("SELECT COUNT(*) FROM users WHERE username = 'admin'")
    if cur.fetchone()[0] == 0:
        hashed_password = generate_password_hash('admin123')
        cur.execute(
            "INSERT INTO users (username, password, email) VALUES (%s, %s, %s)",
            ('admin', hashed_password, 'admin@example.com')
        )
    
    conn.commit()
    cur.close()
    conn.close()
 
 
 
def check_and_add_columns():
    conn = get_db_connection()
    cur = conn.cursor()
    
    # 테이블 컬럼 확인 쿼리
    cur.execute("""
        SELECT column_name 
        FROM information_schema.columns 
        WHERE table_name = 'uploaded_files'
    """)
    existing_columns = [row[0] for row in cur.fetchall()]
    
    # 추가하려는 컬럼 목록
    columns_to_add = {
        "bridge_name": "VARCHAR(255)",  # 교량명
        "length": "NUMERIC",           # 연장
        "width": "NUMERIC",            # 폭
        "structure_type": "VARCHAR(255)",  # 구조형식
        "span_count": "INTEGER",       # 경간수
        "expansion_joint_location": "TEXT"  # 신축이음위치
    }
    
    # 필요한 컬럼이 없으면 추가
    for column, column_type in columns_to_add.items():
        if column not in existing_columns:
            cur.execute(f"ALTER TABLE uploaded_files ADD COLUMN {column} {column_type}")
            print(f"컬럼 '{column}'이 추가되었습니다.")
        else:
            print(f"컬럼 '{column}'은 이미 존재합니다. 추가를 건너뜁니다.")
    
    conn.commit()
    cur.close()
    conn.close()
# 자연 정렬 함수
def natural_sort_key(s):
    return [int(text) if text.isdigit() else text.lower()
            for text in re.split('([0-9]+)', s)]
    
# 손상내용 정규화 함수
 
# 파일 업로드 설정
UPLOAD_FOLDER = 'uploads'
if not os.path.exists(UPLOAD_FOLDER):
    os.makedirs(UPLOAD_FOLDER)

app.config['UPLOAD_FOLDER'] = UPLOAD_FOLDER

class BridgeEvaluation:
    def __init__(self):
        self.bridge_data = {}
        self.evaluation_results = {}

    def calculate_areas(self, length, width, span_count):
        # 면적 계산 로직 구현
        girder_area = length * width * 0.3
        crossbeam_area = width * span_count * 0.2
        abutment_area = width * 2 * 5  # 가정: 교대 높이 5m
        pier_area = width * (span_count - 1) * 4  # 가정: 교각 높이 4m
        
        return {
            'girder_area': girder_area,
            'crossbeam_area': crossbeam_area,
            'abutment_area': abutment_area,
            'pier_area': pier_area
        }

    def evaluate_condition(self, data):
        # 상태평가 로직 구현
        defect_score = sum(data['weights']) / len(data['weights'])
        
        if defect_score >= 0.7:
            grade = 'E'
        elif defect_score >= 0.55:
            grade = 'D'
        elif defect_score >= 0.4:
            grade = 'C'
        elif defect_score >= 0.25:
            grade = 'B'
        else:
            grade = 'A'
            
        return {
            'defect_score': defect_score,
            'grade': grade
        }

bridge_eval = BridgeEvaluation()
 
 
 
# 로그인 체크 데코레이터
def login_required(f):
    def decorated_function(*args, **kwargs):
        if 'username' not in session:
            return redirect(url_for('login'))
        return f(*args, **kwargs)
    decorated_function.__name__ = f.__name__
    return decorated_function

@app.route('/login', methods=['GET', 'POST'])
def login():
    if request.method == 'POST':
        username = request.form['username']
        password = request.form['password']
        
        conn = get_db_connection()
        cur = conn.cursor()
        
        try:
            cur.execute('SELECT * FROM users WHERE username = %s', (username,))
            user = cur.fetchone()
            
            if user and check_password_hash(user[2], password):
                session['user_id'] = user[0]
                session['username'] = user[1]
                session['email'] = user[3]
                flash('로그인 성공!', 'success')
                return redirect(url_for('index'))
            else:
                flash('아이디 또는 비밀번호가 올바르지 않습니다.', 'error')
        except Exception as e:
            flash(f'로그인 중 오류가 발생했습니다: {str(e)}', 'error')
        finally:
            cur.close()
            conn.close()
    
    return render_template('login.html')

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

@app.route('/signup', methods=['GET', 'POST'])
def signup():
    if request.method == 'POST':
        username = request.form['username']
        email = request.form['email']
        password = request.form['password']
        confirm_password = request.form['confirm_password']
        
        # 비밀번호 확인
        if password != confirm_password:
            flash('Passwords do not match')
            return redirect(url_for('signup'))
            
        # 이메일 형식 검증
        if not re.match(r"[^@]+@[^@]+\.[^@]+", email):
            flash('Invalid email format')
            return redirect(url_for('signup'))
            
        conn = get_db_connection()
        cur = conn.cursor()
        
        try:
            # 사용자 이름 중복 확인
            cur.execute('SELECT * FROM users WHERE username = %s', (username,))
            if cur.fetchone():
                flash('Username already exists')
                return redirect(url_for('signup'))
                
            # 이메일 중복 확인
            cur.execute('SELECT * FROM users WHERE email = %s', (email,))
            if cur.fetchone():
                flash('Email already exists')
                return redirect(url_for('signup'))
                
            # 새 사용자 추가
            hashed_password = generate_password_hash(password)
            cur.execute(
                'INSERT INTO users (username, password, email) VALUES (%s, %s, %s)',
                (username, hashed_password, email)
            )
            conn.commit()
            flash('Account created successfully! Please login.')
            return redirect(url_for('login'))
            
        except Exception as e:
            flash('An error occurred. Please try again.')
            return redirect(url_for('signup'))
            
        finally:
            cur.close()
            conn.close()
            
    return render_template('signup.html')

@app.route('/', methods=['GET', 'POST'])
@login_required
def index():
    if request.method == 'POST':
        if 'file' in request.files:
            file = request.files['file']
            if file.filename == '':
                flash('파일이 선택되지 않았습니다.', 'error')
                return redirect(request.url)
            
            if file and file.filename.endswith('.xlsx'):
                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)
                    
                    # 파일 정보 저장
                    cur.execute(
                        '''
                        INSERT INTO uploaded_files 
                        (user_id, filename, original_filename, file_data, bridge_name, length, width, structure_type, span_count, 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,
                            length = EXCLUDED.length,
                            width = EXCLUDED.width,
                            structure_type = EXCLUDED.structure_type,
                            span_count = EXCLUDED.span_count,
                            expansion_joint_location = EXCLUDED.expansion_joint_location
                        ''',
                        (
                            session['user_id'],
                            secure_filename(file.filename),
                            file.filename,
                            file_data,
                            request.form.get('bridge_name'),
                            request.form.get('length') or None,  # 빈 문자열을 None으로 처리
                            request.form.get('width') or None,   # 빈 문자열을 None으로 처리
                            request.form.get('structure_type'),
                            request.form.get('span_count') or None,  # 빈 문자열을 None으로 처리
                            request.form.get('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(request.url)
            else:
                flash('올바른 Excel 파일(.xlsx)을 업로드해주세요.', 'error')
                return redirect(request.url)
    
    # 사용자의 최근 업로드 파일 목록 가져오기
    conn = get_db_connection()
    cur = conn.cursor()

    # 모든 컬럼 가져오기
    cur.execute("""
        SELECT column_name 
        FROM information_schema.columns 
        WHERE table_name = 'uploaded_files'
    """)
    columns = [row[0] for row in cur.fetchall()]

    # 데이터 가져오기
    cur.execute(f"""
        SELECT {', '.join(columns)} 
        FROM uploaded_files 
        WHERE user_id = %s 
        ORDER BY upload_date DESC
    """, (session['user_id'],))

    # 데이터를 files에 담기
    files = [dict(zip(columns, row)) for row in cur.fetchall()]

    cur.close()
    conn.close()
    
    return render_template('files.html', files=files)

# 보수방안 분류
def classify_repair(desc):
    desc = remove_special_characters(desc)  # 특수문자 제거
    
    original_desc = desc
    is_pavement = any(keyword in desc for keyword in ["교면포장", "포장", "연석", "신축이음", "포장균열"])

    desc = (desc.replace("보수부", "")
                .replace("받침콘크리트", "")
                .replace("받침몰탈", "")
                .replace("받침", "")
                .replace("전단키", "")
                .replace("연석", ""))
    
    # ✅ 명시된 문자열 먼저 우선 처리
    if re.search(r"균열\(0\.3mm\)|균열\(0\.3mm이상\)|균열\(0\.3㎜\)|균열\(0\.3㎜이상\)", desc): 
        return "주입보수"
    if re.search(r"균열\(0\.3mm미만\)|균열\(0\.2mm이하\)|균열\(0\.2㎜이하\)|균열\(0\.3㎜미만\)", desc):
        return "표면처리"
    
    if '균열' in desc:
        match = re.search(r'(\d+(\.\d+)?)mm', desc)
        if match:
            crack_size = float(match.group(1))
            if crack_size >= 1.0:
                return "충진보수"
            elif crack_size >= 0.3:
                return "주입보수"
            else:
                return "표면처리"
        else:
            return "표면처리"

    if is_pavement:
        if re.search("균열|망상균열", original_desc):
            return "실링보수"
        elif re.search("파손|패임|들뜸", original_desc):
            return "부분재포장"
        else:
            return "주의관찰"

    if re.search("신축이음|이음장치", desc):
    # ✅ '후타재'가 포함되어 있으면 무조건 주의관찰
        if re.search("후타재", desc):
            return "주의관찰"
        # ✅ 그 외 파손/탈락은 신축이음 재설치
        if re.search("본체파손|본체탈락|탈락|파손", desc):
            return "주의관찰"

    if re.search("철근노출", desc): return "단면보수(방청)"     
    if re.search("박리|들뜸|박락|재료분리|파손|침식|세굴|층분리", desc): return "단면보수"
    if re.search("백태|누수흔적|오염|망상균열|흔적|균열부백태|누수오염", desc): return "표면처리"
    if re.search("부식|도장박리|도장박락|도장|플레이트", desc): return "도장보수"
    if re.search("탈락|망실|미설치", desc): return "재설치"
    if re.search("막힘|퇴적|적치", desc): return "청소"
    if re.search("배수관탈락|길이부족", desc): return "배수관 재설치"

    return "주의관찰"

   # 한글, 영문, 숫자만 남기고 모두 제거
 

# 이하 생략된 부분은 그대로 이어서 정리 가능합니다!
# 우선순위 분류
def match_priority(desc):
    desc = remove_special_characters(desc)  # 특수문자 제거

    if re.search("포장균열|포장망상균열", desc):
        return "3"

    if re.search("신축이음|이음장치", desc):
        if re.search("본체파손|본체탈락|탈락|파손", desc):
            return "2"

    if re.search("교면포장|포장", desc):
        if re.search("파손|패임|들뜸", desc):
            return "2"

    if re.search(r"균열\(0\.3mm\)|균열\(0\.3mm이상\)|철근노출|세굴", desc):
        return "1"

    if re.search("주의관찰|청소", desc):
        return "3"

    return "2"

# 단가 매핑
def match_unit_price(desc):
    desc = remove_special_characters(desc)  # 특수문자 제거

    desc = normalize_damage(desc)
    check = (desc.replace("보수부", "")
                .replace("받침콘크리트", "")
                .replace("받침몰탈", "")
                .replace("받침", "")
                .replace("전단키", "")
                .replace("연석", ""))

    if re.search("교면포장|포장", desc):
        if re.search("균열", desc): return 20000
        if re.search("파손|패임", desc): return 40000
        if re.search("들뜸|탈락", desc): return 40000

    if re.search("신축이음|이음장치", desc):
        if re.search("본체파손|본체탈락|탈락|파손", desc): return 300000

    if re.search(r"균열\(0\.3mm이상\)", check): return 87000
    if re.search(r"균열\(0\.3mm\)", check): return 87000
    if re.search(r"균열\(0\.3mm미만\)", check): return 62000
    if re.search(r"균열\(0\.2mm\)", check): return 62000
    if re.search("백태", check): return 62000
    if re.search("균열", check): return 62000
    if re.search("철근노출", check): return 310000
    if re.search("박리|들뜸|박락|재료분리|파손|침식|세굴|층분리", check): return 300000
    if re.search("백태|누수흔적|오염|망상균열|흔적|균열부백태|누수오염", check): return 62000
    if re.search("부식|도장박리|도장박락|도장|플레이트", check): return 65000
    if re.search("탈락|망실|미설치", check): return 100000
    if re.search("막힘|퇴적|적치", check): return 11000
    if re.search("배수관탈락|길이부족", check): return 250000

    return 30000
# 보수물량 계산 함수
def adjust(row):
    if row['단위'] in ['개소', 'ea', 'EA']:
        return row['손상물량']
    elif ('균열' in row['손상내용'] or '균열부' in row['손상내용']) and any(x in row['손상내용'] for x in ['0.2', '0.3㎜미만', '0.3mm미만']):
        return round(row['손상물량'] * 1.2 * 0.25, 2)
    else:
        return round(row['손상물량'] * 1.2, 2)

# 보수물량표 및 개략공사비표 생성
def generate_repair_tables(df:None,filename):
    #import pandas as pd
    #from flask import session
    conn = get_db_connection()
    cur = conn.cursor()
    repair = None
    repair_html = ""
    cost_html = ""
    eval_html = "" 
    
    cur.execute('''
    SELECT COUNT(*) 
    FROM file_damage_details 
    WHERE user_id = %s AND filename = %s
    ''', (session['user_id'], filename))

    count = cur.fetchone()[0]

    if count > 0:
        # ✅ 해당 조건에 맞는 데이터가 존재할 때 실행할 코드
        print(f"{count}개의 보수물량 데이터가 존재합니다.")
        # 예: 업데이트나 출력 처리
    else:
        print("해당 사용자 파일에 대한 데이터가 존재하지 않습니다.")
        # 예: 새로 계산하거나 
            
    
    
    # [1] df가 주어진 경우: 계산 및 생성
    if df is not None:
        unique_components = sort_components(df['부재명'].unique())
        df = df[df['부재명'].isin(unique_components)]
        
        repair = df.groupby(['부재명', '손상내용', '단위'])[['손상물량', '개소']].sum().reset_index()
        repair['보수방안'] = repair['손상내용'].apply(classify_repair)
        repair['우선순위'] = repair['손상내용'].apply(match_priority)
        repair['단가'] = repair['손상내용'].apply(match_unit_price)
        repair['보수물량'] = repair.apply(adjust, axis=1).round(2)
        repair['개략공사비'] = repair['보수물량'] * repair['단가']
        
    if count > 0:
        # [2] df가 없을 경우 DB에서 불러오기
        cur.execute('''
            SELECT component_name, damage_description, repair_method, priority, damage_quantity,
                repair_quantity, count, unit_price, estimated_cost, unit
            FROM file_damage_details
            WHERE user_id = %s AND filename = %s
        ''', (session['user_id'], filename))
        rows = cur.fetchall()
        columns = ['부재명', '손상내용', '보수방안', '우선순위', '손상물량', '보수물량', '개소', '단가', '개략공사비', '단위']
        if rows:
            repair = pd.DataFrame(rows, columns=columns)
        else:
            cur.close()
            conn.close()
            return "", "", "<p>📭 저장된 데이터가 없습니다.</p>"

    cur.close()
    conn.close()

    # [3] 보수물량표 생성
    repair_html += '<div style="text-align:right"><span style="padding:10px">할증율 : 20%</span>'
    repair_html += '<input type="button" class="btn btn-secondary" value="저장" onclick="saveCostTable()"/></div>'
    repair_html += '<div class="table-container repair-table"><table class="table-striped"><thead><tr>'
    repair_html += '<th>부재명</th><th>손상내용</th><th>단위</th><th>손상물량</th><th>보수물량</th><th>개소</th>'
    repair_html += '<th>보수방안</th><th>우선순위</th><th>단가</th><th>개략공사비</th></tr></thead><tbody>'
    
    repair['부재명_순서'] = repair['부재명'].apply(lambda x: COMPONENT_ORDER.index(normalize_component(x)) if normalize_component(x) in COMPONENT_ORDER else len(COMPONENT_ORDER))


    repair=repair.sort_values('부재명_순서').drop('부재명_순서', axis=1)

    for idx, row in repair.iterrows():
        repair_html += f'''
        <tr>
            <td>{row["부재명"]}</td>
            <td>{row["손상내용"]}</td>
            <td>{row["단위"]}</td>
            <td>{row["손상물량"]:.2f}</td>
            <td>{row["보수물량"]:.2f}</td>
            <td>{int(row["개소"])}</td>
            <td><input type="text" class="form-control repair-method" name="repair_method_{idx}" value="{row["보수방안"]}"></td>
            <td><input type="text" class="form-control priority" name="priority_{idx}" value="{row["우선순위"]}"></td>
            <td><input type="number" class="form-control unit-price" name="unit_price_{idx}" value="{int(row["단가"])}" step="1"></td>
            <td class="total-cost" style="text-align:right">{int(row["개략공사비"]):,}</td>
        </tr>
        '''

    repair_html += '</tbody></table></div>'

    # [4] 개략공사비 요약표
    filtered = repair[repair['보수방안'] != '주의관찰'].copy()
    result = filtered.groupby(['부재명', '보수방안', '우선순위'], dropna=False).agg({
        '보수물량': 'sum',
        '개소': 'sum',
        '손상내용': lambda x: ', '.join(sorted(set(x))),
        '단가': 'first',
        '개략공사비': 'sum'
    }).reset_index()

    result['부재명_순서'] = result['부재명'].apply(lambda x: COMPONENT_ORDER.index(normalize_component(x)) if normalize_component(x) in COMPONENT_ORDER else len(COMPONENT_ORDER))
    result = result.sort_values('부재명_순서').drop('부재명_순서', axis=1)

    cost_html += '<div class="table-container cost-table"><table class="table-striped"><thead><tr>'
    cost_html += '<th>부재명</th><th>손상내용</th><th>보수방안</th><th>우선순위</th><th>보수물량</th><th>개소</th><th>단가</th><th>개략공사비</th></tr></thead><tbody>'

    total_cost = 0
    for _, row in result.iterrows():
        cost_html += f'''
        <tr>
            <td>{row["부재명"]}</td>
            <td>{row["손상내용"]}</td>
            <td>{row["보수방안"]}</td>
            <td>{row["우선순위"]}</td>
            <td>{row["보수물량"]:.2f}</td>
            <td>{int(row["개소"])}</td>
            <td class="cost-amount">{int(row["단가"]):,}</td>
            <td class="cost-amount">{int(row["개략공사비"]):,}</td>
        </tr>
        '''
        total_cost += row["개략공사비"]

    # 총계 및 우선순위 요약
    cost_html += f'''
        <tr class="table-primary">
            <td colspan="7" class="text-end"><strong>총계</strong></td>
            <td class="cost-amount"><strong id="costTotal_text">{int(total_cost):,}</strong></td>
        </tr>
        </tbody></table></div>
    '''

    cost_html += '<h4 class="mt-4">우선순위별 공사비 요약</h4>'
    cost_html += '<table id="cost_sum" class="table table-striped"><thead><tr>'
    cost_html += '<th>우선순위</th><th>순공사비</th><th>제경비(50%)</th><th>전체 공사비</th></tr></thead><tbody>'

    total_sum = 0
    for prio, amount in result.groupby('우선순위')['개략공사비'].sum().items():
        soft = int(amount)
        indirect = int(soft * 0.5)
        total = soft + indirect
        total_sum += total
        cost_html += f'<tr><td>{prio}</td><td style="text-align:right;">{soft:,}</td><td style="text-align:right;">{indirect:,}</td><td style="text-align:right;">{total:,}</td></tr>'

    cost_html += f'''
        <tr class="table-primary">
            <td><strong>총괄 개략공사비</strong></td>
            <td></td><td></td>
            <td style="text-align:right;"><strong>{total_sum:,}</strong></td>
        </tr>
        </tbody></table>
    '''

    # [5] 상태 평가표 생성
    eval_html += '<div class="eval-table"><table class="table table-striped"><thead><tr>'
    eval_html += '<th>부재명</th><th>보수방안</th><th>우선순위</th></tr></thead><tbody>'

    eval_data = {}
    for _, row in repair.iterrows():
        component = row['부재명']
        method = row['보수방안']
        priority = row['우선순위']
        if component not in eval_data:
            eval_data[component] = {'methods': set(), 'priority': priority}
        eval_data[component]['methods'].add(method)

    for component in sort_components(list(eval_data.keys())):
        data = eval_data[component]
        eval_html += f'<tr><td>{component}</td><td>{", ".join(sorted(data["methods"]))}</td><td>{data["priority"]}</td></tr>'

    eval_html += '</tbody></table></div>'

    return repair_html, cost_html, eval_html





    # if df:
    #     # 부재명 정렬
    #     unique_components = sort_components(df['부재명'].unique())
    #     df = df[df['부재명'].isin(unique_components)]
        
        
    #     #else:
    #         # 보수물량표 생성
    #     repair = df.groupby(['부재명', '손상내용', '단위'])[['손상물량', '개소']].sum().reset_index()
    #     repair['보수방안'] = repair['손상내용'].apply(classify_repair)
    #     repair['우선순위'] = repair['손상내용'].apply(match_priority)
    #     repair['단가'] = repair['손상내용'].apply(match_unit_price)
        
    #     repair['보수물량'] = repair.apply(adjust, axis=1)
    #     repair['보수물량'] = repair['보수물량'].round(2)
    #     repair['개략공사비'] = repair['보수물량'] * repair['단가']
        
        
        
        
    #     # 부재명 순서대로 정렬
    #     repair['부재명_순서'] = repair['부재명'].apply(lambda x: COMPONENT_ORDER.index(normalize_component(x)) if normalize_component(x) in COMPONENT_ORDER else len(COMPONENT_ORDER))
    #     repair = repair.sort_values('부재명_순서').drop('부재명_순서', axis=1)
        
    #     # 보수물량표 HTML 생성
    #     repair_html = '<div  style="text-align:right"><span style="padding:10px">할증율 : 20%</span>'
        
    #     repair_html += ' &nbsp;<input type="button" class="btn btn-secondary" value="저장" onclick="saveCostTable()"/></div>'
            
    #     repair_html += '<div class="table-container  repair-table"> <table class="table-striped"><thead><tr>'
    #     repair_html += '<th>부재명</th><th>손상내용</th><th>단위</th><th>손상물량</th><th>보수물량</th><th>개소</th>'
    #     repair_html += '<th>보수방안</th><th>우선순위</th><th>단가</th><th>개략공사비</th></tr></thead><tbody>'
        
     
    # conn = get_db_connection()
    # cur = conn.cursor()    
    # cur.execute('''
    #     SELECT component_name, damage_description, repair_method, priority,damage_quantity,
    #         repair_quantity, count, unit_price, estimated_cost,unit
    #     FROM file_damage_details
    #     WHERE user_id = %s AND filename != %s
    # ''', (session['user_id'], filename))

    # rows = cur.fetchall()
    # cur.close()
    # conn.close() 
    # if rows:
    #     # ✅ DB에서 가져온 결과로 DataFrame 생성
    #     columns = ['부재명', '손상내용', '보수방안', '우선순위','손상물량', '보수물량', '개소', '단가', '개략공사비','단위']
    #     repair = pd.DataFrame(rows, columns=columns)
     
    
     
    # for idx, row in repair.iterrows():
    #     repair_html += '<tr>'
    #     repair_html += f'<td style="min-width:150px">{row["부재명"]}</td>'
    #     repair_html += f'<td style="min-width:150px">{row["손상내용"]}</td>'
    #     repair_html += f'<td style="width:50px">{row["단위"]}</td>'
    #     repair_html += f'<td>{row["손상물량"]:.2f}</td>'
    #     repair_html += f'<td>{row["보수물량"]:.2f}</td>'
    #     repair_html += f'<td>{int(row["개소"])}</td>'
    #     repair_html += f'<td><input type="text" class="form-control repair-method" name="repair_method_{idx}" value="{row["보수방안"]}"></td>'
    #     repair_html += f'<td><input type="text" class="form-control priority" name="priority_{idx}" value="{row["우선순위"]}"></td>
    #     repair_html += f'<td><input type="number" class="form-control unit-price" name="unit_price_{idx}" value="{int(row["단가"])}" step="1"></td>'
    #     repair_html += f'<td class="total-cost" style="text-align:right">{int(row["개략공사비"]):,}</td>'
    #     repair_html += '</tr>'
    
    # repair_html += '</tbody></table></div>'
    
    # # 개략공사비표 생성
    # filtered = repair[repair['보수방안'] != '주의관찰'].copy()
    # result = filtered.groupby(['부재명', '보수방안', '우선순위'], dropna=False).agg({
    #     '보수물량': 'sum',
    #     '개소': 'sum',
    #     '손상내용': lambda x: ', '.join(sorted(set(x))),
    #     '단가': 'first',
    #     '개략공사비': 'sum'
    # }).reset_index()
    
    # # 부재명 순서대로 정렬
    # result['부재명_순서'] = result['부재명'].apply(lambda x: COMPONENT_ORDER.index(normalize_component(x)) if normalize_component(x) in COMPONENT_ORDER else len(COMPONENT_ORDER))
    # result = result.sort_values('부재명_순서').drop('부재명_순서', axis=1)
    
    # total_cost = 0

    # cost_html = """<div class="table-container cost-table">
    #         <table  class="table-striped">
    #             <thead>
    #             <tr>"""
                
    # cost_html += '<th style="min-width:150px">부재명</th><th style="min-width:150px">손상내용</th><th style="min-width:150px">보수방안</th><th>우선순위</th><th>보수물량</th><th>개소</th><th>단가</th><th>개략공사비</th></tr></thead>'
   
    # cost_html += """</tr>
    #             <tbody>
    #         """
    # # 데이터 예시 추가
    # # 
    # # 이부분에서 테이블 file_damage_details  에 존재하면 테이블 데이터에서 가져와서 아래 for 문의 처리 하고 없으면 아래 for문의 처리 합니다. 
    
     
  
    # for _, row in result.iterrows():
    #     cost_html += '<tr>'
    #     cost_html += f'<td style="min-width:150px">{row["부재명"]}</td>'
    #     cost_html += f'<td style="min-width:150px">{row["손상내용"]}</td>'
    #     cost_html += f'<td>{row["보수방안"]}</td>'
    #     cost_html += f'<td>{row["우선순위"]}</td>'
    #     cost_html += f'<td>{row["보수물량"]:.2f}</td>'
    #     cost_html += f'<td>{int(row["개소"])}</td>'
    #     cost_html += f'<td class="cost-amount">{int(row["단가"]):,}</td>'
    #     cost_html += f'<td class="cost-amount">{int(row["개략공사비"]):,}</td>'
    #     cost_html += '</tr>'
    #     total_cost += row['개략공사비']

    


        
    # cost_html += """
    #             </tbody>
    #         </table>
    #         </div>
    #     """
        
        
    # # 개략공사비표 HTML 생성
    # #cost_html += '<div class="cost-table"><table class="table table-striped"><thead><tr>'
    # #cost_html += '<th>부재명</th><th>손상내용</th><th>보수방안</th><th>우선순위</th><th>보수물량</th><th>개소</th><th>단가</th><th>개략공사비</th></tr></thead><tbody>'
    
     
    
    # # 총계 행 추가
    # cost_html += '<tr class="table-primary">'
    # cost_html += '<td colspan="7" class="text-end"><strong>총계</strong></td>'
    # cost_html += f'<td class="cost-amount"><strong id="costTotal_text">{int(total_cost):,}</strong></td>'
    # cost_html += '</tr>'
    # cost_html += '</tbody></table>'
    
    # # 우선순위별 집계표 추가
    # cost_html += '<h4 class="mt-4">우선순위별 공사비 요약</h4>'
    # cost_html += '<table  id="cost_sum"  class="table table-striped"><thead><tr>'
    # cost_html += '<th>우선순위</th><th>순공사비</th><th>제경비(50%)</th><th>전체 공사비</th></tr></thead><tbody>
    
   
    
    
    
    # group_by_priority = result.groupby('우선순위')['개략공사비'].sum()
    # total_sum = 0
    
    # for prio, amount in group_by_priority.items():
    #     soft = int(amount)
    #     indirect = int(soft * 0.5)
    #     total = soft + indirect
    #     total_sum += total
        
    #     cost_html += '<tr>'
    #     cost_html += f'<td>{prio}</td>'
    #     cost_html += f'<td style=" text-align: right;">{soft:,}</td>'
    #     cost_html += f'<td style=" text-align: right;">{indirect:,}</td>'
    #     cost_html += f'<td style=" text-align: right;">{total:,}</td>'
    #     cost_html += '</tr>'
     
    # # 총괄 개략공사비
    # cost_html += '<tr class="table-primary">'
    # cost_html += '<td><strong>총괄 개략공사비</strong></td>'
    # cost_html += '<td></td>'
    # cost_html += '<td></td>'
    # cost_html += f'<td style=" text-align: right;"><strong>{total_sum:,}</strong></td>'
    # cost_html += '</tr>'
    # cost_html += '</tbody></table></div>'
    
    # # 상태평가표 HTML 생성 
    # eval_html = '<div class="eval-table"><table class="table table-striped"><thead><tr>'
    # eval_html += '<th style="min-width:150px">부재명</th><th style="min-width:150px">보수방안</th><th>우선순위</th></tr></thead><tbody>'
    
    # # 보수물량표 데이터를 기반으로 상태평가표 생성
    # eval_data = {}
    # for _, row in repair.iterrows():
    #     component = row['부재명']
    #     method = row['보수방안']
    #     priority = row['우선순위']
        
    #     if component not in eval_data:
    #         eval_data[component] = {
    #             'methods': set(),
    #             'priority': priority
    #         }
    #     eval_data[component]['methods'].add(method)
    
    # # 상태평가표 데이터 정렬 및 HTML 생성
    # for component in unique_components:
    #     if component not in eval_data:
    #         continue
    #     data = eval_data[component]
    #     eval_html += '<tr>'
    #     eval_html += f'<td>{component}</td>'
    #     eval_html += f'<td>{", ".join(sorted(data["methods"]))}</td>'
    #     eval_html += f'<td>{data["priority"]}</td>'
    #     eval_html += '</tr>'
    
    # eval_html += '</tbody></table></div>'
    # return repair_html, cost_html, eval_html



@app.route('/getData', methods=['GET', 'POST'])
def getData():
    if request.method == 'POST':
        filename = request.form['file']
        
        
        repair_html, cost_html, eval_html = generate_repair_tables(df,filename)

    return send_from_directory('static/css', 'style.css')

@app.route('/style.css')
def style():
    return send_from_directory('static/css', 'style.css')

@app.route('/js/<path:filename>')
def serve_js(filename):
    return send_from_directory('static/js', filename)

@app.route('/download/<table_type>')
def download_table(table_type):
    if not os.path.exists('cache.csv'):
        return "데이터가 없습니다."
    
    df = pd.read_csv('cache.csv')
    
    if table_type == 'detail':
        # 부재별 집계표 생성
        doc = Document()
        doc.add_heading('부재별 집계표', 0)
        
        for name, group in df.groupby('부재명'):
            doc.add_heading(f'부재명: {name}', level=1)
            
            # 관찰 결과 요약 추가
            unique_damages = sorted(set(group['손상내용']))
            damage_text = ', '.join(unique_damages)
            summary = f"{name}에 대한 외관조사결과에서 조사된 바와 같이, {damage_text} 등의 손상이 조사되었다."
            doc.add_paragraph(summary)
            
            # 부재위치 목록 가져오기
            positions = sorted(group['부재위치'].unique(), key=natural_sort_key)
            
            # 테이블 생성 (합계 열을 포함하여 +2)
            table = doc.add_table(rows=2, cols=3 + len(positions) * 2 + 2)
            table.style = 'Table Grid'
            
            # 표 스타일 설정
            for row in table.rows:
                for cell in row.cells:
                    # 셀 여백 설정
                    cell.paragraphs[0].paragraph_format.space_before = Pt(0)
                    cell.paragraphs[0].paragraph_format.space_after = Pt(0)
                    # 텍스트 정렬 설정
                    cell.paragraphs[0].alignment = WD_ALIGN_PARAGRAPH.CENTER
                    cell.vertical_alignment = WD_ALIGN_VERTICAL.CENTER
                    # 폰트 크기 설정
                    for paragraph in cell.paragraphs:
                        for run in paragraph.runs:
                            run.font.size = Pt(9)
            
            # 첫 번째 행 헤더 (부재위치)
            header_cells = table.rows[0].cells
            header_cells[0].text = '부재명'
            header_cells[1].text = '손상내용'
            header_cells[2].text = '단위'
            
            # 부재위치별 헤더 추가 (첫 번째 행)
            for i, pos in enumerate(positions):
                header_cells[3 + i*2].text = pos
                header_cells[4 + i*2].text = pos
            
            # 합계 열 헤더 추가 (첫 번째 행)
            header_cells[-2].text = '합계'
            header_cells[-1].text = '합계'
            
            # 두 번째 행 헤더 (손상물량/개소)
            subheader_cells = table.rows[1].cells
            subheader_cells[0].text = ''
            subheader_cells[1].text = ''
            subheader_cells[2].text = ''
            
            # 부재위치별 서브헤더 추가 (두 번째 행)
            for i in range(len(positions)):
                subheader_cells[3 + i*2].text = '손상물량'
                subheader_cells[4 + i*2].text = '개소'
            
            # 합계 열 서브헤더 추가 (두 번째 행)
            subheader_cells[-2].text = '손상물량'
            subheader_cells[-1].text = '개소'
            
            # 손상내용별로 데이터 그룹화
            damage_groups = group.groupby('손상내용')
            
            # 데이터 추가
            for damage, damage_group in damage_groups:
                row_cells = table.add_row().cells
                row_cells[0].text = str(name)
                row_cells[1].text = str(damage)
                row_cells[2].text = str(damage_group['단위'].iloc[0])  # 첫 번째 행의 단위 사용
                
                # 부재위치별 데이터 추가
                total_damage = 0
                total_count = 0
                for i, pos in enumerate(positions):
                    pos_data = damage_group[damage_group['부재위치'] == pos]
                    if not pos_data.empty:
                        damage = float(pos_data['손상물량'].iloc[0])
                        count = int(pos_data['개소'].iloc[0])
                        row_cells[3 + i*2].text = f"{damage:.2f}"
                        row_cells[4 + i*2].text = str(count)
                        total_damage += damage
                        total_count += count
                    else:
                        row_cells[3 + i*2].text = '-'
                        row_cells[4 + i*2].text = '-'
                
                # 합계 데이터 추가
                row_cells[-2].text = f"{total_damage:.2f}"
                row_cells[-1].text = str(total_count)
                
                # 새로 추가된 행의 스타일 설정
                for cell in row_cells:
                    cell.paragraphs[0].paragraph_format.space_before = Pt(0)
                    cell.paragraphs[0].paragraph_format.space_after = Pt(0)
                    cell.paragraphs[0].alignment = WD_ALIGN_PARAGRAPH.CENTER
                    cell.vertical_alignment = WD_ALIGN_VERTICAL.CENTER
                    for paragraph in cell.paragraphs:
                        for run in paragraph.runs:
                            run.font.size = Pt(9)
            
            # 손상별 원인 및 대책방안 추가
            doc.add_heading('손상별 원인 및 대책방안', level=2)
            
            for dmg in unique_damages:
                repair_method = classify_repair(dmg)
                doc.add_paragraph(f'손상내용: {dmg} (보수방안: {repair_method})', style='Heading 3')
                
                try:
                    normalized_dmg = normalize_damage(dmg)
                    
                    # 균열 관련 처리
                    if '균열' in normalized_dmg and not any(x in normalized_dmg for x in ['백태', '누수', '부']):
                        if re.search(r'균열\(?[a-zA-Z]*=?[\d.]+(mm|㎜)', normalized_dmg):
                            if "균열" in damage_solutions:
                                selected_solutions = random.sample(damage_solutions["균열"], 2)
                                for solution in selected_solutions:
                                    formatted_solution = solution.replace('{name}', name).replace('{보수방안}', repair_method)
                                    doc.add_paragraph(formatted_solution)
                    # 다른 손상 유형 처리
                    elif normalized_dmg in damage_solutions and len(damage_solutions[normalized_dmg]) >= 2:
                        selected_solutions = random.sample(damage_solutions[normalized_dmg], 2)
                        for solution in selected_solutions:
                            formatted_solution = solution.replace('{name}', name).replace('{보수방안}', repair_method)
                            doc.add_paragraph(formatted_solution)
                except Exception:
                    pass
                
                doc.add_paragraph('-' * 50)  # 구분선 추가
            
            doc.add_page_break()  # 부재별 구분을 위한 페이지 나누기
    
    elif table_type == 'overall':
        # 외관조사 총괄표 생성
        overall = df.groupby(['부재명', '손상내용', '단위'])[['손상물량', '개소']].sum().reset_index()
        overall['손상물량'] = overall['손상물량'].round(2)
        
        doc = Document()
        doc.add_heading('외관조사 총괄표', 0)
        
        table = doc.add_table(rows=1, cols=5)
        table.style = 'Table Grid'
        
        # 표 스타일 설정
        for row in table.rows:
            for cell in row.cells:
                # 셀 여백 설정
                cell.paragraphs[0].paragraph_format.space_before = Pt(0)
                cell.paragraphs[0].paragraph_format.space_after = Pt(0)
                # 텍스트 정렬 설정
                cell.paragraphs[0].alignment = WD_ALIGN_PARAGRAPH.CENTER
                cell.vertical_alignment = WD_ALIGN_VERTICAL.CENTER
                # 폰트 크기 설정
                for paragraph in cell.paragraphs:
                    for run in paragraph.runs:
                        run.font.size = Pt(9)
        
        # 헤더 추가
        header_cells = table.rows[0].cells
        header_cells[0].text = '부재명'
        header_cells[1].text = '손상내용'
        header_cells[2].text = '단위'
        header_cells[3].text = '손상물량'
        header_cells[4].text = '개소'
        
        # 데이터 추가
        for _, row in overall.iterrows():
            row_cells = table.add_row().cells
            row_cells[0].text = str(row['부재명'])
            row_cells[1].text = str(row['손상내용'])
            row_cells[2].text = str(row['단위'])
            row_cells[3].text = f"{float(row['손상물량']):.2f}"
            row_cells[4].text = str(row['개소'])
            
            # 새로 추가된 행의 스타일 설정
            for cell in row_cells:
                cell.paragraphs[0].paragraph_format.space_before = Pt(0)
                cell.paragraphs[0].paragraph_format.space_after = Pt(0)
                cell.paragraphs[0].alignment = WD_ALIGN_PARAGRAPH.CENTER
                cell.vertical_alignment = WD_ALIGN_VERTICAL.CENTER
                for paragraph in cell.paragraphs:
                    for run in paragraph.runs:
                        run.font.size = Pt(9)
    
    elif table_type == 'repair':
        # 보수물량표 생성
        repair = df.groupby(['부재명', '손상내용', '단위'])[['손상물량', '개소']].sum().reset_index()
        repair['보수방안'] = repair['손상내용'].apply(classify_repair)
        repair['우선순위'] = repair['손상내용'].apply(match_priority)
        repair['단가'] = repair['손상내용'].apply(match_unit_price)
        repair['보수물량'] = repair.apply(adjust, axis=1)
        repair['보수물량'] = repair['보수물량'].round(2)
        repair['개략공사비'] = repair['보수물량'] * repair['단가']
        
        doc = Document()
        doc.add_heading('보수물량표', 0)
        
        table = doc.add_table(rows=1, cols=9)
        table.style = 'Table Grid'
        
        # 표 스타일 설정
        for row in table.rows:
            for cell in row.cells:
                # 셀 여백 설정
                cell.paragraphs[0].paragraph_format.space_before = Pt(0)
                cell.paragraphs[0].paragraph_format.space_after = Pt(0)
                # 텍스트 정렬 설정
                cell.paragraphs[0].alignment = WD_ALIGN_PARAGRAPH.CENTER
                cell.vertical_alignment = WD_ALIGN_VERTICAL.CENTER
                # 폰트 크기 설정
                for paragraph in cell.paragraphs:
                    for run in paragraph.runs:
                        run.font.size = Pt(9)
        
        # 헤더 추가
        header_cells = table.rows[0].cells
        header_cells[0].text = '부재명'
        header_cells[1].text = '손상내용'
        header_cells[2].text = '단위'
        header_cells[3].text = '손상물량'
        header_cells[4].text = '보수물량'
        header_cells[5].text = '개소'
        header_cells[6].text = '보수방안'
        header_cells[7].text = '우선순위'
        header_cells[8].text = '단가'
        
        
        # 데이터 추가
        for _, row in repair.iterrows():
            row_cells = table.add_row().cells
            row_cells[0].text = str(row['부재명'])
            row_cells[1].text = str(row['손상내용'])
            row_cells[2].text = str(row['단위'])
            row_cells[3].text = f"{float(row['손상물량']):.2f}"
            row_cells[4].text = f"{float(row['보수물량']):.2f}"
            row_cells[5].text = str(row['개소'])
            row_cells[6].text = str(row['보수방안'])
            row_cells[7].text = str(row['우선순위'])
            row_cells[8].text = f"{int(row['단가']):,}"
            
            # 새로 추가된 행의 스타일 설정
            for cell in row_cells:
                cell.paragraphs[0].paragraph_format.space_before = Pt(0)
                cell.paragraphs[0].paragraph_format.space_after = Pt(0)
                cell.paragraphs[0].alignment = WD_ALIGN_PARAGRAPH.CENTER
                cell.vertical_alignment = WD_ALIGN_VERTICAL.CENTER
                for paragraph in cell.paragraphs:
                    for run in paragraph.runs:
                        run.font.size = Pt(9)
    
    elif table_type == 'cost':
        # 개략공사비표 생성
        repair = df.groupby(['부재명', '손상내용', '단위'])[['손상물량', '개소']].sum().reset_index()
        repair['보수방안'] = repair['손상내용'].apply(classify_repair)
        repair['우선순위'] = repair['손상내용'].apply(match_priority)
        repair['단가'] = repair['손상내용'].apply(match_unit_price)
        repair['보수물량'] = repair.apply(adjust, axis=1)
        repair['보수물량'] = repair['보수물량'].round(2)
        repair['개략공사비'] = repair['보수물량'] * repair['단가']
        
        filtered = repair[repair['보수방안'] != '주의관찰'].copy()
        result = filtered.groupby(['부재명', '보수방안', '우선순위'], dropna=False).agg({
            '보수물량': 'sum',
            '개소': 'sum',
            '손상내용': lambda x: ', '.join(sorted(set(x))),
            '단가': 'first',
            '개략공사비': 'sum'
        }).reset_index()
        
        doc = Document()
        doc.add_heading('개략공사비표', 0)
        
        # 개략공사비표
        table = doc.add_table(rows=1, cols=6)
        table.style = 'Table Grid'
        
        # 표 스타일 설정
        for row in table.rows:
            for cell in row.cells:
                # 셀 여백 설정
                cell.paragraphs[0].paragraph_format.space_before = Pt(0)
                cell.paragraphs[0].paragraph_format.space_after = Pt(0)
                # 텍스트 정렬 설정
                cell.paragraphs[0].alignment = WD_ALIGN_PARAGRAPH.CENTER
                cell.vertical_alignment = WD_ALIGN_VERTICAL.CENTER
                # 폰트 크기 설정
                for paragraph in cell.paragraphs:
                    for run in paragraph.runs:
                        run.font.size = Pt(9)
        
        header_cells = table.rows[0].cells
        header_cells[0].text = '부재명'
        header_cells[1].text = '보수방안'
        header_cells[2].text = '우선순위'
        header_cells[3].text = '보수물량'
        header_cells[4].text = '개소'
        header_cells[5].text = '개략공사비'
        
        for _, row in result.iterrows():
            row_cells = table.add_row().cells
            row_cells[0].text = str(row['부재명'])
            row_cells[1].text = str(row['보수방안'])
            row_cells[2].text = str(row['우선순위'])
            row_cells[3].text = f"{float(row['보수물량']):.2f}"
            row_cells[4].text = str(row['개소'])
            row_cells[5].text = f"{int(row['개략공사비']):,}"
            
            # 새로 추가된 행의 스타일 설정
            for cell in row_cells:
                cell.paragraphs[0].paragraph_format.space_before = Pt(0)
                cell.paragraphs[0].paragraph_format.space_after = Pt(0)
                cell.paragraphs[0].alignment = WD_ALIGN_PARAGRAPH.CENTER
                cell.vertical_alignment = WD_ALIGN_VERTICAL.CENTER
                for paragraph in cell.paragraphs:
                    for run in paragraph.runs:
                        run.font.size = Pt(9)
        
        # 우선순위별 요약
        doc.add_heading('우선순위별 공사비 요약', level=1)
        summary_table = doc.add_table(rows=1, cols=4)
        summary_table.style = 'Table Grid'
        
        # 표 스타일 설정
        for row in summary_table.rows:
            for cell in row.cells:
                # 셀 여백 설정
                cell.paragraphs[0].paragraph_format.space_before = Pt(0)
                cell.paragraphs[0].paragraph_format.space_after = Pt(0)
                # 텍스트 정렬 설정
                cell.paragraphs[0].alignment = WD_ALIGN_PARAGRAPH.CENTER
                cell.vertical_alignment = WD_ALIGN_VERTICAL.CENTER
                # 폰트 크기 설정
                for paragraph in cell.paragraphs:
                    for run in paragraph.runs:
                        run.font.size = Pt(9)
        
        header_cells = summary_table.rows[0].cells
        header_cells[0].text = '우선순위'
        header_cells[1].text = '순공사비'
        header_cells[2].text = '제경비(50%)'
        header_cells[3].text = '전체 공사비'
        
        group_by_priority = result.groupby('우선순위')['개략공사비'].sum()
        total_sum = 0
        
        for prio, amount in group_by_priority.items():
            soft = int(amount)
            indirect = int(soft * 0.5)
            total = soft + indirect
            total_sum += total
            
            row_cells = summary_table.add_row().cells
            row_cells[0].text = str(prio)
            row_cells[1].text = f"{soft:,}"
            row_cells[2].text = f"{indirect:,}"
            row_cells[3].text = f"{total:,}"
            
            # 새로 추가된 행의 스타일 설정
            for cell in row_cells:
                cell.paragraphs[0].paragraph_format.space_before = Pt(0)
                cell.paragraphs[0].paragraph_format.space_after = Pt(0)
                cell.paragraphs[0].alignment = WD_ALIGN_PARAGRAPH.CENTER
                cell.vertical_alignment = WD_ALIGN_VERTICAL.CENTER
                for paragraph in cell.paragraphs:
                    for run in paragraph.runs:
                        run.font.size = Pt(9)
        
        # 총괄 개략공사비
        row_cells = summary_table.add_row().cells
        row_cells[0].text = '총괄 개략공사비'
        row_cells[1].text = ''
        row_cells[2].text = ''
        row_cells[3].text = f"{total_sum:,}"
        
        # 새로 추가된 행의 스타일 설정
        for cell in row_cells:
            cell.paragraphs[0].paragraph_format.space_before = Pt(0)
            cell.paragraphs[0].paragraph_format.space_after = Pt(0)
            cell.paragraphs[0].alignment = WD_ALIGN_PARAGRAPH.CENTER
            cell.vertical_alignment = WD_ALIGN_VERTICAL.CENTER
            for paragraph in cell.paragraphs:
                for run in paragraph.runs:
                    run.font.size = Pt(9)
    
    # 문서를 메모리에 저장
    doc_stream = io.BytesIO()
    doc.save(doc_stream)
    doc_stream.seek(0)
    
    # 파일 이름 설정
    if table_type == 'detail':
        filename = '부재별_집계표.docx'
    elif table_type == 'overall':
        filename = '외관조사_총괄표.docx'
    elif table_type == 'repair':
        filename = '보수물량표.docx'
    elif table_type == 'cost':
        filename = '개략공사비표.docx'
    else:
        filename = f'{table_type}_표.docx'
    
    return send_file(
        doc_stream,
        as_attachment=True,
        download_name=filename,
        mimetype='application/vnd.openxmlformats-officedocument.wordprocessingml.document'
    )

@app.route('/update_repair', methods=['POST'])
def update_repair():
    try:
        repair_data = request.get_json()
        
        # 보수방안이 '주의관찰'인 항목 제외
        filtered_data = [item for item in repair_data if item['repairMethod'] != '주의관찰']
        
        # 부재명별로 그룹화
        component_groups = {}
        for item in filtered_data:
            component = item['component']
            if component not in component_groups:
                component_groups[component] = []
            component_groups[component].append(item)
        
        # 개략공사비표 HTML 생성
        cost_table_html = '<table class="table table-bordered">'
        cost_table_html += '<thead><tr><th>부재명</th><th>보수방안</th><th>우선순위</th><th>단가</th><th>물량</th><th>공사비</th></tr></thead>'
        cost_table_html += '<tbody>'
        
        total_cost = 0
        for component, items in component_groups.items():
            # 같은 부재명 내에서 보수방안과 우선순위가 같은 항목들을 그룹화
            grouped_items = {}
            for item in items:
                key = (item['repairMethod'], item['priority'])
                if key not in grouped_items:
                    grouped_items[key] = {
                        'component': item['component'],
                        'repairMethod': item['repairMethod'],
                        'priority': item['priority'],
                        'unitPrice': item['unitPrice'],
                        'quantity': 0
                    }
                grouped_items[key]['quantity'] += item['quantity']
            
            # 그룹화된 항목들을 테이블에 추가
            for key, item in grouped_items.items():
                cost = float(item['unitPrice']) * float(item['quantity'])
                total_cost += cost
                cost_table_html += f'<tr><td>{item["component"]}</td><td>{item["repairMethod"]}</td><td>{item["priority"]}</td><td>{item["unitPrice"]}</td><td>{item["quantity"]}</td><td>{cost:,.0f}</td></tr>'
        
        cost_table_html += '</tbody></table>'
        
        # 우선순위별 합계 HTML 생성
        priority_table_html = '<table class="table table-bordered">'
        priority_table_html += '<thead><tr><th>우선순위</th><th>공사비</th><th>직접공사비</th><th>간접공사비(50%)</th><th>총공사비</th></tr></thead>'
        priority_table_html += '<tbody>'
        
        # 우선순위별로 그룹화
        priority_groups = {}
        for component, items in component_groups.items():
            for item in items:
                priority = item['priority']
                if priority not in priority_groups:
                    priority_groups[priority] = 0
                priority_groups[priority] += float(item['unitPrice']) * float(item['quantity'])
        
        for priority, cost in priority_groups.items():
            direct_cost = cost
            indirect_cost = cost * 0.5
            total_priority_cost = direct_cost + indirect_cost
            priority_table_html += f'<tr><td>{priority}</td><td>{cost:,.0f}</td><td>{direct_cost:,.0f}</td><td>{indirect_cost:,.0f}</td><td>{total_priority_cost:,.0f}</td></tr>'
        
        total_direct_cost = total_cost
        total_indirect_cost = total_cost * 0.5
        total_all_cost = total_direct_cost + total_indirect_cost
        priority_table_html += f'<tr><td>총계</td><td>{total_cost:,.0f}</td><td>{total_direct_cost:,.0f}</td><td>{total_indirect_cost:,.0f}</td><td>{total_all_cost:,.0f}</td></tr>'
        priority_table_html += '</tbody></table>'
        
        return jsonify({
            'cost_table': cost_table_html,
            'priority_table': priority_table_html
        })
        
    except Exception as e:
        return jsonify({'error': str(e)}), 500


 
 
      



@app.route('/pivot_detail', methods=['POST'])
def pivot_detail():
    try:
        data = request.get_json()

        filename=session['current_filename'] 
        
        detail_html = pivot_detail_view(filename,data['pivot']); 
        return jsonify({
            'detail_html': detail_html,
            
        })
        
    except Exception as e:
        return jsonify({'error': str(e)}), 500


@app.route('/evaluate', methods=['POST'])
def evaluate():
    data = request.get_json()
    
    # 입력 데이터 검증
    required_fields = [
        'bridgeName', 'length', 'width', 'structureType', 'spanCount',
        'slabType', 'girderType', 'crossbeamType', 'pavementType',
        'slabArea', 'girderArea', 'crossbeamArea', 'pavementArea'
    ]
    
    if not all(field in data for field in required_fields):
        return jsonify({'error': '필수 입력 항목이 누락되었습니다.'}), 400
    
    try:
        # 면적 계산 및 타입 정보 처리
        areas = {
            'slab': {
                'type': data['slabType'],
                'area': float(data['slabArea'])
            },
            'girder': {
                'type': data['girderType'],
                'area': float(data['girderArea'])
            },
            'crossbeam': {
                'type': data['crossbeamType'],
                'area': float(data['crossbeamArea'])
            },
            'pavement': {
                'type': data['pavementType'],
                'area': float(data['pavementArea'])
            }
        }
        
        # 상태평가 수행
        evaluation_data = {
            'weights': data.get('weights', [1] * 10),  # 기본 가중치
            'values': data.get('values', [0.5] * 10),  # 기본 값
            'areas': areas  # 부재별 면적 및 타입 정보
        }
        
        results = bridge_eval.evaluate_condition(evaluation_data)
        
        return jsonify({
            'areas': areas,
            'evaluation': results
        })
        
    except Exception as e:
        return jsonify({'error': f'평가 중 오류가 발생했습니다: {str(e)}'}), 500

@app.route('/pricing')
def pricing():
    return render_template('pricing.html')

@app.route('/view_file/<filename>')
@login_required
def view_file(filename, pivot=False):
    conn = get_db_connection()
    cur = conn.cursor()
    session['current_filename'] =filename
    try:
        cur.execute(
            "SELECT file_data FROM uploaded_files WHERE filename = %s AND user_id = %s",
            (filename, session['user_id'])
        )
        result = cur.fetchone()
        
        if not result:
            flash('파일을 찾을 수 없습니다.')
            return redirect(url_for('index'))
            
        # JSON 데이터를 DataFrame으로 변환
        file_data = result[0]
        if isinstance(file_data, str):
            file_data = json.loads(file_data)
        df = pd.DataFrame(file_data)
        
        # DataFrame 데이터 정리 및 trim 처리
        df = clean_dataframe_data(df)
        
        cur.close()
        conn.close()

        # 부재명 정렬
        unique_components = sort_components(df['부재명'].unique())
        df = df[df['부재명'].isin(unique_components)]
        
        # 데이터 처리 및 HTML 생성
        detail_html = ""
        detail_html_header_link = ""
        
        overall_html = ""
        repair_html = ""
        cost_html = ""
        eval_html = ""
        
        pivot = True
        detail_html = pivot_detail_view(filename,pivot); 
        print(filename)


        # [2] 전체 집계표 생성
        overall = df.groupby(['부재명', '손상내용', '단위'])[['손상물량', '개소']].sum().reset_index()
        overall['손상물량'] = overall['손상물량'].round(2)

        # 부재명 순서대로 정렬
        overall['부재명_순서'] = overall['부재명'].apply(lambda x: COMPONENT_ORDER.index(normalize_component(x)) if normalize_component(x) in COMPONENT_ORDER else len(COMPONENT_ORDER))
        overall = overall.sort_values('부재명_순서').drop('부재명_순서', axis=1)
        
        overall_html += "<div class='table-container' >"

        overall_html += overall.to_html(classes='table table-striped', index=False, border=0)
        overall_html += "</div>"
        
        repair_html, cost_html, eval_html = generate_repair_tables(df,filename)
        
        # 바닥판 평가
        slab_df = df[df['부재명'] == '바닥판'].copy()
        slab_eval_table = []

        for pos in sorted(slab_df['부재위치'].unique(), key=natural_sort_key):
            sub = slab_df[slab_df['부재위치'] == pos]
            area = sub['점검면적'].sum()
            
            crack_width = sub[sub['손상내용'].str.contains('균열', na=False)]['폭'].max()
            crack_ratio = sub[sub['손상내용'].str.contains('균열', na=False)]['손상물량'].sum()
            leak_ratio = sub[sub['손상내용'].str.contains('백태|누수', na=False)]['손상물량'].sum()
            surface_damage_ratio = sub[sub['손상내용'].str.contains('박락|파손|재료분리|층분리', na=False)]['손상물량'].sum()
           

           

            rebar_ratio = sub[sub['손상내용'].str.contains('철근부식', na=False)]['손상물량'].sum()

            grade = evaluate_slab_condition(
                crack_width=crack_width,
                crack_ratio=crack_ratio,
                leak_ratio=leak_ratio,
                surface_damage_ratio=surface_damage_ratio,
                rebar_corrosion_ratio=rebar_ratio
            )

            slab_eval_table.append({
                "구분": pos,
                "점검면적": round(area, 1),
                "균열폭": crack_width if not pd.isna(crack_width) else '-',
                "균열율": round(crack_ratio, 2) if crack_ratio > 0 else '-',
                "백태": round(leak_ratio, 2) if leak_ratio > 0 else '-',
                "표면손상": round(surface_damage_ratio, 3) if surface_damage_ratio > 0 else '-',
                "철근부식": round(rebar_ratio, 2) if rebar_ratio > 0 else '-',
                "등급": grade
            })
        
        return render_template(
            'index_re.html',
            active_tab=request.args.get('tab', 'detail'),
            detail_html=detail_html,
            overall_html=overall_html,
            repair_html=repair_html,
            cost_html=cost_html,
            eval_html=eval_html,
            file_data=file_data,
            slab_eval_table=slab_eval_table,
            error_message=None
        )
        
    except Exception as e:
        flash(f'파일을 불러오는 중 오류가 발생했습니다: {str(e)}')
        return redirect(url_for('index'))
    finally:
        cur.close()
        conn.close()

@app.route('/delete_file/<filename>', methods=['POST'])
@login_required
def delete_file(filename):
    conn = get_db_connection()
    cur = conn.cursor()
    
    try:
        cur.execute(
            "DELETE FROM uploaded_files WHERE filename = %s AND user_id = %s",
            (filename, session['user_id'])
        )
        conn.commit()
        flash('파일이 성공적으로 삭제되었습니다.', 'success')
    except Exception as e:
        flash(f'파일 삭제 중 오류가 발생했습니다: {str(e)}', 'error')
    finally:
        cur.close()
        conn.close()
    
    return redirect(url_for('index'))

@app.route('/update_file_damage_details', methods=['POST'])
@login_required
def update_file_damage_details():
    data = request.get_json()
    user_id = session['user_id']
    username = session.get('username', 'unknown')
    filename = session.get('current_filename', 'unnamed_file')  # 현재 열람 중인 파일명

    try:
        conn = get_db_connection()
        cur = conn.cursor()

        # ✅ 기존 데이터 삭제
        cur.execute('''
            DELETE FROM file_damage_details
            WHERE user_id = %s AND filename = %s
        ''', (user_id, filename))

        # ✅ 새 데이터 삽입
        for key, detail in data.items():
            cur.execute('''
                INSERT INTO file_damage_details (
                    user_id, username, filename,unit,
                    component_name, damage_description,
                    repair_method, priority,damage_quantity,
                    repair_quantity, count, unit_price, estimated_cost
                ) VALUES (%s, %s, %s,%s, %s, %s, %s, %s, %s,%s, %s, %s, %s)
            ''', (
                user_id,
                username,
                filename,
                detail["unit"],
                detail["component"], 
                detail["damage"],
                detail["method"],
                detail["priority"],
                detail["damage_quantity"],
                detail["quantity"],
                detail["count"],
                detail["unitPrice"],
                detail["totalCost"]
            ))


        conn.commit()
         
        repair_html, cost_htmlxxxx, eval_html = generate_repair_tables(None,filename) 

        return jsonify({"message": "보수물량 저장 완료","repair_html":repair_html,"cost_html":cost_htmlxxxx,"eval_html":eval_html})

    except Exception as e:
        print("업데이트 오류:", e)
        conn.rollback()
        return jsonify({"message": "오류 발생", "error": str(e)}), 500

    finally:
        cur.close()
        conn.close()
        
def process_slab_damage_data(component_data, damage_mapping=None):
    """
    부재별 집계표 데이터를 기반으로 콘크리트 바닥판 상태평가표 데이터를 생성합니다.
    
    Args:
        component_data (list): 부재별 집계표 데이터 리스트
        damage_mapping (dict): 손상 유형별 매핑 설정
            예시: {
                '균열': {
                    '1방향': {
                        'keywords': ['1방향', '균열'],
                        'length_factor': 0.25
                    },
                    '2방향': {
                        'keywords': ['2방향', '균열'],
                        'length_factor': 0.25
                    }
                },
                '누수': {
                    'keywords': ['누수', '백태']
                },
                '표면손상': {
                    'keywords': ['표면손상', '박락', '파손']
                },
                '철근부식': {
                    'keywords': ['철근부식']
                }
            }
        
    Returns:
        dict: 상태평가표 데이터
    """
    # 기본 손상 매핑 설정
    default_mapping = {
        '균열': {
            '1방향': {
                'keywords': ['균열부백태', '균열'],
                'length_factor': 0.25
            },
            '2방향': {
                'keywords': ['망상균열', '균열'],
                'length_factor': 0.25
            }
        },
        '누수': {
            'keywords': ['누수', '백태']
        },
        '표면손상': {
            'keywords': ['박리', '박락', '파손']
        },
        '철근부식': {
            'keywords': ['철근노출']
        }
    }
    
    # 사용자 정의 매핑이 있으면 기본 매핑을 업데이트
    if damage_mapping:
        for damage_type, mapping in damage_mapping.items():
            if damage_type in default_mapping:
                if isinstance(mapping, dict) and 'keywords' in mapping:
                    default_mapping[damage_type]['keywords'] = mapping['keywords']
                elif isinstance(mapping, dict):
                    for sub_type, sub_mapping in mapping.items():
                        if sub_type in default_mapping[damage_type]:
                            if 'keywords' in sub_mapping:
                                default_mapping[damage_type][sub_type]['keywords'] = sub_mapping['keywords']
                            if 'length_factor' in sub_mapping:
                                default_mapping[damage_type][sub_type]['length_factor'] = sub_mapping['length_factor']
    
    slab_data = {
        '구분': '콘크리트 바닥판',
        '점검면적': 0,
        '1방향 균열 최대폭': 0,
        '1방향 균열 균열율': 0,
        '2방향 균열 최대폭': 0,
        '2방향 균열 균열율': 0,
        '누수 및 백태 면적율': 0,
        '표면손상 면적율': 0,
        '철근부식 손상면적율': 0,
        '상태평가결과': 'a'
    }
    
    # 해당 경간의 콘크리트 바닥판 데이터만 필터링 ('바닥판'이라는 단어가 포함된 경우 포함)
    slab_components = [comp for comp in component_data if '바닥판' in comp['부재구분']]
    
    if not slab_components:
        return slab_data
    
    # 점검면적 계산 (모든 바닥판의 면적 합계)
    total_area = sum(float(comp['면적']) for comp in slab_components)
    slab_data['점검면적'] = total_area
    
    # 손상 데이터 초기화
    max_crack_width_1d = 0
    total_crack_length_1d = 0
    max_crack_width_2d = 0
    total_crack_length_2d = 0
    leak_area = 0
    surface_damage_area = 0
    rebar_corrosion_area = 0
    
    for comp in slab_components:
        damage_desc = comp['손상내용']
        area = float(comp['면적'])
        damage_quantity = float(comp['손상물량'])
        
        # 균열 처리
        if all(keyword in damage_desc for keyword in default_mapping['균열']['1방향']['keywords']):
            crack_pattern = r'(\d+(?:\.\d+)?)\s*(?:mm|㎜|m|M)'
            crack_match = re.search(crack_pattern, damage_desc)
            
            if crack_match:
                crack_width = float(crack_match.group(1))
                length_factor = default_mapping['균열']['1방향']['length_factor']
                crack_length = damage_quantity * length_factor  # 손상물량(균열 길이)에 length_factor 곱하기
                max_crack_width_1d = max(max_crack_width_1d, crack_width)
                total_crack_length_1d += crack_length
                
        elif all(keyword in damage_desc for keyword in default_mapping['균열']['2방향']['keywords']):
            crack_pattern = r'(\d+(?:\.\d+)?)\s*(?:mm|㎜|m|M)'
            crack_match = re.search(crack_pattern, damage_desc)
            
            if crack_match:
                crack_width = float(crack_match.group(1))
                length_factor = default_mapping['균열']['2방향']['length_factor']
                crack_length = damage_quantity * length_factor  # 손상물량(균열 길이)에 length_factor 곱하기
                max_crack_width_2d = max(max_crack_width_2d, crack_width)
                total_crack_length_2d += crack_length
        
        # 누수 및 백태 처리
        if any(keyword in damage_desc for keyword in default_mapping['누수']['keywords']):
            leak_area += damage_quantity  # 손상물량 그대로 사용
            surface_damage_area += damage_quantity  # 표면손상에도 포함
        
        # 표면손상 처리
        if any(keyword in damage_desc for keyword in default_mapping['표면손상']['keywords']):
            surface_damage_area += damage_quantity  # 손상물량 그대로 사용
        
        # 철근부식 처리 - 철근노출이 포함된 모든 손상내용 포함
        if any(keyword in damage_desc for keyword in ['철근노출', '철근부식', '부식']) and '잡철근노출' not in damage_desc:
            rebar_corrosion_area += damage_quantity  # 손상물량 그대로 사용
    
    # 결과 계산
    slab_data['1방향 균열 최대폭'] = max_crack_width_1d
    slab_data['1방향 균열 균열율'] = (total_crack_length_1d / total_area) * 100 if total_area > 0 else 0
    slab_data['2방향 균열 최대폭'] = '-' if max_crack_width_2d == 0 else max_crack_width_2d
    slab_data['2방향 균열 균열율'] = (total_crack_length_2d / total_area) * 100 if total_area > 0 else 0
    slab_data['누수 및 백태 면적율'] = (leak_area / total_area) * 100 if total_area > 0 else 0
    slab_data['표면손상 면적율'] = (surface_damage_area / total_area) * 100 if total_area > 0 else 0
    slab_data['철근부식 손상면적율'] = (rebar_corrosion_area / total_area) * 100 if total_area > 0 else 0
    
    # 상태평가결과 계산
    max_crack_width = max(max_crack_width_1d, max_crack_width_2d)
    max_crack_ratio = max(slab_data['1방향 균열 균열율'], slab_data['2방향 균열 균열율'])
    leak_ratio = slab_data['누수 및 백태 면적율']
    surface_damage_ratio = slab_data['표면손상 면적율']
    rebar_corrosion_ratio = slab_data['철근부식 손상면적율']
    
    # 등급 초기값
    grade = 'a'
    
    # 균열폭 기준
    if max_crack_width >= 1.0:
        grade = 'e'
    elif max_crack_width >= 0.5:
        grade = 'd'
    elif max_crack_width >= 0.3:
        grade = 'c'
    elif max_crack_width >= 0.1:
        grade = 'b'
    
    # 균열률 기준
    if max_crack_ratio >= 20:
        grade = max(grade, 'e')
    elif max_crack_ratio >= 10:
        grade = max(grade, 'd')
    elif max_crack_ratio >= 2:
        grade = max(grade, 'c')
    elif max_crack_ratio > 0:
        grade = max(grade, 'b')
    
    # 누수 및 백태 기준
    if leak_ratio >= 10:
        grade = max(grade, 'c')
    elif leak_ratio > 0:
        grade = max(grade, 'b')
    
    # 표면손상 기준
    if surface_damage_ratio >= 10:
        grade = max(grade, 'd')
    elif surface_damage_ratio >= 2:
        grade = max(grade, 'c')
    elif surface_damage_ratio > 0:
        grade = max(grade, 'b')
    
    # 철근부식 기준
    if rebar_corrosion_ratio >= 2:
        grade = max(grade, 'd')
    elif rebar_corrosion_ratio > 0:
        grade = max(grade, 'c')
    
    slab_data['상태평가결과'] = grade
    
    # 데이터베이스에 등급 업데이트
    conn = get_db_connection()
    cursor = conn.cursor()
    try:
        cursor.execute('''
            UPDATE component_damage
            SET 상태평가결과 = ?
            WHERE 부재구분 LIKE '%바닥판%'
        ''', (grade,))
        conn.commit()
    except Exception as e:
        print(f"Error updating grade in database: {e}")
    finally:
        conn.close()
    
    return slab_data

@app.route('/index_re')
def index_re():
    conn = get_db_connection()
    cursor = conn.cursor()
    
    # 부재별 집계표 데이터 조회
    cursor.execute('''
        SELECT 부재구분, 경간, 면적, 손상내용, 손상물량
        FROM component_damage
        ORDER BY 부재구분, 경간
    ''')
    component_data = cursor.fetchall()
    
    # 사용자 정의 손상 매핑 설정
    custom_damage_mapping = {
        '균열': {
            '1방향': {
                'keywords': ['균열부백태', '균열'],
                'length_factor': 0.25
            },
            '2방향': {
                'keywords': ['망상균열', '균열'],
                'length_factor': 0.25
            }
        },
        '누수': {
            'keywords': ['누수', '백태']
        },
        '표면손상': {
            'keywords': ['박리', '박락', '파손']
        },
        '철근부식': {
            'keywords': ['철근노출']
        }
    }
    
    # 콘크리트 바닥판 데이터 처리
    slab_data = process_slab_damage_data(component_data, custom_damage_mapping)
    
    conn.close()
    
    return render_template('index_re.html', slab_data=slab_data)

@app.route('/update_file', methods=['POST'])
def update_file():
    file_id = request.form.get('file_id')
    print(f"Received file_id: {file_id}")  # 디버깅용 로그
    bridge_name = request.form.get('bridge_name')
    length = request.form.get('length') or None  # 빈 문자열을 None으로 처리
    width = request.form.get('width') or None  # 빈 문자열을 None으로 처리
    structure_type = request.form.get('structure_type')
    span_count = request.form.get('span_count') or None  # 빈 문자열을 None으로 처리
    expansion_joint_location = request.form.get('expansion_joint_location')

    # 데이터베이스 업데이트 로직
    conn = get_db_connection()
    cur = conn.cursor()
    cur.execute('''
        UPDATE uploaded_files
        SET bridge_name = %s, length = %s, width = %s, structure_type = %s,
            span_count = %s, expansion_joint_location = %s
        WHERE id = %s
    ''', (bridge_name, length, width, structure_type, span_count, expansion_joint_location, int(file_id)))
    conn.commit()
    cur.close()
    conn.close()

    flash('파일 정보가 성공적으로 업데이트되었습니다.', 'success')
    return redirect(url_for('index'))

if __name__ == '__main__':
    init_db()
    check_and_add_columns()
    app.run(debug=True, port=5051)