from flask import request, jsonify
from utils.database import get_db
from utils.auth import get_user_id_from_token, build_location, get_admin_from_token
from utils.helpers import error_response, build_search_conditions
from utils.config import get_config_value
from utils.sensitive_filter import check_sensitive_words

def check_skill_permission(skill_id, user_id, require_owner=True):
    """
    检查用户是否有权限操作技能
    返回 (has_permission, is_admin, error_message)
    """
    conn = get_db()
    cursor = conn.cursor()
    try:
        cursor.execute('SELECT publisher_id FROM skills WHERE id = ? AND deleted_at IS NULL', (skill_id,))
        skill = cursor.fetchone()
        
        if not skill:
            return False, False, '技能不存在'
        
        # 检查是否是管理员
        is_admin = get_admin_from_token(None, user_id)
        
        # 如果是管理员，直接放行
        if is_admin:
            return True, True, None
        
        # 如果需要所有者权限，检查是否是发布者
        if require_owner and skill['publisher_id'] != user_id:
            return False, False, '无权操作此技能'
        
        return True, False, None
    finally:
        conn.close()

def register_skill_routes(app):
    @app.route('/api/skills', methods=['GET'])
    def get_skills():
        search = request.args.get('search')
        page = int(request.args.get('page', 1))
        limit = int(request.args.get('limit', 10))
        offset = (page - 1) * limit
        
        conn = get_db()
        cursor = conn.cursor()
        try:
            search_fields = ['s.title', 's.description', 's.province', 's.city', 's.district', 's.street']
            search_cond, search_params = build_search_conditions(search_fields, search)
            
            if search_cond:
                cursor.execute(f'SELECT COUNT(*) FROM skills s WHERE s.deleted_at IS NULL{search_cond}', search_params)
                total = cursor.fetchone()[0]
                
                cursor.execute(f'''
                    SELECT s.*, u.phone as publisher_phone
                    FROM skills s
                    LEFT JOIN users u ON s.publisher_id = u.id
                    WHERE s.deleted_at IS NULL{search_cond}
                    ORDER BY s.created_at DESC LIMIT ? OFFSET ?
                ''', [*search_params, limit, offset])
            else:
                cursor.execute('SELECT COUNT(*) FROM skills WHERE deleted_at IS NULL')
                total = cursor.fetchone()[0]
                
                cursor.execute('''
                    SELECT s.*, u.phone as publisher_phone
                    FROM skills s
                    LEFT JOIN users u ON s.publisher_id = u.id
                    WHERE s.deleted_at IS NULL ORDER BY s.created_at DESC LIMIT ? OFFSET ?
                ''', (limit, offset))
        
            rows = cursor.fetchall()

            publisher_ids = list(set(row['publisher_id'] for row in rows if row['publisher_id']))
            acceptor_rating_map = get_bulk_user_rating_rates(publisher_ids)

            items = []
            for row in rows:
                skill = dict(row)
                skill['acceptor_rating_rate'] = acceptor_rating_map.get(row['publisher_id'], '-')
                skill['location'] = build_location(skill, include_detail=True)
                items.append(skill)

            return jsonify({
                'items': items,
                'total': total,
                'page': page,
                'limit': limit
            })
        finally:
            conn.close()

    @app.route('/api/skills/<int:skill_id>', methods=['GET'])
    def get_skill(skill_id):
        conn = get_db()
        cursor = conn.cursor()
        try:
            cursor.execute('SELECT * FROM skills WHERE id = ? AND deleted_at IS NULL', (skill_id,))
            row = cursor.fetchone()
            
            if not row:
                return error_response('技能不存在')
            
            result = dict(row)
            result['location'] = build_location(result, include_detail=True)
            
            return jsonify(result)
        finally:
            conn.close()

    @app.route('/api/skills/count', methods=['GET'])
    def get_skill_count():
        auth_header = request.headers.get('Authorization')
        if not auth_header or not auth_header.startswith('Bearer '):
            return error_response('未登录', 401)
        
        token = auth_header.split(' ')[1]
        user_id = get_user_id_from_token(token)
        
        if not user_id:
            return error_response('登录已过期', 401)
        
        conn = get_db()
        cursor = conn.cursor()
        try:
            cursor.execute('SELECT COUNT(*) FROM skills WHERE publisher_id = ? AND deleted_at IS NULL', (user_id,))
            current_count = cursor.fetchone()[0]

            max_skills = int(get_config_value('max_publish_skills', '1'))
            
            return jsonify({'count': current_count, 'max': max_skills})
        finally:
            conn.close()

    @app.route('/api/skills', methods=['POST'])
    def create_skill():
        data = request.json

        # 敏感词过滤
        found, word = check_sensitive_words(
            data.get('title'),
            data.get('description'),
            data.get('detail_location')
        )
        if found:
            return error_response('文字中存在敏感词，请修改后再发布')

        conn = get_db()
        cursor = conn.cursor()
        try:
            publisher_id = None
            auth_token = request.headers.get('Authorization', '').replace('Bearer ', '')
            if auth_token:
                cursor.execute('SELECT user_id FROM sessions WHERE token = ?', (auth_token,))
                session = cursor.fetchone()
                if session:
                    cursor.execute('SELECT * FROM users WHERE id = ? AND deleted_at IS NULL', (session['user_id'],))
                    user = cursor.fetchone()
                    if user:
                        if user['enabled'] != 1:
                            return error_response('账户已被禁用，无法发布技能', 403)
                        
                        publisher_id = user['id']
                        
                        max_skills = int(get_config_value('max_publish_skills', '1'))
                        cursor.execute('SELECT COUNT(*) FROM skills WHERE publisher_id = ? AND deleted_at IS NULL', (publisher_id,))
                        current_count = cursor.fetchone()[0]
                        
                        if current_count >= max_skills:
                            return error_response(f'已发布的技能数最多为{max_skills}个，暂时无法发布')
        
            cursor.execute('''
                INSERT INTO skills (title, description, province, city, district, street, detail_location, publisher_id, publisher_name, contact)
                VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            ''', (
                data.get('title'),
                data.get('description'),
                data.get('province'),
                data.get('city'),
                data.get('district'),
                data.get('street'),
                data.get('detail_location'),
                publisher_id,
                data.get('publisher_name'),
                data.get('contact')
            ))
            
            skill_id = cursor.lastrowid
            conn.commit()
            
            return jsonify({'id': skill_id, **data})
        except Exception as e:
            conn.rollback()
            raise e
        finally:
            conn.close()

    @app.route('/api/skills/<int:skill_id>', methods=['PUT'])
    def update_skill(skill_id):
        # 权限验证
        auth_header = request.headers.get('Authorization')
        if not auth_header or not auth_header.startswith('Bearer '):
            return error_response('请先登录', 401)
        
        token = auth_header.split(' ')[1]
        user_id = get_user_id_from_token(token)
        
        if not user_id:
            return error_response('登录已过期，请重新登录', 401)
        
        # 检查是否有权限更新技能（发布者或管理员）
        has_permission, is_admin, error_msg = check_skill_permission(skill_id, user_id, require_owner=True)
        if not has_permission:
            return error_response(error_msg, 403)
        
        data = request.json

        # 敏感词过滤
        found, word = check_sensitive_words(
            data.get('title'),
            data.get('description'),
            data.get('detail_location')
        )
        if found:
            return error_response('文字中存在敏感词，请修改后再发布')

        conn = get_db()
        cursor = conn.cursor()
        try:
            update_fields = []
            update_values = []
            
            if 'title' in data:
                update_fields.append('title=?')
                update_values.append(data['title'])
            if 'description' in data:
                update_fields.append('description=?')
                update_values.append(data['description'])
            if 'province' in data:
                update_fields.append('province=?')
                update_values.append(data['province'])
            if 'city' in data:
                update_fields.append('city=?')
                update_values.append(data['city'])
            if 'district' in data:
                update_fields.append('district=?')
                update_values.append(data['district'])
            if 'street' in data:
                update_fields.append('street=?')
                update_values.append(data['street'])
            if 'detail_location' in data:
                update_fields.append('detail_location=?')
                update_values.append(data['detail_location'])
            
            if not update_fields:
                return error_response('没有需要更新的字段')
            
            update_values.append(skill_id)
            cursor.execute(f'UPDATE skills SET {", ".join(update_fields)} WHERE id = ?', update_values)
            conn.commit()
            
            cursor.execute('SELECT * FROM skills WHERE id = ?', (skill_id,))
            updated_skill = cursor.fetchone()
            
            result = dict(updated_skill)
            result['location'] = build_location(result)
            return jsonify(result)
        except Exception as e:
            conn.rollback()
            raise e
        finally:
            conn.close()

    @app.route('/api/skills/my', methods=['GET'])
    def get_my_skills():
        auth_header = request.headers.get('Authorization')
        if not auth_header or not auth_header.startswith('Bearer '):
            return error_response('未登录', 401)

        token = auth_header.split(' ')[1]
        user_id = get_user_id_from_token(token)

        if not user_id:
            return error_response('登录已过期', 401)

        conn = get_db()
        cursor = conn.cursor()
        try:
            cursor.execute('''
                SELECT * FROM skills WHERE publisher_id = ? AND deleted_at IS NULL ORDER BY created_at DESC
            ''', (user_id,))
            rows = cursor.fetchall()

            skills = []
            for row in rows:
                skill = dict(row)
                skill['location'] = build_location(skill, include_detail=True)
                skills.append(skill)

            return jsonify(skills)
        finally:
            conn.close()

    @app.route('/api/skills/<int:skill_id>', methods=['DELETE'])
    def delete_skill(skill_id):
        # 权限验证
        auth_header = request.headers.get('Authorization')
        if not auth_header or not auth_header.startswith('Bearer '):
            return error_response('请先登录', 401)
        
        token = auth_header.split(' ')[1]
        user_id = get_user_id_from_token(token)
        
        if not user_id:
            return error_response('登录已过期，请重新登录', 401)
        
        # 检查是否有权限删除技能（发布者或管理员）
        has_permission, is_admin, error_msg = check_skill_permission(skill_id, user_id, require_owner=True)
        if not has_permission:
            return error_response(error_msg, 403)
        
        conn = get_db()
        cursor = conn.cursor()
        try:
            cursor.execute('UPDATE skills SET deleted_at = datetime(\'now\') WHERE id = ?', (skill_id,))
            conn.commit()
            
            return jsonify({'success': True, 'message': '删除成功'})
        except Exception as e:
            conn.rollback()
            raise e
        finally:
            conn.close()

    def get_bulk_user_rating_rates(user_ids):
        if not user_ids:
            return {}

        conn = get_db()
        cursor = conn.cursor()
        try:
            placeholders = ','.join(['?'] * len(user_ids))
            cursor.execute(f'''
                SELECT
                    acceptor_id,
                    COUNT(*) as total,
                    SUM(CASE WHEN rating = 'good' THEN 1 ELSE 0 END) as positive_count
                FROM task_ratings
                WHERE rating_type = 'publisher_to_acceptor'
                  AND acceptor_id IN ({placeholders})
                GROUP BY acceptor_id
            ''', user_ids)
            rows = cursor.fetchall()

            rating_map = {}
            for row in rows:
                if row['total'] > 0:
                    rate = round((row['positive_count'] / row['total']) * 100)
                    rating_map[row['acceptor_id']] = f'{rate}%'
                else:
                    rating_map[row['acceptor_id']] = '-'

            for user_id in user_ids:
                if user_id not in rating_map:
                    rating_map[user_id] = '-'

            return rating_map
        finally:
            conn.close()

    def get_bulk_publisher_rating_rates(user_ids):
        if not user_ids:
            return {}

        conn = get_db()
        cursor = conn.cursor()
        try:
            placeholders = ','.join(['?'] * len(user_ids))
            cursor.execute(f'''
                SELECT
                    t.publisher_id,
                    COUNT(*) as total,
                    SUM(CASE WHEN tr.rating = 'good' THEN 1 ELSE 0 END) as positive_count
                FROM task_ratings tr
                JOIN tasks t ON tr.task_id = t.id
                WHERE tr.rating_type = 'acceptor_to_publisher'
                  AND t.publisher_id IN ({placeholders})
                GROUP BY t.publisher_id
            ''', user_ids)
            rows = cursor.fetchall()

            rating_map = {}
            for row in rows:
                if row['total'] > 0:
                    rate = round((row['positive_count'] / row['total']) * 100)
                    rating_map[row['publisher_id']] = f'{rate}%'
                else:
                    rating_map[row['publisher_id']] = '-'

            for user_id in user_ids:
                if user_id not in rating_map:
                    rating_map[user_id] = '-'

            return rating_map
        finally:
            conn.close()