ylt_diy/lib/db_init.py

"""
数据库初始化
"""
from lib.db import execute_update, execute_query
from lib.logger import log_info, log_error, log_warning


def _column_exists(table_name, column_name):
    """检查字段是否存在（通过 information_schema 查询）"""
    try:
        result = execute_query("""
            SELECT COUNT(*) as cnt 
            FROM information_schema.columns 
            WHERE table_schema = DATABASE() 
              AND table_name = %s 
              AND column_name = %s
        """, (table_name, column_name))
        return result and result[0]['cnt'] > 0
    except Exception:
        return False


def _add_column_if_not_exists(table_name, column_def_sql, column_name):
    """安全地添加字段：先检查是否存在，不存在再添加"""
    if _column_exists(table_name, column_name):
        log_info(f"[数据库] 字段 {table_name}.{column_name} 已存在，跳过", 'db_init')
        return True
    try:
        execute_update(f"ALTER TABLE {table_name} ADD COLUMN {column_def_sql}")
        log_info(f"[数据库] 新增字段 {table_name}.{column_name} 成功", 'db_init')
        return True
    except Exception as e:
        log_warning(f"[数据库] 新增字段 {table_name}.{column_name} 失败: {e}", 'db_init')
        return False


def _set_table_comment(table_name, comment):
    """设置表备注（Doris暂不支持通过SQL修改表注释，预留函数）"""
    pass


