from flask import Flask, render_template, jsonify, request, g, abort
from database import get_db, init_db
import os
from datetime import datetime

app = Flask(__name__)
app.config['JSON_AS_ASCII'] = False

STATUS_MAP = {
    'open': '招标中', 'evaluating': '评审中', 'closed': '已结束',
    'pending': '待提交', 'submitted': '已提交', 'won': '已中标', 'lost': '未中标',
}

PER_PAGE = 10


def status_text(status):
    return STATUS_MAP.get(status, status)


def bid_status_text(status):
    return STATUS_MAP.get(status, status)


app.jinja_env.globals['status_text'] = status_text
app.jinja_env.globals['bid_status_text'] = bid_status_text


@app.context_processor
def inject_globals():
    return {'year': datetime.now().year, 'status_map': STATUS_MAP}


@app.before_request
def before_request():
    g.db = get_db()


@app.teardown_request
def teardown_request(exc):
    db = getattr(g, 'db', None)
    if db is not None:
        db.close()


def get_stats(db):
    return {
        'total_projects': db.execute('SELECT COUNT(*) as c FROM projects').fetchone()['c'],
        'open_projects': db.execute("SELECT COUNT(*) as c FROM projects WHERE status='open'").fetchone()['c'],
        'evaluating': db.execute("SELECT COUNT(*) as c FROM projects WHERE status='evaluating'").fetchone()['c'],
        'closed': db.execute("SELECT COUNT(*) as c FROM projects WHERE status='closed'").fetchone()['c'],
        'total_bids': db.execute('SELECT COUNT(*) as c FROM bids').fetchone()['c'],
        'total_budget': db.execute('SELECT COALESCE(SUM(budget),0) as s FROM projects').fetchone()['s'],
    }


def paginate(rows, page, per_page):
    total = len(rows)
    start = (page - 1) * per_page
    return rows[start:start + per_page], total, (total + per_page - 1) // per_page


@app.route('/')
def index():
    db = g.db
    stats = get_stats(db)
    recent = db.execute('SELECT * FROM projects ORDER BY created_at DESC LIMIT 5').fetchall()
    return render_template('index.html', stats=stats, recent=recent)


@app.route('/api/stats')
def api_stats():
    db = g.db
    stats = get_stats(db)
    by_category = db.execute(
        'SELECT category, COUNT(*) as c, COALESCE(SUM(budget),0) as budget FROM projects GROUP BY category'
    ).fetchall()
    stats['by_category'] = [dict(r) for r in by_category]
    return jsonify(stats)


@app.route('/projects')
def projects():
    db = g.db
    status = request.args.get('status', '')
    search = request.args.get('q', '').strip()
    page = max(1, request.args.get('page', 1, type=int))

    sql = 'SELECT * FROM projects WHERE 1=1'
    params = []
    if status:
        sql += ' AND status=?'
        params.append(status)
    if search:
        sql += ' AND (name LIKE ? OR code LIKE ?)'
        params.extend([f'%{search}%', f'%{search}%'])
    sql += ' ORDER BY created_at DESC'
    rows = db.execute(sql, params).fetchall()
    items, total, pages = paginate(rows, page, PER_PAGE)
    return render_template('projects.html', projects=items, current_status=status,
                           search=search, page=page, pages=pages, total=total)


@app.route('/project/<int:pid>')
def project_detail(pid):
    db = g.db
    project = db.execute('SELECT * FROM projects WHERE id=?', (pid,)).fetchone()
    if not project:
        abort(404)
    bids = db.execute(
        '''SELECT b.*, bi.name as bidder_name, bi.company, bi.qualification,
                  e.technical_score, e.price_score, e.business_score, e.total_score,
                  e.evaluator, e.comment as eval_comment
           FROM bids b
           JOIN bidders bi ON b.bidder_id=bi.id
           LEFT JOIN evaluations e ON e.bid_id=b.id
           WHERE b.project_id=? ORDER BY b.quote_price ASC''', (pid,)
    ).fetchall()
    return render_template('project_detail.html', project=project, bids=bids)


@app.route('/bidders')
def bidders():
    db = g.db
    search = request.args.get('q', '').strip()
    if search:
        rows = db.execute(
            'SELECT * FROM bidders WHERE name LIKE ? OR company LIKE ? ORDER BY name',
            (f'%{search}%', f'%{search}%')
        ).fetchall()
    else:
        rows = db.execute('SELECT * FROM bidders ORDER BY name').fetchall()
    return render_template('bidders.html', bidders=rows, search=search)


