wuhan-saga/database/wuhan_saga_complete.sql

-- ================================================================
-- Wuhan Saga official website - 数据库完整初始化脚本
-- 内容：建库、建表、索引、FULLTEXT、外键、示例/种子数据
-- MySQL 5.7+ / 8.0+ | utf8mb4 | InnoDB
-- 使用方式：mysql -uUSER -p < wuhan_saga_complete.sql
-- 表前缀 f_；主键为 {表名}_id；审计字段 create_by/update_by/create_time/update_time；
-- remark、del_flag（0正常 1删除）
-- ================================================================

SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci;
SET FOREIGN_KEY_CHECKS = 0;

-- 创建数据库
CREATE DATABASE IF NOT EXISTS wuhan_saga
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE wuhan_saga;

-- ================================================================
-- 系统用户表
-- ================================================================
DROP TABLE IF EXISTS sys_user;
CREATE TABLE sys_user (
  user_id BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键',
  username VARCHAR(64) NOT NULL COMMENT '用户名',
  password VARCHAR(200) NOT NULL COMMENT '密码(BCrypt)',
  nickname VARCHAR(100) COMMENT '昵称',
  avatar VARCHAR(255) COMMENT '头像路径',
  email VARCHAR(100) COMMENT '邮箱',
  phone VARCHAR(20) COMMENT '手机号',
  status TINYINT(1) NOT NULL DEFAULT 1 COMMENT '状态：0禁用 1正常',
  create_by VARCHAR(64) DEFAULT NULL COMMENT '创建人',
  update_by VARCHAR(64) DEFAULT NULL COMMENT '更新人',
  create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  remark VARCHAR(500) DEFAULT NULL COMMENT '备注',
  del_flag TINYINT(1) NOT NULL DEFAULT 0 COMMENT '删除标志：0正常 1删除',
  PRIMARY KEY (user_id),
  UNIQUE KEY uk_sys_user_username (username)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='系统用户表';

-- ================================================================
-- 公司信息表
-- ================================================================
DROP TABLE IF EXISTS f_company_info;
CREATE TABLE f_company_info (
  company_info_id BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键',
  name_zh VARCHAR(200) NOT NULL COMMENT '公司名称（中文）',
  name_en VARCHAR(200) NOT NULL COMMENT '公司名称（英文）',
  established_year INT NOT NULL COMMENT '成立年份',
  business_scope_zh TEXT COMMENT '主营业务（中文）',
  business_scope_en TEXT COMMENT '主营业务（英文）',
  core_data_zh TEXT COMMENT '核心数据（中文）',
  core_data_en TEXT COMMENT '核心数据（英文）',
  technical_source_zh TEXT COMMENT '技术来源（中文）',
  technical_source_en TEXT COMMENT '技术来源（英文）',
  markets VARCHAR(500) COMMENT '覆盖市场（逗号分隔）',
  vision_zh TEXT COMMENT '企业愿景（中文）',
  vision_en TEXT COMMENT '企业愿景（英文）',
  mission_zh TEXT COMMENT '企业使命（中文）',
  mission_en TEXT COMMENT '企业使命（英文）',
  values_zh VARCHAR(500) COMMENT '核心价值观（中文）',
  values_en VARCHAR(500) COMMENT '核心价值观（英文）',
  spirit_zh VARCHAR(200) COMMENT '企业精神（中文）',
  spirit_en VARCHAR(200) COMMENT '企业精神（英文）',
  phone VARCHAR(50) COMMENT '联系电话',
  email VARCHAR(100) COMMENT '电子邮箱',
  address_zh VARCHAR(500) COMMENT '公司地址（中文）',
  address_en VARCHAR(500) COMMENT '公司地址（英文）',
  website VARCHAR(200) COMMENT '官网地址',
  create_by VARCHAR(64) DEFAULT NULL COMMENT '创建人',
  update_by VARCHAR(64) DEFAULT NULL COMMENT '更新人',
  create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  remark VARCHAR(500) DEFAULT NULL COMMENT '备注',
  del_flag TINYINT(1) NOT NULL DEFAULT 0 COMMENT '删除标志：0正常 1删除',
  PRIMARY KEY (company_info_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='公司信息（单条或主记录）';

-- ================================================================
-- 联系方式表
-- ================================================================
DROP TABLE IF EXISTS f_contact;
CREATE TABLE f_contact (
  contact_id BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键',
  company_info_id BIGINT NOT NULL COMMENT '关联公司',
  phone VARCHAR(50) COMMENT '电话',
  fax VARCHAR(50) COMMENT '传真',
  email VARCHAR(100) COMMENT '邮箱',
  address_zh VARCHAR(500) COMMENT '地址（中文）',
  address_en VARCHAR(500) COMMENT '地址（英文）',
  zip_code VARCHAR(20) COMMENT '邮编',
  map_coordinates VARCHAR(100) COMMENT '地图坐标',
  business_hours_zh VARCHAR(200) COMMENT '营业时间（中文）',
  business_hours_en VARCHAR(200) COMMENT '营业时间（英文）',
  create_by VARCHAR(64) DEFAULT NULL COMMENT '创建人',
  update_by VARCHAR(64) DEFAULT NULL COMMENT '更新人',
  create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  remark VARCHAR(500) DEFAULT NULL COMMENT '备注',
  del_flag TINYINT(1) NOT NULL DEFAULT 0 COMMENT '删除标志：0正常 1删除',
  PRIMARY KEY (contact_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='联系方式';

-- ================================================================
-- 关于我们表
-- ================================================================
DROP TABLE IF EXISTS f_about;
CREATE TABLE f_about (
  about_id BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键',
  company_info_id BIGINT NOT NULL COMMENT '关联公司',
  section_type VARCHAR(50) NOT NULL COMMENT '内容类型 introduction/vision/mission/values/spirit',
  title_zh VARCHAR(200) NOT NULL COMMENT '标题（中文）',
  title_en VARCHAR(200) NOT NULL COMMENT '标题（英文）',
  content_zh TEXT COMMENT '内容（中文）',
  content_en TEXT COMMENT '内容（英文）',
  image_path VARCHAR(255) COMMENT '配图相对路径',
  is_published TINYINT(1) NOT NULL DEFAULT 1 COMMENT '是否发布',
  sort_order INT NOT NULL DEFAULT 0 COMMENT '排序',
  create_by VARCHAR(64) DEFAULT NULL COMMENT '创建人',
  update_by VARCHAR(64) DEFAULT NULL COMMENT '更新人',
  create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  remark VARCHAR(500) DEFAULT NULL COMMENT '备注',
  del_flag TINYINT(1) NOT NULL DEFAULT 0 COMMENT '删除标志：0正常 1删除',
  PRIMARY KEY (about_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='关于我们分块内容';

-- ================================================================
-- 轮播图表
-- ================================================================
DROP TABLE IF EXISTS f_banner;
CREATE TABLE f_banner (
  banner_id BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键',
  company_info_id BIGINT NOT NULL COMMENT '关联公司',
  image_path VARCHAR(255) NOT NULL COMMENT '图片相对路径',
  title_zh VARCHAR(200) NOT NULL COMMENT '标题（中文）',
  title_en VARCHAR(200) NOT NULL COMMENT '标题（英文）',
  subtitle_zh VARCHAR(500) COMMENT '副标题（中文）',
  subtitle_en VARCHAR(500) COMMENT '副标题（英文）',
  sort_order INT NOT NULL DEFAULT 0 COMMENT '排序',
  is_published TINYINT(1) NOT NULL DEFAULT 1 COMMENT '是否发布',
  create_by VARCHAR(64) DEFAULT NULL COMMENT '创建人',
  update_by VARCHAR(64) DEFAULT NULL COMMENT '更新人',
  create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  remark VARCHAR(500) DEFAULT NULL COMMENT '备注',
  del_flag TINYINT(1) NOT NULL DEFAULT 0 COMMENT '删除标志：0正常 1删除',
  PRIMARY KEY (banner_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='首页轮播图';

-- ================================================================
-- 车间展示表
-- ================================================================
DROP TABLE IF EXISTS f_workshop;
CREATE TABLE f_workshop (
  workshop_id BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键',
  company_info_id BIGINT NOT NULL COMMENT '关联公司',
  workshop_type VARCHAR(50) NOT NULL COMMENT '车间类型 machining/heat_treatment/assembly',
  name_zh VARCHAR(200) NOT NULL COMMENT '车间名称（中文）',
  name_en VARCHAR(200) NOT NULL COMMENT '车间名称（英文）',
  description_zh TEXT COMMENT '描述（中文）',
  description_en TEXT COMMENT '描述（英文）',
  equipment_list_zh TEXT COMMENT '设备清单（中文）',
  equipment_list_en TEXT COMMENT '设备清单（英文）',
  area_size VARCHAR(50) COMMENT '占地面积',
  image_path VARCHAR(255) COMMENT '配图相对路径',
  is_published TINYINT(1) NOT NULL DEFAULT 1 COMMENT '是否发布',
  sort_order INT NOT NULL DEFAULT 0 COMMENT '排序',
  create_by VARCHAR(64) DEFAULT NULL COMMENT '创建人',
  update_by VARCHAR(64) DEFAULT NULL COMMENT '更新人',
  create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  remark VARCHAR(500) DEFAULT NULL COMMENT '备注',
  del_flag TINYINT(1) NOT NULL DEFAULT 0 COMMENT '删除标志：0正常 1删除',
  PRIMARY KEY (workshop_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='车间展示';

-- ================================================================
-- 核心技术表
-- ================================================================
DROP TABLE IF EXISTS f_core_technology;
CREATE TABLE f_core_technology (
  core_technology_id BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键',
  name_zh VARCHAR(200) NOT NULL COMMENT '技术名称（中文）',
  name_en VARCHAR(200) NOT NULL COMMENT '技术名称（英文）',
  description_zh TEXT COMMENT '技术描述（中文）',
  description_en TEXT COMMENT '技术描述（英文）',
  advantages_zh TEXT COMMENT '技术优势（中文）',
  advantages_en TEXT COMMENT '技术优势（英文）',
  icon_class VARCHAR(100) COMMENT '图标类名',
  is_published TINYINT(1) NOT NULL DEFAULT 1 COMMENT '是否发布',
  sort_order INT NOT NULL DEFAULT 0 COMMENT '排序',
  create_by VARCHAR(64) DEFAULT NULL COMMENT '创建人',
  update_by VARCHAR(64) DEFAULT NULL COMMENT '更新人',
  create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  remark VARCHAR(500) DEFAULT NULL COMMENT '备注',
  del_flag TINYINT(1) NOT NULL DEFAULT 0 COMMENT '删除标志：0正常 1删除',
  PRIMARY KEY (core_technology_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='核心技术';

-- ================================================================
-- 产品分类表
-- ================================================================
DROP TABLE IF EXISTS f_product_category;
CREATE TABLE f_product_category (
  product_category_id BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键',
  name_zh VARCHAR(100) NOT NULL COMMENT '分类名称（中文）',
  name_en VARCHAR(100) NOT NULL COMMENT '分类名称（英文）',
  module_type VARCHAR(20) NOT NULL DEFAULT 'product_line' COMMENT '模块类型 product_line/equipment/spare_part',
  parent_id BIGINT NULL COMMENT '父分类ID',
  icon_class VARCHAR(100) COMMENT '图标类名',
  sort_order INT NOT NULL DEFAULT 0 COMMENT '排序',
  is_published TINYINT(1) NOT NULL DEFAULT 1 COMMENT '是否发布',
  create_by VARCHAR(64) DEFAULT NULL COMMENT '创建人',
  update_by VARCHAR(64) DEFAULT NULL COMMENT '更新人',
  create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  remark VARCHAR(500) DEFAULT NULL COMMENT '备注',
  del_flag TINYINT(1) NOT NULL DEFAULT 0 COMMENT '删除标志：0正常 1删除',
  PRIMARY KEY (product_category_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='产品分类';

-- ================================================================
-- 整线机组产品表
-- ================================================================
DROP TABLE IF EXISTS f_product_line;
CREATE TABLE f_product_line (
  product_line_id BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键',
  category_id BIGINT NOT NULL COMMENT '所属分类',
  name_zh VARCHAR(200) NOT NULL COMMENT '产品名称（中文）',
  name_en VARCHAR(200) NOT NULL COMMENT '产品名称（英文）',
  short_name VARCHAR(50) COMMENT '简称或缩写',
  description_zh TEXT COMMENT '产品介绍（中文）',
  description_en TEXT COMMENT '产品介绍（英文）',
  specifications_zh TEXT COMMENT '技术规格（中文）',
  specifications_en TEXT COMMENT '技术规格（英文）',
  capacity VARCHAR(200) COMMENT '产能或产量数据',
  applications_zh TEXT COMMENT '应用领域（中文）',
  applications_en TEXT COMMENT '应用领域（英文）',
  achievements VARCHAR(200) COMMENT '业绩数据',
  cover_image VARCHAR(255) COMMENT '主封面图路径',
  applicable_materials_zh TEXT COMMENT '适用材料（中文）',
  applicable_materials_en TEXT COMMENT '适用材料（英文）',
  thickness_range VARCHAR(200) COMMENT '厚度范围',
  width_range VARCHAR(200) COMMENT '宽度范围',
  running_speed VARCHAR(200) COMMENT '运行速度',
  equipment_composition_zh TEXT COMMENT '设备组成（中文）',
  equipment_composition_en TEXT COMMENT '设备组成（英文）',
  technical_highlights_zh TEXT COMMENT '技术亮点（中文）',
  technical_highlights_en TEXT COMMENT '技术亮点（英文）',
  application_cases_zh TEXT COMMENT '应用案例（中文）',
  application_cases_en TEXT COMMENT '应用案例（英文）',
  excerpt_zh TEXT COMMENT '摘要（中文）',
  excerpt_en TEXT COMMENT '摘要（英文）',
  content_zh LONGTEXT COMMENT '正文（中文）',
  content_en LONGTEXT COMMENT '正文（英文）',
  meta_title_zh VARCHAR(200) COMMENT 'SEO标题（中文）',
  meta_title_en VARCHAR(200) COMMENT 'SEO标题（英文）',
  meta_description_zh VARCHAR(500) COMMENT 'SEO描述（中文）',
  meta_description_en VARCHAR(500) COMMENT 'SEO描述（英文）',
  meta_keywords VARCHAR(500) COMMENT 'SEO关键词',
  is_featured TINYINT(1) NOT NULL DEFAULT 0 COMMENT '是否推荐',
  is_published TINYINT(1) NOT NULL DEFAULT 1 COMMENT '是否发布',
  sort_order INT NOT NULL DEFAULT 0 COMMENT '排序',
  create_by VARCHAR(64) DEFAULT NULL COMMENT '创建人',
  update_by VARCHAR(64) DEFAULT NULL COMMENT '更新人',
  create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  remark VARCHAR(500) DEFAULT NULL COMMENT '备注',
  del_flag TINYINT(1) NOT NULL DEFAULT 0 COMMENT '删除标志：0正常 1删除',
  PRIMARY KEY (product_line_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='整线机组';

-- ================================================================
-- 核心单体设备表
-- ================================================================
DROP TABLE IF EXISTS f_single_equipment;
CREATE TABLE f_single_equipment (
  single_equipment_id BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键',
  category_id BIGINT NOT NULL COMMENT '所属分类',
  name_zh VARCHAR(200) NOT NULL COMMENT '设备名称（中文）',
  name_en VARCHAR(200) NOT NULL COMMENT '设备名称（英文）',
  description_zh TEXT COMMENT '设备描述（中文）',
  description_en TEXT COMMENT '设备描述（英文）',
  technical_highlights_zh TEXT COMMENT '技术亮点（中文）',
  technical_highlights_en TEXT COMMENT '技术亮点（英文）',
  cover_image VARCHAR(255) COMMENT '设备实拍照片',
  function_description_zh TEXT COMMENT '功能说明（中文）',
  function_description_en TEXT COMMENT '功能说明（英文）',
  specifications_zh TEXT COMMENT '技术参数（中文）',
  specifications_en TEXT COMMENT '技术参数（英文）',
  product_advantages_zh TEXT COMMENT '产品优势（中文）',
  product_advantages_en TEXT COMMENT '产品优势（英文）',
  applicable_lines_zh TEXT COMMENT '适配产线（中文）',
  applicable_lines_en TEXT COMMENT '适配产线（英文）',
  application_cases_zh TEXT COMMENT '应用案例（中文）',
  application_cases_en TEXT COMMENT '应用案例（英文）',
  excerpt_zh TEXT COMMENT '摘要（中文）',
  excerpt_en TEXT COMMENT '摘要（英文）',
  content_zh LONGTEXT COMMENT '正文（中文）',
  content_en LONGTEXT COMMENT '正文（英文）',
  is_published TINYINT(1) NOT NULL DEFAULT 1 COMMENT '是否发布',
  sort_order INT NOT NULL DEFAULT 0 COMMENT '排序',
  create_by VARCHAR(64) DEFAULT NULL COMMENT '创建人',
  update_by VARCHAR(64) DEFAULT NULL COMMENT '更新人',
  create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  remark VARCHAR(500) DEFAULT NULL COMMENT '备注',
  del_flag TINYINT(1) NOT NULL DEFAULT 0 COMMENT '删除标志：0正常 1删除',
  PRIMARY KEY (single_equipment_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='核心单体设备';

-- ================================================================
-- 备品备件表
-- ================================================================
DROP TABLE IF EXISTS f_spare_part;
CREATE TABLE f_spare_part (
  spare_part_id BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键',
  category_id BIGINT NOT NULL COMMENT '所属分类',
  name_zh VARCHAR(200) NOT NULL COMMENT '备件名称（中文）',
  name_en VARCHAR(200) NOT NULL COMMENT '备件名称（英文）',
  model VARCHAR(100) COMMENT '型号',
  description_zh TEXT COMMENT '描述（中文）',
  description_en TEXT COMMENT '描述（英文）',
  material VARCHAR(200) COMMENT '材质（旧字段，保留兼容）',
  applications TEXT COMMENT '应用场景（旧字段，保留兼容）',
  cover_image VARCHAR(255) COMMENT '产品实物图',
  model_spec VARCHAR(200) COMMENT '型号规格（W型/U型/I型等）',
  applicable_line_model VARCHAR(200) COMMENT '适配机组型号',
  material_zh VARCHAR(200) COMMENT '材质（中文）',
  material_en VARCHAR(200) COMMENT '材质（英文）',
  lifespan VARCHAR(200) COMMENT '使用寿命',
  product_advantages_zh TEXT COMMENT '产品优势（中文）',
  product_advantages_en TEXT COMMENT '产品优势（英文）',
  excerpt_zh TEXT COMMENT '摘要（中文）',
  excerpt_en TEXT COMMENT '摘要（英文）',
  content_zh LONGTEXT COMMENT '正文（中文）',
  content_en LONGTEXT COMMENT '正文（英文）',
  specifications_zh TEXT COMMENT '规格（中文）',
  specifications_en TEXT COMMENT '规格（英文）',
  is_published TINYINT(1) NOT NULL DEFAULT 1 COMMENT '是否发布',
  sort_order INT NOT NULL DEFAULT 0 COMMENT '排序',
  create_by VARCHAR(64) DEFAULT NULL COMMENT '创建人',
  update_by VARCHAR(64) DEFAULT NULL COMMENT '更新人',
  create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  remark VARCHAR(500) DEFAULT NULL COMMENT '备注',
  del_flag TINYINT(1) NOT NULL DEFAULT 0 COMMENT '删除标志：0正常 1删除',
  PRIMARY KEY (spare_part_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='备品备件';

-- ================================================================
-- 整线机组与单体设备关联表
-- ================================================================
DROP TABLE IF EXISTS f_product_line_equipment;
CREATE TABLE f_product_line_equipment (
  id BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键',
  product_line_id BIGINT NOT NULL COMMENT '整线机组ID',
  equipment_id BIGINT NOT NULL COMMENT '单体设备ID',
  sort_order INT NOT NULL DEFAULT 0 COMMENT '排序',
  create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (id),
  UNIQUE KEY uk_line_equipment (product_line_id, equipment_id),
  KEY idx_ple_equipment (equipment_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='整线机组与单体设备关联';

-- ================================================================
-- 案例分类表
-- ================================================================
DROP TABLE IF EXISTS f_case_category;
CREATE TABLE f_case_category (
  case_category_id BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键',
  name_zh VARCHAR(100) NOT NULL COMMENT '分类名称（中文）',
  name_en VARCHAR(100) NOT NULL COMMENT '分类名称（英文）',
  sort_order INT NOT NULL DEFAULT 0 COMMENT '排序',
  is_published TINYINT(1) NOT NULL DEFAULT 1 COMMENT '是否发布',
  create_by VARCHAR(64) DEFAULT NULL COMMENT '创建人',
  update_by VARCHAR(64) DEFAULT NULL COMMENT '更新人',
  create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  remark VARCHAR(500) DEFAULT NULL COMMENT '备注',
  del_flag TINYINT(1) NOT NULL DEFAULT 0 COMMENT '删除标志：0正常 1删除',
  PRIMARY KEY (case_category_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='案例分类';

-- ================================================================
-- 成功案例表
-- ================================================================
DROP TABLE IF EXISTS f_case_study;
CREATE TABLE f_case_study (
  case_study_id BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键',
  category_id BIGINT NOT NULL COMMENT '所属分类',
  client_name_zh VARCHAR(200) NOT NULL COMMENT '客户名称（中文）',
  client_name_en VARCHAR(200) NOT NULL COMMENT '客户名称（英文）',
  project_name_zh VARCHAR(200) COMMENT '项目名称（中文）',
  project_name_en VARCHAR(200) COMMENT '项目名称（英文）',
  location VARCHAR(200) COMMENT '项目地点',
  description_zh TEXT COMMENT '项目描述（中文）',
  description_en TEXT COMMENT '项目描述（英文）',
  scope_zh TEXT COMMENT '项目范围（中文）',
  scope_en TEXT COMMENT '项目范围（英文）',
  achievement_zh TEXT COMMENT '项目成果（中文）',
  achievement_en TEXT COMMENT '项目成果（英文）',
  is_overseas TINYINT(1) NOT NULL DEFAULT 0 COMMENT '是否海外项目',
  is_featured TINYINT(1) NOT NULL DEFAULT 0 COMMENT '是否推荐',
  is_published TINYINT(1) NOT NULL DEFAULT 1 COMMENT '是否发布',
  sort_order INT NOT NULL DEFAULT 0 COMMENT '排序',
  create_by VARCHAR(64) DEFAULT NULL COMMENT '创建人',
  update_by VARCHAR(64) DEFAULT NULL COMMENT '更新人',
  create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  remark VARCHAR(500) DEFAULT NULL COMMENT '备注',
  del_flag TINYINT(1) NOT NULL DEFAULT 0 COMMENT '删除标志：0正常 1删除',
  PRIMARY KEY (case_study_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='成功案例';

-- ================================================================
-- 新闻分类表
-- ================================================================
DROP TABLE IF EXISTS f_news_category;
CREATE TABLE f_news_category (
  category_id BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键',
  name_zh VARCHAR(100) NOT NULL COMMENT '分类名称（中文）',
  name_en VARCHAR(100) NOT NULL COMMENT '分类名称（英文）',
  sort_order INT NOT NULL DEFAULT 0 COMMENT '排序',
  is_published TINYINT(1) NOT NULL DEFAULT 1 COMMENT '是否发布',
  create_by VARCHAR(64) DEFAULT NULL COMMENT '创建人',
  update_by VARCHAR(64) DEFAULT NULL COMMENT '更新人',
  create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  remark VARCHAR(500) DEFAULT NULL COMMENT '备注',
  del_flag TINYINT(1) NOT NULL DEFAULT 0 COMMENT '删除标志：0正常 1删除',
  PRIMARY KEY (category_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='新闻分类';

-- ================================================================
-- 新闻文章表
-- ================================================================
DROP TABLE IF EXISTS f_news;
CREATE TABLE f_news (
  news_id BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键',
  category_id BIGINT NOT NULL COMMENT '所属分类',
  title_zh VARCHAR(200) NOT NULL COMMENT '标题（中文）',
  title_en VARCHAR(200) NOT NULL COMMENT '标题（英文）',
  excerpt_zh TEXT COMMENT '摘要（中文）',
  excerpt_en TEXT COMMENT '摘要（英文）',
  content_zh LONGTEXT COMMENT '正文（中文）',
  content_en LONGTEXT COMMENT '正文（英文）',
  cover_image VARCHAR(255) COMMENT '封面图相对路径',
  meta_title_zh VARCHAR(200) COMMENT 'SEO标题（中文）',
  meta_title_en VARCHAR(200) COMMENT 'SEO标题（英文）',
  meta_description_zh VARCHAR(500) COMMENT 'SEO描述（中文）',
  meta_description_en VARCHAR(500) COMMENT 'SEO描述（英文）',
  meta_keywords VARCHAR(500) COMMENT 'SEO关键词',
  is_featured TINYINT(1) NOT NULL DEFAULT 0 COMMENT '是否推荐',
  is_published TINYINT(1) NOT NULL DEFAULT 1 COMMENT '是否发布',
  view_count INT NOT NULL DEFAULT 0 COMMENT '浏览量',
  sort_order INT NOT NULL DEFAULT 0 COMMENT '排序',
  create_by VARCHAR(64) DEFAULT NULL COMMENT '创建人',
  update_by VARCHAR(64) DEFAULT NULL COMMENT '更新人',
  create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  remark VARCHAR(500) DEFAULT NULL COMMENT '备注',
  del_flag TINYINT(1) NOT NULL DEFAULT 0 COMMENT '删除标志：0正常 1删除',
  PRIMARY KEY (news_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='新闻文章';

-- ================================================================
-- 媒体库表
-- ================================================================
DROP TABLE IF EXISTS f_media_library;
CREATE TABLE f_media_library (
  media_library_id BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键',
  file_path VARCHAR(255) NOT NULL COMMENT '文件相对路径',
  file_name VARCHAR(200) NOT NULL COMMENT '文件名',
  file_type VARCHAR(50) COMMENT '文件类型 image/video/document',
  category VARCHAR(50) COMMENT '资源分类 banner/logo/factory/about/product-line/equipment/spare/case',
  title_zh VARCHAR(200) COMMENT '标题（中文）',
  title_en VARCHAR(200) COMMENT '标题（英文）',
  description_zh VARCHAR(500) COMMENT '描述（中文）',
  description_en VARCHAR(500) COMMENT '描述（英文）',
  file_size BIGINT COMMENT '文件大小字节',
  width INT COMMENT '宽度像素',
  height INT COMMENT '高度像素',
  is_published TINYINT(1) NOT NULL DEFAULT 1 COMMENT '是否发布',
  sort_order INT NOT NULL DEFAULT 0 COMMENT '排序',
  create_by VARCHAR(64) DEFAULT NULL COMMENT '创建人',
  update_by VARCHAR(64) DEFAULT NULL COMMENT '更新人',
  create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  remark VARCHAR(500) DEFAULT NULL COMMENT '备注',
  del_flag TINYINT(1) NOT NULL DEFAULT 0 COMMENT '删除标志：0正常 1删除',
  PRIMARY KEY (media_library_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='媒体库';

-- ================================================================
-- 产品与媒体关联表
-- ================================================================
DROP TABLE IF EXISTS f_product_media;
CREATE TABLE f_product_media (
  product_media_id BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键',
  product_id BIGINT NOT NULL COMMENT '产品ID',
  product_type VARCHAR(20) NOT NULL DEFAULT 'product_line' COMMENT '产品类型 product_line/equipment/spare_part',
  media_id BIGINT NOT NULL COMMENT '媒体库ID',
  sort_order INT NOT NULL DEFAULT 0 COMMENT '排序',
  create_by VARCHAR(64) DEFAULT NULL COMMENT '创建人',
  update_by VARCHAR(64) DEFAULT NULL COMMENT '更新人',
  create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  remark VARCHAR(500) DEFAULT NULL COMMENT '备注',
  del_flag TINYINT(1) NOT NULL DEFAULT 0 COMMENT '删除标志：0正常 1删除',
  PRIMARY KEY (product_media_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='产品与媒体关联';

-- ================================================================
-- 案例与媒体关联表
-- ================================================================
DROP TABLE IF EXISTS f_case_media;
CREATE TABLE f_case_media (
  case_media_id BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键',
  case_id BIGINT NOT NULL COMMENT '案例ID',
  media_id BIGINT NOT NULL COMMENT '媒体库ID',
  sort_order INT NOT NULL DEFAULT 0 COMMENT '排序',
  create_by VARCHAR(64) DEFAULT NULL COMMENT '创建人',
  update_by VARCHAR(64) DEFAULT NULL COMMENT '更新人',
  create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  remark VARCHAR(500) DEFAULT NULL COMMENT '备注',
  del_flag TINYINT(1) NOT NULL DEFAULT 0 COMMENT '删除标志：0正常 1删除',
  PRIMARY KEY (case_media_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='案例与媒体关联';

-- ================================================================
-- 索引创建
-- ================================================================

-- Banner索引
CREATE INDEX idx_f_banner_company ON f_banner (company_info_id);
CREATE INDEX idx_f_banner_sort_published ON f_banner (sort_order, is_published);

-- About索引
CREATE INDEX idx_f_about_company ON f_about (company_info_id);
CREATE INDEX idx_f_about_section_sort ON f_about (section_type, sort_order, is_published);

-- Workshop索引
CREATE INDEX idx_f_workshop_company ON f_workshop (company_info_id);
CREATE INDEX idx_f_workshop_type_sort ON f_workshop (workshop_type, sort_order, is_published);

-- Product Category索引
CREATE INDEX idx_f_product_category_parent ON f_product_category (parent_id);
CREATE INDEX idx_f_product_category_sort_published ON f_product_category (sort_order, is_published);

-- Product Line索引
CREATE INDEX idx_f_product_line_category ON f_product_line (category_id);
CREATE INDEX idx_f_product_line_featured_published ON f_product_line (is_featured, is_published);
CREATE INDEX idx_f_product_line_sort ON f_product_line (sort_order);

-- Single Equipment索引
CREATE INDEX idx_f_single_equipment_category ON f_single_equipment (category_id);
CREATE INDEX idx_f_single_equipment_sort_published ON f_single_equipment (sort_order, is_published);

-- Spare Part索引
CREATE INDEX idx_f_spare_part_category ON f_spare_part (category_id);
CREATE INDEX idx_f_spare_part_sort_published ON f_spare_part (sort_order, is_published);

-- Core Technology索引
CREATE INDEX idx_f_core_technology_sort_published ON f_core_technology (sort_order, is_published);

-- Case Category索引
CREATE INDEX idx_f_case_category_sort_published ON f_case_category (sort_order, is_published);

-- Case Study索引
CREATE INDEX idx_f_case_study_category ON f_case_study (category_id);
CREATE INDEX idx_f_case_study_overseas_published ON f_case_study (is_overseas, is_published);
CREATE INDEX idx_f_case_study_featured ON f_case_study (is_featured, is_published);

-- News Category索引
CREATE INDEX idx_f_news_category_sort_published ON f_news_category (sort_order, is_published);

-- News索引
CREATE INDEX idx_f_news_category ON f_news (category_id);
CREATE INDEX idx_f_news_published_featured ON f_news (is_published, is_featured);
CREATE INDEX idx_f_news_create_time ON f_news (create_time);

-- Contact索引
CREATE INDEX idx_f_contact_company ON f_contact (company_info_id);

-- Media Library索引
CREATE INDEX idx_f_media_library_category ON f_media_library (category);
CREATE INDEX idx_f_media_library_sort_published ON f_media_library (sort_order, is_published);

-- Product Media索引
CREATE UNIQUE INDEX uk_f_product_media_pair ON f_product_media (product_id, product_type, media_id);
CREATE INDEX idx_f_product_media_media ON f_product_media (media_id);

-- Case Media索引
CREATE UNIQUE INDEX uk_f_case_media_pair ON f_case_media (case_id, media_id);
CREATE INDEX idx_f_case_media_media ON f_case_media (media_id);

-- ================================================================
-- FULLTEXT索引 (MySQL 5.7+)
-- ================================================================

ALTER TABLE f_product_line ADD FULLTEXT INDEX ft_product_line_zh (name_zh, description_zh, specifications_zh);
ALTER TABLE f_product_line ADD FULLTEXT INDEX ft_product_line_en (name_en, description_en, specifications_en);

ALTER TABLE f_single_equipment ADD FULLTEXT INDEX ft_single_equipment_zh (name_zh, description_zh, technical_highlights_zh);
ALTER TABLE f_single_equipment ADD FULLTEXT INDEX ft_single_equipment_en (name_en, description_en, technical_highlights_en);

ALTER TABLE f_spare_part ADD FULLTEXT INDEX ft_spare_part_zh (name_zh, description_zh);
ALTER TABLE f_spare_part ADD FULLTEXT INDEX ft_spare_part_en (name_en, description_en);

ALTER TABLE f_case_study ADD FULLTEXT INDEX ft_case_study_zh (client_name_zh, project_name_zh, description_zh);
ALTER TABLE f_case_study ADD FULLTEXT INDEX ft_case_study_en (client_name_en, project_name_en, description_en);

ALTER TABLE f_news ADD FULLTEXT INDEX ft_news_zh (title_zh, excerpt_zh, content_zh);
ALTER TABLE f_news ADD FULLTEXT INDEX ft_news_en (title_en, excerpt_en, content_en);

-- ================================================================
-- 种子数据（先插入，再创建外键约束）
-- ================================================================

-- 系统用户（管理员）
INSERT INTO sys_user (username, password, nickname, status) VALUES
  ('admin', '$2a$10$mKCJqgXbF53ZMUSykD.dSOaBQ30I5IVZk8x4PmpETgXkCD8403GVa', '管理员', 1);

-- 公司信息
INSERT INTO f_company_info (
  company_info_id, name_zh, name_en, established_year,
  business_scope_zh, business_scope_en,
  core_data_zh, core_data_en,
  technical_source_zh, technical_source_en,
  markets,
  vision_zh, vision_en, mission_zh, mission_en,
  values_zh, values_en, spirit_zh, spirit_en,
  website
) VALUES (
  1,
  '武汉萨格工程技术有限公司',
  'Wuhan Saga Engineering Technology Co., Ltd.',
  2020,
  '集板带后处理为主营业务的金属深加工技术和装备供应商。',
  'Metal deep processing technology and equipment supplier focused on strip post-processing.',
  '已向国内外客户提供20套连续板带处理生产线；参与制定相关行业产品国家标准。',
  'Delivered 20+ continuous strip processing lines worldwide; contributed to national standards for related industry products.',
  '技术团队来自原冶金部部属设计研究院及国内钢铁企业设计院，组合国外知名公司工作经历与留学归国人员。',
  'Technical team from former Ministry of Metallurgy design institutes and major domestic steel design institutes, complemented by professionals with overseas experience.',
  '俄罗斯、乌兹别克斯坦、土耳其、南美洲',
  '以先进的板材处理技术和优质的服务，做全球知名的装备供应商。',
  'To be a worldwide known equipment supplier by advanced strip processing technology and superior services.',
  '以板材处理技术为核心，向国内外用户提供先进完善的板材系统解决方案。',
  'Based on strip processing technology, provide advanced and complete strip system solutions for customers at home and abroad.',
  '诚信、尊重、公平、多元',
  'Integrity, respect, fairness, diversity',
  '创新、团结、高效',
  'Innovation, unity, and efficiency',
  'https://www.wuhansaga.com/'
);

-- 产品分类
INSERT INTO f_product_category (product_category_id, name_zh, name_en, module_type, parent_id, sort_order) VALUES
  (1, '整线机组', 'Complete Production Line', 'product_line', NULL, 10),
  (11, '热镀锌/镀铝锌机组', 'Hot-dip Galvanizing/Aluminum-zinc Coating Line', 'product_line', 1, 11),
  (12, '锌铝镁机组', 'Zinc-aluminum-magnesium Coating Line', 'product_line', 1, 12),
  (13, '普碳钢连续退火线', 'Carbon Steel Continuous Annealing Line', 'product_line', 1, 13),
  (14, '冷轧板退火镀锌两用机组', 'Cold-rolled Sheet Annealing and Galvanizing Dual-purpose Line', 'product_line', 1, 14),
  (15, '彩涂机组', 'Color Coating Line', 'product_line', 1, 15),
  (16, '高效电工钢（硅钢）连续处理机组', 'High-efficiency Electrical Steel (Silicon Steel) Continuous Processing Line', 'product_line', 1, 16),
  (17, '碳钢酸洗机组', 'Carbon Steel Pickling Line', 'product_line', 1, 17),
  (18, '不锈钢连续退火酸洗机组', 'Stainless Steel Continuous Annealing and Pickling Line', 'product_line', 1, 18),
  (19, '拉矫线、脱脂线、重卷线', 'Tension Leveling Line, Degreasing Line, Recoiling Line', 'product_line', 1, 19),
  (2, '核心单体设备', 'Core Single Equipment', 'equipment', NULL, 20),
  (51, '红外辐射炉', 'Infrared Radiation Furnace', 'equipment', 2, 51),
  (52, '硅钢轧前感应加热', 'Induction Heating for Silicon Steel Before Rolling', 'equipment', 2, 52),
  (53, '气雾冷却', 'Aerosol Cooling', 'equipment', 2, 53),
  (54, '焊缝退火感应加热', 'Induction Heating for Weld Seam Annealing', 'equipment', 2, 54),
  (55, '氢气回收装置', 'Hydrogen Recovery Device', 'equipment', 2, 55),
  (56, '镀后冷却', 'Post-coating Cooling', 'equipment', 2, 56),
  (57, '涂层干燥感应加热', 'Induction Heating for Coating Drying', 'equipment', 2, 57),
  (58, '合金化炉', 'Alloying Furnace', 'equipment', 2, 58),
  (59, '镀锡软熔感应加热', 'Induction Heating for Tin Plating Reflow', 'equipment', 2, 59),
  (60, '电磁驱渣器', 'Electromagnetic Dross Remover', 'equipment', 2, 60),
  (61, '直燃无焰加热', 'Direct-fired Flameless Heating', 'equipment', 2, 61),
  (62, '圆盘剪、拉弯矫直、涂机、开收卷机', 'Circular Shear, Stretch-bend Straightener, Coater, Uncoiler/Coiler', 'equipment', 2, 62),
  (3, '备品备件', 'Spare Parts', 'spare_part', NULL, 30),
  (71, 'W型/I型/U型辐射管', 'W-type/I-type/U-type Radiant Tubes', 'spare_part', 3, 71),
  (72, '翅片管换热器', 'Finned Tube Heat Exchanger', 'spare_part', 3, 72),
  (73, '炉辊、张力辊、镀铬辊、PU辊', 'Furnace Rolls, Tension Rolls, Chrome-plated Rolls, PU Rolls', 'spare_part', 3, 73),
  (74, '水淬辊、汲料辊、托辊', 'Water Quenching Rolls, Dross Skimming Rolls, Support Rolls', 'spare_part', 3, 74),
  (75, '轴套、衬套、滑块、支臂', 'Sleeves, Bushings, Sliders, Support Arms', 'spare_part', 3, 75);

ALTER TABLE f_product_category AUTO_INCREMENT = 100;

-- 案例分类
INSERT INTO f_case_category (name_zh, name_en, sort_order) VALUES
  ('热镀锌/铝锌产线', 'Hot Dip Galvanizing / Galvalume', 10),
  ('锌铝镁产线改造', 'ZAM Line Revamp', 20),
  ('硅钢处理', 'Silicon Steel Processing', 30),
  ('不锈钢处理', 'Stainless Steel Processing', 40),
  ('酸洗线', 'Pickling Lines', 50),
  ('感应加热', 'Induction Heating', 60),
  ('氢气回收', 'Hydrogen Recovery', 70),
  ('镀后冷却', 'Post-plating Cooling', 80);

-- 新闻分类
INSERT INTO f_news_category (name_zh, name_en, sort_order) VALUES
  ('公司新闻', 'Company News', 10),
  ('行业动态', 'Industry News', 20),
  ('技术文章', 'Technical Articles', 30);

-- 关于我们
INSERT INTO f_about (company_info_id, section_type, title_zh, title_en, content_zh, content_en, sort_order) VALUES
  (1, 'introduction', '公司简介', 'Company Profile',
   '武汉萨格工程技术有限公司，是一家集板带后处理为主营业务的金属深加工技术和装备供应商。自2020年成立至今，一直致力于金属板带生产的技术研发、技术服务和装备供应。SAGA公司已在带钢酸洗机组、带钢连续热镀（铝）锌机组、带钢连续退火机组、硅钢（电工钢）全工序处理机组等领域取得优异成绩。公司专注节能环保技术研发推广，以冷轧处理技术为核心，提供先进完善的冷轧系统解决方案。',
   'Wuhan Saga Engineering Technology Co., Ltd. is a metal deep processing technology and equipment supplier focused on strip post-processing. Since 2020 it has been committed to R&D, steel strip production technical services and equipment supply, with strong track record in pickling, hot dip (aluminum) zinc, continuous annealing, and full-process electrical steel lines, with emphasis on energy saving and environmental solutions.',
   10),
  (1, 'vision', '企业愿景', 'Vision',
   '以先进的板材处理技术和优质的服务，做全球知名的装备供应商。',
   'To be a worldwide known equipment supplier by advanced strip processing technology and superior services.',
   20),
  (1, 'mission', '企业使命', 'Mission',
   '以板材处理技术为核心，向国内外用户提供先进完善的板材系统解决方案。',
   'Based on strip processing technology, provide advanced strip solutions for customers at home and abroad.',
   30),
  (1, 'values', '核心价值观', 'Core Values',
   '诚信、尊重、公平、多元',
   'Integrity, respect, fairness, diversity',
   40),
  (1, 'spirit', '企业精神', 'Spirit',
   '创新、团结、高效',
   'Innovation, unity, and efficiency',
   50);

-- 车间展示
INSERT INTO f_workshop (company_info_id, workshop_type, name_zh, name_en, description_zh, description_en, equipment_list_zh, equipment_list_en, area_size, sort_order) VALUES
  (1, 'machining', '机加工车间', 'Machining Shop',
   '占地面积4000㎡，配备各类加工机床数十台，包括铣床、车床、磨床、钻床、数控车床、龙门铣床、立式车床、插床、线切割、折弯机、数控火焰切割机、卧式镗铣床等。',
   '4,000 m² machining area with dozens of machine tools including milling, turning, grinding, drilling, CNC lathes, gantry mills, vertical lathes, shaping, wire cutting, press brake, CNC flame cutting, horizontal boring-milling machines, etc.',
   '6130车床、1500立式车床、插床、龙门铣床、数控车床、数控火焰切割机、卧式车床、卧式镗铣床、卧式万能铣床、线切割、折弯机。',
   '6130 lathe, 1500 vertical lathe, shaping machine, gantry milling, CNC lathe, CNC flame cutting, horizontal lathe, horizontal boring-milling, universal milling, wire cutting, press brake.',
   '4000㎡',
   10),
  (1, 'heat_treatment', '热处理车间', 'Heat Treatment Shop',
   '专业从事金属材料热处理，工艺包括淬火、回火、正火、等温淬火、退火、硬化，通过加热、保温、冷却流程优化金属硬度、韧性、强度。应用领域：机械制造、汽车、冶金、航空航天、核工业、机电。',
   'Professional metal heat treatment: quenching, tempering, normalizing, isothermal quenching, annealing, hardening; optimizes hardness, toughness and strength. Applications: machinery, automotive, metallurgy, aerospace, nuclear, electromechanical.',
   '炉体、燃气炉、电炉、液压机、淬火槽等。',
   'Furnace bodies, gas furnaces, electric furnaces, hydraulic presses, quenching tanks, etc.',
   NULL,
   20),
  (1, 'assembly', '装配车间', 'Assembly Shop',
   '负责设备组件质量检验、装配、预装、调试，是生产流程关键环节。',
   'Quality inspection, assembly, pre-assembly and commissioning of equipment components — a key step in the production process.',
   NULL,
   NULL,
   NULL,
   30);

-- 轮播图
INSERT INTO f_banner (company_info_id, image_path, title_zh, title_en, subtitle_zh, subtitle_en, sort_order) VALUES
  (1, '/uploads/banner/banner-01.jpg', '连续板带处理产线', 'Continuous Strip Processing Lines',
   '20+套连续板带处理生产线 worldwide experience', '20+ Continuous Strip Processing Lines', 1),
  (1, '/uploads/banner/banner-02.jpg', '硅钢连续处理技术', 'Silicon Steel Processing',
   '无取向与取向硅钢全工序处理', 'Full-process electrical steel solutions', 2),
  (1, '/uploads/banner/banner-03.jpg', '感应加热解决方案', 'Induction Heating',
   '电磁感应加热与节能应用', 'Advanced induction heating solutions', 3);

-- 联系方式
INSERT INTO f_contact (company_info_id, email, address_zh, address_en, business_hours_zh, business_hours_en) VALUES
  (1, 'info@wuhansaga.com',
   '（请填写实际办公地址）',
   '(Please fill in office address)',
   '工作日 9:00-17:00',
   'Weekdays 9:00-17:00');

-- 核心技术
INSERT INTO f_core_technology (name_zh, name_en, description_zh, description_en, sort_order) VALUES
  ('节能环保技术', 'Energy Saving & Environmental Technology',
   '高效节能辐射管换热系统、热保护气体循环预热、无氧化加热、氢气回收等。',
   'High-efficiency radiant tube heat recovery, protective atmosphere preheat, non-oxidizing heating, hydrogen recovery, etc.',
   10),
  ('感应加热技术', 'Induction Heating Technology',
   '感应加热、闭环温控；应用于焊缝退火、硅钢轧前加热、涂层干燥等。',
   'Induction heating with closed-loop temperature control for weld annealing, electrical steel preheat, coating drying, etc.',
   20),
  ('气雾冷却技术', 'Air-Mist Cooling Technology',
   '高氢高速喷气冷却、气雾冷却、喷气冷却（BLADE喷嘴等）。',
   'High-hydrogen jet cooling, air-mist and jet cooling (e.g. BLADE nozzles).',
   30),
  ('智能控制技术', 'Intelligent Control Technology',
   '智能PLC控制系统，保障机组稳定运行与工艺优化。',
   'Intelligent PLC-based control for stable operation and process optimization.',
   40);

-- 成套机组产品
INSERT INTO f_product_line (category_id, name_zh, name_en, short_name, description_zh, description_en, specifications_zh, specifications_en, achievements, sort_order) VALUES
  (11, '热镀锌/镀铝锌机组', 'Hot-dip Galvanizing/Aluminum-zinc Coating Line', 'GI/GL',
   '带钢表面热浸镀锌/铝锌合金，阴极保护性强。',
   'Hot dip zinc or zinc-aluminum alloy coating with strong cathodic protection.',
   '冷轧0.12–2.5mm，热轧1.5–5mm；宽度550–1500mm；工艺速度max 200m/min（GL线可达240m/min）；炉型：卧式、立式、立卧结合。',
   'CR 0.12–2.5mm, HR 1.5–5mm; width 550–1500mm; speed up to 200 m/min (GL up to 240); horizontal, vertical or hybrid furnaces.',
   '国内外已建成200+条生产线', 11),
  (12, '锌铝镁机组', 'Zinc-aluminum-magnesium Coating Line', 'ZAM',
   '连续热镀生产，耐腐蚀性为传统纯锌涂层10–20倍。',
   'Continuous hot-dip coating; corrosion resistance 10–20× conventional zinc.',
   NULL, NULL,
   '多条产线应用与改造经验', 12),
  (13, '普碳钢连续退火线', 'Carbon Steel Continuous Annealing Line', 'CAL',
   '适用于CQ、DQ、DDQ、EDDQ、SEDDQ、HSS、T3–T5等钢种。',
   'For CQ, DQ, DDQ, EDDQ, SEDDQ, HSS, T3–T5 grades.',
   '规格0.15–1.5mm，宽度550–1500mm；速度max 200m/min。',
   '0.15–1.5mm, width 550–1500mm; up to 200 m/min.',
   '国内外近10条', 13),
  (14, '冷轧板退火镀锌两用机组', 'Cold-rolled Sheet Annealing and Galvanizing Dual-purpose Line', 'CAL+CGL',
   '适用SPCC、SPCD、SPCE、IF等钢种。',
   'For SPCC, SPCD, SPCE, IF and similar grades.',
   '厚度0.2–2.0mm，宽度800–1500mm；钢卷最大重量30T。',
   'Thickness 0.2–2.0mm, width 800–1500mm; coil weight up to 30 t.',
   NULL, 14),
  (15, '彩涂机组', 'Color Coating Line', 'CCL',
   '二涂二烘、三涂三烘；建材板、装饰板、家电板；涂层种类齐全。',
   '2C2B / 3C3B; construction, decorative and appliance panels; multiple paint systems.',
   '干膜厚度23–70μm；国内外已承建近20条。',
   'Dry film 23–70 μm; nearly 20 lines delivered domestically and abroad.',
   '近20条国内外', 15),
  (16, '高效电工钢（硅钢）连续处理机组', 'High-efficiency Electrical Steel (Silicon Steel) Continuous Processing Line', 'CAL/CGL-Si',
   '无取向与取向硅钢（CGO/HiB）连续处理，为电机、变压器提供核心原料。',
   'Non-oriented and grain-oriented (CGO/HiB) silicon steel processing for motors and transformers.',
   NULL, NULL,
   '无取向近20条、取向5条', 16),
  (17, '碳钢酸洗机组', 'Carbon Steel Pickling Line', NULL,
   '推拉式与连续式；浅槽紊流酸洗、五级逆流漂洗。',
   'Push-pull and continuous types; shallow turbulent pickling, five-stage countercurrent rinsing.',
   '配置800吨平整机、双头回转塔式圆盘剪等。',
   'Including 800 t skin pass mill, dual-head rotary disc shear, etc.',
   NULL, 17),
  (18, '不锈钢连续退火酸洗机组', 'Stainless Steel Continuous Annealing and Pickling Line', NULL,
   '热轧、冷轧、光亮退火等类型。',
   'Hot rolled, cold rolled, bright annealing configurations.',
   NULL, NULL,
   '国内外10条以上', 18),
  (19, '拉矫线、脱脂线、重卷线', 'Tension Leveling Line, Degreasing Line, Recoiling Line', NULL,
   '张力矫直、脱脂清洗、重卷分卷等后处理及辅机。',
   'Tension leveling, degreasing, recoiling and slitting auxiliaries.',
   NULL, NULL,
   NULL, 19);

-- 单体设备
INSERT INTO f_single_equipment (category_id, name_zh, name_en, technical_highlights_zh, technical_highlights_en, sort_order) VALUES
  (51, '红外辐射炉', 'Infrared Radiation Furnace', '涂层干燥，非接触加热', 'Non-contact coating drying', 10),
  (52, '硅钢轧前感应加热', 'Induction Heating for Silicon Steel Before Rolling', '感应加热、闭环温控', 'Induction heating with closed-loop temperature control', 20),
  (53, '气雾冷却', 'Aerosol Cooling', '高氢高速喷气冷却、气雾冷却（BLADE喷嘴）', 'High-hydrogen jet cooling, air-mist cooling (BLADE nozzles)', 30),
  (54, '焊缝退火感应加热', 'Induction Heating for Weld Seam Annealing', '焊缝退火、消除应力', 'Weld annealing, stress relief', 40),
  (55, '氢气回收装置', 'Hydrogen Recovery Device', '回收率>75%，2-3年回本', 'Recovery rate >75%, typical payback 2–3 years', 50),
  (56, '镀后冷却', 'Post-coating Cooling', '喷气冷却、气雾冷却', 'Jet and air-mist cooling', 60),
  (57, '涂层干燥感应加热', 'Induction Heating for Coating Drying', '感应加热涂层干燥', 'Induction heating for coating drying', 70),
  (58, '合金化炉', 'Alloying Furnace', '直接火焰加热（FLOX/MILD），NOx降50-70%', 'FLOX/MILD firing, NOx reduction 50-70%', 80),
  (59, '镀锡软熔感应加热', 'Induction Heating for Tin Plating Reflow', '镀锡板软熔处理', 'Tin plate reflow treatment', 90),
  (60, '电磁驱渣器', 'Electromagnetic Dross Remover', '替代人工除渣，自动化锌锅', 'Automated zinc pot skimming', 100),
  (61, '直燃无焰加热', 'Direct-fired Flameless Heating', '无焰燃烧，低NOx排放', 'Flameless combustion, low NOx emission', 110),
  (62, '圆盘剪、拉弯矫直、涂机、开收卷机', 'Circular Shear, Stretch-bend Straightener, Coater, Uncoiler/Coiler', '精整设备', 'Finishing equipment', 120);

-- 备品备件（17 条单品，category_id 为五大分组 71–75）
INSERT INTO f_spare_part (category_id, name_zh, name_en, description_zh, description_en, sort_order) VALUES
  (71, 'W 型辐射管', 'W-radiant tube', '备品备件', 'Spare parts', 30),
  (71, 'I 型辐射管', 'I-type Radiant Tube', '备品备件', 'Spare parts', 40),
  (71, 'U 型辐射管', 'U-Radiant Tube', '备品备件', 'Spare parts', 50),
  (72, '翅片管换热器', 'Finned Tube Heat Exchanger', '备品备件', 'Spare parts', 10),
  (72, 'RJC 换热器', 'RJC Heat exchanger', '备品备件', 'Spare parts', 20),
  (73, '炉辊', 'Hearth Roll', '备品备件', 'Spare parts', 90),
  (73, '大炉辊', 'Big Hearth Roll', '备品备件', 'Spare parts', 100),
  (73, '镀铬辊', 'Chromed Roll', '备品备件', 'Spare parts', 110),
  (73, '氯丁胶转向辊', 'Rubber Turning Roll', '备品备件', 'Spare parts', 120),
  (73, '聚氨酯辊', 'PU Roll', '备品备件', 'Spare parts', 130),
  (73, '张力辊', 'Bridle Roll', '备品备件', 'Spare parts', 170),
  (74, '水淬辊', 'Water Quenching Roll', '备品备件', 'Spare parts', 160),
  (74, '汲料辊', 'Pick Up Roll', '备品备件', 'Spare parts', 140),
  (74, '托辊', 'Support Roll', '备品备件', 'Spare parts', 150),
  (75, '滑块', 'Slide Block', '备品备件', 'Spare parts', 60),
  (75, '轴套衬套', 'Bush and Sleeve', '备品备件', 'Spare parts', 70),
  (75, '支臂', 'Support Arm', '备品备件', 'Spare parts', 80);

-- 成功案例
INSERT INTO f_case_study (category_id, client_name_zh, client_name_en, location, is_overseas, sort_order) VALUES
  (1, '河北兆建', 'Hebaizhao Steel', '中国', 0, 10),
  (1, '印尼钢铁', 'Indonesia Steel', '印度尼西亚', 1, 20),
  (3, '大力神', 'Dalishi', '中国', 0, 30),
  (6, '中国宝武', 'China Baowu', '中国', 0, 40),
  (7, '宝钢硅钢', 'Baosteel Silicon', '中国', 0, 50),
  (8, '首钢京唐', 'Shougang Jingtang', '中国', 0, 60);

-- 媒体库
INSERT INTO f_media_library (
  media_library_id, file_path, file_name, file_type, category, title_zh, title_en, sort_order
) VALUES
  (1, '/uploads/banner/banner-01.jpg', 'banner-01.jpg', 'image', 'banner', '首页轮播1', 'Home banner 1', 1),
  (2, '/uploads/banner/banner-02.jpg', 'banner-02.jpg', 'image', 'banner', '首页轮播2', 'Home banner 2', 2),
  (3, '/uploads/banner/banner-03.jpg', 'banner-03.jpg', 'image', 'banner', '首页轮播3', 'Home banner 3', 3);

-- 案例媒体关联
INSERT INTO f_case_media (case_id, media_id, sort_order) VALUES
  (1, 1, 10),
  (4, 2, 20);

-- 产品媒体关联
INSERT INTO f_product_media (product_id, media_id, sort_order) VALUES
  (1, 1, 10),
  (2, 2, 20);

-- ================================================================
-- 自增ID对齐
-- ================================================================

ALTER TABLE f_company_info AUTO_INCREMENT = 2;
ALTER TABLE f_case_category AUTO_INCREMENT = 9;
ALTER TABLE f_news_category AUTO_INCREMENT = 4;
ALTER TABLE f_media_library AUTO_INCREMENT = 500;

-- ================================================================
-- 外键约束（在数据插入后创建）
-- ================================================================

ALTER TABLE f_product_category
  ADD CONSTRAINT fk_f_product_category_parent
    FOREIGN KEY (parent_id) REFERENCES f_product_category (product_category_id)
    ON DELETE SET NULL ON UPDATE CASCADE;

ALTER TABLE f_banner
  ADD CONSTRAINT fk_f_banner_company
    FOREIGN KEY (company_info_id) REFERENCES f_company_info (company_info_id)
    ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE f_about
  ADD CONSTRAINT fk_f_about_company
    FOREIGN KEY (company_info_id) REFERENCES f_company_info (company_info_id)
    ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE f_workshop
  ADD CONSTRAINT fk_f_workshop_company
    FOREIGN KEY (company_info_id) REFERENCES f_company_info (company_info_id)
    ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE f_contact
  ADD CONSTRAINT fk_f_contact_company
    FOREIGN KEY (company_info_id) REFERENCES f_company_info (company_info_id)
    ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE f_product_line
  ADD CONSTRAINT fk_f_product_line_category
    FOREIGN KEY (category_id) REFERENCES f_product_category (product_category_id)
    ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE f_single_equipment
  ADD CONSTRAINT fk_f_single_equipment_category
    FOREIGN KEY (category_id) REFERENCES f_product_category (product_category_id)
    ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE f_spare_part
  ADD CONSTRAINT fk_f_spare_part_category
    FOREIGN KEY (category_id) REFERENCES f_product_category (product_category_id)
    ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE f_case_study
  ADD CONSTRAINT fk_f_case_study_category
    FOREIGN KEY (category_id) REFERENCES f_case_category (case_category_id)
    ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE f_news
  ADD CONSTRAINT fk_f_news_category
    FOREIGN KEY (category_id) REFERENCES f_news_category (category_id)
    ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE f_product_media
  ADD CONSTRAINT fk_f_product_media_media
    FOREIGN KEY (media_id) REFERENCES f_media_library (media_library_id)
    ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE f_product_line_equipment
  ADD CONSTRAINT fk_f_ple_product_line
    FOREIGN KEY (product_line_id) REFERENCES f_product_line (product_line_id)
    ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT fk_f_ple_equipment
    FOREIGN KEY (equipment_id) REFERENCES f_single_equipment (single_equipment_id)
    ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE f_case_media
  ADD CONSTRAINT fk_f_case_media_case
    FOREIGN KEY (case_id) REFERENCES f_case_study (case_study_id)
    ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT fk_f_case_media_media
    FOREIGN KEY (media_id) REFERENCES f_media_library (media_library_id)
    ON DELETE CASCADE ON UPDATE CASCADE;

SET FOREIGN_KEY_CHECKS = 1;

-- ================================================================
-- 脚本结束
-- ================================================================
SELECT 'Database initialization completed successfully!' AS result;