ajyy/backend/docs/sql/001_init.sql

-- ajyy 初始化 DDL（MySQL 5.7+ / utf8mb4）对照文档
-- 与 SQLAlchemy ORM 模型对齐；生产可用 Alembic 或手动执行。

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

CREATE TABLE IF NOT EXISTS `sys_role` (
  `id` varchar(64) NOT NULL,
  `role_code` varchar(64) NOT NULL,
  `role_name` varchar(64) NOT NULL,
  `remark` varchar(255) DEFAULT NULL,
  `del_flag` varchar(1) NOT NULL DEFAULT '0',
  `create_time` datetime DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  `create_by` varchar(64) DEFAULT NULL,
  `update_by` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_sys_role_code` (`role_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `sys_user` (
  `id` varchar(64) NOT NULL,
  `username` varchar(64) NOT NULL,
  `password_hash` varchar(255) NOT NULL,
  `display_name` varchar(64) NOT NULL DEFAULT '',
  `role_id` varchar(64) NOT NULL,
  `status` varchar(1) NOT NULL DEFAULT '0' COMMENT '0启用 1停用',
  `del_flag` varchar(1) NOT NULL DEFAULT '0',
  `create_time` datetime DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  `create_by` varchar(64) DEFAULT NULL,
  `update_by` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_sys_user_username` (`username`),
  KEY `idx_sys_user_role` (`role_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `sys_menu` (
  `id` varchar(64) NOT NULL,
  `parent_id` varchar(64) NOT NULL DEFAULT '0',
  `menu_code` varchar(64) NOT NULL,
  `menu_name` varchar(64) NOT NULL,
  `path` varchar(255) DEFAULT '',
  `menu_type` varchar(1) NOT NULL COMMENT 'M目录 C菜单 F按钮',
  `sort` int NOT NULL DEFAULT 0,
  `visible` varchar(1) NOT NULL DEFAULT '1',
  `del_flag` varchar(1) NOT NULL DEFAULT '0',
  `create_time` datetime DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  `create_by` varchar(64) DEFAULT NULL,
  `update_by` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_sys_menu_code` (`menu_code`),
  KEY `idx_sys_menu_parent` (`parent_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `sys_role_menu` (
  `id` varchar(64) NOT NULL,
  `role_id` varchar(64) NOT NULL,
  `menu_id` varchar(64) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_srm_role` (`role_id`),
  KEY `idx_srm_menu` (`menu_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `feedback` (
  `id` varchar(64) NOT NULL,
  `biz_no` varchar(64) NOT NULL,
  `fb_type` varchar(16) NOT NULL COMMENT '投诉|表扬',
  `target_name` varchar(64) DEFAULT NULL,
  `target_dept` varchar(128) DEFAULT NULL,
  `target_post` varchar(64) DEFAULT NULL,
  `content` text NOT NULL,
  `reporter_name` varchar(64) DEFAULT NULL,
  `reporter_unit` varchar(128) DEFAULT NULL,
  `reporter_post` varchar(64) DEFAULT NULL,
  `reporter_phone` varchar(32) DEFAULT NULL,
  `attachments` json DEFAULT NULL,
  `status` varchar(16) NOT NULL DEFAULT '待处理' COMMENT '待处理|处理中|已办结',
  `handle_remark` text,
  `del_flag` varchar(1) NOT NULL DEFAULT '0',
  `create_time` datetime DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  `create_by` varchar(64) DEFAULT NULL,
  `update_by` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_feedback_biz_no` (`biz_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `suggestion` (
  `id` varchar(64) NOT NULL,
  `biz_no` varchar(64) NOT NULL,
  `content` text NOT NULL,
  `contact_name` varchar(64) DEFAULT NULL,
  `contact_unit` varchar(128) DEFAULT NULL,
  `contact_post` varchar(64) DEFAULT NULL,
  `contact_phone` varchar(32) DEFAULT NULL,
  `attachments` json DEFAULT NULL,
  `status` varchar(16) NOT NULL DEFAULT '待查阅' COMMENT '待查阅|已采纳|已回复',
  `handle_remark` text,
  `del_flag` varchar(1) NOT NULL DEFAULT '0',
  `create_time` datetime DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  `create_by` varchar(64) DEFAULT NULL,
  `update_by` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_suggestion_biz_no` (`biz_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `hotline_config` (
  `id` varchar(64) NOT NULL,
  `phone` varchar(512) DEFAULT '',
  `email` varchar(512) DEFAULT '',
  `address` varchar(1024) DEFAULT '',
  `work_time` varchar(255) DEFAULT '',
  `del_flag` varchar(1) NOT NULL DEFAULT '0',
  `create_time` datetime DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  `create_by` varchar(64) DEFAULT NULL,
  `update_by` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `qrcode_config` (
  `id` varchar(64) NOT NULL,
  `land_url` varchar(512) DEFAULT '',
  `title` varchar(128) DEFAULT '',
  `image_url` varchar(512) DEFAULT '',
  `del_flag` varchar(1) NOT NULL DEFAULT '0',
  `create_time` datetime DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  `create_by` varchar(64) DEFAULT NULL,
  `update_by` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `attachment` (
  `id` varchar(64) NOT NULL,
  `file_name` varchar(255) NOT NULL,
  `object_key` varchar(512) NOT NULL,
  `url` varchar(512) NOT NULL,
  `size` int DEFAULT 0,
  `biz_type` varchar(32) DEFAULT NULL,
  `biz_id` varchar(64) DEFAULT NULL,
  `del_flag` varchar(1) NOT NULL DEFAULT '0',
  `create_time` datetime DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  `create_by` varchar(64) DEFAULT NULL,
  `update_by` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_attachment_biz` (`biz_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `oper_log` (
  `id` varchar(64) NOT NULL,
  `user_id` varchar(64) DEFAULT NULL,
  `username` varchar(64) DEFAULT NULL,
  `module` varchar(64) DEFAULT NULL,
  `action` varchar(64) DEFAULT NULL,
  `detail` text,
  `ip` varchar(64) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_oper_log_user` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SET FOREIGN_KEY_CHECKS = 1;