# -*- coding: utf-8 -*-
"""
[项目配置]
文件名: app.py
版本: v1.3.7
更新日期: 2026-02-24
更新内容: 代码审查修复 - 连接泄漏/SQL注入/emoji兼容性/版本号
"""

import streamlit as st
import sqlite3
import pandas as pd
import hashlib
import plotly.express as px
from datetime import datetime, timedelta
import io

# ==========================================
# 1. 配置与常量定义
# ==========================================
DB_FILE = 'crm_data.db'
st.set_page_config(
    page_title="天津硕博霖客户信息管理系统",
    page_icon=None,
    layout="wide",
    initial_sidebar_state="expanded"
)

# 业务常量
INDUSTRIES = ["海洋船舶", "港口机械", "电力", "空气行业", "其他"]
EQUIPMENT_TYPES = ["空气压缩机", "柴油发动机", "发电机组", "车辆", "液压设备", "齿轮箱", "其他"]
STATUS_OPTIONS = ["意向客户", "报价谈判", "成交签约", "维护"]

# --- 报销相关常量 (已更新) ---
# 1. 通用基础费用类型
BASE_EXPENSE_TYPES = ["办公费", "招待费", "差旅费", "交通费", "物流费", "油费", "福利费", "其他"]

# 2. 原始票据类型 (直接使用基础类型)
EXPENSE_TYPES_ORIGINAL = BASE_EXPENSE_TYPES

# 3. 冲顶票据类型 (新增置顶项 + 基础类型)
EXPENSE_TYPES_OFFSET = ["客户费用", "税务费用", "工资提成"] + BASE_EXPENSE_TYPES

EXPENSE_STATUS = ["待审核", "已通过", "已驳回"]

# ==========================================
# 2. 数据库管理模块
# ==========================================

def get_db_connection():
    conn = sqlite3.connect(DB_FILE, check_same_thread=False)
    conn.row_factory = sqlite3.Row
    return conn

def init_db():
    conn = get_db_connection()
    c = conn.cursor()

    # Users 表
    c.execute('''
        CREATE TABLE IF NOT EXISTS users (
            username TEXT PRIMARY KEY,
            password_hash TEXT NOT NULL,
            role TEXT NOT NULL,
            permissions TEXT NOT NULL
        )
    ''')

    # Clients 表
    c.execute('''
        CREATE TABLE IF NOT EXISTS clients (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            description TEXT,
            industry TEXT,
            address TEXT,
            equipment_type TEXT,
            status TEXT,
            contact_person TEXT,
            phone TEXT,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    ''')

    # Follow_ups 表
    c.execute('''
        CREATE TABLE IF NOT EXISTS follow_ups (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            client_id INTEGER,
            note TEXT,
            operator TEXT,
            timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (client_id) REFERENCES clients (id)
        )
    ''')

    # Expenses 表
    c.execute('''
        CREATE TABLE IF NOT EXISTS expenses (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            applicant TEXT,
            apply_date DATE,
            description TEXT,
            location TEXT,
            amount REAL,
            invoice_type_original TEXT,
            invoice_type_offset TEXT,
            remarks TEXT,
            status TEXT DEFAULT '待审核',
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    ''')

    # 默认管理员
    c.execute('SELECT count(*) FROM users')
    if c.fetchone()[0] == 0:
        default_pwd_hash = hashlib.sha256("Yu@crm123".encode()).hexdigest()
        c.execute(
            'INSERT INTO users (username, password_hash, role, permissions) VALUES (?, ?, ?, ?)',
            ('admin', default_pwd_hash, 'admin', 'view,edit,delete')
        )
        print("系统初始化：默认管理员账户已创建")
    
    conn.commit()
    conn.close()

init_db()

# ==========================================
# 3. 工具函数
# ==========================================

def hash_password(password):
    return hashlib.sha256(password.encode()).hexdigest()

def verify_user(username, password):
    conn = get_db_connection()
    c = conn.cursor()
    c.execute('SELECT * FROM users WHERE username = ?', (username,))
    user = c.fetchone()
    conn.close()
    if user and user['password_hash'] == hash_password(password):
        return user
    return None

def run_query(query, params=(), fetch=False):
    conn = get_db_connection()
    try:
        c = conn.cursor()
        c.execute(query, params)
        if fetch:
            data = c.fetchall()
            return data
        conn.commit()
        return True
    except Exception as e:
        st.error(f"数据库操作错误: {e}")
        return False
    finally:
        conn.close()

