
-- 经销商品牌得分表
CREATE TABLE `cpa_brandcore_statistics`
(
    `id`                       VARCHAR(36) NOT NULL COMMENT '主键UUID',
    `dealer_code`              VARCHAR(36)          DEFAULT NULL COMMENT '经销商编码',
    `dealer_name`              VARCHAR(100)         DEFAULT NULL COMMENT '经销商名称',
    `big_area`                 VARCHAR(50)          DEFAULT NULL COMMENT '大区',
    `statistics_date`          DATE                 DEFAULT NULL COMMENT '统计日期（按日统计时使用）',
    `statistics_type`          VARCHAR(20) NOT NULL COMMENT '统计类型：total-总计，daily-按日，weekly-按周，monthly-按月',
    `total_records`            INT UNSIGNED                DEFAULT 0      COMMENT '总记录数',
    `total_score`              INT UNSIGNED                DEFAULT 0      COMMENT '总得分',
    `average_score_percentage` DECIMAL(5, 2)        DEFAULT 0.00 COMMENT '平均得分百分比',
    `positive_count`           INT UNSIGNED                DEFAULT 0      COMMENT '积极情感数量',
    `neutral_count`            INT UNSIGNED                DEFAULT 0      COMMENT '中性情感数量',
    `negative_count`           INT UNSIGNED                DEFAULT 0      COMMENT '消极情感数量',
    `positive_rate`            DECIMAL(5, 2)        DEFAULT 0.00 COMMENT '积极率百分比',
    `neutral_rate`             DECIMAL(5, 2)        DEFAULT 0.00 COMMENT '中性率百分比',
    `negative_rate`            DECIMAL(5, 2)        DEFAULT 0.00 COMMENT '消极率百分比',
    `earliest_interaction`     DATETIME             DEFAULT NULL COMMENT '最早交互时间',
    `latest_interaction`       DATETIME             DEFAULT NULL COMMENT '最晚交互时间',
    `created_at`               DATETIME    NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `created_by`               VARCHAR(36)          DEFAULT NULL COMMENT '创建人',
    `updated_at`               DATETIME    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    `updated_by`               VARCHAR(36)          DEFAULT NULL COMMENT '更新人',
    `is_deleted`               TINYINT UNSIGNED   NOT NULL DEFAULT 0      COMMENT '是否删除(0-未删除,1-已删除)',

    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_dealer_date_type` (`dealer_code`, `statistics_date`, `statistics_type`),
    KEY                        `idx_dealer_code` (`dealer_code`),
    KEY                        `idx_dealer_name` (`dealer_name`),
    KEY                        `idx_big_area` (`big_area`),
    KEY                        `idx_statistics_date` (`statistics_date`),
    KEY                        `idx_statistics_type` (`statistics_type`),
    KEY                        `idx_total_score` (`total_score`),
    KEY                        `idx_average_score_percentage` (`average_score_percentage`),
    KEY                        `idx_created_at` (`created_at`),
    KEY                        `idx_is_deleted` (`is_deleted`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='经销商品牌得分表';