@app.route('/statistics')
def statistics():
    db = g.db
    by_category = db.execute(
        'SELECT category, COUNT(*) as c, COALESCE(SUM(budget),0) as budget FROM projects GROUP BY category'
    ).fetchall()
    by_status = db.execute(
        'SELECT status, COUNT(*) as c FROM projects GROUP BY status'
    ).fetchall()
    avg_quote = db.execute(
        '''SELECT p.name, AVG(b.quote_price) as avg_price, COUNT(b.id) as bid_count
           FROM projects p LEFT JOIN bids b ON p.id=b.project_id
           GROUP BY p.id ORDER BY avg_price DESC'''
    ).fetchall()
    top_bidders = db.execute(
        '''SELECT bi.name, COUNT(b.id) as bid_count, AVG(b.quote_price) as avg_price
           FROM bidders bi LEFT JOIN bids b ON bi.id=b.bidder_id
           GROUP BY bi.id ORDER BY bid_count DESC LIMIT 5'''
    ).fetchall()
    return render_template('statistics.html', by_category=by_category, by_status=by_status,
                           avg_quote=avg_quote, top_bidders=top_bidders)


# ===== API: 项目 CRUD =====

@app.route('/api/projects', methods=['GET', 'POST'])
def api_projects():
    db = g.db
    if request.method == 'POST':
        data = request.get_json(silent=True) or {}
        for k in ('name', 'code'):
            if not data.get(k):
                return jsonify({'error': f'缺少必填字段: {k}'}), 400
        try:
            cur = db.execute(
                '''INSERT INTO projects (name, code, category, budget, status, publish_date, deadline, description)
                   VALUES (?,?,?,?,?,?,?,?)''',
                (data['name'], data['code'], data.get('category', ''),
                 float(data.get('budget', 0)), data.get('status', 'open'),
                 data.get('publish_date', ''), data.get('deadline', ''), data.get('description', ''))
            )
            db.commit()
            return jsonify({'id': cur.lastrowid, 'message': '创建成功'}), 201
        except Exception as e:
            db.rollback()
            msg = str(e)
            if 'UNIQUE constraint' in msg:
                msg = '项目编号已存在，请使用其他编号'
            return jsonify({'error': msg}), 400
    rows = db.execute('SELECT * FROM projects ORDER BY created_at DESC').fetchall()
    return jsonify([dict(r) for r in rows])


@app.route('/api/projects/<int:pid>', methods=['GET', 'PUT', 'DELETE'])
def api_project_detail(pid):
    db = g.db
    if request.method == 'DELETE':
        try:
            db.execute('DELETE FROM evaluations WHERE bid_id IN (SELECT id FROM bids WHERE project_id=?)', (pid,))
            db.execute('DELETE FROM bids WHERE project_id=?', (pid,))
            db.execute('DELETE FROM projects WHERE id=?', (pid,))
            db.commit()
            return jsonify({'message': '删除成功'})
        except Exception as e:
            db.rollback()
            return jsonify({'error': str(e)}), 400
    if request.method == 'PUT':
        data = request.get_json(silent=True) or {}
        try:
            db.execute(
                '''UPDATE projects SET name=?, code=?, category=?, budget=?, status=?,
                   publish_date=?, deadline=?, description=? WHERE id=?''',
                (data.get('name', ''), data.get('code', ''), data.get('category', ''),
                 float(data.get('budget', 0)), data.get('status', 'open'),
                 data.get('publish_date', ''), data.get('deadline', ''),
                 data.get('description', ''), pid)
            )
            db.commit()
            return jsonify({'message': '更新成功'})
        except Exception as e:
            db.rollback()
            return jsonify({'error': str(e)}), 400
    row = db.execute('SELECT * FROM projects WHERE id=?', (pid,)).fetchone()
    if not row:
        return jsonify({'error': '项目不存在'}), 404
    return jsonify(dict(row))


# ===== API: 投标方 CRUD =====

@app.route('/api/bidders', methods=['GET', 'POST'])
def api_bidders():
    db = g.db
    if request.method == 'POST':
        data = request.get_json(silent=True) or {}
        if not data.get('name'):
            return jsonify({'error': '缺少必填字段: name'}), 400
        try:
            cur = db.execute(
                '''INSERT INTO bidders (name, company, contact, phone, qualification)
                   VALUES (?,?,?,?,?)''',
                (data['name'], data.get('company', ''), data.get('contact', ''),
                 data.get('phone', ''), data.get('qualification', ''))
            )
            db.commit()
            return jsonify({'id': cur.lastrowid, 'message': '创建成功'}), 201
        except Exception as e:
            db.rollback()
            return jsonify({'error': str(e)}), 400
    rows = db.execute('SELECT * FROM bidders ORDER BY name').fetchall()
    return jsonify([dict(r) for r in rows])