def to_excel(df):
    output = io.BytesIO()
    with pd.ExcelWriter(output, engine='openpyxl') as writer:
        df.to_excel(writer, index=False, sheet_name='Sheet1')
    processed_data = output.getvalue()
    return processed_data

# ==========================================
# 4. 页面功能模块
# ==========================================

def login_page():
    st.markdown("<h1 style='text-align: center;'>天津硕博霖 CRM 系统登录</h1>", unsafe_allow_html=True)
    col1, col2, col3 = st.columns([1, 2, 1])
    with col2:
        with st.form("login_form"):
            username = st.text_input("用户名")
            password = st.text_input("密码", type="password")
            submitted = st.form_submit_button("登录", use_container_width=True)
            if submitted:
                user = verify_user(username, password)
                if user:
                    st.session_state['logged_in'] = True
                    st.session_state['username'] = user['username']
                    st.session_state['role'] = user['role']
                    st.session_state['permissions'] = user['permissions']
                    st.rerun()
                else:
                    st.error("用户名或密码错误")

def sidebar_menu():
    st.sidebar.title(f"欢迎, {st.session_state['username']}")
    st.sidebar.markdown("---")
    menu = st.sidebar.radio("功能导航", ["客户录入", "客户列表与跟进", "经营看板", "报销管理"])
    
    # 管理员菜单
    if st.session_state['role'] == 'admin':
        st.sidebar.markdown("---")
        with st.sidebar.expander("管理员工具 (用户管理)"):
            tab_add, tab_manage = st.tabs(["新增", "管理"])
            with tab_add:
                with st.form("add_user_form"):
                    new_u = st.text_input("用户名")
                    new_p = st.text_input("密码", type="password")
                    new_r = st.selectbox("角色", ["user", "admin"])
                    new_perm = st.text_input("权限", value="view,edit")
                    if st.form_submit_button("创建"):
                        if new_u and new_p:
                            run_query('INSERT INTO users (username, password_hash, role, permissions) VALUES (?, ?, ?, ?)', 
                                      (new_u, hash_password(new_p), new_r, new_perm))
                            st.success("创建成功")
            with tab_manage:
                conn = get_db_connection()
                users_df = pd.read_sql("SELECT username FROM users", conn)
                conn.close()
                target_user = st.selectbox("选择用户", users_df['username'])
                if target_user:
                    new_pwd = st.text_input("重置密码", type="password")
                    if st.button("保存密码"):
                         run_query("UPDATE users SET password_hash=? WHERE username=?", (hash_password(new_pwd), target_user))
                         st.success("密码已重置")
                    if st.button("删除用户", type="primary"):
                        if target_user != st.session_state['username']:
                            run_query("DELETE FROM users WHERE username=?", (target_user,))
                            st.rerun()
    
    st.sidebar.markdown("---")
    if st.sidebar.button("退出登录"):
        st.session_state.clear()
        st.rerun()
    return menu

# --- 业务模块：客户录入 ---
def page_new_client():
    st.header("新增客户录入")
    with st.form("new_client"):
        col1, col2 = st.columns(2)
        with col1:
            name = st.text_input("客户名称 (必填)")
            industry = st.selectbox("所属行业", INDUSTRIES)
            contact = st.text_input("联系人")
            phone = st.text_input("联系电话")
        with col2:
            eq_type = st.selectbox("关键设备类型", EQUIPMENT_TYPES)
            status = st.selectbox("目前状态", STATUS_OPTIONS)
            address = st.text_input("地址")
            desc = st.text_area("备注")
        if st.form_submit_button("提交录入") and name:
            run_query('INSERT INTO clients (name, description, industry, address, equipment_type, status, contact_person, phone) VALUES (?,?,?,?,?,?,?,?)',
                      (name, desc, industry, address, eq_type, status, contact, phone))
            st.success(f"客户 {name} 已录入")

