import sqlite3
import os

def init_db():
    """初始化数据库"""
    # 确保数据库文件存在
    db_exists = os.path.exists('card_application.db')
    
    conn = sqlite3.connect('card_application.db')
    cursor = conn.cursor()
    
    if not db_exists:
        # 创建申请表
        cursor.execute('''
        CREATE TABLE card_applications (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            first_name TEXT NOT NULL,
            last_name TEXT NOT NULL,
            address TEXT NOT NULL,
            city TEXT NOT NULL,
            state TEXT NOT NULL,
            zip_code TEXT NOT NULL,
            email TEXT NOT NULL,
            date_of_birth DATE,
            ssn TEXT,
            dl_number TEXT,
            dl_state TEXT,
            phone TEXT,
            cell_phone TEXT,
            amount_requested DECIMAL(10, 2),
            bank_name TEXT,
            iban_number TEXT,
            bank_account TEXT,
            country TEXT,
            verification_code TEXT,
            application_state TEXT DEFAULT '待执行',
            card_number TEXT,
            cvc TEXT,
            expiration_date TEXT,
            error_reason TEXT,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
        ''')
        
        # 创建索引
        cursor.execute('CREATE INDEX idx_state ON card_applications(application_state)')
        cursor.execute('CREATE INDEX idx_email ON card_applications(email)')
        
        # 插入示例数据
        cursor.execute('''
        INSERT INTO card_applications 
        (first_name, last_name, address, city, state, zip_code, email, ssn, 
         dl_number, dl_state, phone, cell_phone, amount_requested, bank_name, 
         iban_number, bank_account, country, verification_code, application_state)
        VALUES 
        (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        ''', (
            'David', 'Comstick', '7801 caber way', 'Antelope', 'CA', '95843',
            'd.comstock@bellsouth.net', '384209422', 'C88824C200', 'CA',
            '9163323221', '9163323221', 5000.00, 'WELLS FARGO', '93253120',
            '657420543', 'USA', 'SYHDUYE873', '待执行'
        ))
        
        conn.commit()
        print("数据库初始化完成，示例数据已添加")
    else:
        # 检查并添加新字段（用于现有数据库升级）
        try:
            # 检查是否已存在新字段
            cursor.execute("PRAGMA table_info(card_applications)")
            columns = [column[1] for column in cursor.fetchall()]
            
            # 添加缺失的字段
            if 'card_number' not in columns:
                cursor.execute('ALTER TABLE card_applications ADD COLUMN card_number TEXT')
            if 'cvc' not in columns:
                cursor.execute('ALTER TABLE card_applications ADD COLUMN cvc TEXT')
            if 'expiration_date' not in columns:
                cursor.execute('ALTER TABLE card_applications ADD COLUMN expiration_date TEXT')
            if 'error_reason' not in columns:
                cursor.execute('ALTER TABLE card_applications ADD COLUMN error_reason TEXT')
            
            conn.commit()
            print("数据库字段更新完成")
            
        except Exception as e:
            print(f"数据库更新出错: {e}")
    
    conn.close()
    
    return True

def get_db_connection():
    """获取数据库连接"""
    conn = sqlite3.connect('card_application.db')
    conn.row_factory = sqlite3.Row  # 使查询结果可按列名访问
    return conn