def init_database_tables():
    """初始化所有数据库表（应用启动时自动调用）"""
    try:
        # 创建配置表
        create_config_table = """
            CREATE TABLE IF NOT EXISTS t_daily_report_config (
                id BIGINT NOT NULL COMMENT '配置 ID',
                config_name VARCHAR(100) NOT NULL COMMENT '配置名称',
                split_type VARCHAR(20) NOT NULL COMMENT '拆分方式',
                split_value VARCHAR(100) NOT NULL COMMENT '拆分值',
                split_name VARCHAR(200) NULL COMMENT '拆分显示名称',
                selected_fields VARCHAR(3000) NOT NULL COMMENT '选择的字段列表',
                sum_fields VARCHAR(3000) NULL COMMENT '求和字段列表',
                time_periods VARCHAR(1000) NULL COMMENT '分时段电量配置',
                merge_telecom TINYINT NULL COMMENT '是否合并特来电数据',
                telecom_vehicle_no VARCHAR(500) NULL COMMENT '特来电车量自编号',
                field_custom_names VARCHAR(2000) NULL COMMENT '字段自定义报表名头',
                show_monthly_total TINYINT NULL COMMENT '是否显示当月充电量总计',
                custom_service_fee_price DECIMAL(10,4) NULL COMMENT '自定义服务费单价(元/kWh)',
                custom_service_fee_name VARCHAR(100) NULL COMMENT '自定义服务费报表表头名称',
                is_active TINYINT NULL COMMENT '是否启用',
                sort_order INT NULL COMMENT '排序顺序',
                create_time DATETIME NULL COMMENT '创建时间',
                update_time DATETIME NULL COMMENT '更新时间'
            )
            UNIQUE KEY(id)
            DISTRIBUTED BY HASH(id) BUCKETS 1
            PROPERTIES("replication_num" = "1")
        """
        execute_update(create_config_table)
        _set_table_comment('t_daily_report_config', '日报配置表')
        log_info("[数据库] t_daily_report_config 表已就绪", 'db_init')

        # 创建历史表
        create_history_table = """
            CREATE TABLE IF NOT EXISTS t_daily_report_history (
                id BIGINT NOT NULL COMMENT '历史 ID',
                report_date VARCHAR(20) NULL COMMENT '报表日期',
                start_time VARCHAR(50) NULL COMMENT '开始时间',
                end_time VARCHAR(50) NULL COMMENT '结束时间',
                split_type VARCHAR(20) NULL COMMENT '拆分方式',
                split_value VARCHAR(100) NULL COMMENT '拆分值',
                split_name VARCHAR(200) NULL COMMENT '拆分显示名称',
                config_id BIGINT NULL COMMENT '配置 ID',
                config_name VARCHAR(200) NULL COMMENT '配置名称',
                total_orders INT NULL COMMENT '订单总数',
                total_amount DECIMAL(10,2) NULL COMMENT '总金额',
                sum_results VARCHAR(2000) NULL COMMENT '求和结果',
                file_path VARCHAR(500) NULL COMMENT '文件路径',
                status TINYINT NULL COMMENT '状态',
                create_time DATETIME NULL COMMENT '创建时间'
            )
            UNIQUE KEY(id)
            DISTRIBUTED BY HASH(id) BUCKETS 1
            PROPERTIES("replication_num" = "1")
        """
        execute_update(create_history_table)
        _set_table_comment('t_daily_report_history', '日报生成历史记录表')
        log_info("[数据库] t_daily_report_history 表已就绪", 'db_init')

        # 为已存在的表添加 config_name 字段（如果不存在）
        _add_column_if_not_exists(
            't_daily_report_history',
            "config_name VARCHAR(200) NULL COMMENT '配置名称'",
            'config_name'
        )

        # 创建求和数据表
        create_sum_data_table = """
            CREATE TABLE IF NOT EXISTS t_daily_report_sum_data (
                id BIGINT NOT NULL COMMENT '主键 ID',
                report_date VARCHAR(20) NULL COMMENT '报表日期',
                config_id BIGINT NULL COMMENT '配置 ID',
                config_name VARCHAR(200) NULL COMMENT '配置名称',
                split_type VARCHAR(20) NULL COMMENT '拆分方式',
                split_value VARCHAR(100) NULL COMMENT '拆分值',
                split_name VARCHAR(200) NULL COMMENT '拆分显示名称',
                sum_field_key VARCHAR(100) NULL COMMENT '求和字段键名',
                sum_field_name VARCHAR(100) NULL COMMENT '求和字段中文名称',
                sum_value DECIMAL(18,3) NULL COMMENT '求和值',
                total_orders INT NULL COMMENT '订单数',
                sort_order INT NULL COMMENT '配置排序号',
                data_source VARCHAR(20) NULL COMMENT '数据来源 auto=自动采集 manual=手动编辑',
                remark VARCHAR(500) NULL COMMENT '备注',
                create_time DATETIME NULL COMMENT '创建时间',
                update_time DATETIME NULL COMMENT '更新时间'
            )
            UNIQUE KEY(id)
            DISTRIBUTED BY HASH(id) BUCKETS 1
            PROPERTIES("replication_num" = "1")
        """
        execute_update(create_sum_data_table)
        _set_table_comment('t_daily_report_sum_data', '日报求和数据采集表')
        log_info("[数据库] t_daily_report_sum_data 表已就绪", 'db_init')

        # 给配置表增加 show_monthly_total 字段
        _add_column_if_not_exists(
            't_daily_report_config',
            "show_monthly_total TINYINT NULL COMMENT '是否显示当月充电量总计'",
            'show_monthly_total'
        )

        # 给求和数据表增加 sort_order 字段
        _add_column_if_not_exists(
            't_daily_report_sum_data',
            "sort_order INT NULL COMMENT '配置排序号'",
            'sort_order'
        )
        
        # 给配置表增加 custom_service_fee_price 字段（自定义服务费单价）
        _add_column_if_not_exists(
            't_daily_report_config',
            "custom_service_fee_price DECIMAL(10,4) NULL COMMENT '自定义服务费单价(元/kWh)'",
            'custom_service_fee_price'
        )
        
        # 给配置表增加 custom_service_fee_name 字段（自定义服务费表头名称）
        _add_column_if_not_exists(
            't_daily_report_config',
            "custom_service_fee_name VARCHAR(100) NULL COMMENT '自定义服务费报表表头名称'",
            'custom_service_fee_name'
        )
        
        # 给配置表增加 show_custom_service_fee 字段（是否显示自定义服务费列）
        _add_column_if_not_exists(
            't_daily_report_config',
            "show_custom_service_fee TINYINT NULL COMMENT '是否显示自定义服务费列(0=不显示,1=显示)'",
            'show_custom_service_fee'
        )
        
        # 给配置表增加 show_total_amount 字段（是否显示实收金额列）
        _add_column_if_not_exists(
            't_daily_report_config',
            "show_total_amount TINYINT NULL COMMENT '是否显示实收金额列(0=不显示,1=显示)'",
            'show_total_amount'
        )
        
        # 给配置表增加 total_amount_name 字段（实收金额表头名称）
        _add_column_if_not_exists(
            't_daily_report_config',
            "total_amount_name VARCHAR(100) NULL COMMENT '实收金额报表表头名称'",
            'total_amount_name'
        )

        # 给配置表增加 balance_electricity 字段（是否找平电量）
        # 当尖+峰+平+谷之和不等于充电电量时，以充电电量为准进行找平
        # 找平顺序：谷 → 平 → 峰 → 尖（加到第一个有值的时段上）
        _add_column_if_not_exists(
            't_daily_report_config',
            "balance_electricity TINYINT NULL DEFAULT 1 COMMENT '是否找平电量(0=否,1=是)'",
            'balance_electricity'
        )
        
        # 创建全局配置表
        create_global_config_table = """
            CREATE TABLE IF NOT EXISTS t_daily_report_global_config (
                id BIGINT NOT NULL DEFAULT 1 COMMENT '主键（固定为1）',
                sum_data_fields VARCHAR(1000) NULL COMMENT '求和数据采集和显示字段列表(JSON格式)',
                create_time DATETIME NULL COMMENT '创建时间',
                update_time DATETIME NULL COMMENT '更新时间'
            )
            UNIQUE KEY(id)
            DISTRIBUTED BY HASH(id) BUCKETS 1
            PROPERTIES("replication_num" = "1")
        """
        execute_update(create_global_config_table)
        _set_table_comment('t_daily_report_global_config', '日报全局配置表')
        log_info("[数据库] t_daily_report_global_config 表已就绪", 'db_init')

        # 初始化全局配置记录（如果不存在）
        try:
            exists = execute_query("SELECT COUNT(*) as cnt FROM t_daily_report_global_config WHERE id = 1")
            if not exists or exists[0]['cnt'] == 0:
                execute_update(
                    "INSERT INTO t_daily_report_global_config (id, create_time, update_time) VALUES (1, NOW(), NOW())"
                )
                log_info("[数据库] 初始化全局配置记录成功", 'db_init')
        except Exception as e:
            log_warning(f"[数据库] 初始化全局配置记录失败: {e}", 'db_init')

        # 数据迁移：给 sort_order 为 NULL 的配置记录按 id 顺序赋值
        try:
            null_count = execute_query(
                "SELECT COUNT(*) as cnt FROM t_daily_report_config WHERE sort_order IS NULL"
            )
            if null_count and null_count[0]['cnt'] > 0:
                log_info(f"[数据库] 检测到 {null_count[0]['cnt']} 条配置 sort_order 为空，开始初始化排序...", 'db_init')
                all_configs = execute_query(
                    "SELECT id FROM t_daily_report_config ORDER BY id ASC"
                )
                for idx, cfg in enumerate(all_configs):
                    execute_update(
                        "UPDATE t_daily_report_config SET sort_order = %s WHERE id = %s",
                        (idx + 1, cfg['id'])
                    )
                log_info("[数据库] 配置 sort_order 初始化完成", 'db_init')
        except Exception as e:
            log_warning(f"[数据库] 初始化 sort_order 时出错: {e}", 'db_init')
        
        # 创建管理员用户表
        create_admin_table = """
            CREATE TABLE IF NOT EXISTS t_daily_report_admin (
                id BIGINT NOT NULL COMMENT '用户 ID',
                username VARCHAR(50) NOT NULL COMMENT '用户名',
                password VARCHAR(200) NOT NULL COMMENT '密码（加密存储）',
                real_name VARCHAR(50) NULL COMMENT '真实姓名',
                token VARCHAR(200) NULL COMMENT '登录令牌',
                token_expire DATETIME NULL COMMENT '令牌过期时间',
                last_login_time DATETIME NULL COMMENT '最后登录时间',
                last_login_ip VARCHAR(50) NULL COMMENT '最后登录IP',
                create_time DATETIME NULL COMMENT '创建时间',
                update_time DATETIME NULL COMMENT '更新时间'
            )
            UNIQUE KEY(id)
            DISTRIBUTED BY HASH(id) BUCKETS 1
            PROPERTIES("replication_num" = "1")
        """
        execute_update(create_admin_table)
        log_info("[数据库] t_daily_report_admin 表已就绪", 'db_init')
        
        # 初始化默认管理员账号
        try:
            admin_count = execute_query(
                "SELECT COUNT(*) as cnt FROM t_daily_report_admin WHERE username = 'admin'"
            )
            if not admin_count or admin_count[0]['cnt'] == 0:
                log_info("[数据库] 初始化默认管理员账号 admin/123586", 'db_init')
                import hashlib
                default_password = hashlib.md5('123586'.encode('utf-8')).hexdigest()
                admin_id = int(__import__('datetime').datetime.now().timestamp() * 1000)
                execute_update(
                    """INSERT INTO t_daily_report_admin 
                       (id, username, password, real_name, create_time, update_time)
                       VALUES (%s, %s, %s, %s, NOW(), NOW())""",
                    (admin_id, 'admin', default_password, '管理员')
                )
                log_info("[数据库] 默认管理员账号创建成功", 'db_init')
        except Exception as e:
            log_warning(f"[数据库] 初始化默认管理员账号时出错: {e}", 'db_init')

        log_info("[数据库] 初始化完成", 'db_init')
        return True
    except Exception as e:
        log_error(f"[数据库] 初始化失败: {e}", 'db_init', exc_info=True)
        return False