# --- 业务模块：客户列表与跟进 ---
def page_client_hub():
    st.header("客户列表与跟进中心")
    search = st.text_input("搜索客户", "")
    conn = get_db_connection()
    sql = "SELECT * FROM clients WHERE name LIKE ? ORDER BY created_at DESC" if search else "SELECT * FROM clients ORDER BY created_at DESC"
    df = pd.read_sql(sql, conn, params=(f'%{search}%',) if search else ())
    conn.close()
    
    st.dataframe(df, use_container_width=True, height=200, hide_index=True, column_config={"id":"ID", "name":"名称", "status":"状态"})
    
    if not df.empty:
        opts = {row['id']: f"{row['name']}" for _, row in df.iterrows()}
        sel_id = st.selectbox("选择客户查看详情", options=list(opts.keys()), format_func=lambda x: opts[x])
        if sel_id:
            tab1, tab2 = st.tabs(["基础信息", "跟进记录"])
            with tab1:
                conn = get_db_connection()
                info = pd.read_sql("SELECT * FROM clients WHERE id=?", conn, params=(sel_id,)).iloc[0]
                conn.close()
                can_edit = "edit" in st.session_state['permissions'] or st.session_state['role'] == "admin"
                with st.form("edit_c"):
                    c1, c2 = st.columns(2)
                    nm = c1.text_input("名称", info['name'], disabled=not can_edit)
                    stt = c2.selectbox("状态", STATUS_OPTIONS, index=STATUS_OPTIONS.index(info['status']) if info['status'] in STATUS_OPTIONS else 0, disabled=not can_edit)
                    dsc = st.text_area("备注", info['description'], disabled=not can_edit)
                    if can_edit:
                         s1, s2 = st.columns(2)
                         with s1:
                             if st.form_submit_button("保存修改"):
                                run_query("UPDATE clients SET name=?, status=?, description=? WHERE id=?", (nm, stt, dsc, sel_id))
                                st.success("已更新")
                                st.rerun()
                         with s2:
                             if st.session_state['role'] == 'admin':
                                 if st.form_submit_button("删除客户", type="primary"):
                                     run_query("DELETE FROM follow_ups WHERE client_id=?", (sel_id,))
                                     run_query("DELETE FROM clients WHERE id=?", (sel_id,))
                                     st.success("已删除")
                                     st.rerun()

            with tab2:
                with st.form("add_f"):
                    note = st.text_area("新跟进")
                    if st.form_submit_button("添加") and note:
                        run_query("INSERT INTO follow_ups (client_id, note, operator) VALUES (?,?,?)", (sel_id, note, st.session_state['username']))
                        st.success("已添加")
                        st.rerun()
                conn = get_db_connection()
                hist = pd.read_sql("SELECT * FROM follow_ups WHERE client_id=? ORDER BY timestamp DESC", conn, params=(sel_id,))
                conn.close()
                for _, r in hist.iterrows():
                    st.info(f"{r['timestamp']} | {r['operator']}: {r['note']}")
                    if r['operator'] == st.session_state['username'] or st.session_state['role'] == 'admin':
                        if st.button("删除", key=f"del_f_{r['id']}"):
                            run_query("DELETE FROM follow_ups WHERE id=?", (r['id'],))
                            st.rerun()

# --- 业务模块：经营看板 ---
def page_dashboard():
    st.header("经营看板")
    conn = get_db_connection()
    try:
        df = pd.read_sql("SELECT status, count(*) as count FROM clients GROUP BY status", conn)
        if not df.empty:
            fig = px.pie(df, values='count', names='status', title='客户状态分布', hole=0.4)
            st.plotly_chart(fig, use_container_width=True)
    finally:
        conn.close()

# ==========================================
# 5. 报销管理模块 (v1.3.7: 批量审批 + 信息完善版)
# ==========================================

