import sqlite3
import datetime
import os

DB_NAME = "guarantee_manager.db"

def get_db_connection():
    conn = sqlite3.connect(DB_NAME)
    conn.row_factory = sqlite3.Row
    return conn

def init_db():
    conn = get_db_connection()
    c = conn.cursor()
    
    # جدول کاربران (برای لاگین ساده)
    c.execute('''
        CREATE TABLE IF NOT EXISTS users (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            username TEXT UNIQUE NOT NULL,
            password TEXT NOT NULL
        )
    ''')
    
    # جدول پیمانکاران
    c.execute('''
        CREATE TABLE IF NOT EXISTS contractors (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            company_name TEXT NOT NULL,
            national_id TEXT,
            phone TEXT,
            address TEXT
        )
    ''')
    
    # جدول ضمانت‌نامه‌ها
    c.execute('''
        CREATE TABLE IF NOT EXISTS guarantees (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            guarantee_number TEXT UNIQUE NOT NULL,
            type TEXT NOT NULL,
            bank_name TEXT NOT NULL,
            branch_name TEXT,
            amount REAL NOT NULL,
            issue_date TEXT NOT NULL,
            due_date TEXT NOT NULL,
            status TEXT NOT NULL,
            contractor_id INTEGER,
            contract_info TEXT,
            description TEXT,
            file_path TEXT,
            FOREIGN KEY (contractor_id) REFERENCES contractors (id)
        )
    ''')
    
    # ایجاد کاربر پیش‌فرض اگر وجود ندارد (admin/admin)
    # در نسخه واقعی باید پسورد هش شود
    c.execute("SELECT * FROM users WHERE username = 'admin'")
    if not c.fetchone():
        c.execute("INSERT INTO users (username, password) VALUES ('admin', 'admin')")
        
    conn.commit()
    conn.close()

# --- توابع پیمانکاران ---
def add_contractor(name, nid, phone, address):
    conn = get_db_connection()
    try:
        conn.execute("INSERT INTO contractors (company_name, national_id, phone, address) VALUES (?, ?, ?, ?)",
                     (name, nid, phone, address))
        conn.commit()
        return True, "پیمانکار با موفقیت افزوده شد."
    except Exception as e:
        return False, str(e)
    finally:
        conn.close()

def get_contractors_list():
    conn = get_db_connection()
    rows = conn.execute("SELECT id, company_name FROM contractors").fetchall()
    conn.close()
    return [(r["company_name"], r["id"]) for r in rows]

def get_all_contractors_df():
    conn = get_db_connection()
    rows = conn.execute("SELECT id, company_name, national_id, phone, address FROM contractors").fetchall()
    conn.close()
    return [list(r) for r in rows]

# --- توابع ضمانت‌نامه‌ها ---
def add_guarantee(g_num, g_type, bank, branch, amount, i_date, d_date, status, c_id, c_info, desc, f_path):
    conn = get_db_connection()
    try:
        conn.execute('''
            INSERT INTO guarantees 
            (guarantee_number, type, bank_name, branch_name, amount, issue_date, due_date, status, contractor_id, contract_info, description, file_path)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        ''', (g_num, g_type, bank, branch, amount, i_date, d_date, status, c_id, c_info, desc, f_path))
        conn.commit()
        return True, "ضمانت‌نامه ذخیره شد."
    except sqlite3.IntegrityError:
        return False, "شماره ضمانت‌نامه تکراری است."
    except Exception as e:
        return False, str(e)
    finally:
        conn.close()

def search_guarantees(search_query="", status_filter=None):
    conn = get_db_connection()
    query = '''
        SELECT g.id, g.guarantee_number, g.type, g.bank_name, g.amount, g.due_date, g.status, c.company_name, g.contract_info
        FROM guarantees g
        LEFT JOIN contractors c ON g.contractor_id = c.id
        WHERE 1=1
    '''
    params = []
    
    if status_filter and status_filter != "همه":
        query += " AND g.status = ?"
        params.append(status_filter)
        
    if search_query:
        query += " AND (g.guarantee_number LIKE ? OR c.company_name LIKE ? OR g.bank_name LIKE ?)"
        params.append(f"%{search_query}%")
        params.append(f"%{search_query}%")
        params.append(f"%{search_query}%")
        
    rows = conn.execute(query, params).fetchall()
    conn.close()
    return [list(r) for r in rows]

def get_alerts(days_threshold=30):
    conn = get_db_connection()
    today = datetime.date.today()
    target_date = today + datetime.timedelta(days=days_threshold)
    
    # تبدیل تاریخ‌ها برای مقایسه رشته‌ای (فرض بر فرمت YYYY-MM-DD)
    today_str = today.strftime("%Y-%m-%d")
    target_str = target_date.strftime("%Y-%m-%d")
    
    query = '''
        SELECT g.guarantee_number, g.bank_name, g.due_date, c.company_name, g.amount
        FROM guarantees g
        LEFT JOIN contractors c ON g.contractor_id = c.id
        WHERE g.status = 'فعال' AND g.due_date <= ? AND g.due_date >= ?
        ORDER BY g.due_date ASC
    '''
    rows = conn.execute(query, (target_str, today_str)).fetchall()
    conn.close()
    return [list(r) for r in rows]

def get_stats():
    conn = get_db_connection()
    total_active = conn.execute("SELECT COUNT(*), SUM(amount) FROM guarantees WHERE status='فعال'").fetchone()
    total_expired = conn.execute("SELECT COUNT(*) FROM guarantees WHERE status='فعال' AND due_date < date('now')").fetchone()
    total_released = conn.execute("SELECT COUNT(*) FROM guarantees WHERE status='آزاد شده'").fetchone()
    conn.close()
    
    count = total_active[0] if total_active[0] else 0
    amount = total_active[1] if total_active[1] else 0
    expired = total_expired[0] if total_expired[0] else 0
    released = total_released[0] if total_released[0] else 0
    
    return count, amount, expired, released

def check_auth(username, password):
    conn = get_db_connection()
    user = conn.execute("SELECT * FROM users WHERE username = ? AND password = ?", (username, password)).fetchone()
    conn.close()
    return user is not None