import aiosqlite from datetime import datetime, timedelta from config import DB_PATH from utils.logger import get_logger logger = get_logger(__name__) async def init_db(): async with aiosqlite.connect(DB_PATH) as db: await db.execute(''' CREATE TABLE IF NOT EXISTS users ( user_id INTEGER PRIMARY KEY, username TEXT, full_name TEXT, role TEXT, balance INTEGER DEFAULT 0, is_vip INTEGER DEFAULT 0, vip_expiry TIMESTAMP, bids_this_month INTEGER DEFAULT 0, bid_month TEXT, joined_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) ''') await db.execute(''' CREATE TABLE IF NOT EXISTS projects ( project_id INTEGER PRIMARY KEY AUTOINCREMENT, client_id INTEGER, title TEXT, description TEXT, budget_min INTEGER, budget_max INTEGER, status TEXT DEFAULT 'open', chosen_freelancer_id INTEGER, is_featured INTEGER DEFAULT 0, deadline_days INTEGER, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (client_id) REFERENCES users(user_id) ) ''') await db.execute(''' CREATE TABLE IF NOT EXISTS bids ( bid_id INTEGER PRIMARY KEY AUTOINCREMENT, project_id INTEGER, freelancer_id INTEGER, amount INTEGER, delivery_days INTEGER, proposal TEXT, status TEXT DEFAULT 'pending', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (project_id) REFERENCES projects(project_id), FOREIGN KEY (freelancer_id) REFERENCES users(user_id) ) ''') await db.execute(''' CREATE TABLE IF NOT EXISTS transactions ( tx_id INTEGER PRIMARY KEY AUTOINCREMENT, user_id INTEGER, amount INTEGER, tx_type TEXT, description TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (user_id) REFERENCES users(user_id) ) ''') await db.execute(''' CREATE TABLE IF NOT EXISTS escrow ( escrow_id INTEGER PRIMARY KEY AUTOINCREMENT, project_id INTEGER UNIQUE, client_id INTEGER, freelancer_id INTEGER, amount INTEGER, status TEXT DEFAULT 'held', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) ''') await db.execute(''' CREATE TABLE IF NOT EXISTS deposit_requests ( deposit_id INTEGER PRIMARY KEY AUTOINCREMENT, user_id INTEGER, amount INTEGER, photo_id TEXT, status TEXT DEFAULT 'pending', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) ''') await db.execute(''' CREATE TABLE IF NOT EXISTS online_payments ( payment_id TEXT PRIMARY KEY, user_id INTEGER, amount INTEGER, gateway TEXT, status TEXT DEFAULT 'pending', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) ''') await db.execute(''' CREATE TABLE IF NOT EXISTS withdrawals ( withdrawal_id INTEGER PRIMARY KEY AUTOINCREMENT, user_id INTEGER, amount INTEGER, card_number TEXT, shaba TEXT, status TEXT DEFAULT 'pending', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (user_id) REFERENCES users(user_id) ) ''') await db.execute(''' CREATE TABLE IF NOT EXISTS portfolios ( portfolio_id INTEGER PRIMARY KEY AUTOINCREMENT, user_id INTEGER, title TEXT, description TEXT, project_url TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (user_id) REFERENCES users(user_id) ) ''') # Concurrency and Performance Pragmas await db.execute('PRAGMA journal_mode = WAL;') await db.execute('PRAGMA synchronous = NORMAL;') await db.execute('PRAGMA temp_store = MEMORY;') await db.execute('PRAGMA foreign_keys = ON;') # Create Indexes for Query Performance await db.execute(''' CREATE TABLE IF NOT EXISTS job_alerts ( alert_id INTEGER PRIMARY KEY AUTOINCREMENT, user_id INTEGER, keyword TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (user_id) REFERENCES users(user_id) ) ''') await db.execute(''' CREATE TABLE IF NOT EXISTS services ( service_id INTEGER PRIMARY KEY AUTOINCREMENT, freelancer_id INTEGER, title TEXT, description TEXT, price INTEGER, delivery_days INTEGER, file_id TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (freelancer_id) REFERENCES users(user_id) ) ''') await db.execute('CREATE INDEX IF NOT EXISTS idx_projects_status ON projects(status);') await db.execute('CREATE INDEX IF NOT EXISTS idx_projects_client ON projects(client_id);') await db.execute('CREATE INDEX IF NOT EXISTS idx_bids_project ON bids(project_id);') await db.execute('CREATE INDEX IF NOT EXISTS idx_bids_freelancer ON bids(freelancer_id);') await db.execute('CREATE INDEX IF NOT EXISTS idx_bids_status ON bids(status);') await db.execute('CREATE INDEX IF NOT EXISTS idx_transactions_user ON transactions(user_id);') await db.commit() # Schema alterations (migrations) for delivery & rating system try: await db.execute("ALTER TABLE projects ADD COLUMN delivery_text TEXT") except: pass try: await db.execute("ALTER TABLE projects ADD COLUMN rating_to_freelancer INTEGER") except: pass try: await db.execute("ALTER TABLE projects ADD COLUMN review_to_freelancer TEXT") except: pass try: await db.execute("ALTER TABLE users ADD COLUMN skills TEXT") except: pass try: await db.execute("ALTER TABLE users ADD COLUMN bio TEXT") except: pass try: await db.execute("ALTER TABLE projects ADD COLUMN cancel_requested_by INTEGER") except: pass try: await db.execute("ALTER TABLE projects ADD COLUMN rating_to_client INTEGER") except: pass try: await db.execute("ALTER TABLE projects ADD COLUMN review_to_client TEXT") except: pass try: await db.execute("ALTER TABLE users ADD COLUMN is_banned INTEGER DEFAULT 0") except: pass try: await db.execute("ALTER TABLE users ADD COLUMN referred_by INTEGER") except: pass try: await db.execute("ALTER TABLE users ADD COLUMN phone_number TEXT") except: pass try: await db.execute("ALTER TABLE users ADD COLUMN national_code TEXT") except: pass try: await db.execute("ALTER TABLE users ADD COLUMN is_verified INTEGER DEFAULT 0") except: pass try: await db.execute("ALTER TABLE users ADD COLUMN verification_rejected_reason TEXT") except: pass try: await db.execute("ALTER TABLE users ADD COLUMN connects INTEGER DEFAULT 5") except: pass try: await db.execute("ALTER TABLE users ADD COLUMN purchased_connects INTEGER DEFAULT 0") except: pass try: await db.execute("ALTER TABLE bids ADD COLUMN boost_bids INTEGER DEFAULT 0") except: pass try: await db.execute("ALTER TABLE projects ADD COLUMN category TEXT") except: pass try: await db.execute("ALTER TABLE projects ADD COLUMN file_id TEXT") except: pass try: await db.execute("ALTER TABLE projects ADD COLUMN project_type TEXT DEFAULT 'project_based'") except: pass try: await db.execute("ALTER TABLE bids ADD COLUMN file_id TEXT") except: pass try: await db.execute("ALTER TABLE projects ADD COLUMN delivery_file_id TEXT") except: pass try: await db.execute("ALTER TABLE portfolios ADD COLUMN file_id TEXT") except: pass await db.execute(''' CREATE TABLE IF NOT EXISTS settings ( key TEXT PRIMARY KEY, value TEXT ) ''') await db.execute(''' CREATE TABLE IF NOT EXISTS support_tickets ( ticket_id INTEGER PRIMARY KEY AUTOINCREMENT, user_id INTEGER, message TEXT, reply TEXT, status TEXT DEFAULT 'open', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (user_id) REFERENCES users(user_id) ) ''') await db.execute(''' CREATE TABLE IF NOT EXISTS promo_codes ( code TEXT PRIMARY KEY, type TEXT, value INTEGER, max_uses INTEGER, uses INTEGER DEFAULT 0, expiry_date TIMESTAMP, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) ''') await db.execute(''' CREATE TABLE IF NOT EXISTS user_promos ( user_id INTEGER, code TEXT, used_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (user_id, code) ) ''') await db.execute(''' CREATE TABLE IF NOT EXISTS milestones ( milestone_id INTEGER PRIMARY KEY AUTOINCREMENT, project_id INTEGER, title TEXT, amount INTEGER, status TEXT DEFAULT 'pending', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (project_id) REFERENCES projects(project_id) ) ''') await db.execute(''' CREATE TABLE IF NOT EXISTS dispute_chats ( project_id INTEGER PRIMARY KEY, client_id INTEGER, freelancer_id INTEGER, admin_id INTEGER, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) ''') await db.execute('CREATE INDEX IF NOT EXISTS idx_milestones_project ON milestones(project_id);') await db.execute(''' CREATE TABLE IF NOT EXISTS jury_cases ( project_id INTEGER PRIMARY KEY, status TEXT DEFAULT 'open', votes_release INTEGER DEFAULT 0, votes_refund INTEGER DEFAULT 0, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) ''') await db.execute(''' CREATE TABLE IF NOT EXISTS jury_votes ( project_id INTEGER, juror_id INTEGER, vote TEXT, voted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (project_id, juror_id) ) ''') await db.execute('CREATE INDEX IF NOT EXISTS idx_jury_votes_project ON jury_votes(project_id);') # New tables for advanced features await db.execute(''' CREATE TABLE IF NOT EXISTS skill_quizzes ( quiz_id INTEGER PRIMARY KEY AUTOINCREMENT, title TEXT UNIQUE, questions_json TEXT ) ''') await db.execute(''' CREATE TABLE IF NOT EXISTS user_quiz_attempts ( user_id INTEGER, quiz_id INTEGER, score INTEGER, passed INTEGER, passed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (user_id, quiz_id) ) ''') await db.execute(''' CREATE TABLE IF NOT EXISTS project_messages ( message_id INTEGER PRIMARY KEY AUTOINCREMENT, project_id INTEGER, sender_id INTEGER, receiver_id INTEGER, message_type TEXT, content TEXT, file_id TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) ''') await db.execute('CREATE INDEX IF NOT EXISTS idx_project_messages_proj ON project_messages(project_id);') # Table to track native Telegram quiz polls await db.execute(''' CREATE TABLE IF NOT EXISTS active_quiz_polls ( poll_id TEXT PRIMARY KEY, user_id INTEGER, quiz_id INTEGER, questions_json TEXT, current_index INTEGER, score INTEGER, quiz_title TEXT ) ''') # Seed default settings defaults = { 'commission_free': '0.10', 'commission_vip': '0.05', 'vip_monthly_price': '100000', 'vip_silver_price': '50000', 'vip_gold_price': '100000', 'vip_platinum_price': '200000', 'commission_silver': '0.05', 'commission_gold': '0.02', 'commission_platinum': '0.00', 'featured_project_price': '50000', 'max_free_bids': '20', 'channel_id': '@xlancer_jobs', 'join_mandatory': '0', 'invite_reward': '5000', 'default_connects': '20', 'invite_connects': '5', 'bid_pack_10_price': '10000', 'bid_pack_30_price': '25000', 'bid_pack_100_price': '70000', 'fulltime_project_price': '100000', 'zarinpal_enabled': '1', 'zarinpal_merchant': 'zarinpal', 'zarinpal_sandbox': '1', 'zibal_enabled': '1', 'zibal_merchant': 'zibal', 'card_to_card_enabled': '1', 'payment_callback_domain': 'http://127.0.0.1:8000', 'bot_status': '1', 'bot_domain': 'http://127.0.0.1:8000', 'admin_card': '6037990000000000', 'admin_card_name': 'مدیریت ایکس‌لنسر', 'min_project_budget': '10000', 'min_wallet_deposit': '10000', 'min_wallet_withdraw': '10000', 'admin_group_payments': '', 'admin_group_disputes': '', 'admin_group_services': '', 'admin_group_projects': '', 'admin_group_support': '', 'admin_group_verifications': '' } for k, v in defaults.items(): await db.execute('INSERT OR IGNORE INTO settings (key, value) VALUES (?, ?)', (k, v)) # Update settings if they are still set to the old default value of '5' await db.execute("UPDATE settings SET value = '20' WHERE key = 'max_free_bids' AND value = '5'") await db.execute("UPDATE settings SET value = '20' WHERE key = 'default_connects' AND value = '5'") # Seed default skill quizzes import json quizzes = [ { "quiz_id": 1, "title": "پایتون", "questions_json": json.dumps([ { "q": "خروجی print(type([]) is list) چیست؟", "options": ["True", "False", "Error", "None"], "answer": 0 }, { "q": "کدام متد برای اضافه کردن عضو به انتهای لیست در پایتون استفاده می‌شود؟", "options": ["add()", "insert()", "append()", "extend()"], "answer": 2 }, { "q": "خروجی print(2 ** 3) چیست؟", "options": ["6", "8", "9", "5"], "answer": 1 }, { "q": "کدام کلمه کلیدی در پایتون برای تعریف تابع استفاده می‌شود؟", "options": ["func", "function", "def", "define"], "answer": 2 }, { "q": "کدام نوع داده در پایتون غیرقابل تغییر (immutable) است؟", "options": ["list", "dict", "set", "tuple"], "answer": 3 } ], ensure_ascii=False) }, { "quiz_id": 2, "title": "طراحی گرافیک", "questions_json": json.dumps([ { "q": "کدام مد رنگی برای کارهای چاپی مناسب‌تر است؟", "options": ["RGB", "CMYK", "HSB", "Grayscale"], "answer": 1 }, { "q": "فرمت استاندارد برای تصاویر بدون پس‌زمینه (transparent) چیست؟", "options": ["JPEG", "PNG", "GIF", "BMP"], "answer": 1 }, { "q": "در طراحی گرافیک، مفهوم DPI مخفف چیست؟", "options": ["Dots Per Inch", "Design Per Image", "Digital Photo Ink", "Data Process Interface"], "answer": 0 }, { "q": "کدام ابزار در ایلوستریتور برای ترسیم مسیرهای برداری دقیق استفاده می‌شود؟", "options": ["Brush Tool", "Pen Tool", "Pencil Tool", "Eraser Tool"], "answer": 1 }, { "q": "کدام‌یک از فرمت‌های زیر برداری (Vector) است؟", "options": ["SVG", "JPG", "PNG", "TIFF"], "answer": 0 } ], ensure_ascii=False) }, { "quiz_id": 3, "title": "ترجمه و نویسندگی", "questions_json": json.dumps([ { "q": "معنی صحیح عبارت اصطلاحی 'Piece of cake' چیست؟", "options": ["یک تکه کیک", "کار بسیار ساده", "شیرینی پختن", "دعوت به مهمانی"], "answer": 1 }, { "q": "کدام کلمه املای صحیحی برای واژه به معنی 'توصیه کردن' در انگلیسی دارد؟", "options": ["Recomend", "Recommend", "Reccomend", "Recoment"], "answer": 1 }, { "q": "در نگارش فارسی، قبل از کدام علامت نگارشی نباید فاصله (Space) گذاشت؟", "options": ["نقطه", "ویرگول", "علامت سوال", "همه موارد"], "answer": 3 }, { "q": "معادل فارسی واژه 'Innovation' چیست؟", "options": ["اختراع", "نوآوری", "کشف", "تکنولوژی"], "answer": 1 }, { "q": "زمان گذشته فعل 'go' چیست؟", "options": ["gone", "goes", "went", "going"], "answer": 2 } ], ensure_ascii=False) } ] for qz in quizzes: await db.execute(''' INSERT OR IGNORE INTO skill_quizzes (quiz_id, title, questions_json) VALUES (?, ?, ?) ''', (qz['quiz_id'], qz['title'], qz['questions_json'])) await db.commit() logger.info("Database initialized successfully.") async def get_user(user_id: int): async with aiosqlite.connect(DB_PATH) as db: db.row_factory = aiosqlite.Row async with db.execute( '''SELECT user_id, username, full_name, role, balance, is_vip, vip_expiry, bids_this_month, bid_month, joined_at, skills, bio, is_banned, referred_by, phone_number, national_code, is_verified, verification_rejected_reason, connects as free_connects, purchased_connects, (connects + purchased_connects) as connects FROM users WHERE user_id = ?''', (user_id,) ) as cursor: return await cursor.fetchone() async def create_user(user_id: int, username: str, full_name: str): async with aiosqlite.connect(DB_PATH) as db: # Check if user already exists async with db.execute('SELECT user_id FROM users WHERE user_id = ?', (user_id,)) as cursor: exists = await cursor.fetchone() if not exists: # Get default connects setting async with db.execute('SELECT value FROM settings WHERE key = ?', ('default_connects',)) as s_cursor: s_row = await s_cursor.fetchone() default_conn = int(s_row[0]) if s_row else 5 await db.execute( 'INSERT OR IGNORE INTO users (user_id, username, full_name, connects) VALUES (?, ?, ?, ?)', (user_id, username, full_name, default_conn) ) await db.commit() async def update_user_full_name(user_id: int, full_name: str): async with aiosqlite.connect(DB_PATH) as db: await db.execute('UPDATE users SET full_name = ? WHERE user_id = ?', (full_name, user_id)) await db.commit() async def set_user_role(user_id: int, role: str): async with aiosqlite.connect(DB_PATH) as db: await db.execute('UPDATE users SET role = ? WHERE user_id = ?', (role, user_id)) await db.commit() async def create_project(client_id: int, title: str, description: str, budget_min: int, budget_max: int, deadline_days: int = 0, category: str = "", file_id: str = None, project_type: str = "project_based") -> int: async with aiosqlite.connect(DB_PATH) as db: cursor = await db.execute( "INSERT INTO projects (client_id, title, description, budget_min, budget_max, deadline_days, category, file_id, project_type, status) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, 'pending_approval')", (client_id, title, description, budget_min, budget_max, deadline_days, category, file_id, project_type) ) await db.commit() return cursor.lastrowid async def get_open_projects(limit: int = 8, offset: int = 0, query: str = None): async with aiosqlite.connect(DB_PATH) as db: db.row_factory = aiosqlite.Row if query: q = f"%{query}%" async with db.execute( 'SELECT * FROM projects WHERE status = "open" AND (title LIKE ? OR description LIKE ? OR category LIKE ?) ORDER BY is_featured DESC, created_at DESC LIMIT ? OFFSET ?', (q, q, q, limit, offset) ) as cursor: return await cursor.fetchall() else: async with db.execute( 'SELECT * FROM projects WHERE status = "open" ORDER BY is_featured DESC, created_at DESC LIMIT ? OFFSET ?', (limit, offset) ) as cursor: return await cursor.fetchall() async def count_open_projects(): async with aiosqlite.connect(DB_PATH) as db: async with db.execute('SELECT COUNT(*) FROM projects WHERE status = "open"') as cursor: row = await cursor.fetchone() return row[0] if row else 0 async def get_project(project_id: int): async with aiosqlite.connect(DB_PATH) as db: db.row_factory = aiosqlite.Row async with db.execute('SELECT * FROM projects WHERE project_id = ?', (project_id,)) as cursor: return await cursor.fetchone() async def get_client_projects(client_id: int): async with aiosqlite.connect(DB_PATH) as db: db.row_factory = aiosqlite.Row async with db.execute( 'SELECT * FROM projects WHERE client_id = ? ORDER BY created_at DESC', (client_id,) ) as cursor: return await cursor.fetchall() async def cancel_project(project_id: int): async with aiosqlite.connect(DB_PATH) as db: await db.execute("UPDATE projects SET status = 'cancelled' WHERE project_id = ?", (project_id,)) await db.commit() async def deduct_connects_conn(db, user_id: int, amount: int) -> bool: async with db.execute('SELECT connects, purchased_connects FROM users WHERE user_id = ?', (user_id,)) as cursor: row = await cursor.fetchone() if not row: return False free_conn = row[0] or 0 purchased_conn = row[1] or 0 if free_conn + purchased_conn < amount: return False if free_conn >= amount: free_conn -= amount else: amount -= free_conn free_conn = 0 purchased_conn -= amount await db.execute('UPDATE users SET connects = ?, purchased_connects = ? WHERE user_id = ?', (free_conn, purchased_conn, user_id)) return True async def check_vip_level_conn(db, user_id: int) -> int: async with db.execute('SELECT is_vip, vip_expiry FROM users WHERE user_id = ?', (user_id,)) as cursor: row = await cursor.fetchone() if not row or not row[0]: return 0 if row[1]: try: expiry = datetime.fromisoformat(row[1]) if datetime.now() > expiry: await db.execute('UPDATE users SET is_vip = 0 WHERE user_id = ?', (user_id,)) return 0 except: return 0 return int(row[0]) async def get_bid_connects_cost(project_id: int, freelancer_id: int) -> int: async with aiosqlite.connect(DB_PATH) as db: db.row_factory = aiosqlite.Row async with db.execute('SELECT budget_max FROM projects WHERE project_id = ?', (project_id,)) as cursor: proj_row = await cursor.fetchone() budget_max = proj_row['budget_max'] if proj_row else 0 level = await check_vip_level(freelancer_id) if budget_max <= 500000: base_cost = 1 elif budget_max <= 1500000: base_cost = 2 elif budget_max <= 5000000: base_cost = 4 else: base_cost = 6 if level == 3: # Platinum return 0 elif level == 2: # Gold return 1 elif level == 1: # Silver return max(1, int(base_cost * 0.50)) return base_cost async def add_bid(project_id: int, freelancer_id: int, amount: int, delivery_days: int, proposal: str, file_id: str = None): async with aiosqlite.connect(DB_PATH) as db: # Check if already bid async with db.execute( 'SELECT bid_id FROM bids WHERE project_id = ? AND freelancer_id = ?', (project_id, freelancer_id) ) as cursor: existing = await cursor.fetchone() if existing: return False cursor = await db.execute( 'INSERT INTO bids (project_id, freelancer_id, amount, delivery_days, proposal, file_id) VALUES (?, ?, ?, ?, ?, ?)', (project_id, freelancer_id, amount, delivery_days, proposal, file_id) ) # Get budget_max to calculate connects cost async with db.execute('SELECT budget_max FROM projects WHERE project_id = ?', (project_id,)) as cursor: proj_row = await cursor.fetchone() budget_max = proj_row[0] if proj_row else 0 # Check VIP level vip_level = await check_vip_level_conn(db, freelancer_id) # Base connects cost if budget_max <= 500000: base_cost = 1 elif budget_max <= 1500000: base_cost = 2 elif budget_max <= 5000000: base_cost = 4 else: base_cost = 6 if vip_level == 3: cost = 0 elif vip_level == 2: cost = 1 elif vip_level == 1: cost = max(1, int(base_cost * 0.50)) else: cost = base_cost if cost > 0: await deduct_connects_conn(db, freelancer_id, cost) await db.execute( 'INSERT INTO transactions (user_id, amount, tx_type, description) VALUES (?, ?, ?, ?)', (freelancer_id, -cost, 'connects_deduction', f'مصرف {cost} کانکت برای پیشنهاد پروژه #{project_id}') ) await db.commit() return cursor.lastrowid async def get_bids_for_project(project_id: int): async with aiosqlite.connect(DB_PATH) as db: db.row_factory = aiosqlite.Row async with db.execute( '''SELECT b.*, u.full_name, u.username, u.is_vip, u.is_verified, (SELECT AVG(rating_to_freelancer) FROM projects WHERE chosen_freelancer_id = b.freelancer_id AND status = 'completed') as avg_rating, (SELECT COUNT(*) FROM projects WHERE chosen_freelancer_id = b.freelancer_id AND status = 'completed') as completed_projects FROM bids b JOIN users u ON b.freelancer_id = u.user_id WHERE b.project_id = ? ORDER BY b.boost_bids DESC, u.is_vip DESC, b.created_at ASC''', (project_id,) ) as cursor: return await cursor.fetchall() async def get_bid(bid_id: int): async with aiosqlite.connect(DB_PATH) as db: db.row_factory = aiosqlite.Row async with db.execute('SELECT * FROM bids WHERE bid_id = ?', (bid_id,)) as cursor: return await cursor.fetchone() async def get_freelancer_bids(freelancer_id: int): async with aiosqlite.connect(DB_PATH) as db: db.row_factory = aiosqlite.Row async with db.execute( '''SELECT b.*, p.title as project_title, p.status as project_status FROM bids b JOIN projects p ON b.project_id = p.project_id WHERE b.freelancer_id = ? ORDER BY b.created_at DESC''', (freelancer_id,) ) as cursor: return await cursor.fetchall() async def accept_bid(bid_id: int, project_id: int, freelancer_id: int, client_id: int, amount: int, milestones_list: list = None) -> bool: async with aiosqlite.connect(DB_PATH) as db: # Re-verify client balance async with db.execute('SELECT balance FROM users WHERE user_id = ?', (client_id,)) as cursor: row = await cursor.fetchone() balance = row[0] if row else 0 if balance < amount: return False # Deduct balance from client await db.execute('UPDATE users SET balance = balance - ? WHERE user_id = ?', (amount, client_id)) # Insert client transaction for escrow block await db.execute( 'INSERT INTO transactions (user_id, amount, tx_type, description) VALUES (?, ?, ?, ?)', (client_id, -amount, 'escrow', f'بلوکه شدن وجه برای پروژه #{project_id}') ) # Create escrow record await db.execute( 'INSERT OR REPLACE INTO escrow (project_id, client_id, freelancer_id, amount, status) VALUES (?, ?, ?, ?, ?)', (project_id, client_id, freelancer_id, amount, 'held') ) # Create milestone records if provided if milestones_list: for m in milestones_list: await db.execute( 'INSERT INTO milestones (project_id, title, amount, status) VALUES (?, ?, ?, ?)', (project_id, m['title'], m['amount'], 'held') ) else: # Default single milestone for full amount await db.execute( 'INSERT INTO milestones (project_id, title, amount, status) VALUES (?, ?, ?, ?)', (project_id, 'تسویه کامل پروژه', amount, 'held') ) # Update bid statuses await db.execute("UPDATE bids SET status = 'accepted' WHERE bid_id = ?", (bid_id,)) await db.execute("UPDATE bids SET status = 'rejected' WHERE project_id = ? AND bid_id != ?", (project_id, bid_id)) # Update project status await db.execute("UPDATE projects SET status = 'in_progress', chosen_freelancer_id = ? WHERE project_id = ?", (freelancer_id, project_id)) await db.commit() return True async def accept_fulltime_bid(bid_id: int, project_id: int, freelancer_id: int, client_id: int) -> bool: async with aiosqlite.connect(DB_PATH) as db: # Update bid statuses await db.execute("UPDATE bids SET status = 'accepted' WHERE bid_id = ?", (bid_id,)) await db.execute("UPDATE bids SET status = 'rejected' WHERE project_id = ? AND bid_id != ?", (project_id, bid_id)) # Update project status (no escrow, direct start) await db.execute("UPDATE projects SET status = 'in_progress', chosen_freelancer_id = ? WHERE project_id = ?", (freelancer_id, project_id)) await db.commit() return True async def check_and_grant_referral_completion_bonus(db, freelancer_id: int): # This expects an active aiosqlite connection 'db' async with db.execute('SELECT COUNT(*) FROM projects WHERE chosen_freelancer_id = ? AND status = "completed"', (freelancer_id,)) as cursor: row = await cursor.fetchone() count = row[0] if row else 0 if count == 1: async with db.execute('SELECT referred_by FROM users WHERE user_id = ?', (freelancer_id,)) as cursor: row = await cursor.fetchone() referrer_id = row[0] if row else None if referrer_id: # Get settings manually to avoid extra connections async with db.execute("SELECT value FROM settings WHERE key = 'invite_reward'", ()) as cursor: s_row = await cursor.fetchone() invite_reward = int(s_row[0]) if s_row else 5000 await db.execute('UPDATE users SET balance = balance + ? WHERE user_id = ?', (invite_reward, referrer_id)) await db.execute( 'INSERT INTO transactions (user_id, amount, tx_type, description) VALUES (?, ?, ?, ?)', (referrer_id, invite_reward, 'referral', f'پاداش تکمیل اولین پروژه زیرمجموعه ({freelancer_id})') ) # Send notification using Bot try: from config import BOT_TOKEN from aiogram import Bot bot = Bot(token=BOT_TOKEN) await bot.send_message( referrer_id, f"🎉 پاداش تکمیل اولین پروژه زیرمجموعه!\n\n" f"کاربر دعوت‌شده توسط شما اولین پروژه خود را با موفقیت تحویل داد و مبلغ {invite_reward:,} تومان پاداش نقدی به حساب شما واریز شد.", parse_mode="HTML" ) await bot.session.close() except Exception as e: logger.error(f"Error sending referral completion notice: {e}") async def complete_project(project_id: int, freelancer_id: int, amount: int, commission_rate: float, client_id: int): # Release all held milestones for this project async with aiosqlite.connect(DB_PATH) as db: db.row_factory = aiosqlite.Row async with db.execute('SELECT * FROM milestones WHERE project_id = ? AND status = "held"', (project_id,)) as cursor: held_milestones = await cursor.fetchall() if not held_milestones: # Backward compatibility for standard projects commission = int(amount * commission_rate) payout = amount - commission async with aiosqlite.connect(DB_PATH) as db: await db.execute("UPDATE projects SET status = 'completed' WHERE project_id = ?", (project_id,)) await db.execute("UPDATE escrow SET status = 'released' WHERE project_id = ?", (project_id,)) await db.execute('UPDATE users SET balance = balance + ? WHERE user_id = ?', (payout, freelancer_id)) await db.execute( 'INSERT INTO transactions (user_id, amount, tx_type, description) VALUES (?, ?, ?, ?)', (freelancer_id, payout, 'earning', f'دریافت حق‌الزحمه پروژه #{project_id}') ) await db.execute( 'INSERT INTO transactions (user_id, amount, tx_type, description) VALUES (?, ?, ?, ?)', (freelancer_id, -commission, 'commission', f'کسر کمیسیون پلتفرم پروژه #{project_id}') ) # Check and grant referral completion bonus await check_and_grant_referral_completion_bonus(db, freelancer_id) await db.commit() else: for m in held_milestones: await release_milestone(m['milestone_id'], commission_rate) async def complete_fulltime_project(project_id: int): async with aiosqlite.connect(DB_PATH) as db: await db.execute("UPDATE projects SET status = 'completed' WHERE project_id = ?", (project_id,)) await db.commit() async def refund_project(project_id: int) -> bool: async with aiosqlite.connect(DB_PATH) as db: db.row_factory = aiosqlite.Row # Check project type async with db.execute('SELECT project_type FROM projects WHERE project_id = ?', (project_id,)) as cursor: proj_row = await cursor.fetchone() project_type = proj_row['project_type'] if proj_row else 'project_based' if project_type == 'full_time': await db.execute("UPDATE projects SET status = 'cancelled' WHERE project_id = ?", (project_id,)) await db.commit() return True async with db.execute('SELECT * FROM milestones WHERE project_id = ? AND status = "held"', (project_id,)) as cursor: held_milestones = await cursor.fetchall() if not held_milestones: # Backward compatibility for standard projects async with aiosqlite.connect(DB_PATH) as db: db.row_factory = aiosqlite.Row async with db.execute('SELECT * FROM escrow WHERE project_id = ? AND status = "held"', (project_id,)) as cursor: escrow = await cursor.fetchone() if not escrow: return False client_id = escrow['client_id'] amount = escrow['amount'] await db.execute('UPDATE escrow SET status = "refunded" WHERE project_id = ?', (project_id,)) await db.execute('UPDATE projects SET status = "cancelled" WHERE project_id = ?', (project_id,)) await db.execute('UPDATE users SET balance = balance + ? WHERE user_id = ?', (amount, client_id)) await db.execute( 'INSERT INTO transactions (user_id, amount, tx_type, description) VALUES (?, ?, ?, ?)', (client_id, amount, 'refund', f'استرداد وجه پروژه #{project_id}') ) await db.commit() return True else: for m in held_milestones: await refund_milestone(m['milestone_id']) return True async def get_bid_by_project_and_status(project_id: int, status: str): async with aiosqlite.connect(DB_PATH) as db: db.row_factory = aiosqlite.Row async with db.execute('SELECT * FROM bids WHERE project_id = ? AND status = ?', (project_id, status)) as cursor: return await cursor.fetchone() async def get_balance(user_id: int) -> int: async with aiosqlite.connect(DB_PATH) as db: async with db.execute('SELECT balance FROM users WHERE user_id = ?', (user_id,)) as cursor: row = await cursor.fetchone() return row[0] if row else 0 async def update_balance(user_id: int, amount: int, tx_type: str, description: str): async with aiosqlite.connect(DB_PATH) as db: await db.execute('UPDATE users SET balance = balance + ? WHERE user_id = ?', (amount, user_id)) await db.execute( 'INSERT INTO transactions (user_id, amount, tx_type, description) VALUES (?, ?, ?, ?)', (user_id, amount, tx_type, description) ) await db.commit() async def get_transactions(user_id: int, limit: int = 5): async with aiosqlite.connect(DB_PATH) as db: db.row_factory = aiosqlite.Row async with db.execute( 'SELECT * FROM transactions WHERE user_id = ? ORDER BY created_at DESC LIMIT ?', (user_id, limit) ) as cursor: return await cursor.fetchall() async def check_vip_level(user_id: int) -> int: async with aiosqlite.connect(DB_PATH) as db: async with db.execute('SELECT is_vip, vip_expiry FROM users WHERE user_id = ?', (user_id,)) as cursor: row = await cursor.fetchone() if not row or not row[0]: return 0 if row[1]: try: expiry = datetime.fromisoformat(row[1]) if datetime.now() > expiry: await db.execute('UPDATE users SET is_vip = 0 WHERE user_id = ?', (user_id,)) await db.commit() return 0 except: return 0 return int(row[0]) async def check_vip_status(user_id: int) -> bool: level = await check_vip_level(user_id) return level > 0 async def get_user_commission_rate(user_id: int) -> float: level = await check_vip_level(user_id) if level == 0: val = await get_setting("commission_free", "0.10") return float(val) elif level == 1: val = await get_setting("commission_silver", "0.05") return float(val) elif level == 2: val = await get_setting("commission_gold", "0.02") return float(val) elif level == 3: val = await get_setting("commission_platinum", "0.00") return float(val) return 0.10 async def activate_vip(user_id: int, level: int = 2, months: int = 1): async with aiosqlite.connect(DB_PATH) as db: db.row_factory = aiosqlite.Row async with db.execute('SELECT vip_expiry, is_vip FROM users WHERE user_id = ?', (user_id,)) as cursor: row = await cursor.fetchone() current_expiry = None if row and row['is_vip'] == level and row['vip_expiry']: try: exp = datetime.fromisoformat(row['vip_expiry']) if exp > datetime.now(): current_expiry = exp except: pass if current_expiry: expiry = current_expiry + timedelta(days=30 * months) else: expiry = datetime.now() + timedelta(days=30 * months) await db.execute( 'UPDATE users SET is_vip = ?, vip_expiry = ? WHERE user_id = ?', (level, expiry.isoformat(), user_id) ) await db.commit() async def get_bid_count_this_month(user_id: int) -> int: async with aiosqlite.connect(DB_PATH) as db: current_month = datetime.now().strftime('%Y-%m') async with db.execute( 'SELECT bids_this_month, bid_month FROM users WHERE user_id = ?', (user_id,) ) as cursor: row = await cursor.fetchone() if row and row[1] == current_month: return row[0] return 0 # Admin stats async def get_all_stats(): async with aiosqlite.connect(DB_PATH) as db: async with db.execute('SELECT COUNT(*) FROM users') as c: users = (await c.fetchone())[0] async with db.execute("SELECT COUNT(*) FROM users WHERE role='client'") as c: clients = (await c.fetchone())[0] async with db.execute("SELECT COUNT(*) FROM users WHERE role='freelancer'") as c: freelancers = (await c.fetchone())[0] async with db.execute("SELECT COUNT(*) FROM projects WHERE status='open'") as c: open_p = (await c.fetchone())[0] async with db.execute("SELECT COUNT(*) FROM projects WHERE status='completed'") as c: done_p = (await c.fetchone())[0] async with db.execute("SELECT COUNT(*) FROM projects WHERE status='disputed'") as c: disputed_p = (await c.fetchone())[0] async with db.execute("SELECT COUNT(*) FROM users WHERE is_vip=1") as c: vips = (await c.fetchone())[0] async with db.execute("SELECT COALESCE(SUM(amount),0) FROM transactions WHERE tx_type='commission'") as c: commission_raw = (await c.fetchone())[0] commission = abs(commission_raw) if commission_raw else 0 async with db.execute("SELECT COALESCE(SUM(amount),0) FROM escrow WHERE status='held'") as c: escrow_held = (await c.fetchone())[0] or 0 async with db.execute("SELECT COALESCE(SUM(amount),0) FROM withdrawals WHERE status='approved'") as c: withdrawals_paid = (await c.fetchone())[0] or 0 async with db.execute("SELECT COALESCE(SUM(amount),0) FROM transactions WHERE tx_type='subscription'") as c: vip_revenue_raw = (await c.fetchone())[0] vip_revenue = abs(vip_revenue_raw) if vip_revenue_raw else 0 return { 'users': users, 'clients': clients, 'freelancers': freelancers, 'open_projects': open_p, 'done_projects': done_p, 'disputed_projects': disputed_p, 'vip_users': vips, 'total_commission': commission, 'total_escrow_held': escrow_held, 'total_withdrawn': withdrawals_paid, 'total_vip_revenue': vip_revenue } async def get_all_users(limit: int = 20): async with aiosqlite.connect(DB_PATH) as db: db.row_factory = aiosqlite.Row async with db.execute('SELECT * FROM users ORDER BY joined_at DESC LIMIT ?', (limit,)) as cursor: return await cursor.fetchall() async def deliver_project(project_id: int, delivery_text: str, delivery_file_id: str = None): async with aiosqlite.connect(DB_PATH) as db: await db.execute( "UPDATE projects SET status = 'delivered', delivery_text = ?, delivery_file_id = ? WHERE project_id = ?", (delivery_text, delivery_file_id, project_id) ) await db.commit() async def request_project_revisions(project_id: int): async with aiosqlite.connect(DB_PATH) as db: await db.execute("UPDATE projects SET status = 'in_progress' WHERE project_id = ?", (project_id,)) await db.commit() async def submit_rating(project_id: int, rating: int, review: str): async with aiosqlite.connect(DB_PATH) as db: await db.execute( "UPDATE projects SET rating_to_freelancer = ?, review_to_freelancer = ? WHERE project_id = ?", (rating, review, project_id) ) await db.commit() async def submit_rating_to_client(project_id: int, rating: int, review: str): async with aiosqlite.connect(DB_PATH) as db: await db.execute( "UPDATE projects SET rating_to_client = ?, review_to_client = ? WHERE project_id = ?", (rating, review, project_id) ) await db.commit() async def update_user_profile(user_id: int, skills: str = None, bio: str = None): async with aiosqlite.connect(DB_PATH) as db: if skills is not None: await db.execute('UPDATE users SET skills = ? WHERE user_id = ?', (skills, user_id)) if bio is not None: await db.execute('UPDATE users SET bio = ? WHERE user_id = ?', (bio, user_id)) await db.commit() async def get_freelancer_reviews(freelancer_id: int, limit: int = 3): async with aiosqlite.connect(DB_PATH) as db: db.row_factory = aiosqlite.Row async with db.execute( '''SELECT p.title, p.review_to_freelancer, p.rating_to_freelancer, u.full_name as client_name FROM projects p JOIN users u ON p.client_id = u.user_id WHERE p.chosen_freelancer_id = ? AND p.status = 'completed' AND p.rating_to_freelancer IS NOT NULL AND p.review_to_freelancer != '' ORDER BY p.created_at DESC LIMIT ?''', (freelancer_id, limit) ) as cursor: return await cursor.fetchall() async def promote_project(project_id: int, client_id: int, price: int) -> bool: async with aiosqlite.connect(DB_PATH) as db: async with db.execute('SELECT balance FROM users WHERE user_id = ?', (client_id,)) as cursor: row = await cursor.fetchone() balance = row[0] if row else 0 if balance < price: return False await db.execute('UPDATE users SET balance = balance - ? WHERE user_id = ?', (price, client_id)) await db.execute('UPDATE projects SET is_featured = 1 WHERE project_id = ?', (project_id,)) await db.execute( 'INSERT INTO transactions (user_id, amount, tx_type, description) VALUES (?, ?, ?, ?)', (client_id, -price, 'promotion', f'ویژه کردن پروژه #{project_id}') ) await db.commit() return True async def request_project_cancel(project_id: int, user_id: int): async with aiosqlite.connect(DB_PATH) as db: await db.execute('UPDATE projects SET cancel_requested_by = ? WHERE project_id = ?', (user_id, project_id)) await db.commit() async def reject_project_cancel(project_id: int): async with aiosqlite.connect(DB_PATH) as db: await db.execute('UPDATE projects SET cancel_requested_by = NULL WHERE project_id = ?', (project_id,)) await db.commit() async def create_withdrawal_request(user_id: int, amount: int, card_number: str, shaba: str) -> int: async with aiosqlite.connect(DB_PATH) as db: # Check user balance async with db.execute('SELECT balance FROM users WHERE user_id = ?', (user_id,)) as cursor: row = await cursor.fetchone() balance = row[0] if row else 0 if balance < amount: return 0 # Deduct balance (hold it) await db.execute('UPDATE users SET balance = balance - ? WHERE user_id = ?', (amount, user_id)) # Log pending transaction await db.execute( 'INSERT INTO transactions (user_id, amount, tx_type, description) VALUES (?, ?, ?, ?)', (user_id, -amount, 'withdrawal_hold', f'درخواست تسویه حساب به مبلغ {amount:,} تومان (در انتظار تایید)') ) # Insert withdrawal request cursor = await db.execute( 'INSERT INTO withdrawals (user_id, amount, card_number, shaba) VALUES (?, ?, ?, ?)', (user_id, amount, card_number, shaba) ) await db.commit() return cursor.lastrowid async def approve_withdrawal_request(withdrawal_id: int) -> bool: async with aiosqlite.connect(DB_PATH) as db: db.row_factory = aiosqlite.Row async with db.execute('SELECT * FROM withdrawals WHERE withdrawal_id = ? AND status = "pending"', (withdrawal_id,)) as cursor: w = await cursor.fetchone() if not w: return False await db.execute('UPDATE withdrawals SET status = "approved" WHERE withdrawal_id = ?', (withdrawal_id,)) # Log approved transaction await db.execute( 'INSERT INTO transactions (user_id, amount, tx_type, description) VALUES (?, ?, ?, ?)', (w['user_id'], 0, 'withdrawal_approve', f'تایید و واریز درخواست تسویه حساب #{withdrawal_id}') ) await db.commit() return True async def reject_withdrawal_request(withdrawal_id: int) -> bool: async with aiosqlite.connect(DB_PATH) as db: db.row_factory = aiosqlite.Row async with db.execute('SELECT * FROM withdrawals WHERE withdrawal_id = ? AND status = "pending"', (withdrawal_id,)) as cursor: w = await cursor.fetchone() if not w: return False user_id = w['user_id'] amount = w['amount'] await db.execute('UPDATE withdrawals SET status = "rejected" WHERE withdrawal_id = ?', (withdrawal_id,)) # Refund user balance await db.execute('UPDATE users SET balance = balance + ? WHERE user_id = ?', (amount, user_id)) # Log refund transaction await db.execute( 'INSERT INTO transactions (user_id, amount, tx_type, description) VALUES (?, ?, ?, ?)', (user_id, amount, 'withdrawal_refund', f'رد درخواست تسویه حساب #{withdrawal_id} و بازگشت وجه') ) await db.commit() return True async def get_withdrawal(withdrawal_id: int): async with aiosqlite.connect(DB_PATH) as db: db.row_factory = aiosqlite.Row async with db.execute('SELECT * FROM withdrawals WHERE withdrawal_id = ?', (withdrawal_id,)) as cursor: return await cursor.fetchone() async def search_projects(query: str, limit: int = 8, offset: int = 0): async with aiosqlite.connect(DB_PATH) as db: db.row_factory = aiosqlite.Row like_query = f"%{query}%" async with db.execute( 'SELECT * FROM projects WHERE status = "open" AND (title LIKE ? OR description LIKE ?) ORDER BY is_featured DESC, created_at DESC LIMIT ? OFFSET ?', (like_query, like_query, limit, offset) ) as cursor: return await cursor.fetchall() async def count_search_projects(query: str) -> int: async with aiosqlite.connect(DB_PATH) as db: like_query = f"%{query}%" async with db.execute( 'SELECT COUNT(*) FROM projects WHERE status = "open" AND (title LIKE ? OR description LIKE ?)', (like_query, like_query) ) as cursor: row = await cursor.fetchone() return row[0] if row else 0 def parse_skills(skills_str: str) -> list: if not skills_str: return [] for sep in ["،", "|", "/", "-", "\n"]: skills_str = skills_str.replace(sep, ",") skills = [s.strip() for s in skills_str.split(",") if s.strip()] return skills async def get_projects_matching_skills(skills: list, limit: int = 8, offset: int = 0): if not skills: return [] async with aiosqlite.connect(DB_PATH) as db: db.row_factory = aiosqlite.Row clauses = [] params = [] for s in skills: clauses.append("(title LIKE ? OR description LIKE ?)") params.extend([f"%{s}%", f"%{s}%"]) sql = f"SELECT * FROM projects WHERE status = 'open' AND ({' OR '.join(clauses)}) ORDER BY is_featured DESC, created_at DESC LIMIT ? OFFSET ?" params.extend([limit, offset]) async with db.execute(sql, params) as cursor: return await cursor.fetchall() async def count_projects_matching_skills(skills: list) -> int: if not skills: return 0 async with aiosqlite.connect(DB_PATH) as db: clauses = [] params = [] for s in skills: clauses.append("(title LIKE ? OR description LIKE ?)") params.extend([f"%{s}%", f"%{s}%"]) sql = f"SELECT COUNT(*) FROM projects WHERE status = 'open' AND ({' OR '.join(clauses)})" async with db.execute(sql, params) as cursor: row = await cursor.fetchone() return row[0] if row else 0 async def get_freelancers_for_matching_job(title: str, description: str, category: str = ""): async with aiosqlite.connect(DB_PATH) as db: db.row_factory = aiosqlite.Row async with db.execute("SELECT user_id, skills FROM users WHERE role = 'freelancer' AND skills IS NOT NULL") as cursor: rows = await cursor.fetchall() matching = [] text_to_search = (title + " " + description + " " + category).lower() for r in rows: skills = parse_skills(r['skills']) # Also include the category as a direct match if the user has a skill matching the category # Or if the skill is found in the title/description matched = False for s in skills: s_lower = s.lower() if s_lower in text_to_search or (category and category.lower() in s_lower): matched = True break if matched: matching.append(r['user_id']) return matching async def add_portfolio_item(user_id: int, title: str, description: str, project_url: str, file_id: str = None) -> int: async with aiosqlite.connect(DB_PATH) as db: cursor = await db.execute( 'INSERT INTO portfolios (user_id, title, description, project_url, file_id) VALUES (?, ?, ?, ?, ?)', (user_id, title, description, project_url, file_id) ) await db.commit() return cursor.lastrowid async def get_user_portfolios(user_id: int): async with aiosqlite.connect(DB_PATH) as db: db.row_factory = aiosqlite.Row async with db.execute('SELECT * FROM portfolios WHERE user_id = ? ORDER BY created_at DESC', (user_id,)) as cursor: return await cursor.fetchall() async def delete_portfolio_item(portfolio_id: int, user_id: int) -> bool: async with aiosqlite.connect(DB_PATH) as db: # Check ownership async with db.execute('SELECT portfolio_id FROM portfolios WHERE portfolio_id = ? AND user_id = ?', (portfolio_id, user_id)) as cursor: row = await cursor.fetchone() if not row: return False await db.execute('DELETE FROM portfolios WHERE portfolio_id = ?', (portfolio_id,)) await db.commit() return True async def get_client_rating_stats(client_id: int): async with aiosqlite.connect(DB_PATH) as db: db.row_factory = aiosqlite.Row async with db.execute( '''SELECT COUNT(*) as total_projects, COALESCE(AVG(rating_to_client), 0) as avg_rating FROM projects WHERE client_id = ? AND status = 'completed' AND rating_to_client IS NOT NULL''', (client_id,) ) as cursor: return await cursor.fetchone() async def submit_client_rating(project_id: int, rating: int, review: str): async with aiosqlite.connect(DB_PATH) as db: await db.execute( "UPDATE projects SET rating_to_client = ?, review_to_client = ? WHERE project_id = ?", (rating, review, project_id) ) await db.commit() async def ban_user(user_id: int): async with aiosqlite.connect(DB_PATH) as db: await db.execute('UPDATE users SET is_banned = 1 WHERE user_id = ?', (user_id,)) await db.commit() async def unban_user(user_id: int): async with aiosqlite.connect(DB_PATH) as db: await db.execute('UPDATE users SET is_banned = 0 WHERE user_id = ?', (user_id,)) await db.commit() async def get_active_escrows(): async with aiosqlite.connect(DB_PATH) as db: db.row_factory = aiosqlite.Row async with db.execute('SELECT * FROM escrow WHERE status = "held" ORDER BY created_at DESC') as cursor: return await cursor.fetchall() async def get_pending_withdrawals(): async with aiosqlite.connect(DB_PATH) as db: db.row_factory = aiosqlite.Row async with db.execute('SELECT * FROM withdrawals WHERE status = "pending" ORDER BY created_at DESC') as cursor: return await cursor.fetchall() async def get_disputed_projects(): async with aiosqlite.connect(DB_PATH) as db: db.row_factory = aiosqlite.Row async with db.execute("SELECT * FROM projects WHERE status = 'disputed' ORDER BY created_at DESC") as cursor: return await cursor.fetchall() async def search_users(query: str): async with aiosqlite.connect(DB_PATH) as db: db.row_factory = aiosqlite.Row if query.isdigit(): sql = "SELECT * FROM users WHERE user_id = ? OR username LIKE ? OR full_name LIKE ? LIMIT 10" p = (int(query), f"%{query}%", f"%{query}%") else: sql = "SELECT * FROM users WHERE username LIKE ? OR full_name LIKE ? LIMIT 10" p = (f"%{query}%", f"%{query}%") async with db.execute(sql, p) as cursor: return await cursor.fetchall() async def withdraw_bid(bid_id: int, freelancer_id: int) -> bool: async with aiosqlite.connect(DB_PATH) as db: db.row_factory = aiosqlite.Row async with db.execute('SELECT * FROM bids WHERE bid_id = ? AND freelancer_id = ?', (bid_id, freelancer_id)) as cursor: bid = await cursor.fetchone() if not bid: return False if bid['status'] != 'pending': return False project_id = bid['project_id'] boost_amount = bid['boost_bids'] or 0 await db.execute('DELETE FROM bids WHERE bid_id = ?', (bid_id,)) # Check VIP status is_vip = 0 async with db.execute('SELECT is_vip, vip_expiry FROM users WHERE user_id = ?', (freelancer_id,)) as cursor: user_row = await cursor.fetchone() if user_row: is_vip_val = user_row[0] vip_expiry_val = user_row[1] if is_vip_val: if vip_expiry_val: try: expiry = datetime.fromisoformat(vip_expiry_val) if datetime.now() <= expiry: is_vip = 1 except: pass else: is_vip = 1 if not is_vip: refund_amount = 1 + boost_amount async with db.execute('SELECT connects, purchased_connects FROM users WHERE user_id = ?', (freelancer_id,)) as cursor: row = await cursor.fetchone() current_free = row[0] or 0 current_purchased = row[1] or 0 max_free = 20 if current_free + refund_amount <= max_free: current_free += refund_amount else: excess = (current_free + refund_amount) - max_free current_free = max_free current_purchased += excess await db.execute('UPDATE users SET connects = ?, purchased_connects = ? WHERE user_id = ?', (current_free, current_purchased, freelancer_id)) await db.execute( 'INSERT INTO transactions (user_id, amount, tx_type, description) VALUES (?, ?, ?, ?)', (freelancer_id, refund_amount, 'connects_refund', f'استرداد {refund_amount} بید (کانکت) بابت لغو پیشنهاد پروژه #{project_id}') ) await db.commit() return True async def boost_bid(bid_id: int, freelancer_id: int, amount: int) -> bool: async with aiosqlite.connect(DB_PATH) as db: db.row_factory = aiosqlite.Row async with db.execute('SELECT * FROM bids WHERE bid_id = ? AND freelancer_id = ?', (bid_id, freelancer_id)) as cursor: bid = await cursor.fetchone() if not bid or bid['status'] != 'pending': return False # Check VIP level vip_level = await check_vip_level_conn(db, freelancer_id) # Calculate cost with discount if vip_level == 1: # Silver cost = max(1, int(amount * 0.90)) elif vip_level == 2: # Gold cost = max(1, int(amount * 0.80)) elif vip_level == 3: # Platinum cost = max(1, int(amount * 0.70)) else: cost = amount success = await deduct_connects_conn(db, freelancer_id, cost) if not success: return False await db.execute( 'INSERT INTO transactions (user_id, amount, tx_type, description) VALUES (?, ?, ?, ?)', (freelancer_id, -cost, 'bid_boost', f'کسر {cost} بید بابت ارتقای پیشنهاد پروژه #{bid["project_id"]} (بوست +{amount})') ) await db.execute('UPDATE bids SET boost_bids = boost_bids + ? WHERE bid_id = ?', (amount, bid_id)) await db.commit() return True async def update_bid(bid_id: int, amount: int, delivery_days: int, proposal: str) -> bool: async with aiosqlite.connect(DB_PATH) as db: await db.execute( 'UPDATE bids SET amount = ?, delivery_days = ?, proposal = ? WHERE bid_id = ?', (amount, delivery_days, proposal, bid_id) ) await db.commit() return True async def update_project_details(project_id: int, title: str, description: str, budget_min: int, budget_max: int, deadline_days: int) -> bool: async with aiosqlite.connect(DB_PATH) as db: await db.execute( 'UPDATE projects SET title = ?, description = ?, budget_min = ?, budget_max = ?, deadline_days = ? WHERE project_id = ?', (title, description, budget_min, budget_max, deadline_days, project_id) ) await db.commit() return True async def save_referral(user_id: int, referrer_id: int) -> bool: async with aiosqlite.connect(DB_PATH) as db: # Check if user already has a referrer async with db.execute('SELECT referred_by FROM users WHERE user_id = ?', (user_id,)) as cursor: row = await cursor.fetchone() if row and row[0] is not None: return False # Already referred # Save referred by await db.execute('UPDATE users SET referred_by = ? WHERE user_id = ?', (referrer_id, user_id)) await db.commit() return True async def get_referral_stats(user_id: int): async with aiosqlite.connect(DB_PATH) as db: # Count referred users async with db.execute('SELECT COUNT(*) FROM users WHERE referred_by = ?', (user_id,)) as cursor: row = await cursor.fetchone() count = row[0] if row else 0 # Calculate earnings (each ref yields 5,000 Tomans) async with db.execute("SELECT COALESCE(SUM(amount), 0) FROM transactions WHERE user_id = ? AND tx_type = 'referral'", (user_id,)) as cursor: row = await cursor.fetchone() earnings = row[0] if row else 0 return {'count': count, 'earnings': earnings} async def get_user_transactions_paginated(user_id: int, limit: int = 10, offset: int = 0): async with aiosqlite.connect(DB_PATH) as db: db.row_factory = aiosqlite.Row async with db.execute( 'SELECT * FROM transactions WHERE user_id = ? ORDER BY created_at DESC LIMIT ? OFFSET ?', (user_id, limit, offset) ) as cursor: return await cursor.fetchall() async def count_user_transactions(user_id: int) -> int: async with aiosqlite.connect(DB_PATH) as db: async with db.execute('SELECT COUNT(*) FROM transactions WHERE user_id = ?', (user_id,)) as cursor: row = await cursor.fetchone() return row[0] if row else 0 async def get_bids_for_project_sorted(project_id: int, sort_by: str = 'vip'): # sort_by options: vip, budget_asc, rating_desc, delivery_asc order_clause = "b.boost_bids DESC, u.is_vip DESC, b.created_at ASC" if sort_by == 'budget_asc': order_clause = "b.boost_bids DESC, b.amount ASC" elif sort_by == 'rating_desc': order_clause = "b.boost_bids DESC, avg_rating DESC, u.is_vip DESC" elif sort_by == 'delivery_asc': order_clause = "b.boost_bids DESC, b.delivery_days ASC" async with aiosqlite.connect(DB_PATH) as db: db.row_factory = aiosqlite.Row async with db.execute( f'''SELECT b.*, u.full_name, u.username, u.is_vip, u.is_verified, (SELECT AVG(rating_to_freelancer) FROM projects WHERE chosen_freelancer_id = b.freelancer_id AND status = 'completed') as avg_rating, (SELECT COUNT(*) FROM projects WHERE chosen_freelancer_id = b.freelancer_id AND status = 'completed') as completed_projects FROM bids b JOIN users u ON b.freelancer_id = u.user_id WHERE b.project_id = ? ORDER BY {order_clause}''', (project_id,) ) as cursor: return await cursor.fetchall() async def get_client_projects_paginated(client_id: int, limit: int = 8, offset: int = 0): async with aiosqlite.connect(DB_PATH) as db: db.row_factory = aiosqlite.Row async with db.execute( 'SELECT *, (SELECT COUNT(*) FROM bids WHERE project_id = projects.project_id) as bid_count FROM projects WHERE client_id = ? ORDER BY created_at DESC LIMIT ? OFFSET ?', (client_id, limit, offset) ) as cursor: return await cursor.fetchall() async def get_client_project_status_stats(client_id: int) -> dict: async with aiosqlite.connect(DB_PATH) as db: db.row_factory = aiosqlite.Row async with db.execute( 'SELECT status, COUNT(*) as cnt FROM projects WHERE client_id = ? GROUP BY status', (client_id,) ) as cursor: rows = await cursor.fetchall() stats = {"open": 0, "in_progress": 0, "delivered": 0, "completed": 0, "cancelled": 0} for r in rows: stats[r['status']] = r['cnt'] return stats async def count_client_projects(client_id: int) -> int: async with aiosqlite.connect(DB_PATH) as db: async with db.execute('SELECT COUNT(*) FROM projects WHERE client_id = ?', (client_id,)) as cursor: row = await cursor.fetchone() return row[0] if row else 0 async def get_freelancer_bids_paginated(freelancer_id: int, limit: int = 8, offset: int = 0): async with aiosqlite.connect(DB_PATH) as db: db.row_factory = aiosqlite.Row async with db.execute( '''SELECT b.*, p.title as project_title, p.status as project_status FROM bids b JOIN projects p ON b.project_id = p.project_id WHERE b.freelancer_id = ? ORDER BY b.created_at DESC LIMIT ? OFFSET ?''', (freelancer_id, limit, offset) ) as cursor: return await cursor.fetchall() async def count_freelancer_bids(freelancer_id: int) -> int: async with aiosqlite.connect(DB_PATH) as db: async with db.execute('SELECT COUNT(*) FROM bids WHERE freelancer_id = ?', (freelancer_id,)) as cursor: row = await cursor.fetchone() return row[0] if row else 0 # Settings Helpers async def get_setting(key: str, default: str = "") -> str: async with aiosqlite.connect(DB_PATH) as db: async with db.execute('SELECT value FROM settings WHERE key = ?', (key,)) as cursor: row = await cursor.fetchone() return row[0] if row else default async def get_setting_int(key: str, default: int = 0) -> int: val = await get_setting(key, str(default)) try: return int(val) except ValueError: return default async def get_setting_float(key: str, default: float = 0.0) -> float: val = await get_setting(key, str(default)) try: return float(val) except ValueError: return default async def update_setting(key: str, value: str): async with aiosqlite.connect(DB_PATH) as db: await db.execute('INSERT OR REPLACE INTO settings (key, value) VALUES (?, ?)', (key, value)) await db.commit() async def get_all_settings(): async with aiosqlite.connect(DB_PATH) as db: db.row_factory = aiosqlite.Row async with db.execute('SELECT * FROM settings ORDER BY key ASC') as cursor: return await cursor.fetchall() async def get_admin_recipients(category: str) -> list[int | str]: """ Get the list of admin user IDs who should receive notifications for a specific category. Looks for a setting named 'admin_group_{category}' in the settings table. If not found or empty, defaults to config.ADMIN_IDS. """ try: from config import ADMIN_IDS admins = list(ADMIN_IDS) except ImportError: admins = [] try: val = await get_setting(f"admin_group_{category}", "") if val: parsed_ids = [] for x in val.split(","): x_clean = x.strip() if not x_clean: continue if (x_clean.startswith('-') and x_clean[1:].isdigit()) or x_clean.isdigit(): parsed_ids.append(int(x_clean)) elif x_clean.startswith('@'): parsed_ids.append(x_clean) if parsed_ids: return parsed_ids except Exception: pass return admins # Verification Helpers async def submit_verification(user_id: int, phone_number: str, national_code: str): async with aiosqlite.connect(DB_PATH) as db: await db.execute( 'UPDATE users SET phone_number = ?, national_code = ?, is_verified = 2, verification_rejected_reason = NULL WHERE user_id = ?', (phone_number, national_code, user_id) ) await db.commit() async def approve_verification(user_id: int) -> bool: async with aiosqlite.connect(DB_PATH) as db: await db.execute('UPDATE users SET is_verified = 1, verification_rejected_reason = NULL WHERE user_id = ?', (user_id,)) await db.commit() return True async def reject_verification(user_id: int, reason: str) -> bool: async with aiosqlite.connect(DB_PATH) as db: await db.execute('UPDATE users SET is_verified = 0, verification_rejected_reason = ? WHERE user_id = ?', (reason, user_id)) await db.commit() return True async def get_pending_verifications(): async with aiosqlite.connect(DB_PATH) as db: db.row_factory = aiosqlite.Row async with db.execute('SELECT * FROM users WHERE is_verified = 2 ORDER BY joined_at DESC') as cursor: return await cursor.fetchall() # Project Approval Helpers async def get_pending_projects(): async with aiosqlite.connect(DB_PATH) as db: db.row_factory = aiosqlite.Row async with db.execute('SELECT * FROM projects WHERE status = "pending_approval" ORDER BY created_at DESC') as cursor: return await cursor.fetchall() async def approve_project(project_id: int) -> bool: async with aiosqlite.connect(DB_PATH) as db: await db.execute("UPDATE projects SET status = 'open' WHERE project_id = ?", (project_id,)) await db.commit() return True async def reject_project(project_id: int) -> bool: async with aiosqlite.connect(DB_PATH) as db: await db.execute("UPDATE projects SET status = 'rejected' WHERE project_id = ?", (project_id,)) await db.commit() return True async def update_connects(user_id: int, amount: int, tx_type: str, description: str): async with aiosqlite.connect(DB_PATH) as db: if tx_type == 'buy_bids': await db.execute('UPDATE users SET purchased_connects = purchased_connects + ? WHERE user_id = ?', (amount, user_id)) else: await db.execute('UPDATE users SET connects = connects + ? WHERE user_id = ?', (amount, user_id)) await db.execute( 'INSERT INTO transactions (user_id, amount, tx_type, description) VALUES (?, ?, ?, ?)', (user_id, amount, tx_type, description) ) await db.commit() async def check_and_grant_monthly_connects(): async with aiosqlite.connect(DB_PATH) as db: db.row_factory = aiosqlite.Row current_month = datetime.now().strftime('%Y-%m') # Get last grant month async with db.execute("SELECT value FROM settings WHERE key = 'last_monthly_grant_month'") as cursor: row = await cursor.fetchone() last_month = row[0] if row else "" if last_month != current_month: # Get target connects setting (defaults to 20) async with db.execute("SELECT value FROM settings WHERE key = 'max_free_bids'") as cursor: s_row = await cursor.fetchone() target_amount = int(s_row[0]) if s_row else 20 # Retrieve all users to update connects and log async with db.execute('SELECT user_id, connects FROM users') as cursor: users = await cursor.fetchall() for u in users: user_id = u['user_id'] current_conn = u['connects'] if u['connects'] is not None else 0 if current_conn != target_amount: await db.execute('UPDATE users SET connects = ? WHERE user_id = ?', (target_amount, user_id)) diff = target_amount - current_conn if diff > 0: desc = f'دریافت {diff} بید (کانکت) جهت تمدید ماهانه سهمیه به {target_amount}' tx_type = 'monthly_grant' else: desc = f'کسر {abs(diff)} بید (کانکت) منقضی شده دوره قبل جهت تمدید سهمیه به {target_amount}' tx_type = 'monthly_expiration' await db.execute( 'INSERT INTO transactions (user_id, amount, tx_type, description) VALUES (?, ?, ?, ?)', (user_id, diff, tx_type, desc) ) # Update last processed month await db.execute("INSERT OR REPLACE INTO settings (key, value) VALUES ('last_monthly_grant_month', ?)", (current_month,)) await db.commit() logger.info(f"Successfully reset/renewed monthly connects to {target_amount} for all users for {current_month}.") async def get_platform_stats(): async with aiosqlite.connect(DB_PATH) as db: # Total users async with db.execute('SELECT COUNT(*) FROM users') as c: users_count = (await c.fetchone())[0] # Active projects async with db.execute("SELECT COUNT(*) FROM projects WHERE status IN ('open', 'in_progress', 'delivered', 'disputed')") as c: active_projects = (await c.fetchone())[0] # Escrow sum async with db.execute("SELECT SUM(amount) FROM escrow WHERE status = 'held'") as c: escrow_sum = (await c.fetchone())[0] or 0 # Earnings async with db.execute("SELECT SUM(ABS(amount)) FROM transactions WHERE tx_type = 'commission'") as c: earnings = (await c.fetchone())[0] or 0 return { 'users_count': users_count, 'active_projects': active_projects, 'escrow_sum': escrow_sum, 'earnings': earnings } async def create_deposit_request(user_id: int, amount: int, photo_id: str) -> int: async with aiosqlite.connect(DB_PATH) as db: cursor = await db.execute( 'INSERT INTO deposit_requests (user_id, amount, photo_id) VALUES (?, ?, ?)', (user_id, amount, photo_id) ) await db.commit() return cursor.lastrowid async def get_deposit_request(deposit_id: int): async with aiosqlite.connect(DB_PATH) as db: db.row_factory = aiosqlite.Row async with db.execute('SELECT * FROM deposit_requests WHERE deposit_id = ?', (deposit_id,)) as cursor: return await cursor.fetchone() async def update_deposit_status(deposit_id: int, status: str): async with aiosqlite.connect(DB_PATH) as db: await db.execute('UPDATE deposit_requests SET status = ? WHERE deposit_id = ?', (status, deposit_id)) await db.commit() async def get_pending_deposits(): async with aiosqlite.connect(DB_PATH) as db: db.row_factory = aiosqlite.Row async with db.execute('SELECT * FROM deposit_requests WHERE status = "pending" ORDER BY created_at DESC') as cursor: return await cursor.fetchall() async def create_online_payment(payment_id: str, user_id: int, amount: int, gateway: str): async with aiosqlite.connect(DB_PATH) as db: await db.execute( 'INSERT INTO online_payments (payment_id, user_id, amount, gateway) VALUES (?, ?, ?, ?)', (payment_id, user_id, amount, gateway) ) await db.commit() async def get_online_payment(payment_id: str): async with aiosqlite.connect(DB_PATH) as db: db.row_factory = aiosqlite.Row async with db.execute('SELECT * FROM online_payments WHERE payment_id = ?', (payment_id,)) as cursor: return await cursor.fetchone() async def update_online_payment_status(payment_id: str, status: str): async with aiosqlite.connect(DB_PATH) as db: await db.execute('UPDATE online_payments SET status = ? WHERE payment_id = ?', (status, payment_id)) await db.commit() async def get_all_users_paginated(limit: int = 10, offset: int = 0): async with aiosqlite.connect(DB_PATH) as db: db.row_factory = aiosqlite.Row async with db.execute('SELECT * FROM users ORDER BY joined_at DESC LIMIT ? OFFSET ?', (limit, offset)) as cursor: return await cursor.fetchall() async def count_all_users(): async with aiosqlite.connect(DB_PATH) as db: async with db.execute('SELECT COUNT(*) FROM users') as cursor: row = await cursor.fetchone() return row[0] if row else 0 async def create_support_ticket(user_id: int, message: str) -> int: async with aiosqlite.connect(DB_PATH) as db: cursor = await db.execute( 'INSERT INTO support_tickets (user_id, message) VALUES (?, ?)', (user_id, message) ) await db.commit() return cursor.lastrowid async def get_support_ticket(ticket_id: int): async with aiosqlite.connect(DB_PATH) as db: db.row_factory = aiosqlite.Row async with db.execute('SELECT * FROM support_tickets WHERE ticket_id = ?', (ticket_id,)) as cursor: return await cursor.fetchone() async def get_open_tickets(): async with aiosqlite.connect(DB_PATH) as db: db.row_factory = aiosqlite.Row async with db.execute('SELECT * FROM support_tickets WHERE status = "open" ORDER BY created_at DESC') as cursor: return await cursor.fetchall() async def answer_support_ticket(ticket_id: int, reply_text: str): async with aiosqlite.connect(DB_PATH) as db: await db.execute( 'UPDATE support_tickets SET reply = ?, status = "closed" WHERE ticket_id = ?', (reply_text, ticket_id) ) await db.commit() async def close_support_ticket(ticket_id: int): async with aiosqlite.connect(DB_PATH) as db: await db.execute('UPDATE support_tickets SET status = "closed" WHERE ticket_id = ?', (ticket_id,)) await db.commit() async def create_promo_code(code: str, type: str, value: int, max_uses: int, expiry_days: int = 30) -> bool: expiry_date = datetime.now() + timedelta(days=expiry_days) expiry_str = expiry_date.isoformat() async with aiosqlite.connect(DB_PATH) as db: await db.execute( 'INSERT OR REPLACE INTO promo_codes (code, type, value, max_uses, expiry_date) VALUES (?, ?, ?, ?, ?)', (code.upper().strip(), type, value, max_uses, expiry_str) ) await db.commit() return True async def get_all_promo_codes(): async with aiosqlite.connect(DB_PATH) as db: db.row_factory = aiosqlite.Row async with db.execute('SELECT * FROM promo_codes ORDER BY created_at DESC') as cursor: return await cursor.fetchall() async def redeem_promo_code(user_id: int, code: str) -> tuple[bool, str]: code = code.upper().strip() async with aiosqlite.connect(DB_PATH) as db: db.row_factory = aiosqlite.Row # 1. Fetch promo code async with db.execute('SELECT * FROM promo_codes WHERE code = ?', (code,)) as cursor: promo = await cursor.fetchone() if not promo: return False, "❌ کد تخفیف/هدیه وارد شده معتبر نیست." # 2. Check expiry expiry = datetime.fromisoformat(promo['expiry_date']) if datetime.now() > expiry: return False, "❌ مهلت استفاده از این کد به پایان رسیده است." # 3. Check usage limit if promo['max_uses'] is not None and promo['uses'] >= promo['max_uses']: return False, "❌ ظرفیت استفاده از این کد به پایان رسیده است." # 4. Check if user already used it async with db.execute('SELECT * FROM user_promos WHERE user_id = ? AND code = ?', (user_id, code)) as cursor: already_used = await cursor.fetchone() if already_used: return False, "❌ شما قبلاً از این کد هدیه استفاده کرده‌اید." # 5. Apply promo benefits p_type = promo['type'] p_value = promo['value'] if p_type == 'balance': await db.execute('UPDATE users SET balance = balance + ? WHERE user_id = ?', (p_value, user_id)) await db.execute( 'INSERT INTO transactions (user_id, amount, tx_type, description) VALUES (?, ?, ?, ?)', (user_id, p_value, 'promo_gift', f'دریافت هدیه بابت ثبت کد {code}') ) success_msg = f"🎁 تبریک! مبلغ {p_value:,} تومان هدیه به کیف پول شما اضافه شد." elif p_type == 'vip': await activate_vip(user_id, months=p_value) success_msg = f"🌟 تبریک! اشتراک VIP ویژه ربات به مدت {p_value} ماه برای شما فعال گردید." else: return False, "❌ نوع کد تخفیف تعریف‌نشده است." # 6. Record usage await db.execute('INSERT INTO user_promos (user_id, code) VALUES (?, ?)', (user_id, code)) await db.execute('UPDATE promo_codes SET uses = uses + 1 WHERE code = ?', (code,)) await db.commit() return True, success_msg async def add_milestones(project_id: int, milestones_list: list[dict]): async with aiosqlite.connect(DB_PATH) as db: for m in milestones_list: await db.execute( 'INSERT INTO milestones (project_id, title, amount, status) VALUES (?, ?, ?, ?)', (project_id, m['title'], m['amount'], 'held') ) await db.commit() async def get_project_milestones(project_id: int): async with aiosqlite.connect(DB_PATH) as db: db.row_factory = aiosqlite.Row async with db.execute('SELECT * FROM milestones WHERE project_id = ? ORDER BY milestone_id ASC', (project_id,)) as cursor: return await cursor.fetchall() async def release_milestone(milestone_id: int, commission_rate: float) -> bool: async with aiosqlite.connect(DB_PATH) as db: db.row_factory = aiosqlite.Row # 1. Fetch milestone details async with db.execute('SELECT * FROM milestones WHERE milestone_id = ? AND status = "held"', (milestone_id,)) as cursor: m = await cursor.fetchone() if not m: return False project_id = m['project_id'] amount = m['amount'] # 2. Fetch project details async with db.execute('SELECT chosen_freelancer_id, client_id FROM projects WHERE project_id = ?', (project_id,)) as cursor: p = await cursor.fetchone() if not p: return False freelancer_id = p['chosen_freelancer_id'] # 3. Calculate payout & commission commission = int(amount * commission_rate) payout = amount - commission # 4. Pay freelancer await db.execute('UPDATE users SET balance = balance + ? WHERE user_id = ?', (payout, freelancer_id)) await db.execute( 'INSERT INTO transactions (user_id, amount, tx_type, description) VALUES (?, ?, ?, ?)', (freelancer_id, payout, 'earning', f'دریافت فاز پرداختی پروژه #{project_id} ({m["title"]})') ) await db.execute( 'INSERT INTO transactions (user_id, amount, tx_type, description) VALUES (?, ?, ?, ?)', (freelancer_id, -commission, 'commission', f'کسر کمیسیون پلتفرم فاز پروژه #{project_id}') ) # 5. Update milestone status await db.execute('UPDATE milestones SET status = "released" WHERE milestone_id = ?', (milestone_id,)) # 6. Check if all milestones are released async with db.execute('SELECT COUNT(*) FROM milestones WHERE project_id = ? AND status = "held"', (project_id,)) as cursor: row = await cursor.fetchone() remaining_held = row[0] if remaining_held == 0: # All milestones released -> Complete the project await db.execute("UPDATE projects SET status = 'completed' WHERE project_id = ?", (project_id,)) await db.execute("UPDATE escrow SET status = 'released' WHERE project_id = ?", (project_id,)) # Check and grant referral completion bonus await check_and_grant_referral_completion_bonus(db, freelancer_id) await db.commit() return True async def refund_milestone(milestone_id: int) -> bool: async with aiosqlite.connect(DB_PATH) as db: db.row_factory = aiosqlite.Row # 1. Fetch milestone async with db.execute('SELECT * FROM milestones WHERE milestone_id = ? AND status = "held"', (milestone_id,)) as cursor: m = await cursor.fetchone() if not m: return False project_id = m['project_id'] amount = m['amount'] # 2. Fetch project async with db.execute('SELECT client_id FROM projects WHERE project_id = ?', (project_id,)) as cursor: p = await cursor.fetchone() if not p: return False client_id = p['client_id'] # 3. Refund client await db.execute('UPDATE users SET balance = balance + ? WHERE user_id = ?', (amount, client_id)) await db.execute( 'INSERT INTO transactions (user_id, amount, tx_type, description) VALUES (?, ?, ?, ?)', (client_id, amount, 'refund', f'استرداد فاز پرداختی پروژه #{project_id} ({m["title"]})') ) # 4. Update milestone status await db.execute('UPDATE milestones SET status = "refunded" WHERE milestone_id = ?', (milestone_id,)) # 5. Check remaining held milestones async with db.execute('SELECT COUNT(*) FROM milestones WHERE project_id = ? AND status = "held"', (project_id,)) as cursor: row = await cursor.fetchone() remaining_held = row[0] if remaining_held == 0: await db.execute("UPDATE projects SET status = 'cancelled' WHERE project_id = ?", (project_id,)) await db.execute("UPDATE escrow SET status = 'refunded' WHERE project_id = ?", (project_id,)) await db.commit() return True async def create_dispute_chat(project_id: int, client_id: int, freelancer_id: int): async with aiosqlite.connect(DB_PATH) as db: await db.execute( 'INSERT OR IGNORE INTO dispute_chats (project_id, client_id, freelancer_id) VALUES (?, ?, ?)', (project_id, client_id, freelancer_id) ) await db.commit() async def get_dispute_chat(project_id: int): async with aiosqlite.connect(DB_PATH) as db: db.row_factory = aiosqlite.Row async with db.execute('SELECT * FROM dispute_chats WHERE project_id = ?', (project_id,)) as cursor: return await cursor.fetchone() async def set_dispute_chat_admin(project_id: int, admin_id: int): async with aiosqlite.connect(DB_PATH) as db: await db.execute('UPDATE dispute_chats SET admin_id = ? WHERE project_id = ?', (admin_id, project_id)) await db.commit() async def get_user_badges_emojis(user_id: int) -> str: user = await get_user(user_id) if not user: return "" is_verified = user['is_verified'] if 'is_verified' in dict(user) else 0 # Check completed projects async with aiosqlite.connect(DB_PATH) as conn: conn.row_factory = aiosqlite.Row async with conn.execute( '''SELECT COUNT(*) as total_projects, COALESCE(AVG(rating_to_freelancer), 0) as avg_rating FROM projects WHERE chosen_freelancer_id = ? AND status = 'completed' AND rating_to_freelancer IS NOT NULL''', (user_id,) ) as cursor: stats = await cursor.fetchone() completed = stats['total_projects'] if stats else 0 avg_rating = stats['avg_rating'] if stats else 0.0 # Check passed skill quizzes async with conn.execute( 'SELECT quiz_id FROM user_quiz_attempts WHERE user_id = ? AND passed = 1', (user_id,) ) as cursor: quiz_rows = await cursor.fetchall() passed_quizzes = [r['quiz_id'] for r in quiz_rows] emojis = [] if is_verified == 1: emojis.append("🛡️") # Add skill badges (1: Python -> 🐍, 2: Graphics -> 🎨, 3: Translation -> 📝) if 1 in passed_quizzes: emojis.append("🐍") if 2 in passed_quizzes: emojis.append("🎨") if 3 in passed_quizzes: emojis.append("📝") if completed >= 20 and avg_rating >= 4.5: emojis.append("🥇") elif completed >= 5: emojis.append("🥈") else: emojis.append("🥉") return "".join(emojis) async def get_skill_quizzes(): async with aiosqlite.connect(DB_PATH) as db: db.row_factory = aiosqlite.Row async with db.execute('SELECT * FROM skill_quizzes ORDER BY quiz_id ASC') as cursor: return await cursor.fetchall() async def get_user_attempts(user_id: int): async with aiosqlite.connect(DB_PATH) as db: db.row_factory = aiosqlite.Row async with db.execute('SELECT * FROM user_quiz_attempts WHERE user_id = ?', (user_id,)) as cursor: return await cursor.fetchall() async def save_quiz_attempt(user_id: int, quiz_id: int, score: int, passed: int): async with aiosqlite.connect(DB_PATH) as db: await db.execute(''' INSERT OR REPLACE INTO user_quiz_attempts (user_id, quiz_id, score, passed) VALUES (?, ?, ?, ?) ''', (user_id, quiz_id, score, passed)) await db.commit() async def save_chat_message(project_id: int, sender_id: int, receiver_id: int, message_type: str, content: str, file_id: str = None, reply_to_id: int = None): async with aiosqlite.connect(DB_PATH) as db: cursor = await db.execute(''' INSERT INTO project_messages (project_id, sender_id, receiver_id, message_type, content, file_id, reply_to_id) VALUES (?, ?, ?, ?, ?, ?, ?) ''', (project_id, sender_id, receiver_id, message_type, content, file_id, reply_to_id)) await db.commit() return cursor.lastrowid async def get_project_chat_transcript(project_id: int) -> str: async with aiosqlite.connect(DB_PATH) as db: db.row_factory = aiosqlite.Row # Fetch project details async with db.execute('SELECT * FROM projects WHERE project_id = ?', (project_id,)) as cursor: proj = await cursor.fetchone() if not proj: return "پروژه یافت نشد." client = await get_user(proj['client_id']) freelancer = await get_user(proj['chosen_freelancer_id']) if proj['chosen_freelancer_id'] else None client_name = client['full_name'] if client else "کارفرما" freelancer_name = freelancer['full_name'] if freelancer else "فریلنسر" transcript = f"📄 گزارش گفتگو پروژه #{project_id}\n" transcript += f"عنوان پروژه: {proj['title']}\n" transcript += f"کارفرما: {client_name} ({proj['client_id']})\n" transcript += f"فریلنسر: {freelancer_name} ({proj['chosen_freelancer_id'] or 'انتخاب نشده'})\n" transcript += "="*40 + "\n\n" async with db.execute( 'SELECT * FROM project_messages WHERE project_id = ? ORDER BY created_at ASC', (project_id,) ) as cursor: messages = await cursor.fetchall() if not messages: transcript += "هیچ پیامی در چت ناهمگام این پروژه ثبت نشده است.\n" return transcript for msg in messages: sender_name = client_name if msg['sender_id'] == proj['client_id'] else freelancer_name time_str = msg['created_at'] msg_type = msg['message_type'] content = msg['content'] or "" file_id_str = f" [فایل: {msg['file_id']}]" if msg['file_id'] else "" transcript += f"[{time_str}] {sender_name}: " if msg_type == 'text': transcript += f"{content}\n" else: transcript += f"[{msg_type}]{file_id_str} {content}\n" return transcript # ── Jury System ────────────────────────────────────────────────────── JURY_QUORUM = 3 # Number of votes needed to resolve async def create_jury_case(project_id: int): async with aiosqlite.connect(DB_PATH) as db: await db.execute( 'INSERT OR IGNORE INTO jury_cases (project_id) VALUES (?)', (project_id,) ) await db.commit() async def get_jury_case(project_id: int): async with aiosqlite.connect(DB_PATH) as db: db.row_factory = aiosqlite.Row async with db.execute('SELECT * FROM jury_cases WHERE project_id = ?', (project_id,)) as cursor: return await cursor.fetchone() async def get_eligible_jurors(exclude_ids: list = None) -> list: """Get verified freelancers with at least 5 completed projects as jurors.""" if exclude_ids is None: exclude_ids = [] async with aiosqlite.connect(DB_PATH) as db: db.row_factory = aiosqlite.Row async with db.execute(''' SELECT u.user_id, u.full_name, u.username FROM users u WHERE u.is_verified = 1 AND u.is_banned = 0 AND (SELECT COUNT(*) FROM projects p WHERE p.chosen_freelancer_id = u.user_id AND p.status = 'completed') >= 5 ''') as cursor: all_jurors = await cursor.fetchall() # Filter out excluded IDs (client, freelancer of the project) return [j for j in all_jurors if j['user_id'] not in exclude_ids] async def cast_jury_vote(project_id: int, juror_id: int, vote: str) -> dict: """Cast a vote. Returns {'status': 'voted'|'already_voted'|'resolved', 'result': 'release'|'refund'|None, 'total_votes': int}""" async with aiosqlite.connect(DB_PATH) as db: db.row_factory = aiosqlite.Row # Check if already voted async with db.execute('SELECT * FROM jury_votes WHERE project_id = ? AND juror_id = ?', (project_id, juror_id)) as cursor: existing = await cursor.fetchone() if existing: return {'status': 'already_voted', 'result': None, 'total_votes': 0} # Check case status async with db.execute('SELECT * FROM jury_cases WHERE project_id = ?', (project_id,)) as cursor: case = await cursor.fetchone() if not case or case['status'] != 'open': return {'status': 'resolved', 'result': None, 'total_votes': 0} # Insert vote await db.execute( 'INSERT INTO jury_votes (project_id, juror_id, vote) VALUES (?, ?, ?)', (project_id, juror_id, vote) ) # Update counters if vote == 'release': await db.execute('UPDATE jury_cases SET votes_release = votes_release + 1 WHERE project_id = ?', (project_id,)) else: await db.execute('UPDATE jury_cases SET votes_refund = votes_refund + 1 WHERE project_id = ?', (project_id,)) await db.commit() # Re-read updated counters async with db.execute('SELECT * FROM jury_cases WHERE project_id = ?', (project_id,)) as cursor: case = await cursor.fetchone() total = case['votes_release'] + case['votes_refund'] # Check quorum if total >= JURY_QUORUM: result = 'release' if case['votes_release'] > case['votes_refund'] else 'refund' await db.execute('UPDATE jury_cases SET status = ? WHERE project_id = ?', (result, project_id)) await db.commit() return {'status': 'resolved', 'result': result, 'total_votes': total} return {'status': 'voted', 'result': None, 'total_votes': total} async def get_jury_votes_for_case(project_id: int) -> list: async with aiosqlite.connect(DB_PATH) as db: db.row_factory = aiosqlite.Row async with db.execute('SELECT * FROM jury_votes WHERE project_id = ?', (project_id,)) as cursor: return await cursor.fetchall() async def save_active_poll(poll_id: str, user_id: int, quiz_id: int, questions_json: str, current_index: int, score: int, quiz_title: str): async with aiosqlite.connect(DB_PATH) as db: await db.execute(''' INSERT OR REPLACE INTO active_quiz_polls (poll_id, user_id, quiz_id, questions_json, current_index, score, quiz_title) VALUES (?, ?, ?, ?, ?, ?, ?) ''', (poll_id, user_id, quiz_id, questions_json, current_index, score, quiz_title)) await db.commit() async def get_active_poll(poll_id: str): async with aiosqlite.connect(DB_PATH) as db: db.row_factory = aiosqlite.Row async with db.execute('SELECT * FROM active_quiz_polls WHERE poll_id = ?', (poll_id,)) as cursor: return await cursor.fetchone() async def delete_active_poll(poll_id: str): async with aiosqlite.connect(DB_PATH) as db: await db.execute('DELETE FROM active_quiz_polls WHERE poll_id = ?', (poll_id,)) await db.commit() async def clear_user_active_polls(user_id: int): async with aiosqlite.connect(DB_PATH) as db: await db.execute('DELETE FROM active_quiz_polls WHERE user_id = ?', (user_id,)) await db.commit() async def update_project_fulltime(project_id: int, is_fulltime: bool): async with get_db() as db: await db.execute('UPDATE projects SET is_fulltime = ? WHERE id = ?', (int(is_fulltime), project_id)) await db.commit() async def get_project_messages(project_id: int): async with aiosqlite.connect(DB_PATH) as db: db.row_factory = aiosqlite.Row async with db.execute('SELECT * FROM project_messages WHERE project_id = ? ORDER BY created_at ASC', (project_id,)) as cursor: rows = await cursor.fetchall() return [dict(row) for row in rows] async def get_freelancer_leaderboard(limit=10): async with aiosqlite.connect(DB_PATH) as db: db.row_factory = aiosqlite.Row # The leaderboard is ranked by the number of completed projects (status = 'completed') and average rating. # Since we don't have a direct 'rating' column in users, we can compute it from projects, or just use completed projects count. cursor = await db.execute(''' SELECT u.user_id, u.full_name, u.username, u.is_vip, COUNT(p.project_id) as completed_count FROM users u JOIN bids b ON u.user_id = b.freelancer_id JOIN projects p ON b.project_id = p.project_id WHERE u.role = 'freelancer' AND p.status = 'completed' AND b.status = 'accepted' GROUP BY u.user_id ORDER BY completed_count DESC, u.is_vip DESC LIMIT ? ''', (limit,)) return await cursor.fetchall() async def get_user_alerts(user_id: int): async with aiosqlite.connect(DB_PATH) as db: db.row_factory = aiosqlite.Row cursor = await db.execute("SELECT * FROM job_alerts WHERE user_id = ?", (user_id,)) return await cursor.fetchall() async def add_job_alert(user_id: int, keyword: str): async with aiosqlite.connect(DB_PATH) as db: await db.execute("INSERT INTO job_alerts (user_id, keyword) VALUES (?, ?)", (user_id, keyword)) await db.commit() return True async def delete_job_alert(alert_id: int, user_id: int): async with aiosqlite.connect(DB_PATH) as db: await db.execute("DELETE FROM job_alerts WHERE alert_id = ? AND user_id = ?", (alert_id, user_id)) await db.commit() return True async def get_matching_freelancers_for_project(title: str, description: str): async with aiosqlite.connect(DB_PATH) as db: db.row_factory = aiosqlite.Row cursor = await db.execute("SELECT user_id, keyword FROM job_alerts") alerts = await cursor.fetchall() matching_users = set() text_to_search = (title + " " + description).lower() for alert in alerts: if alert['keyword'].lower() in text_to_search: matching_users.add(alert['user_id']) return list(matching_users) async def get_freelancer_badge(user_id: int) -> str: async with aiosqlite.connect(DB_PATH) as db: db.row_factory = aiosqlite.Row async with db.execute( '''SELECT COUNT(*) as total_projects, COALESCE(AVG(rating_to_freelancer), 0) as avg_rating FROM projects WHERE chosen_freelancer_id = ? AND status = 'completed' AND rating_to_freelancer IS NOT NULL''', (user_id,) ) as cursor: stats = await cursor.fetchone() if not stats: return "تازه‌کار 🥉" total = stats['total_projects'] rating = stats['avg_rating'] if total >= 10 and rating >= 4.5: return "متخصص تاییدشده 🥇" elif total >= 3 and rating >= 4.0: return "ستاره نوظهور 🥈" else: return "تازه‌کار 🥉" async def create_service(freelancer_id: int, title: str, desc: str, price: int, days: int, file_id: str): async with aiosqlite.connect(DB_PATH) as db: cursor = await db.execute( "INSERT INTO services (freelancer_id, title, description, price, delivery_days, file_id) VALUES (?, ?, ?, ?, ?, ?)", (freelancer_id, title, desc, price, days, file_id) ) await db.commit() return cursor.lastrowid async def get_freelancer_services(freelancer_id: int): async with aiosqlite.connect(DB_PATH) as db: db.row_factory = aiosqlite.Row cursor = await db.execute("SELECT * FROM services WHERE freelancer_id = ?", (freelancer_id,)) return await cursor.fetchall() async def get_all_services(limit: int = 10, offset: int = 0): async with aiosqlite.connect(DB_PATH) as db: db.row_factory = aiosqlite.Row cursor = await db.execute( "SELECT s.*, u.full_name as freelancer_name, u.username as freelancer_username FROM services s JOIN users u ON s.freelancer_id = u.user_id ORDER BY s.created_at DESC LIMIT ? OFFSET ?", (limit, offset) ) return await cursor.fetchall() async def count_all_services(): async with aiosqlite.connect(DB_PATH) as db: cursor = await db.execute("SELECT COUNT(*) FROM services") return (await cursor.fetchone())[0] async def get_service(service_id: int): async with aiosqlite.connect(DB_PATH) as db: db.row_factory = aiosqlite.Row cursor = await db.execute("SELECT s.*, u.full_name as freelancer_name, u.username as freelancer_username FROM services s JOIN users u ON s.freelancer_id = u.user_id WHERE s.service_id = ?", (service_id,)) return await cursor.fetchone() async def delete_service(service_id: int, freelancer_id: int): async with aiosqlite.connect(DB_PATH) as db: await db.execute("DELETE FROM services WHERE service_id = ? AND freelancer_id = ?", (service_id, freelancer_id)) await db.commit() async def get_freelancer_scorecard(freelancer_id: int) -> dict: async with aiosqlite.connect(DB_PATH) as conn: conn.row_factory = aiosqlite.Row # 1. Basic completed projects and rating stats async with conn.execute( '''SELECT COUNT(*) as total, COALESCE(AVG(rating_to_freelancer), 0) as avg_rating FROM projects WHERE chosen_freelancer_id = ? AND status = 'completed' AND rating_to_freelancer IS NOT NULL''', (freelancer_id,) ) as cursor: stats = await cursor.fetchone() completed_count = stats['total'] if stats else 0 avg_rating = stats['avg_rating'] if stats else 0.0 # 2. On-Time Delivery Rate async with conn.execute( 'SELECT COUNT(*) FROM projects WHERE chosen_freelancer_id = ? AND status = "disputed"', (freelancer_id,) ) as cursor: row = await cursor.fetchone() disputed_count = row[0] if row else 0 on_time_rate = 100 if completed_count > 0 or disputed_count > 0: total_active = completed_count + disputed_count on_time_rate = int(((completed_count) / total_active) * 100) if total_active > 0 else 100 # Response Rate (simulated based on bid response or default high) response_rate = 98 # Professional default return { 'completed_count': completed_count, 'avg_rating': round(avg_rating, 1), 'on_time_rate': on_time_rate, 'response_rate': response_rate } async def get_project_chat_summary(project_id: int, limit: int = 5): async with aiosqlite.connect(DB_PATH) as db: db.row_factory = aiosqlite.Row async with db.execute( '''SELECT pm.*, u.full_name as sender_name FROM project_messages pm JOIN users u ON pm.sender_id = u.user_id WHERE pm.project_id = ? ORDER BY pm.created_at DESC LIMIT ?''', (project_id, limit) ) as cursor: rows = await cursor.fetchall() return list(reversed(rows)) async def award_gamification_rewards(freelancer_id: int) -> tuple[int, str]: async with aiosqlite.connect(DB_PATH) as db: # 1. Count completed projects async with db.execute('SELECT COUNT(*) FROM projects WHERE chosen_freelancer_id = ? AND status = "completed"', (freelancer_id,)) as cursor: row = await cursor.fetchone() completed_count = row[0] if row else 0 # Determine reward bonus_connects = 0 reward_key = "" badge_name = "" if completed_count == 1: bonus_connects = 5 reward_key = "completed_1" badge_name = "🥇 قهرمان کار" elif completed_count == 5: bonus_connects = 15 reward_key = "completed_5" badge_name = "⭐ ستاره ایکس‌لنسر" elif completed_count == 10: bonus_connects = 30 reward_key = "completed_10" badge_name = "💎 اسطوره ایکس‌لنسر" if bonus_connects > 0: # 2. Check if reward transaction already exists desc_check = f"%{reward_key}%" async with db.execute('SELECT COUNT(*) FROM transactions WHERE user_id = ? AND tx_type = "milestone_reward" AND description LIKE ?', (freelancer_id, desc_check)) as cursor: t_row = await cursor.fetchone() exists = t_row[0] if t_row else 0 if exists == 0: # Award connects await db.execute('UPDATE users SET connects = connects + ? WHERE user_id = ?', (bonus_connects, freelancer_id)) # Insert transaction await db.execute( 'INSERT INTO transactions (user_id, amount, tx_type, description) VALUES (?, 0, ?, ?)', (freelancer_id, 'milestone_reward', f'هدیه {bonus_connects} کانکت به مناسبت کسب نشان {badge_name} ({reward_key})') ) await db.commit() return bonus_connects, badge_name return 0, "" async def get_weekly_freelancer_leaderboard(limit=5): async with aiosqlite.connect(DB_PATH) as db: db.row_factory = aiosqlite.Row # Rank by count of 'earning' transactions in the last 7 days cursor = await db.execute(''' SELECT u.user_id, u.full_name, u.username, u.is_vip, COUNT(t.tx_id) as completed_count FROM users u JOIN transactions t ON u.user_id = t.user_id WHERE u.role = 'freelancer' AND t.tx_type = 'earning' AND t.created_at >= datetime('now', '-7 days') GROUP BY u.user_id ORDER BY completed_count DESC, u.is_vip DESC LIMIT ? ''', (limit,)) return await cursor.fetchall() async def get_tickets_by_status(status: str = 'open'): async with aiosqlite.connect(DB_PATH) as db: db.row_factory = aiosqlite.Row async with db.execute('SELECT * FROM support_tickets WHERE status = ? ORDER BY created_at DESC', (status,)) as cursor: return await cursor.fetchall() async def get_recent_transactions_by_user(user_id: int, limit: int = 10): async with aiosqlite.connect(DB_PATH) as db: db.row_factory = aiosqlite.Row async with db.execute( 'SELECT * FROM transactions WHERE user_id = ? ORDER BY created_at DESC LIMIT ?', (user_id, limit) ) as cursor: return await cursor.fetchall() async def get_active_projects_by_user(user_id: int): async with aiosqlite.connect(DB_PATH) as db: db.row_factory = aiosqlite.Row async with db.execute( '''SELECT * FROM projects WHERE (client_id = ? OR chosen_freelancer_id = ?) AND status IN ('open', 'in_progress', 'delivered', 'disputed') ORDER BY created_at DESC''', (user_id, user_id) ) as cursor: return await cursor.fetchall() async def resolve_dispute_split(project_id: int, freelancer_amount: int, client_amount: int) -> bool: async with aiosqlite.connect(DB_PATH) as db: db.row_factory = aiosqlite.Row # 1. Get project async with db.execute('SELECT * FROM projects WHERE project_id = ?', (project_id,)) as cursor: project = await cursor.fetchone() if not project: return False client_id = project['client_id'] freelancer_id = project['chosen_freelancer_id'] if not freelancer_id: return False # 2. Get escrow record async with db.execute('SELECT * FROM escrow WHERE project_id = ? AND status = "held"', (project_id,)) as cursor: escrow = await cursor.fetchone() if not escrow: return False # 3. Update status await db.execute("UPDATE projects SET status = 'completed' WHERE project_id = ?", (project_id,)) await db.execute("UPDATE escrow SET status = 'released' WHERE project_id = ?", (project_id,)) # 4. Handle client refund if client_amount > 0: await db.execute('UPDATE users SET balance = balance + ? WHERE user_id = ?', (client_amount, client_id)) await db.execute( 'INSERT INTO transactions (user_id, amount, tx_type, description) VALUES (?, ?, ?, ?)', (client_id, client_amount, 'refund', f'استرداد بخشی از وجه داوری پروژه #{project_id}') ) # 5. Handle freelancer payout if freelancer_amount > 0: async with db.execute('SELECT is_vip FROM users WHERE user_id = ?', (freelancer_id,)) as cursor: u_row = await cursor.fetchone() is_vip = u_row['is_vip'] if u_row else 0 async with db.execute('SELECT val FROM settings WHERE key = ?', ('commission_vip',)) as cursor: v_row = await cursor.fetchone() comm_vip = float(v_row['val']) if v_row else 0.05 async with db.execute('SELECT val FROM settings WHERE key = ?', ('commission_free',)) as cursor: f_row = await cursor.fetchone() comm_free = float(f_row['val']) if f_row else 0.10 commission_rate = comm_vip if is_vip else comm_free commission = int(freelancer_amount * commission_rate) payout = freelancer_amount - commission await db.execute('UPDATE users SET balance = balance + ? WHERE user_id = ?', (payout, freelancer_id)) await db.execute( 'INSERT INTO transactions (user_id, amount, tx_type, description) VALUES (?, ?, ?, ?)', (freelancer_id, payout, 'earning', f'دریافت سهم داوری پروژه #{project_id}') ) if commission > 0: await db.execute( 'INSERT INTO transactions (user_id, amount, tx_type, description) VALUES (?, ?, ?, ?)', (freelancer_id, -commission, 'commission', f'کسر کمیسیون پلتفرم پروژه #{project_id}') ) # 6. Resolve milestones await db.execute("UPDATE milestones SET status = 'released' WHERE project_id = ? AND status = 'held'", (project_id,)) await db.commit() return True async def get_recent_activity_stats(): async with aiosqlite.connect(DB_PATH) as db: db.row_factory = aiosqlite.Row # New users in last 24h async with db.execute("SELECT COUNT(*) FROM users WHERE joined_at >= datetime('now', '-1 day')") as cursor: row = await cursor.fetchone() new_users_24h = row[0] if row else 0 # New projects in last 24h async with db.execute("SELECT COUNT(*) FROM projects WHERE created_at >= datetime('now', '-1 day')") as cursor: row = await cursor.fetchone() new_projects_24h = row[0] if row else 0 # Completed projects in last 24h async with db.execute("SELECT COUNT(*) FROM projects WHERE status = 'completed' AND created_at >= datetime('now', '-1 day')") as cursor: row = await cursor.fetchone() completed_projects_24h = row[0] if row else 0 return { 'new_users_24h': new_users_24h, 'new_projects_24h': new_projects_24h, 'completed_projects_24h': completed_projects_24h } async def get_project_transactions(project_id: int): async with aiosqlite.connect(DB_PATH) as db: db.row_factory = aiosqlite.Row async with db.execute( "SELECT * FROM transactions WHERE description LIKE ? ORDER BY created_at ASC", (f'%#{project_id}%',) ) as cursor: rows = await cursor.fetchall() return [dict(r) for r in rows]