#!/usr/bin/env python3
"""
数据库操作模块
"""

import sqlite3
from flask import g
from config import DB_FILE


def get_db():
    """获取数据库连接"""
    if 'db' not in g:
        g.db = sqlite3.connect(DB_FILE)
        g.db.row_factory = sqlite3.Row
    return g.db


def close_db(error):
    """关闭数据库连接"""
    if hasattr(g, 'db'):
        g.db.close()


def init_db():
    """初始化数据库"""
    conn = sqlite3.connect(DB_FILE)
    conn.row_factory = sqlite3.Row
    c = conn.cursor()
    
    # Cron 任务表
    c.execute('''CREATE TABLE IF NOT EXISTS cron_tasks (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        expression TEXT NOT NULL,
        command TEXT NOT NULL,
        description TEXT,
        category TEXT DEFAULT 'other',
        enabled INTEGER DEFAULT 1,
        created_at TEXT DEFAULT CURRENT_TIMESTAMP,
        updated_at TEXT DEFAULT CURRENT_TIMESTAMP
    )''')
    
    # Cron 历史版本表
    c.execute('''CREATE TABLE IF NOT EXISTS cron_history (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        task_id INTEGER NOT NULL,
        version INTEGER NOT NULL,
        name TEXT,
        expression TEXT,
        command TEXT,
        description TEXT,
        category TEXT,
        enabled INTEGER,
        operation TEXT,
        operator TEXT DEFAULT 'system',
        created_at TEXT DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (task_id) REFERENCES cron_tasks(id)
    )''')
    
    # Cron 执行日志表
    c.execute('''CREATE TABLE IF NOT EXISTS cron_logs (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        task_id INTEGER,
        execution_time TEXT DEFAULT CURRENT_TIMESTAMP,
        status TEXT,
        output TEXT,
        FOREIGN KEY (task_id) REFERENCES cron_tasks(id)
    )''')
    
    # 创建索引
    c.execute('CREATE INDEX IF NOT EXISTS idx_cron_history_task ON cron_history(task_id)')
    c.execute('CREATE INDEX IF NOT EXISTS idx_cron_logs_task ON cron_logs(task_id)')
    
    conn.commit()
    conn.close()