-- 司法行政机构管理 SYS_ORG（达梦8）
-- 模式：JZ_TEST
-- 开发环境结构变更时可先 DROP 再 CREATE

DROP TABLE IF EXISTS SYS_ORG;

CREATE TABLE SYS_ORG (
    JGBM         VARCHAR(18)  NOT NULL,
    JGMC         VARCHAR(300) NOT NULL,
    JGJC         VARCHAR(60)  NULL,
    YWMC         VARCHAR(60)  NULL,
    JGDM         VARCHAR(20)  NOT NULL,
    FZR          VARCHAR(20)  NOT NULL,
    JGLB         VARCHAR(4)   NOT NULL,
    JGXZJB       VARCHAR(2)   NOT NULL,
    JGLSCJ       VARCHAR(2)   NOT NULL,
    SJSFXZZGJG   VARCHAR(18)  NOT NULL,
    LXDH         VARCHAR(40)  NULL,
    CZHM         VARCHAR(40)  NULL,
    DZYX         VARCHAR(60)  NULL,
    XZQHDM       VARCHAR(6)   NOT NULL,
    DZ           VARCHAR(200) NULL,
    JD           DECIMAL(12,4) NULL,
    WD           DECIMAL(12,4) NULL,
    ORG_STATUS   TINYINT      NOT NULL DEFAULT 1,
    DELETED      TINYINT      NOT NULL DEFAULT 0,
    CREATE_BY    VARCHAR(64)  NULL,
    CREATE_TIME  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UPDATE_BY    VARCHAR(64)  NULL,
    UPDATE_TIME  DATETIME     NULL,
    PRIMARY KEY (JGBM)
);

COMMENT ON TABLE SYS_ORG IS '司法行政机关基础信息（标准表3）';
COMMENT ON COLUMN SYS_ORG.JGBM IS '机构编码（主键）';
COMMENT ON COLUMN SYS_ORG.JGMC IS '机构名称';
COMMENT ON COLUMN SYS_ORG.JGJC IS '机构简称';
COMMENT ON COLUMN SYS_ORG.YWMC IS '英文名称';
COMMENT ON COLUMN SYS_ORG.JGDM IS '机构代码（业务唯一）';
COMMENT ON COLUMN SYS_ORG.FZR IS '负责人';
COMMENT ON COLUMN SYS_ORG.JGLB IS '机构类别';
COMMENT ON COLUMN SYS_ORG.JGXZJB IS '机构行政级别';
COMMENT ON COLUMN SYS_ORG.JGLSCJ IS '机构隶属层级';
COMMENT ON COLUMN SYS_ORG.SJSFXZZGJG IS '上级司法行政机关编码';
COMMENT ON COLUMN SYS_ORG.LXDH IS '联系电话';
COMMENT ON COLUMN SYS_ORG.CZHM IS '传真号码';
COMMENT ON COLUMN SYS_ORG.DZYX IS '电子邮箱';
COMMENT ON COLUMN SYS_ORG.XZQHDM IS '行政区划代码';
COMMENT ON COLUMN SYS_ORG.DZ IS '地址';
COMMENT ON COLUMN SYS_ORG.JD IS '经度';
COMMENT ON COLUMN SYS_ORG.WD IS '纬度';
COMMENT ON COLUMN SYS_ORG.ORG_STATUS IS '状态（0停用 1正常）';
COMMENT ON COLUMN SYS_ORG.DELETED IS '逻辑删除（0未删 1已删）';
COMMENT ON COLUMN SYS_ORG.CREATE_BY IS '创建者';
COMMENT ON COLUMN SYS_ORG.CREATE_TIME IS '创建时间';
COMMENT ON COLUMN SYS_ORG.UPDATE_BY IS '更新者';
COMMENT ON COLUMN SYS_ORG.UPDATE_TIME IS '更新时间';

CREATE UNIQUE INDEX UK_SYS_ORG_JGDM ON SYS_ORG(JGDM, DELETED);
CREATE INDEX IDX_SYS_ORG_PARENT ON SYS_ORG(SJSFXZZGJG, DELETED);
CREATE INDEX IDX_SYS_ORG_AREA ON SYS_ORG(XZQHDM);
CREATE INDEX IDX_SYS_ORG_NAME ON SYS_ORG(JGMC);
CREATE INDEX IDX_SYS_ORG_STATUS ON SYS_ORG(ORG_STATUS, DELETED);