def page_expenses():
    st.header("销售报销管理")
    
    is_admin = st.session_state['role'] == 'admin'
    
    if 'expense_buffer' not in st.session_state:
        st.session_state['expense_buffer'] = []
    
    tabs = ["新建申请 (批量)", "我的记录"]
    if is_admin:
        tabs = ["新建申请 (批量)", "我的记录", "审批中心", "统计与全局维护"]
    
    active_tab = st.tabs(tabs)
    
    # --- Tab 1: 新建申请 ---
    with active_tab[0]:
        col_input, col_preview = st.columns([1, 1])
        with col_input:
            st.markdown("#### 1. 录入明细")
            st.caption("填完点击“加入列表”，最后统一提交。")
            with st.form("expense_entry_form", clear_on_submit=True):
                r1, r2 = st.columns(2)
                app_date = r1.date_input("发生日期", datetime.now())
                amount = r2.number_input("金额 (元)", min_value=0.0, step=1.0)
                desc = st.text_input("费用描述", placeholder="例如: 招待天津港客户")
                r3_1, r3_2 = st.columns(2)
                location = r3_1.text_input("消费地点")
                invoice_orig = r3_1.selectbox("原始票据", EXPENSE_TYPES_ORIGINAL)
                invoice_offset = st.selectbox("冲顶票据", EXPENSE_TYPES_OFFSET)
                remarks = st.text_area("备注", height=60)
                
                if st.form_submit_button("[+] 加入待提交列表"):
                    if amount > 0 and desc:
                        st.session_state['expense_buffer'].append({
                            "apply_date": app_date, "amount": amount, "description": desc,
                            "location": location, "invoice_type_original": invoice_orig,
                            "invoice_type_offset": invoice_offset, "remarks": remarks
                        })
                        st.success("已加入！")
                        st.rerun()
                    else:
                        st.warning("请补全金额和描述")

        with col_preview:
            st.markdown(f"#### 2. 待提交列表 ({len(st.session_state['expense_buffer'])})")
            if st.session_state['expense_buffer']:
                for i, item in enumerate(st.session_state['expense_buffer']):
                    with st.container():
                        c_info, c_del = st.columns([5, 1])
                        with c_info:
                            st.markdown(f"**¥{item['amount']}** | {item['description']}")
                            st.caption(f"{item['apply_date']} | 原:{item['invoice_type_original']} / 冲:{item['invoice_type_offset']}")
                        with c_del:
                            if st.button("删除", key=f"del_item_{i}", help="删除此条"):
                                st.session_state['expense_buffer'].pop(i)
                                st.rerun()
                        st.divider()
                c1, c2 = st.columns([2,1])
                with c1:
                    if st.button("批量提交所有", type="primary", use_container_width=True):
                        conn = get_db_connection()
                        c = conn.cursor()
                        try:
                            for item in st.session_state['expense_buffer']:
                                c.execute('''INSERT INTO expenses (applicant, apply_date, description, location, amount, 
                                invoice_type_original, invoice_type_offset, remarks, status) VALUES (?,?,?,?,?,?,?,?,?)''', 
                                (st.session_state['username'], item['apply_date'], item['description'], item['location'], 
                                item['amount'], item['invoice_type_original'], item['invoice_type_offset'], item['remarks'], '待审核'))
                            conn.commit()
                            st.session_state['expense_buffer'] = []
                            st.success("提交成功！")
                            st.rerun()
                        finally: conn.close()
                with c2:
                    if st.button("清空列表"):
                        st.session_state['expense_buffer'] = []
                        st.rerun()
            else:
                st.info("暂无待提交记录。")

    # --- Tab 2: 我的记录 ---
    with active_tab[1]:
        conn = get_db_connection()
        my_df = pd.read_sql("SELECT * FROM expenses WHERE applicant=? ORDER BY apply_date DESC", conn, params=(st.session_state['username'],))
        conn.close()
        
        st.dataframe(my_df, use_container_width=True, hide_index=True)
        
        pending = my_df[my_df['status']=='待审核']
        if not pending.empty:
            st.markdown("---")
            with st.expander("撤回申请 (退回修改)", expanded=True):
                st.caption("撤回后，单据将退回【新建申请】列表，方便修改后重新提交。")
                del_id = st.selectbox("选择要撤回的记录", pending['id'], format_func=lambda x: f"#{x} - ¥{pending[pending['id']==x]['amount'].values[0]} - {pending[pending['id']==x]['description'].values[0]}")
                if st.button("撤回并修改"):
                    row_data = pending[pending['id'] == del_id].iloc[0]
                    st.session_state['expense_buffer'].append({
                        "apply_date": row_data['apply_date'], "amount": row_data['amount'],
                        "description": row_data['description'], "location": row_data['location'],
                        "invoice_type_original": row_data['invoice_type_original'],
                        "invoice_type_offset": row_data['invoice_type_offset'], "remarks": row_data['remarks']
                    })
                    run_query("DELETE FROM expenses WHERE id=?", (del_id,))
                    st.success(f"单据 #{del_id} 已撤回！")
                    st.rerun()
        
        st.markdown("---")
        st.markdown("#### 打印我的报销单")
        if not my_df.empty:
            my_print_ids = st.multiselect("选择要打印的明细", my_df['id'], format_func=lambda x: f"#{x} | {my_df[my_df['id']==x]['apply_date'].values[0]} | ¥{my_df[my_df['id']==x]['amount'].values[0]}", key="print_multiselect_user")
            
            if my_print_ids:
                print_df = my_df[my_df['id'].isin(my_print_ids)]
                total_print_amt = print_df['amount'].sum()
                
                html_content = """
                <style>
                    @media print {
                        .stApp { visibility: hidden; height: 0; overflow: hidden; }
                        .invoice-box, .invoice-box * { visibility: visible; }
                        .invoice-box { position: absolute; left: 0; top: 0; width: 100%; margin: 0; padding: 30px; border: none; }
                        @page { margin: 0; }
                        body { margin: 1cm; }
                    }
                    body { display: flex; justify-content: center; background-color: white; }
                    .invoice-box { width: 750px; padding: 30px; border: 2px solid #000; font-family: 'SimHei', Arial, sans-serif; color: #000; background-color: white; margin-top: 20px; }
                    .header { text-align: center; margin-bottom: 25px; }
                    .header h2 { margin: 0; padding-bottom: 10px; border-bottom: 2px solid #000; letter-spacing: 2px; }
                    table { width: 100%; border-collapse: collapse; margin-bottom: 20px; font-size: 14px; }
                    th { background: #f0f0f0; font-weight: bold; border: 1px solid #000; padding: 10px; text-align: center; }
                    td { border: 1px solid #000; padding: 8px; text-align: center; vertical-align: middle; }
                    .col-desc { text-align: left; }
                    .total-row td { border: 2px solid #000; background: #fff; font-weight: bold; font-size: 16px; }
                    .footer { display: flex; justify-content: space-between; margin-top: 60px; padding: 0 10px; }
                    .sig-line { width: 30%; border-top: 1px solid #000; text-align: center; padding-top: 10px; font-size: 15px; }
                </style>
                <div class="invoice-box">
                    <div class="header">
                        <h2>天津硕博霖 - 费用报销汇总单</h2>
                        <p style="text-align:right; margin-top:8px; font-size:14px;">打印日期: """ + datetime.now().strftime("%Y-%m-%d") + """</p>
                    </div>
                    <table>
                        <thead>
                            <tr>
                                <th width="10%">单号</th>
                                <th width="12%">申请人</th>
                                <th width="12%">日期</th>
                                <th width="36%">费用描述 / 票据类型</th>
                                <th width="15%">消费地点</th>
                                <th width="15%">金额 (元)</th>
                            </tr>
                        </thead>
                        <tbody>
                """
                for _, row in print_df.iterrows():
                    html_content += f"""
                    <tr>
                        <td>#{row['id']}</td>
                        <td>{row['applicant']}</td>
                        <td>{row['apply_date']}</td>
                        <td class="col-desc">
                            {row['description']}<br>
                            <span style="font-size:12px; color:#555;">(原:{row['invoice_type_original']} / 冲:{row['invoice_type_offset']})</span>
                        </td>
                        <td>{row['location']}</td>
                        <td>¥{row['amount']}</td>
                    </tr>
                    """
                html_content += f"""
                    <tr class="total-row">
                        <td colspan="5" style="text-align:right; padding-right:15px;">合计金额 (Total)</td>
                        <td>¥{total_print_amt:,.2f}</td>
                    </tr>
                </tbody>
                </table>
                <div class="footer">
                    <div class="sig-line">申请人签字</div>
                    <div class="sig-line">部门领导审批</div>
                    <div class="sig-line">主管会计审核</div>
                </div>
                </div>
                """
                st.components.v1.html(html_content, height=900, scrolling=True)

    if is_admin:
        # --- Tab 3: 审批中心 (核心更新区域) ---
        with active_tab[2]:
            st.markdown("#### 批量审批中心")
            
            # 1. 获取所有待审核数据
            conn = get_db_connection()
            # 读取所有字段
            pending_df = pd.read_sql("SELECT * FROM expenses WHERE status='待审核' ORDER BY apply_date", conn)
            conn.close()
            
            if not pending_df.empty:
                # 2. 增加一个 'Select' 列，默认为 False，放在第一列
                pending_df.insert(0, "选择", False)
                
                # 3. 使用 data_editor 实现可勾选的表格
                st.caption("请勾选左侧复选框选择单据，然后点击下方按钮批量处理。可直接在表格中查看完整信息。")
                edited_df = st.data_editor(
                    pending_df,
                    column_config={
                        "选择": st.column_config.CheckboxColumn(
                            "选择",
                            help="勾选以进行批量操作",
                            default=False,
                        ),
                        "id": st.column_config.NumberColumn("单号", width="small", help="系统唯一ID"),
                        "applicant": st.column_config.TextColumn("申请人", width="small"),
                        "apply_date": st.column_config.DateColumn("日期", format="YYYY-MM-DD"),
                        "amount": st.column_config.NumberColumn("金额", format="¥%.2f"),
                        "description": st.column_config.TextColumn("费用描述", width="medium"),
                        "location": st.column_config.TextColumn("地点"),
                        "invoice_type_original": st.column_config.TextColumn("原始票据"),
                        "invoice_type_offset": st.column_config.TextColumn("冲顶票据"),
                        "remarks": st.column_config.TextColumn("备注", width="medium"),
                        "status": st.column_config.TextColumn("状态", disabled=True),
                        "created_at": st.column_config.DatetimeColumn("提交时间", format="D MMM, HH:mm", disabled=True),
                    },
                    disabled=["id", "applicant", "apply_date", "amount", "description", 
                              "location", "invoice_type_original", "invoice_type_offset", 
                              "remarks", "status", "created_at"], # 禁止修改数据，只允许勾选
                    hide_index=True,
                    use_container_width=True
                )
                
                # 4. 批量操作按钮
                # 筛选出被勾选的行
                selected_rows = edited_df[edited_df["选择"] == True]
                
                col_approve, col_reject = st.columns([1, 1])
                
                with col_approve:
                    # 批量通过按钮
                    if st.button(f"✅ 批量通过 ({len(selected_rows)}项)", type="primary", use_container_width=True):
                        if not selected_rows.empty:
                            id_list = selected_rows['id'].tolist()
                            # 批量更新数据库
                            conn = get_db_connection()
                            c = conn.cursor()
                            # 使用 executemany 或者循环更新，这里为了安全用参数化查询
                            placeholders = ', '.join(['?'] * len(id_list))
                            sql = f"UPDATE expenses SET status='已通过' WHERE id IN ({placeholders})"
                            c.execute(sql, id_list)
                            conn.commit()
                            conn.close()
                            
                            st.success(f"已通过 {len(id_list)} 条申请！")
                            st.rerun()
                        else:
                            st.warning("请先勾选需要通过的单据。")
                            
                with col_reject:
                    # 批量驳回按钮
                    if st.button(f"❌ 批量驳回 ({len(selected_rows)}项)", use_container_width=True):
                        if not selected_rows.empty:
                            id_list = selected_rows['id'].tolist()
                            conn = get_db_connection()
                            c = conn.cursor()
                            placeholders = ', '.join(['?'] * len(id_list))
                            sql = f"UPDATE expenses SET status='已驳回' WHERE id IN ({placeholders})"
                            c.execute(sql, id_list)
                            conn.commit()
                            conn.close()
                            
                            st.error(f"已驳回 {len(id_list)} 条申请！")
                            st.rerun()
                        else:
                            st.warning("请先勾选需要驳回的单据。")
            else:
                st.info("目前没有待审批的单据。")

        # --- Tab 4: 统计与维护 ---
        with active_tab[3]:
            st.markdown("#### 全局统计与导出")
            start_d = st.date_input("起始日期", datetime.now().replace(day=1))
            conn = get_db_connection()
            all_df = pd.read_sql("SELECT * FROM expenses WHERE apply_date >= ? ORDER BY apply_date DESC", conn, params=(str(start_d),))
            conn.close()
            
            if not all_df.empty:
                st.metric("总金额", f"¥{all_df['amount'].sum():,.2f}")
                st.download_button("导出Excel", to_excel(all_df), "所有报销.xlsx")
                
                with st.expander("全局单据维护", expanded=True):
                    opts = {row['id']: f"#{row['id']} {row['applicant']} ¥{row['amount']}" for _, row in all_df.iterrows()}
                    mid = st.selectbox("选择单据", list(opts.keys()), format_func=lambda x: opts[x])
                    c1, c2 = st.columns(2)
                    if c1.button("重置为待审核"): run_query("UPDATE expenses SET status='待审核' WHERE id=?", (mid,)); st.rerun()
                    if c2.button("彻底删除", type="primary"): run_query("DELETE FROM expenses WHERE id=?", (mid,)); st.rerun()
# ==========================================
# 6. 主程序入口
# ==========================================

def main():
    if 'logged_in' not in st.session_state:
        st.session_state['logged_in'] = False

    if not st.session_state['logged_in']:
        login_page()
    else:
        selected_page = sidebar_menu()
        if selected_page == "客户录入":
            page_new_client()
        elif selected_page == "客户列表与跟进":
            page_client_hub()
        elif selected_page == "经营看板":
            page_dashboard()
        elif selected_page == "报销管理":
            page_expenses()

if __name__ == '__main__':
    main()