@app.route('/api/bidders/<int:bid>', methods=['GET', 'PUT', 'DELETE'])
def api_bidder_detail(bid):
    db = g.db
    if request.method == 'DELETE':
        try:
            db.execute('DELETE FROM evaluations WHERE bid_id IN (SELECT id FROM bids WHERE bidder_id=?)', (bid,))
            db.execute('DELETE FROM bids WHERE bidder_id=?', (bid,))
            db.execute('DELETE FROM bidders WHERE id=?', (bid,))
            db.commit()
            return jsonify({'message': '删除成功'})
        except Exception as e:
            db.rollback()
            return jsonify({'error': str(e)}), 400
    if request.method == 'PUT':
        data = request.get_json(silent=True) or {}
        try:
            db.execute(
                '''UPDATE bidders SET name=?, company=?, contact=?, phone=?, qualification=? WHERE id=?''',
                (data.get('name', ''), data.get('company', ''), data.get('contact', ''),
                 data.get('phone', ''), data.get('qualification', ''), bid)
            )
            db.commit()
            return jsonify({'message': '更新成功'})
        except Exception as e:
            db.rollback()
            return jsonify({'error': str(e)}), 400
    row = db.execute('SELECT * FROM bidders WHERE id=?', (bid,)).fetchone()
    if not row:
        return jsonify({'error': '投标方不存在'}), 404
    return jsonify(dict(row))


# ===== API: 投标 CRUD =====

@app.route('/api/bids', methods=['GET', 'POST'])
def api_bids():
    db = g.db
    if request.method == 'POST':
        data = request.get_json(silent=True) or {}
        for k in ('project_id', 'bidder_id'):
            if not data.get(k):
                return jsonify({'error': f'缺少必填字段: {k}'}), 400
        try:
            cur = db.execute(
                '''INSERT INTO bids (project_id, bidder_id, quote_price, quote_duration, status, remark)
                   VALUES (?,?,?,?,?,?)''',
                (data['project_id'], data['bidder_id'],
                 float(data.get('quote_price', 0)), int(data.get('quote_duration', 0)),
                 data.get('status', 'pending'), data.get('remark', ''))
            )
            db.commit()
            return jsonify({'id': cur.lastrowid, 'message': '投标提交成功'}), 201
        except Exception as e:
            db.rollback()
            return jsonify({'error': str(e)}), 400
    pid = request.args.get('project_id', type=int)
    if pid:
        rows = db.execute('SELECT * FROM bids WHERE project_id=? ORDER BY quote_price', (pid,)).fetchall()
    else:
        rows = db.execute('SELECT * FROM bids ORDER BY submit_date DESC').fetchall()
    return jsonify([dict(r) for r in rows])


@app.route('/api/bids/<int:bid_id>', methods=['GET', 'PUT', 'DELETE'])
def api_bid_detail(bid_id):
    db = g.db
    if request.method == 'DELETE':
        try:
            db.execute('DELETE FROM evaluations WHERE bid_id=?', (bid_id,))
            db.execute('DELETE FROM bids WHERE id=?', (bid_id,))
            db.commit()
            return jsonify({'message': '删除成功'})
        except Exception as e:
            db.rollback()
            return jsonify({'error': str(e)}), 400
    if request.method == 'PUT':
        data = request.get_json(silent=True) or {}
        try:
            db.execute(
                '''UPDATE bids SET project_id=?, bidder_id=?, quote_price=?, quote_duration=?, status=?, remark=? WHERE id=?''',
                (data.get('project_id'), data.get('bidder_id'),
                 float(data.get('quote_price', 0)), int(data.get('quote_duration', 0)),
                 data.get('status', 'pending'), data.get('remark', ''), bid_id)
            )
            db.commit()
            return jsonify({'message': '更新成功'})
        except Exception as e:
            db.rollback()
            return jsonify({'error': str(e)}), 400
    row = db.execute('SELECT * FROM bids WHERE id=?', (bid_id,)).fetchone()
    if not row:
        return jsonify({'error': '投标记录不存在'}), 404
    return jsonify(dict(row))


# ===== API: 评审 CRUD =====

