#!/usr/bin/env python
# -*- coding: utf-8 -*-
import sqlite3
import os

DATABASE_PATH = os.path.join(os.path.dirname(__file__), '../database/app.db')

def get_db():
    conn = sqlite3.connect(DATABASE_PATH)
    conn.row_factory = sqlite3.Row
    return conn

print('=== 数据库重复数据清理 ===')

if os.path.exists(DATABASE_PATH):
    print(f'数据库文件存在: {DATABASE_PATH}')
    conn = get_db()
    cursor = conn.cursor()
    
    # === 清理轮播图重复数据 ===
    print('\n=== 清理轮播图重复数据 ===')
    cursor.execute('''
        SELECT page_type, sort_order, COUNT(*) as count, MIN(id) as keep_id
        FROM carousels
        GROUP BY page_type, sort_order
        HAVING COUNT(*) > 1
    ''')
    carousel_duplicates = cursor.fetchall()
    
    if carousel_duplicates:
        for dup in carousel_duplicates:
            print(f'page_type={dup["page_type"]}, sort_order={dup["sort_order"]}, 数量={dup["count"]}, 保留ID={dup["keep_id"]}')
            cursor.execute('''
                DELETE FROM carousels
                WHERE page_type = ? AND sort_order = ? AND id != ?
            ''', (dup['page_type'], dup['sort_order'], dup['keep_id']))
        conn.commit()
        print('轮播图重复数据已清理')
    else:
        print('轮播图没有重复数据')
    
    # === 清理任务表重复标题 ===
    print('\n=== 清理任务表重复标题 ===')
    cursor.execute('''
        SELECT title, COUNT(*) as count, MIN(id) as keep_id
        FROM tasks
        GROUP BY title
        HAVING COUNT(*) > 1
    ''')
    task_duplicates = cursor.fetchall()
    
    if task_duplicates:
        for dup in task_duplicates:
            print(f'标题="{dup["title"]}", 数量={dup["count"]}, 保留ID={dup["keep_id"]}')
            cursor.execute('''
                DELETE FROM tasks
                WHERE title = ? AND id != ?
            ''', (dup['title'], dup['keep_id']))
        conn.commit()
        print('任务表重复标题已清理')
    else:
        print('任务表没有重复标题')
    
    # === 显示清理后的数据 ===
    print('\n=== 清理后轮播图数据 ===')
    cursor.execute('SELECT * FROM carousels ORDER BY page_type, sort_order')
    carousels = cursor.fetchall()
    for c in carousels:
        print(f'ID={c["id"]}, page_type={c["page_type"]}, sort_order={c["sort_order"]}')
    
    print('\n=== 清理后任务表数据 ===')
    cursor.execute('SELECT id, title FROM tasks ORDER BY id')
    tasks = cursor.fetchall()
    for t in tasks:
        print(f'ID={t["id"]}, title={t["title"]}')
    
    conn.close()
    print('\n清理完成!')
else:
    print('数据库文件不存在')