-- ============================================================
-- 湛江市智慧人社运营运维项目 - 职业指导服务 达梦(DM)建表与视图脚本
-- 修复说明：
-- 1. 修正列名 ID_CARD 为 ID_NUMBER
-- 2. 增加模式前缀 ZJRS_JEECG_BOOT
-- 3. 修正 TIMESTAMPDIFF 为达梦语法 YEARS_BETWEEN
-- ============================================================

-- 1. 表结构创建

-- 目录表
CREATE TABLE IF NOT EXISTS CAREER_GUIDANCE_CATEGORY (
  ID VARCHAR(36) NOT NULL,
  PARENT_ID VARCHAR(36),
  NAME VARCHAR(200) NOT NULL,
  SORT_NO INTEGER,
  CREATE_BY VARCHAR(50),
  CREATE_TIME TIMESTAMP,
  UPDATE_BY VARCHAR(50),
  UPDATE_TIME TIMESTAMP,
  SYS_ORG_CODE VARCHAR(50),
  PRIMARY KEY (ID)
);

-- 文档表
CREATE TABLE IF NOT EXISTS CAREER_GUIDANCE_DOCUMENT (
  ID VARCHAR(36) NOT NULL,
  CATEGORY_ID VARCHAR(36),
  NAME VARCHAR(200) NOT NULL,
  FILE_URL VARCHAR(1000),
  SORT_NO INTEGER,
  CREATE_BY VARCHAR(50),
  CREATE_TIME TIMESTAMP,
  UPDATE_BY VARCHAR(50),
  UPDATE_TIME TIMESTAMP,
  SYS_ORG_CODE VARCHAR(50),
  PRIMARY KEY (ID)
);

CREATE TABLE IF NOT EXISTS CAREER_GUIDANCE_EXPERT (
  ID VARCHAR(36) NOT NULL,
  NAME VARCHAR(100) NOT NULL,
  CONTACT_PHONE VARCHAR(50),
  EXPERTISE_AREA VARCHAR(500),
  CREATE_BY VARCHAR(50),
  CREATE_TIME TIMESTAMP,
  UPDATE_BY VARCHAR(50),
  UPDATE_TIME TIMESTAMP,
  SYS_ORG_CODE VARCHAR(50),
  PRIMARY KEY (ID)
);

CREATE TABLE IF NOT EXISTS CAREER_GUIDANCE_SERVICE (
  ID VARCHAR(36) NOT NULL,
  PERSONAL_ID VARCHAR(36) NOT NULL,
  APPLICATION_REASON VARCHAR(1000) NOT NULL,
  APPLICATION_DATE DATE,
  IS_FOLLOWED_UP VARCHAR(10) DEFAULT '否',
  LAST_NOTIFY_TIME TIMESTAMP,
  CREATE_BY VARCHAR(50),
  CREATE_TIME TIMESTAMP,
  UPDATE_BY VARCHAR(50),
  UPDATE_TIME TIMESTAMP,
  SYS_ORG_CODE VARCHAR(50),
  PRIMARY KEY (ID)
);

CREATE TABLE IF NOT EXISTS CAREER_GUIDANCE_RECORD (
  ID VARCHAR(36) NOT NULL,
  SERVICE_ID VARCHAR(36) NOT NULL,
  RECORD_TYPE INTEGER NOT NULL,
  TITLE VARCHAR(200),
  CONTENT VARCHAR(2000) NOT NULL,
  RECORD_TIME TIMESTAMP NOT NULL,
  OPERATOR VARCHAR(100) NOT NULL,
  CREATE_BY VARCHAR(50),
  CREATE_TIME TIMESTAMP,
  UPDATE_BY VARCHAR(50),
  UPDATE_TIME TIMESTAMP,
  SYS_ORG_CODE VARCHAR(50),
  PRIMARY KEY (ID)
);

-- 2. 视图定义
CREATE OR REPLACE VIEW V_CAREER_GUIDANCE_SERVICE AS
SELECT
    cgs.ID,
    cgs.PERSONAL_ID,
    cgs.APPLICATION_REASON,
    cgs.APPLICATION_DATE,
    cgs.IS_FOLLOWED_UP,
    cgs.LAST_NOTIFY_TIME,
    pi.FULL_NAME AS NAME,
    pi.ID_NUMBER AS ID_CARD,
    pi.GENDER,
    YEARS_BETWEEN(pi.BIRTH_DATE, SYSDATE) AS AGE,
    pi.EDUCATION,
    pi.HOUSEHOLD_LOCATION,
    pi.CONTACT_PHONE,
    cgs.CREATE_TIME
FROM CAREER_GUIDANCE_SERVICE cgs
LEFT JOIN ZJRS_JEECG_BOOT.PERSONAL_INFO pi ON cgs.PERSONAL_ID = pi.ID;