gx-command-center/backend/sql/dashboard.sql

-- department
CREATE TABLE `department`
(
    `id`            BIGINT       NOT NULL AUTO_INCREMENT COMMENT '部门ID',
    `code`          VARCHAR(10)  NOT NULL COMMENT '部门编码',
    `name`          VARCHAR(30) DEFAULT NULL COMMENT '部门名称',
    `unique_id`     VARCHAR(100) NOT NULL COMMENT '唯一标识符',
    `sync_trace_id` DATETIME     NOT NULL COMMENT '同步时间',
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_department_code` (`code`),
    UNIQUE KEY `uk_department_unique_id` (`unique_id`),
    INDEX           `idx_department_sync_time` (`sync_trace_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='部门信息表';
-- park
CREATE TABLE `park`
(
    `id`            BIGINT       NOT NULL AUTO_INCREMENT,
    `code`          VARCHAR(20)  NOT NULL,
    `name`          VARCHAR(60)  NOT NULL,
    `synch_time`    DATETIME     NOT NULL,
    `sync_trace_id` VARCHAR(100) NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_park_code` (`code`),
    UNIQUE KEY `uk_park_sync_trace_id` (`sync_trace_id`),
    INDEX           `idx_park_synch_time` (`synch_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
-- hotel
CREATE TABLE `hotel`
(
    `id`            BIGINT       NOT NULL AUTO_INCREMENT,
    `code`          VARCHAR(10)  NOT NULL,
    `name`          VARCHAR(30),
    `synch_time`    DATETIME     NOT NULL,
    `sync_trace_id` VARCHAR(100) NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_hotel_code` (`code`),
    UNIQUE KEY `uk_hotel_sync_trace_id` (`sync_trace_id`),
    INDEX           `idx_hotel_synch_time` (`synch_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
-- kpi_revenue_attendance
CREATE TABLE `kpi_revenue_attendance`
(
    `id`                       BIGINT       NOT NULL AUTO_INCREMENT,
    `park_code`                VARCHAR(32)  NOT NULL COMMENT '1.YI 2.SWAD 3.FWAD 4.YWW 5.WBW 6.QAW 7.CLYMB 8.TeamLab',
    `date`                     DATETIME     NOT NULL,
    `attendance`               INT,
    `onsite_concierge_revenue` DECIMAL(24, 6) COMMENT 'AED',
    `revenue_generated`        DECIMAL(24, 6) COMMENT 'AED',
    `ap_visitors`              INT,
    `vip_visitors`             INT,
    `synch_time`               DATETIME     NOT NULL,
    `sync_trace_id`            VARCHAR(100) NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_kpi_revenue_attendance_date` (`park_code`, `date`),
    INDEX                      `idx_kpi_date` (`date`),
    INDEX                      `idx_kpi_sync` (`synch_time`, `sync_trace_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- kpi_concierge
CREATE TABLE `kpi_concierge`
(
    `id`                       bigint       NOT NULL AUTO_INCREMENT,
    `department`               varchar(10)    DEFAULT NULL,
    `date`                     datetime     NOT NULL,
    `revenue_generated`        int            DEFAULT NULL COMMENT 'AED',
    `call_sla`                 decimal(24, 6) DEFAULT NULL,
    `call_offered`             int            DEFAULT NULL,
    `call_waiting`             int            DEFAULT NULL,
    `call_capacity`            int            DEFAULT NULL,
    `call_occupancy`           int            DEFAULT NULL,
    `call_abandonment`         int            DEFAULT NULL,
    `call_aht`                 int            DEFAULT NULL COMMENT 'mins',
    `call_cast`                decimal(5, 2)  DEFAULT NULL,
    `chatbot_sla`              decimal(24, 6) DEFAULT NULL,
    `chatbot_contacts_offered` int            DEFAULT NULL,
    `chatbot_transfers`        int            DEFAULT NULL,
    `synch_time`               datetime     NOT NULL,
    `sync_trace_id`            varchar(100) NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_kpi_concierge_date` (`department`,`date`),
    KEY                        `idx_kpi_date` (`date`),
    KEY                        `idx_kpi_sync` (`synch_time`,`sync_trace_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
-- google_reviews_post
CREATE TABLE `google_reviews_post`
(
    `id`            int          NOT NULL AUTO_INCREMENT,
    `subject`       varchar(255) DEFAULT NULL,
    `content`       text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci,
    `content_url`   varchar(255) DEFAULT NULL COMMENT 'content_url',
    `avatar_url`    varchar(255) DEFAULT NULL,
    `like_num`      int          DEFAULT '0',
    `comment_count` int          DEFAULT '0',
    `review_date`   datetime     DEFAULT NULL,
    `reposts`       int          DEFAULT '0',
    `park_code`     varchar(10)  DEFAULT NULL,
    `synch_time`    datetime     NOT NULL,
    `sync_trace_id` varchar(100) NOT NULL,
    PRIMARY KEY (`id`),
    KEY             `idx_review_park` (`park_code`),
    KEY             `idx_review_date` (`review_date`),
    KEY             `idx_review_sync` (`synch_time`),
    FULLTEXT KEY `idx_review_content` (`subject`,`content`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
-- kpi_social_media_sentimental
CREATE TABLE `kpi_social_media_sentimental`
(
    `id`                           BIGINT       NOT NULL AUTO_INCREMENT,
    `park_code`                    VARCHAR(20)  NOT NULL,
    `date`                         DATETIME     NOT NULL,
    `google_review`                decimal(24, 6) default null,
    `trip_advisor`                 decimal(24, 6) default null,
    `google_evaluation_word`       TEXT,
    `trip_advisor_evaluation_word` TEXT,
    `synch_time`                   DATETIME     NOT NULL,
    `sync_trace_id`                VARCHAR(100) NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_kpi_social_media_sentimental_date` (`park_code`, `date`),
    INDEX                          `idx_sentiment_date` (`date`),
    INDEX                          `idx_sentiment_sync` (`synch_time`),
    FULLTEXT                       INDEX `idx_sentiment_words` (`google_evaluation_word`, `trip_advisor_evaluation_word`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
-- kpi_digital_gx
CREATE TABLE `kpi_digital_gx`
(
    `id`                    BIGINT       NOT NULL AUTO_INCREMENT,
    `app_uptime`            DATETIME,
    `downtime_notification` INT DEFAULT 0,
    `current_app_sessions`  INT DEFAULT 0,
    `most_used_screens`     VARCHAR(255),
    `avg_timespend`         DECIMAL(24, 6) COMMENT 'Average time in minutes',
    `app_revenue`           DECIMAL(24, 6),
    `yas_app_csat`          DECIMAL(24, 6) COMMENT 'Customer Satisfaction Score (0-100)',
    `synch_time`            DATETIME     NOT NULL,
    `sync_trace_id`         VARCHAR(100) NOT NULL,
    PRIMARY KEY (`id`),
    INDEX                   `idx_digital_uptime` (`app_uptime`),
    INDEX                   `idx_digital_sync` (`synch_time`, `sync_trace_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
-- kpi_yas_island_campaigns
CREATE TABLE `kpi_yas_island_campaigns`
(
    `id`                        BIGINT       NOT NULL AUTO_INCREMENT,
    `mtd_campaigns`             INT,
    `total_campaign_email_sent` INT,
    `delivered`                 INT,
    `email_open_rate`           DECIMAL(24, 6),
    `click_rate`                DECIMAL(24, 6),
    `annual_passholders_total`  INT,
    `renewed`                   INT,
    `expired`                   INT,
    `date`                      DATETIME,
    `synch_time`                DATETIME     NOT NULL,
    `sync_trace_id`             VARCHAR(100) NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_campaign_date` (`date`),
    INDEX                       `idx_sync_info` (`synch_time`, `sync_trace_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- kpi_journey_touch_point
CREATE TABLE `kpi_journey_touch_point`
(
    `id`                 BIGINT       NOT NULL AUTO_INCREMENT,
    `park_code`          VARCHAR(32)  NOT NULL COMMENT '1.YI 2.SWAD 3.FWAD 4.YWW 5.WBW 6.QAW 7.CLYMB 8.TeamLab',
    `experience_project` VARCHAR(100),
    `value`              DECIMAL(24, 6),
    `date`               DATETIME,
    `synch_time`         DATETIME     NOT NULL,
    `sync_trace_id`      VARCHAR(100) NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_journey_touch_point_date_parkcode_exp` (`date`,`park_code`,`experience_project`),
    INDEX                `idx_sync_info` (`synch_time`, `sync_trace_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
-- kpi_park_guest_experience
CREATE TABLE `kpi_park_guest_experience`
(
    `id`                   bigint       NOT NULL AUTO_INCREMENT,
    `park_code`            varchar(20)    DEFAULT NULL,
    `gss`                  decimal(24, 6) DEFAULT NULL,
    `nps`                  decimal(24, 6) DEFAULT NULL,
    `new_vog_measurements` decimal(24, 6) DEFAULT NULL,
    `happiness_meters`     decimal(24, 6) DEFAULT NULL,
    `queue_time`           decimal(24, 6) DEFAULT NULL,
    `park_ticket_type`     varchar(255)   DEFAULT NULL,
    `date`                 datetime       DEFAULT NULL,
    `visitor_origin`       varchar(30)    DEFAULT NULL,
    `sentiment`            varchar(20)    DEFAULT NULL,
    `revenue`              decimal(24, 6) DEFAULT NULL,
    `synch_time`           datetime     NOT NULL,
    `sync_trace_id`        varchar(100) NOT NULL,
    `project`              varchar(100) NOT NULL COMMENT 'project',
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_park_guest_experience_date_parkcode_project` (`date`,`park_code`,`project`),
    KEY                    `idx_date` (`date`),
    KEY                    `idx_visitor_origin` (`visitor_origin`),
    KEY                    `idx_sentiment` (`sentiment`),
    KEY                    `idx_sync` (`synch_time`,`sync_trace_id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
--  kpi_hotel
CREATE TABLE `kpi_hotel`
(
    `id`            BIGINT       NOT NULL AUTO_INCREMENT,
    `hotel_code`    VARCHAR(255),
    `gss`           DECIMAL(24, 6),
    `gss_yesterday` DECIMAL(24, 6),
    `nps`           DECIMAL(24, 6),
    `nps_yesterday` DECIMAL(24, 6),
    `check_in`      INT,
    `booking`       INT,
    `datetime`      DATETIME,
    `synch_time`    DATETIME     NOT NULL,
    `sync_trace_id` VARCHAR(100) NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_hotel_datetime` (`hotel_code`, `datetime`),
    INDEX           `idx_datetime` (`datetime`),
    INDEX           `idx_sync` (`synch_time`, `sync_trace_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
-- kpi park
CREATE TABLE `kpi_park`
(
    `id`               BIGINT       NOT NULL AUTO_INCREMENT,
    `park_code`        VARCHAR(255),
    `gss`              DECIMAL(24, 6),
    `gss_yesterday`    DECIMAL(24, 6),
    `nps`              DECIMAL(24, 6),
    `nps_yesterday`    DECIMAL(24, 6),
    `visitor_in_park`  INT,
    `total_attendance` INT,
    `datetime`         DATETIME,
    `synch_time`       DATETIME     NOT NULL,
    `sync_trace_id`    VARCHAR(100) NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_kpi_park_datetime` (`park_code`, `datetime`),
    INDEX              `idx_datetime` (`datetime`),
    INDEX              `idx_sync` (`synch_time`, `sync_trace_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
-- kpi_visitor_country_breakdown
CREATE TABLE `kpi_visitor_country_breakdown`
(
    `id`            BIGINT        NOT NULL AUTO_INCREMENT COMMENT '自增主键',
    `park_code`     VARCHAR(20)   NOT NULL COMMENT '园区代码',
    `country`       VARCHAR(50)   NOT NULL COMMENT '国家名称',
    `visitor_count` INT UNSIGNED NOT NULL COMMENT '游客数量',
    `percentage`    DECIMAL(5, 2) NOT NULL COMMENT '占比百分比（%）',
    `data_date`     DATE          NOT NULL COMMENT '数据日期（YYYY-MM-DD）',
    `synch_time`    DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '数据同步时间',
    `women`         INT UNSIGNED NOT NULL COMMENT '女生',
    `man`           INT UNSIGNED NOT NULL COMMENT '男生',
    `sync_trace_id` VARCHAR(100)  NOT NULL COMMENT '同步追踪ID',
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_country_date` (`park_code`,`country`, `data_date`) COMMENT '同一个园区同一国家同一天数据唯一',
    KEY             `idx_data_date` (`data_date`) COMMENT '按日期查询索引'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='游客按居住国家分布统计';

-- kpi_transport
CREATE TABLE IF NOT EXISTS kpi_transport
(
    id
    BIGINT
    NOT
    NULL
    AUTO_INCREMENT
    COMMENT
    '主键ID',
    on_time_rate
    DECIMAL
(
    5,
    2
) NOT NULL COMMENT '准点率(%)',
    on_schedule_rate DECIMAL
(
    5,
    2
) NOT NULL COMMENT '准点率(冗余字段，与on_time_rate一致)',
    line_count INT NOT NULL COMMENT '线路数量(条)',
    passenger_count INT NOT NULL COMMENT '乘客数量(万人)',
    bus_count INT NOT NULL COMMENT '公交车数量(辆)',
    on_time_rate_change DECIMAL
(
    5,
    2
) NOT NULL COMMENT '准点率环比变化(%)',
    data_date DATE NOT NULL COMMENT '统计日期',
    synch_time DATETIME NOT NULL COMMENT '数据同步时间',
    sync_trace_id VARCHAR
(
    100
) NOT NULL COMMENT '同步追踪唯一ID',
    PRIMARY KEY
(
    id
),
    UNIQUE KEY `uk_transport_date`
(
    `data_date`
)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '运输行业KPI指标表（含同步信息）';

-- 同步日志表
CREATE TABLE sys_data_sync_log
(
    log_id        BIGINT PRIMARY KEY AUTO_INCREMENT,
    job_id        BIGINT       NOT NULL COMMENT 'XXL-Job任务ID',
    trace_id      VARCHAR(100)  NOT NULL COMMENT '全链路追踪ID',
    source_system VARCHAR(100) NOT NULL COMMENT '原系统',
    source_table  VARCHAR(255) NOT NULL COMMENT '原表',
    target_table  VARCHAR(100) NOT NULL COMMENT '目标表',
    data_count    INT          NOT NULL COMMENT '同步数据量',
    status        CHAR(1)      NOT NULL COMMENT '状态（0成功 1失败）',
    error_msg     TEXT COMMENT '错误信息',
    start_time    DATETIME     NOT NULL COMMENT '开始时间',
    end_time      DATETIME COMMENT '结束时间',
    duration      BIGINT COMMENT '耗时(ms)',
    create_by     VARCHAR(64) DEFAULT '' COMMENT '创建者',
    create_time   DATETIME    DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'
) ENGINE=InnoDB COMMENT='数据同步日志表';

CREATE TABLE `park_events`
(
    `id`                BIGINT       NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    `event_title`       VARCHAR(255) NOT NULL COMMENT '活动标题',
    `event_description` TEXT COMMENT '活动描述',
    `start_date`        DATE         NOT NULL COMMENT '活动开始日期',
    `end_date`          DATE         NOT NULL COMMENT '活动结束日期',
    `venue`             VARCHAR(100) NOT NULL COMMENT '活动地点',
    `event_image_url`   VARCHAR(500) COMMENT '活动图片URL',
    `park_code`         VARCHAR(50)  NOT NULL COMMENT '园区编码（如FERRARI_WORLD）',
    `synch_time`        DATETIME     NOT NULL COMMENT '数据同步时间',
    `sync_trace_id`     VARCHAR(100) COMMENT '同步追踪ID（用于数据同步审计）',
    PRIMARY KEY (`id`),
    -- 联合唯一索引：确保同一园区、同一时间段内标题唯一
    UNIQUE KEY `uk_park_event_unique` (`park_code`, `event_title`, `start_date`, `end_date`),
    -- 普通索引（优化查询）
    KEY                 `idx_event_date` (`start_date`, `end_date`), -- 按日期范围查询
    KEY                 `sync_trace_id` (`synch_time`)               -- 按同步时间查询
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='园区活动信息表';

-- 园区票类型
CREATE TABLE `park_ticket_type`
(
    `id`                  BIGINT        NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    `park_code`           VARCHAR(50)   NOT NULL COMMENT '园区编码（如FERRARI_WORLD）',
    `statistics_date`     DATE          NOT NULL COMMENT '统计日期',
    `monthly_sales_total` INT           NOT NULL COMMENT '月度售票总量（图片中：17392）',
    `ticket_type`         VARCHAR(50)   NOT NULL COMMENT '游客类型（动态类型，如AP/VIP/Elite/General admission）',
    `ticket_rate`         DECIMAL(5, 2) NOT NULL COMMENT '该类型占比（%，如8.00，支持动态类型）',
    `synch_time`          DATETIME      NOT NULL COMMENT '数据同步时间',
    `sync_trace_id`       VARCHAR(100) COMMENT '同步追踪ID（用于数据同步审计）',
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_park_ticket_type` (`park_code`, `statistics_date`, `ticket_type`) COMMENT '同一园区同一月份同一类型数据唯一',
    KEY                   `idx_statistics_date` (`statistics_date`),
    KEY                   `idx_ticket_type` (`ticket_type`),
    KEY                   `idx_synch_time` (`synch_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='园区月度售票类型统计表';

CREATE TABLE `park_age_group_statistics`
(
    `id`              BIGINT        NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    `park_code`       VARCHAR(50)   NOT NULL COMMENT '园区编码（如FERRARI_WORLD）',
    `statistics_date` DATE          NOT NULL COMMENT '统计日期（如2025-08-31）',
    `age_group`       VARCHAR(20)   NOT NULL COMMENT '年龄段（如0-17、18-24、25-34、36-54）',
    `age_ratio`       DECIMAL(5, 2) NOT NULL COMMENT '该年龄段占比（%，如8.00）',
    `synch_time`      DATETIME      NOT NULL COMMENT '数据同步时间',
    `sync_trace_id`   VARCHAR(100) COMMENT '同步追踪ID（用于数据同步审计）',
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_park_date_group` (`park_code`, `statistics_date`, `age_group`) COMMENT '同一园区同一日期同一年龄段数据唯一',
    KEY               `idx_statistics_date` (`statistics_date`),
    KEY               `idx_age_group` (`age_group`),
    KEY               `idx_synch_time` (`synch_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='园区游客年龄段统计表';

CREATE TABLE `new_vog_measurements`
(
    `id`               bigint                                 NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    `park_code`        varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '园区编码（如FERRARI_WORLD）',
    `measurement_time` datetime                               NOT NULL COMMENT '测量时间（精确到小时，如2025-08-25 08:00:00）',
    `vog_value`        int                                    NOT NULL COMMENT 'VOG测量值（图片中的柱状图数值，如158、247）',
    `synch_time`       datetime                               NOT NULL COMMENT '数据同步时间',
    `sync_trace_id`    varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '同步追踪ID（用于数据同步审计）',
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_new_vog_measurements_time` (`park_code`,`measurement_time`) COMMENT '同一园区同一时间点数据唯一',
    KEY                `idx_measurement_time` (`measurement_time`),
    KEY                `idx_park_code` (`park_code`),
    KEY                `idx_synch_time` (`synch_time`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='VOG测量数据表（每小时统计）';

CREATE TABLE IF NOT EXISTS sentiment_analysis
(
    `id`
    INT
(
    11
) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    `park_code` varchar
(
    50
) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '园区编码（如FERRARI_WORLD）',
    `positive_count` INT
(
    11
) NOT NULL DEFAULT 0 COMMENT '正面情感数量',
    `negative_count` INT
(
    11
) NOT NULL DEFAULT 0 COMMENT '负面情感数量',
    `neutral_count` INT
(
    11
) NOT NULL DEFAULT 0 COMMENT '中性情感数量',
    `positive_rate` DECIMAL
(
    5,
    2
) NOT NULL COMMENT '正面情感百分比(%)',
    `negative_rate` DECIMAL
(
    5,
    2
) NOT NULL COMMENT '负面情感百分比(%)',
    `neutral_rate` DECIMAL
(
    5,
    2
) NOT NULL COMMENT '中性情感百分比(%)',
    `positive_trend` DECIMAL
(
    5,
    2
) NULL COMMENT '正面环比变化(%)',
    `negative_trend` DECIMAL
(
    5,
    2
) NULL COMMENT '负面环比变化(%)',
    `neutral_trend` DECIMAL
(
    5,
    2
) NULL COMMENT '中性环比变化(%)',
    `tags` VARCHAR
(
    512
) NULL COMMENT '热门标签(逗号分隔)',
    `analysis_date` DATE NOT NULL COMMENT '分析日期',
    `synch_time` DATETIME NOT NULL COMMENT '数据同步时间',
    `sync_trace_id` VARCHAR
(
    100
) COMMENT '同步追踪ID（用于数据同步审计）',
    PRIMARY KEY
(
    `id`
),
    UNIQUE KEY `uk_analysis_date`
(
    `analysis_date`,
    `park_code`
) COMMENT '确保每天数据唯一'
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户情感分析结果表';

CREATE TABLE IF NOT EXISTS map_center
(
    `id`
    INT
(
    11
) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    `park_code` varchar
(
    50
) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '园区编码（如FERRARI_WORLD）',
    `analysis_date` DATE NOT NULL COMMENT '分析日期',
    `business_start_time` varchar
(
    50
) NULL COMMENT '营业时间-开始时间（如：10:00）',
    `business_end_time` varchar
(
    50
) NULL COMMENT '营业时间-结束时间（如：20:00）',
    `facility_name` VARCHAR
(
    100
) not NULL COMMENT '设施名称（如：Formula rossa）',
    `facility_status` ENUM
(
    'OPEN',
    'CLOSED',
    'MAINTENANCE',
    'SCHEDULED'
) NOT NULL COMMENT '设施状态：开放/关闭/维护/计划中',
    `display_order` INT
(
    11
) NOT NULL DEFAULT 0 COMMENT '展示顺序（用于前端排序）',
    `synch_time` DATETIME NOT NULL COMMENT '数据同步时间',
    `sync_trace_id` VARCHAR
(
    100
) COMMENT '同步追踪ID（用于数据同步审计）',
    PRIMARY KEY
(
    `id`
),
    UNIQUE KEY `uk_map_center_date_parkCode`
(
    `analysis_date`,
    `park_code`,
    `facility_name`
) COMMENT '确保设施名称唯一',
    KEY `idx_sync_trace_id`
(
    `sync_trace_id`
) COMMENT '追踪id'
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='园区中心展示信息表（营业时间、设施状态）';

CREATE TABLE `sync_rule`
(
    `id`            bigint       NOT NULL AUTO_INCREMENT,
    `module_name`   varchar(100) NOT NULL COMMENT '功能模块名称',
    `field_name`    varchar(100) NOT NULL COMMENT '指标字段名',
    `rule_type`     tinyint      NOT NULL COMMENT '规则类型(1:空值默认值 2:阈值告警)',
    `default_value` varchar(255)   DEFAULT NULL COMMENT '默认值',
    `threshold_min` decimal(24, 6) DEFAULT NULL COMMENT '阈值下限',
    `threshold_max` decimal(24, 6) DEFAULT NULL COMMENT '阈值上限',
    `alert_email`   varchar(500)   DEFAULT NULL COMMENT '告警邮箱(多个用逗号分隔)',
    `status`        tinyint        DEFAULT '1' COMMENT '状态(1启用 0停用)',
    `create_by`     varchar(64)    DEFAULT '' COMMENT '创建者',
    `create_time`   datetime       DEFAULT NULL COMMENT '创建时间',
    `update_by`     varchar(64)    DEFAULT '' COMMENT '更新者',
    `update_time`   datetime       DEFAULT NULL COMMENT '更新时间',
    `remark`        varchar(500)   DEFAULT NULL COMMENT '备注',
    PRIMARY KEY (`id`),
    UNIQUE KEY `idx_module_field` (`module_name`,`field_name`,`rule_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='数据同步规则表';



CREATE TABLE `alert_record`
(
    `id`            bigint         NOT NULL AUTO_INCREMENT,
    `module_name`   varchar(100)   NOT NULL COMMENT '模块名称',
    `field_name`    varchar(100)   NOT NULL COMMENT '指标字段',
    `current_value` decimal(24, 6) NOT NULL COMMENT '当前值',
    `threshold_min` decimal(24, 6) DEFAULT NULL COMMENT '阈值下限',
    `threshold_max` decimal(24, 6) DEFAULT NULL COMMENT '阈值上限',
    `alert_time`    datetime       NOT NULL COMMENT '报警时间',
    `alert_status`  tinyint        DEFAULT '0' COMMENT '处理状态(0未处理 1已发送 2已忽略)',
    `process_time`  datetime       DEFAULT NULL COMMENT '处理时间',
    `process_by`    varchar(64)    DEFAULT NULL COMMENT '处理人',
    `alert_content` text COMMENT '报警内容',
    `alert_emails`  varchar(500)   DEFAULT NULL COMMENT '通知邮箱',
    PRIMARY KEY (`id`),
    UNIQUE KEY `idx_module_field` (`module_name`,`field_name`,`alert_time`),
    KEY             `idx_status_time` (`alert_status`,`alert_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='报警记录表';

CREATE TABLE `kpi_revenue`
(
    `id`                       BIGINT       NOT NULL AUTO_INCREMENT,
    `date`                     DATETIME     NOT NULL,
    `revenue_generated`        DECIMAL(24, 6) COMMENT 'AED',
    `onsite_concierge_revenue` DECIMAL(24, 6) COMMENT 'AED',
    `synch_time`               DATETIME     NOT NULL,
    `sync_trace_id`            VARCHAR(100) NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_kpi_park_date` (`date`),
    INDEX                      `idx_kpi_date` (`date`),
    INDEX                      `idx_kpi_sync` (`synch_time`, `sync_trace_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `kpi_daily_park_revenue`
(
    `id`             bigint                                  NOT NULL AUTO_INCREMENT COMMENT '自增主键',
    `report_date`    date                                    NOT NULL COMMENT '报告日期',
    `park_code`      varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '园区编码',
    `ticket_revenue` decimal(15, 2) COMMENT '门票总收入',
    `retail_revenue` decimal(15, 2) COMMENT '园区内交易总收入',
    `synch_time`     datetime                                NOT NULL COMMENT '同步时间',
    `sync_trace_id`  varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '同步追踪id',
    PRIMARY KEY (`id`),
    UNIQUE KEY `idx_report_date_park` (`report_date`,`park_code`) COMMENT '日期和园区唯一索引'
) ENGINE=InnoDB AUTO_INCREMENT=382 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='园区内交易总收入';