@app.route('/api/bids/<int:bid_id>/evaluate', methods=['POST'])
def api_evaluate(bid_id):
    db = g.db
    data = request.get_json(silent=True) or {}
    for k in ('technical_score', 'price_score', 'business_score'):
        if k not in data:
            return jsonify({'error': f'缺少必填字段: {k}'}), 400
    total = (float(data['technical_score']) * 0.4 +
             float(data['price_score']) * 0.3 +
             float(data['business_score']) * 0.3)
    try:
        existing = db.execute('SELECT id FROM evaluations WHERE bid_id=?', (bid_id,)).fetchone()
        if existing:
            db.execute(
                '''UPDATE evaluations SET technical_score=?, price_score=?, business_score=?, total_score=?, evaluator=?, comment=? WHERE bid_id=?''',
                (float(data['technical_score']), float(data['price_score']),
                 float(data['business_score']), round(total, 1),
                 data.get('evaluator', ''), data.get('comment', ''), bid_id)
            )
            db.commit()
            return jsonify({'total_score': round(total, 1), 'message': '评审更新成功'})
        cur = db.execute(
            '''INSERT INTO evaluations (bid_id, technical_score, price_score, business_score, total_score, evaluator, comment)
               VALUES (?,?,?,?,?,?,?)''',
            (bid_id, float(data['technical_score']), float(data['price_score']),
             float(data['business_score']), round(total, 1),
             data.get('evaluator', ''), data.get('comment', ''))
        )
        db.commit()
        return jsonify({'id': cur.lastrowid, 'total_score': round(total, 1), 'message': '评审完成'}), 201
    except Exception as e:
        db.rollback()
        return jsonify({'error': str(e)}), 400


# ===== 后台管理 =====

@app.route('/admin')
def admin_dashboard():
    db = g.db
    stats = get_stats(db)
    stats['total_bidders'] = db.execute('SELECT COUNT(*) as c FROM bidders').fetchone()['c']
    stats['total_evaluations'] = db.execute('SELECT COUNT(*) as c FROM evaluations').fetchone()['c']
    recent_projects = db.execute('SELECT * FROM projects ORDER BY created_at DESC LIMIT 5').fetchall()
    recent_bids = db.execute(
        '''SELECT b.*, p.name as project_name, bi.name as bidder_name
           FROM bids b JOIN projects p ON b.project_id=p.id JOIN bidders bi ON b.bidder_id=bi.id
           ORDER BY b.submit_date DESC LIMIT 5'''
    ).fetchall()
    return render_template('admin/dashboard.html', stats=stats,
                           recent_projects=recent_projects, recent_bids=recent_bids)


@app.route('/admin/projects')
def admin_projects():
    db = g.db
    rows = db.execute('SELECT * FROM projects ORDER BY created_at DESC').fetchall()
    return render_template('admin/projects.html', projects=[dict(r) for r in rows])


@app.route('/admin/bidders')
def admin_bidders():
    db = g.db
    rows = db.execute('SELECT * FROM bidders ORDER BY name').fetchall()
    return render_template('admin/bidders.html', bidders=[dict(r) for r in rows])


@app.route('/admin/bids')
def admin_bids():
    db = g.db
    rows = db.execute(
        '''SELECT b.*, p.name as project_name, p.code as project_code, bi.name as bidder_name
           FROM bids b JOIN projects p ON b.project_id=p.id JOIN bidders bi ON b.bidder_id=bi.id
           ORDER BY b.submit_date DESC'''
    ).fetchall()
    projects = db.execute('SELECT id, name FROM projects ORDER BY name').fetchall()
    bidders = db.execute('SELECT id, name FROM bidders ORDER BY name').fetchall()
    return render_template('admin/bids.html', bids=[dict(r) for r in rows], projects=projects, bidders=bidders)


@app.route('/admin/evaluations')
def admin_evaluations():
    db = g.db
    rows = db.execute(
        '''SELECT e.*, b.id as bid_id, p.name as project_name, bi.name as bidder_name, b.quote_price
           FROM evaluations e JOIN bids b ON e.bid_id=b.id
           JOIN projects p ON b.project_id=p.id JOIN bidders bi ON b.bidder_id=bi.id
           ORDER BY e.eval_date DESC'''
    ).fetchall()
    return render_template('admin/evaluations.html', evaluations=rows)


@app.errorhandler(404)
def not_found(e):
    if request.path.startswith('/api/'):
        return jsonify({'error': '资源不存在'}), 404
    return render_template('404.html'), 404


@app.errorhandler(500)
def server_error(e):
    if request.path.startswith('/api/'):
        return jsonify({'error': '服务器内部错误'}), 500
    return render_template('500.html'), 500


if __name__ == '__main__':
    init_db()
    port = int(os.environ.get('PORT', 5000))
    debug = os.environ.get('FLASK_DEBUG', '0') == '1'
    app.run(host='0.0.0.0', port=port, debug=debug)