def optimize_sum_data_indexes():
    """优化求和数据表的索引，提升查询性能"""
    try:
        log_info("[数据库] 开始优化 t_daily_report_sum_data 表索引", 'db_init')
        
        # 检查倒排索引是否存在
        def _index_exists(index_name):
            try:
                result = execute_query("SHOW INDEX FROM t_daily_report_sum_data")
                for row in (result or []):
                    key_name = row.get('Key_name', '') or row.get('Index_name', '')
                    if key_name == index_name:
                        return True
            except Exception:
                pass
            return False
        
        # 添加倒排索引以加速查询
        # report_date 索引 - 日期范围查询
        try:
            if not _index_exists('idx_report_date'):
                execute_update("""
                    ALTER TABLE t_daily_report_sum_data 
                    ADD INDEX idx_report_date (report_date) 
                    USING INVERTED PROPERTIES("parser" = "none")
                    COMMENT '报表日期索引'
                """)
                log_info("[数据库] 添加 report_date 倒排索引", 'db_init')
            else:
                log_info("[数据库] report_date 倒排索引已存在", 'db_init')
        except Exception as e:
            log_warning(f"[数据库] 添加 report_date 索引失败: {e}", 'db_init')
        
        # config_id 索引 - 配置查询
        try:
            if not _index_exists('idx_config_id'):
                execute_update("""
                    ALTER TABLE t_daily_report_sum_data 
                    ADD INDEX idx_config_id (config_id) 
                    USING INVERTED PROPERTIES("parser" = "none")
                    COMMENT '配置ID索引'
                """)
                log_info("[数据库] 添加 config_id 倒排索引", 'db_init')
            else:
                log_info("[数据库] config_id 倒排索引已存在", 'db_init')
        except Exception as e:
            log_warning(f"[数据库] 添加 config_id 索引失败: {e}", 'db_init')
        
        # data_source 索引 - 数据来源查询
        try:
            if not _index_exists('idx_data_source'):
                execute_update("""
                    ALTER TABLE t_daily_report_sum_data 
                    ADD INDEX idx_data_source (data_source) 
                    USING INVERTED PROPERTIES("parser" = "none")
                    COMMENT '数据来源索引'
                """)
                log_info("[数据库] 添加 data_source 倒排索引", 'db_init')
            else:
                log_info("[数据库] data_source 倒排索引已存在", 'db_init')
        except Exception as e:
            log_warning(f"[数据库] 添加 data_source 索引失败: {e}", 'db_init')
        
        # sum_field_key 索引 - 字段查询
        try:
            if not _index_exists('idx_sum_field_key'):
                execute_update("""
                    ALTER TABLE t_daily_report_sum_data 
                    ADD INDEX idx_sum_field_key (sum_field_key) 
                    USING INVERTED PROPERTIES("parser" = "none")
                    COMMENT '求和字段索引'
                """)
                log_info("[数据库] 添加 sum_field_key 倒排索引", 'db_init')
            else:
                log_info("[数据库] sum_field_key 倒排索引已存在", 'db_init')
        except Exception as e:
            log_warning(f"[数据库] 添加 sum_field_key 索引失败: {e}", 'db_init')
        
        # config_name 索引 - 配置名称模糊搜索
        try:
            if not _index_exists('idx_config_name'):
                execute_update("""
                    ALTER TABLE t_daily_report_sum_data 
                    ADD INDEX idx_config_name (config_name) 
                    USING INVERTED PROPERTIES("parser" = "none")
                    COMMENT '配置名称索引'
                """)
                log_info("[数据库] 添加 config_name 倒排索引", 'db_init')
            else:
                log_info("[数据库] config_name 倒排索引已存在", 'db_init')
        except Exception as e:
            log_warning(f"[数据库] 添加 config_name 索引失败: {e}", 'db_init')
        
        # split_type 索引 - 拆分类型查询
        try:
            if not _index_exists('idx_split_type'):
                execute_update("""
                    ALTER TABLE t_daily_report_sum_data 
                    ADD INDEX idx_split_type (split_type) 
                    USING INVERTED PROPERTIES("parser" = "none")
                    COMMENT '拆分类型索引'
                """)
                log_info("[数据库] 添加 split_type 倒排索引", 'db_init')
            else:
                log_info("[数据库] split_type 倒排索引已存在", 'db_init')
        except Exception as e:
            log_warning(f"[数据库] 添加 split_type 索引失败: {e}", 'db_init')
        
        # split_value 索引 - 拆分值查询
        try:
            if not _index_exists('idx_split_value'):
                execute_update("""
                    ALTER TABLE t_daily_report_sum_data 
                    ADD INDEX idx_split_value (split_value) 
                    USING INVERTED PROPERTIES("parser" = "none")
                    COMMENT '拆分值索引'
                """)
                log_info("[数据库] 添加 split_value 倒排索引", 'db_init')
            else:
                log_info("[数据库] split_value 倒排索引已存在", 'db_init')
        except Exception as e:
            log_warning(f"[数据库] 添加 split_value 索引失败: {e}", 'db_init')
        
        log_info("[数据库] 求和数据表索引优化完成", 'db_init')
        return True
    except Exception as e:
        log_error(f"[数据库] 优化索引失败: {e}", 'db_init', exc_info=True)
        return False