CREATE TABLE IF NOT EXISTS "oa_attendance" (
    "id" bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    "user_id" bigint NOT NULL, "type" tinyint NOT NULL, "status" tinyint NOT NULL,
    "attendance_time" timestamp NOT NULL, "attendance_ip" varchar(64), "remark" varchar(500),
    "creator" varchar(64) DEFAULT '', "create_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updater" varchar(64) DEFAULT '', "update_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "deleted" bit NOT NULL DEFAULT FALSE, "tenant_id" bigint NOT NULL DEFAULT 0,
    PRIMARY KEY ("id")
);


CREATE TABLE IF NOT EXISTS "oa_schedule" (
    "id" bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    "type" tinyint NOT NULL,
    "priority" tinyint NOT NULL,
    "title" varchar(255) NOT NULL,
    "description" varchar(1000),
    "start_time" timestamp NOT NULL,
    "end_time" timestamp NOT NULL,
    "remind" bit NOT NULL DEFAULT FALSE,
    "reminded" bit NOT NULL DEFAULT FALSE,
    "creator" varchar(64) DEFAULT '', "create_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updater" varchar(64) DEFAULT '', "update_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "deleted" bit NOT NULL DEFAULT FALSE, "tenant_id" bigint NOT NULL DEFAULT 0,
    PRIMARY KEY ("id")
);

CREATE TABLE IF NOT EXISTS "oa_schedule_participant" (
    "id" bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    "schedule_id" bigint NOT NULL, "user_id" bigint NOT NULL,
    "read_status" bit NOT NULL DEFAULT FALSE, "read_time" timestamp,
    "creator" varchar(64) DEFAULT '', "create_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updater" varchar(64) DEFAULT '', "update_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "deleted" bit NOT NULL DEFAULT FALSE, "tenant_id" bigint NOT NULL DEFAULT 0,
    PRIMARY KEY ("id")
);

CREATE TABLE IF NOT EXISTS "oa_plan" (
    "id" bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    "type" tinyint NOT NULL, "status" tinyint NOT NULL,
    "title" varchar(100) NOT NULL, "label" varchar(255), "content" text NOT NULL,
    "summary" text, "comment" text, "start_time" timestamp NOT NULL, "end_time" timestamp NOT NULL,
    "file_urls" varchar(5000),
    "creator" varchar(64) DEFAULT '', "create_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updater" varchar(64) DEFAULT '', "update_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "deleted" bit NOT NULL DEFAULT FALSE, "tenant_id" bigint NOT NULL DEFAULT 0,
    PRIMARY KEY ("id")
);

CREATE TABLE IF NOT EXISTS "oa_work_report" (
    "id" bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    "no" varchar(64) NOT NULL, "type" tinyint NOT NULL, "status" tinyint NOT NULL,
    "dept_id" bigint,
    "start_time" timestamp NOT NULL, "end_time" timestamp NOT NULL,
    "title" varchar(255) NOT NULL, "summary" text, "plan" text, "problem" text,
    "work_items" varchar(10000), "plan_items" varchar(10000), "file_urls" varchar(5000),
    "remark" varchar(1000),
    "creator" varchar(64) DEFAULT '', "create_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updater" varchar(64) DEFAULT '', "update_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "deleted" bit NOT NULL DEFAULT FALSE, "tenant_id" bigint NOT NULL DEFAULT 0,
    PRIMARY KEY ("id"),
    KEY work_report_idx_no (`tenant_id`, `no`)
);

CREATE TABLE IF NOT EXISTS "oa_note_category" (
    "id" bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    "user_id" bigint NOT NULL, "name" varchar(255) NOT NULL, "sort" int NOT NULL DEFAULT 0,
    "creator" varchar(64) DEFAULT '', "create_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updater" varchar(64) DEFAULT '', "update_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "deleted" bit NOT NULL DEFAULT FALSE, "tenant_id" bigint NOT NULL DEFAULT 0,
    PRIMARY KEY ("id")
);

CREATE TABLE IF NOT EXISTS "oa_note" (
    "id" bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    "category_id" bigint, "type" tinyint NOT NULL,
    "priority" tinyint NOT NULL, "title" varchar(255) NOT NULL, "content" text,
    "favorite" bit NOT NULL DEFAULT FALSE, "file_urls" varchar(5000),
    "creator" varchar(64) DEFAULT '', "create_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updater" varchar(64) DEFAULT '', "update_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "deleted" bit NOT NULL DEFAULT FALSE, "tenant_id" bigint NOT NULL DEFAULT 0,
    PRIMARY KEY ("id")
);

CREATE TABLE IF NOT EXISTS "oa_note_receiver" (
    "id" bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    "note_id" bigint NOT NULL, "user_id" bigint NOT NULL,
    "creator" varchar(64) DEFAULT '', "create_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updater" varchar(64) DEFAULT '', "update_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "deleted" bit NOT NULL DEFAULT FALSE, "tenant_id" bigint NOT NULL DEFAULT 0,
    PRIMARY KEY ("id")
);

CREATE TABLE IF NOT EXISTS "oa_contact_category" (
    "id" bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    "user_id" bigint NOT NULL, "name" varchar(50) NOT NULL, "sort" int NOT NULL DEFAULT 0,
    "creator" varchar(64) DEFAULT '', "create_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updater" varchar(64) DEFAULT '', "update_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "deleted" bit NOT NULL DEFAULT FALSE, "tenant_id" bigint NOT NULL DEFAULT 0,
    PRIMARY KEY ("id")
);

CREATE TABLE IF NOT EXISTS "oa_contact" (
    "id" bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    "category_id" bigint, "name" varchar(50) NOT NULL,
    "pinyin" varchar(100), "sex" tinyint, "mobile" varchar(20), "email" varchar(100),
    "address" varchar(255), "company_name" varchar(100), "company_phone" varchar(30),
    "avatar" varchar(512), "remark" varchar(500),
    "creator" varchar(64) DEFAULT '', "create_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updater" varchar(64) DEFAULT '', "update_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "deleted" bit NOT NULL DEFAULT FALSE, "tenant_id" bigint NOT NULL DEFAULT 0,
    PRIMARY KEY ("id")
);

CREATE TABLE IF NOT EXISTS "oa_contact_share" (
    "id" bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    "contact_id" bigint NOT NULL, "user_id" bigint NOT NULL, "category_id" bigint,
    "handle_status" bit NOT NULL DEFAULT FALSE,
    "creator" varchar(64) DEFAULT '', "create_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updater" varchar(64) DEFAULT '', "update_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "deleted" bit NOT NULL DEFAULT FALSE, "tenant_id" bigint NOT NULL DEFAULT 0,
    PRIMARY KEY ("id")
);

CREATE TABLE IF NOT EXISTS "oa_task" (
    "publish_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "id" bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    "type" tinyint NOT NULL, "status" tinyint NOT NULL,
    "title" varchar(255) NOT NULL, "description" varchar(2000) NOT NULL, "comment" varchar(1000),
    "start_time" timestamp NOT NULL, "end_time" timestamp NOT NULL,
    "top" bit NOT NULL DEFAULT FALSE, "canceled" bit NOT NULL DEFAULT FALSE,
    "creator" varchar(64) DEFAULT '', "create_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updater" varchar(64) DEFAULT '', "update_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "deleted" bit NOT NULL DEFAULT FALSE, "tenant_id" bigint NOT NULL DEFAULT 0,
    PRIMARY KEY ("id")
);

CREATE TABLE IF NOT EXISTS "oa_task_receiver" (
    "id" bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    "task_id" bigint NOT NULL, "user_id" bigint NOT NULL, "status" tinyint NOT NULL,
    "creator" varchar(64) DEFAULT '', "create_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updater" varchar(64) DEFAULT '', "update_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "deleted" bit NOT NULL DEFAULT FALSE, "tenant_id" bigint NOT NULL DEFAULT 0,
    PRIMARY KEY ("id")
);

CREATE TABLE IF NOT EXISTS "oa_task_log" (
    "id" bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    "task_id" bigint NOT NULL, "user_id" bigint NOT NULL, "status" tinyint, "content" varchar(1000),
    "creator" varchar(64) DEFAULT '', "create_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updater" varchar(64) DEFAULT '', "update_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "deleted" bit NOT NULL DEFAULT FALSE, "tenant_id" bigint NOT NULL DEFAULT 0,
    PRIMARY KEY ("id")
);

CREATE TABLE IF NOT EXISTS "oa_announcement" (
    "id" bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    "type" tinyint NOT NULL, "priority" tinyint NOT NULL,
    "title" varchar(255) NOT NULL, "content" text, "url" varchar(512),
    "top" bit NOT NULL DEFAULT FALSE,
    "creator" varchar(64) DEFAULT '', "create_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updater" varchar(64) DEFAULT '', "update_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "deleted" bit NOT NULL DEFAULT FALSE, "tenant_id" bigint NOT NULL DEFAULT 0,
    PRIMARY KEY ("id")
);

CREATE TABLE IF NOT EXISTS "oa_announcement_receiver" (
    "id" bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    "announcement_id" bigint NOT NULL, "receiver_user_id" bigint NOT NULL,
    "read_status" bit NOT NULL DEFAULT FALSE,
    "creator" varchar(64) DEFAULT '', "create_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updater" varchar(64) DEFAULT '', "update_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "deleted" bit NOT NULL DEFAULT FALSE, "tenant_id" bigint NOT NULL DEFAULT 0,
    PRIMARY KEY ("id"),
    KEY announcement_receiver_idx_announcement_id_receiver_user_id (`announcement_id`, `receiver_user_id`, `deleted`)
);

CREATE TABLE IF NOT EXISTS "oa_discussion" (
    "id" bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    "user_id" bigint NOT NULL, "type" tinyint NOT NULL, "title" varchar(255) NOT NULL,
    "content" text, "file_urls" varchar(5000), "visit_count" int NOT NULL DEFAULT 0,
    "vote_multiple" bit, "vote_start_time" timestamp, "vote_end_time" timestamp,
    "creator" varchar(64) DEFAULT '', "create_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updater" varchar(64) DEFAULT '', "update_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "deleted" bit NOT NULL DEFAULT FALSE, "tenant_id" bigint NOT NULL DEFAULT 0,
    PRIMARY KEY ("id")
);

CREATE TABLE IF NOT EXISTS "oa_discussion_reply" (
    "id" bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    "discussion_id" bigint NOT NULL, "user_id" bigint NOT NULL, "parent_id" bigint NOT NULL DEFAULT 0,
    "reply_user_id" bigint, "content" text NOT NULL,
    "creator" varchar(64) DEFAULT '', "create_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updater" varchar(64) DEFAULT '', "update_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "deleted" bit NOT NULL DEFAULT FALSE, "tenant_id" bigint NOT NULL DEFAULT 0,
    PRIMARY KEY ("id")
);

CREATE TABLE IF NOT EXISTS "oa_discussion_like" (
    "id" bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    "discussion_id" bigint, "reply_id" bigint, "user_id" bigint NOT NULL,
    "creator" varchar(64) DEFAULT '', "create_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updater" varchar(64) DEFAULT '', "update_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "deleted" bit NOT NULL DEFAULT FALSE, "tenant_id" bigint NOT NULL DEFAULT 0,
    PRIMARY KEY ("id")
);

CREATE TABLE IF NOT EXISTS "oa_vote_option" (
    "id" bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    "discussion_id" bigint NOT NULL, "title" varchar(200) NOT NULL, "color" varchar(20),
    "sort" int NOT NULL DEFAULT 0,
    "creator" varchar(64) DEFAULT '', "create_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updater" varchar(64) DEFAULT '', "update_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "deleted" bit NOT NULL DEFAULT FALSE, "tenant_id" bigint NOT NULL DEFAULT 0,
    PRIMARY KEY ("id")
);

CREATE TABLE IF NOT EXISTS "oa_vote_record" (
    "id" bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    "discussion_id" bigint NOT NULL, "option_id" bigint NOT NULL, "user_id" bigint NOT NULL,
    "creator" varchar(64) DEFAULT '', "create_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updater" varchar(64) DEFAULT '', "update_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "deleted" bit NOT NULL DEFAULT FALSE, "tenant_id" bigint NOT NULL DEFAULT 0,
    PRIMARY KEY ("id"),
    KEY vote_record_idx_option_id_user_id (`option_id`, `user_id`, `deleted`)
);

CREATE INDEX IF NOT EXISTS "idx_schedule_id_user_id" ON "oa_schedule_participant" ("schedule_id", "user_id", "deleted");
CREATE INDEX IF NOT EXISTS "idx_note_id_user_id" ON "oa_note_receiver" ("note_id", "user_id", "deleted");

-- 企业邮箱：仅保存服务配置和账号，不缓存远端邮件
CREATE TABLE IF NOT EXISTS oa_mail_provider (
  id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY,
  name varchar(255) NOT NULL,
  imap json NOT NULL,
  smtp json NOT NULL,
  status tinyint NOT NULL,
  creator varchar(64) DEFAULT '',
  create_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updater varchar(64) DEFAULT '',
  update_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  deleted bit NOT NULL DEFAULT FALSE,
  tenant_id bigint NOT NULL DEFAULT 0,
  PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS oa_seal (
  id bigint NOT NULL AUTO_INCREMENT,
  `dept_id` bigint NOT NULL,
  `no` varchar(64) NOT NULL,
  `name` varchar(128) NOT NULL,
  `type` int NOT NULL,
  `category` int DEFAULT NULL,
  `keeper_user_id` bigint NOT NULL,
  `keeper_dept_id` bigint NOT NULL,
  `status` int NOT NULL DEFAULT 0,
  `purchase_time` datetime DEFAULT NULL,
  `enable_time` datetime DEFAULT NULL,
  `disable_time` datetime DEFAULT NULL,
  `pic_url` varchar(2048) DEFAULT NULL,
  `sort` int NOT NULL DEFAULT 0,
  `remark` varchar(500) DEFAULT NULL,
  creator varchar(64) DEFAULT '',
  create_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updater varchar(64) DEFAULT '',
  update_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  deleted bit NOT NULL DEFAULT FALSE,
  tenant_id bigint NOT NULL DEFAULT 0,
  PRIMARY KEY (id),
  KEY seal_idx_no (`tenant_id`, `no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='印章';

CREATE TABLE IF NOT EXISTS oa_seal_apply (
  id bigint NOT NULL AUTO_INCREMENT,
  `no` varchar(64) NOT NULL,
  `user_id` bigint NOT NULL,
  `dept_id` bigint DEFAULT NULL,
  `seal_id` bigint NOT NULL,
  `seal_no` varchar(64) NOT NULL,
  `seal_name` varchar(128) NOT NULL,
  `seal_type` tinyint DEFAULT NULL COMMENT '印章类型快照',
  `keeper_dept_id` bigint DEFAULT NULL COMMENT '保管部门编号快照',
  `keeper_user_id` bigint NOT NULL,
  `reason` varchar(500) NOT NULL,
  `type` tinyint NOT NULL,
  `mode` tinyint NOT NULL,
  `document_title` varchar(255) DEFAULT NULL,
  `document_type` varchar(64) DEFAULT NULL,
  `document_count` int NOT NULL,
  `contract_price` decimal(18,2) DEFAULT NULL,
  `contract_party` varchar(255) DEFAULT NULL,
  `expected_use_time` datetime NOT NULL,
  `actual_use_time` datetime DEFAULT NULL,
  `expected_return_time` datetime DEFAULT NULL,
  `actual_return_time` datetime DEFAULT NULL,
  `use_status` tinyint NOT NULL DEFAULT 0,
  `status` tinyint NOT NULL DEFAULT -1,
  `process_instance_id` varchar(64) DEFAULT NULL,
  `urgent` bit NOT NULL DEFAULT FALSE,
  file_urls varchar(12000) DEFAULT NULL,
  `remark` varchar(500) DEFAULT NULL,
  creator varchar(64) DEFAULT '',
  create_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updater varchar(64) DEFAULT '',
  update_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  deleted bit NOT NULL DEFAULT FALSE,
  tenant_id bigint NOT NULL DEFAULT 0,
  PRIMARY KEY (id),
  KEY seal_apply_idx_no (`tenant_id`, `no`),
  KEY seal_apply_idx_process_instance (`tenant_id`, `process_instance_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用印申请';

CREATE TABLE IF NOT EXISTS oa_mail_account (
  id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY,
  provider_id bigint NOT NULL,
  mail varchar(255) NOT NULL,
  username varchar(255) NOT NULL,
  password text NOT NULL,
  default_status bit NOT NULL,
  status tinyint NOT NULL,
  creator varchar(64) DEFAULT '',
  create_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updater varchar(64) DEFAULT '',
  update_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  deleted bit NOT NULL DEFAULT FALSE,
  tenant_id bigint NOT NULL DEFAULT 0,
  PRIMARY KEY (id)
);

-- 企业邮箱：手动同步的文件夹与邮件索引，不保存正文、凭据和附件
CREATE TABLE IF NOT EXISTS oa_mail_folder (
  id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY,
  account_id bigint NOT NULL,
  name varchar(255) NOT NULL,
  type varchar(20) NOT NULL,
  uid_validity bigint DEFAULT NULL,
  sync_time datetime DEFAULT NULL,
  available bit NOT NULL DEFAULT TRUE,
  creator varchar(64) DEFAULT '',
  create_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updater varchar(64) DEFAULT '',
  update_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  deleted bit NOT NULL DEFAULT FALSE,
  tenant_id bigint NOT NULL DEFAULT 0,
  PRIMARY KEY (id),
  KEY mail_folder_idx_account_folder (`tenant_id`, `account_id`, `name`)
);

CREATE TABLE IF NOT EXISTS oa_mail_message (
  id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY,
  account_id bigint NOT NULL,
  folder_id bigint NOT NULL,
  uid_validity bigint NOT NULL,
  uid bigint NOT NULL,
  subject longtext NOT NULL,
  content clob DEFAULT NULL,
  reply_tos varchar(20000) DEFAULT NULL,
  attachments varchar(20000) DEFAULT NULL,
  sender text,
  recipients varchar(20000),
  ccs varchar(20000),
  receive_time datetime DEFAULT NULL,
  read_status bit NOT NULL DEFAULT FALSE,
  has_attach bit NOT NULL DEFAULT FALSE,
  size int DEFAULT NULL,
  creator varchar(64) DEFAULT '',
  create_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updater varchar(64) DEFAULT '',
  update_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  deleted bit NOT NULL DEFAULT FALSE,
  tenant_id bigint NOT NULL DEFAULT 0,
  PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS oa_file_node (
 id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
 parent_id bigint NOT NULL DEFAULT 0,
 type tinyint NOT NULL,
 name varchar(255) NOT NULL,
 extension varchar(64),
 category tinyint,
 size bigint DEFAULT 0,
 url varchar(2048),
 dept_id bigint,
 status tinyint NOT NULL DEFAULT 0,
 creator varchar(64) DEFAULT '',
 create_time timestamp DEFAULT CURRENT_TIMESTAMP,
 updater varchar(64) DEFAULT '',
 update_time timestamp DEFAULT CURRENT_TIMESTAMP,
 deleted bit DEFAULT FALSE,
 tenant_id bigint DEFAULT 0
);

CREATE TABLE IF NOT EXISTS oa_file_permission (
 id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
 node_id bigint NOT NULL,
 subject_type tinyint NOT NULL,
 subject_id bigint NOT NULL,
 level tinyint NOT NULL,
 inherit bit NOT NULL,
 expire_time timestamp,
 creator varchar(64) DEFAULT '',
 create_time timestamp DEFAULT CURRENT_TIMESTAMP,
 updater varchar(64) DEFAULT '',
 update_time timestamp DEFAULT CURRENT_TIMESTAMP,
 deleted bit DEFAULT FALSE,
 tenant_id bigint DEFAULT 0
);

CREATE TABLE IF NOT EXISTS oa_file_favorite (
 id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
 node_id bigint NOT NULL,
 user_id bigint NOT NULL,
 creator varchar(64) DEFAULT '',
 create_time timestamp DEFAULT CURRENT_TIMESTAMP,
 updater varchar(64) DEFAULT '',
 update_time timestamp DEFAULT CURRENT_TIMESTAMP,
 deleted bit DEFAULT FALSE,
 tenant_id bigint DEFAULT 0
);

CREATE TABLE IF NOT EXISTS oa_vehicle (
  id bigint NOT NULL AUTO_INCREMENT PRIMARY KEY,
  no varchar(32) NOT NULL,
  name varchar(128) NOT NULL,
  type varchar(64) NOT NULL,
  category varchar(64),
  brand_model varchar(128),
  seat_count int NOT NULL,
  bare_price decimal(18,2) NOT NULL DEFAULT 0,
  compulsory_insurance_expire_time datetime,
  commercial_insurance_expire_time datetime,
  inspection_expire_time datetime,
  pic_url varchar(2048),
  dept_id bigint,
  status tinyint NOT NULL DEFAULT 0,
  sort int NOT NULL DEFAULT 0,
  remark varchar(500),
  creator varchar(64) DEFAULT '',
  create_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updater varchar(64) DEFAULT '',
  update_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  deleted bit NOT NULL DEFAULT FALSE,
  tenant_id bigint NOT NULL DEFAULT 0,
  KEY vehicle_idx_no (`tenant_id`, `no`)
);

CREATE TABLE IF NOT EXISTS oa_vehicle_return (
  id bigint NOT NULL AUTO_INCREMENT PRIMARY KEY,
  no varchar(64) NOT NULL,
  apply_id bigint NOT NULL,
  vehicle_id bigint NOT NULL,
  user_id bigint NOT NULL,
  dept_id bigint,
  actual_start_time datetime,
  start_location varchar(255),
  reason varchar(500),
  passenger varchar(500),
  actual_return_time datetime,
  return_location varchar(255),
  status tinyint NOT NULL DEFAULT -1,
  process_instance_id varchar(64),
  file_urls varchar(12000) DEFAULT NULL,
  remark varchar(500),
  creator varchar(64) DEFAULT '',
  create_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updater varchar(64) DEFAULT '',
  update_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  deleted bit NOT NULL DEFAULT FALSE,
  tenant_id bigint NOT NULL DEFAULT 0,
  KEY vehicle_return_idx_process_instance (`tenant_id`, `process_instance_id`),
  KEY vehicle_return_idx_no (`tenant_id`, `no`)
);

CREATE TABLE IF NOT EXISTS oa_vehicle_apply (
  id bigint NOT NULL AUTO_INCREMENT PRIMARY KEY,
  no varchar(64) NOT NULL,
  vehicle_id bigint,
  vehicle_no varchar(32),
  user_id bigint NOT NULL,
  dept_id bigint,
  start_time datetime,
  end_time datetime,
  start_location varchar(255),
  end_location varchar(255),
  reason varchar(500),
  passenger varchar(500),
  status tinyint NOT NULL DEFAULT -1,
  return_status tinyint NOT NULL DEFAULT 0,
  process_instance_id varchar(64),
  file_urls varchar(12000) DEFAULT NULL,
  remark varchar(500),
  creator varchar(64) DEFAULT '',
  create_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updater varchar(64) DEFAULT '',
  update_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  deleted bit NOT NULL DEFAULT FALSE,
  tenant_id bigint NOT NULL DEFAULT 0,
  KEY vehicle_apply_idx_no (`tenant_id`, `no`),
  KEY vehicle_apply_idx_process_instance (`tenant_id`, `process_instance_id`)
);

CREATE TABLE IF NOT EXISTS "oa_supply_item" (
    "id" bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    "dept_id" bigint NOT NULL,
    "name" varchar(128) NOT NULL,
    "no" varchar(64) DEFAULT NULL,
    "category" int NOT NULL,
    "manage_type" int NOT NULL,
    "model" varchar(128),
    "unit" varchar(32),
    "reference_price" decimal(18,2),
    "stock_quantity" int NOT NULL DEFAULT 0,
    "min_stock_quantity" int NOT NULL DEFAULT 0,
    "pic_url" varchar(2048),
    "status" int NOT NULL DEFAULT 0,
    "sort" int NOT NULL DEFAULT 0,
    "remark" varchar(500),
    "creator" varchar(64) DEFAULT '',
    "create_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updater" varchar(64) DEFAULT '',
    "update_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "deleted" bit NOT NULL DEFAULT FALSE,
    "tenant_id" bigint NOT NULL DEFAULT 0,
    PRIMARY KEY ("id"),
    KEY supply_item_idx_no (`tenant_id`, `no`, `deleted`)
);

CREATE TABLE IF NOT EXISTS "oa_supply_apply" (
    "id" bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    "no" varchar(64) NOT NULL,
    "dept_id" bigint NOT NULL,
    "apply_time" timestamp,
    "use_type" int,
    "pickup_method" int,
    "reason" varchar(500),
    "status" int NOT NULL DEFAULT -1,
    "process_instance_id" varchar(64),
    "file_urls" varchar(10000),
    "remark" varchar(500),
    "creator" varchar(64) DEFAULT '',
    "create_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updater" varchar(64) DEFAULT '',
    "update_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "deleted" bit NOT NULL DEFAULT FALSE,
    "tenant_id" bigint NOT NULL DEFAULT 0,
    PRIMARY KEY ("id"),
    KEY supply_apply_idx_no (`tenant_id`, `no`, `deleted`),
    KEY supply_apply_idx_creator (`tenant_id`, `creator`, `deleted`, `id`)
);

CREATE TABLE IF NOT EXISTS "oa_supply_apply_item" (
    "id" bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    "apply_id" bigint NOT NULL,
    "item_id" bigint NOT NULL,
    "item_name" varchar(128) NOT NULL,
    "model" varchar(128),
    "unit" varchar(32),
    "manage_type" int NOT NULL,
    "apply_quantity" int,
    "issued_quantity" int NOT NULL DEFAULT 0,
    "returned_quantity" int NOT NULL DEFAULT 0,
    "status" int NOT NULL DEFAULT -1,
    "issue_user_id" bigint,
    "issue_time" timestamp,
    "issue_remark" varchar(500),
    "return_remark" varchar(500),
    "creator" varchar(64) DEFAULT '',
    "create_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updater" varchar(64) DEFAULT '',
    "update_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "deleted" bit NOT NULL DEFAULT FALSE,
    "tenant_id" bigint NOT NULL DEFAULT 0,
    PRIMARY KEY ("id"),
    KEY supply_apply_item_idx_apply_id (`tenant_id`, `apply_id`, `deleted`, `id`),
    KEY supply_apply_item_idx_item_id (`tenant_id`, `item_id`, `deleted`)
);









CREATE TABLE IF NOT EXISTS "oa_meeting_room" (
    "id" bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    "name" varchar(100) NOT NULL,
    "location" varchar(255) NOT NULL,
    "type" tinyint NOT NULL,
    "manager_user_id" bigint NOT NULL,
    "status" tinyint NOT NULL,
    "pic_url" varchar(1024),
    "seat_count" int,
    "equipments" varchar(5000),
    "allow_booking" bit NOT NULL,
    "need_approval" bit NOT NULL,
    "booking_scope" tinyint NOT NULL,
    "booking_user_ids" varchar(5000),
    "sort" int NOT NULL,
    "remark" varchar(200),
    "file_urls" varchar(5000),
    "creator" varchar(64) DEFAULT '', "create_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updater" varchar(64) DEFAULT '', "update_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "deleted" bit NOT NULL DEFAULT FALSE, "tenant_id" bigint NOT NULL DEFAULT 0,
    PRIMARY KEY ("id")
);

CREATE TABLE IF NOT EXISTS "oa_meeting_room_booking" (
    "id" bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    "no" varchar(64) NOT NULL,
    "room_id" bigint NOT NULL,
    "title" varchar(200) NOT NULL,
    "start_time" timestamp NOT NULL,
    "end_time" timestamp NOT NULL,
    "moderator_user_id" bigint NOT NULL,
    "attendee_user_ids" varchar(5000),
    "reminder_type" tinyint NOT NULL,
    "reminded" bit NOT NULL DEFAULT FALSE,
    "description" varchar(500),
    "dept_id" bigint,
    "status" tinyint NOT NULL,
    "process_instance_id" varchar(64),
    "need_approval" bit,
    "use_status" tinyint NOT NULL,
    "remark" varchar(500),
    "file_urls" varchar(5000),
    "creator" varchar(64) DEFAULT '', "create_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updater" varchar(64) DEFAULT '', "update_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "deleted" bit NOT NULL DEFAULT FALSE, "tenant_id" bigint NOT NULL DEFAULT 0,
    PRIMARY KEY ("id"),
    KEY meeting_room_booking_idx_no (`tenant_id`, `no`)
);

CREATE TABLE IF NOT EXISTS oa_reimbursement (
  id bigint NOT NULL AUTO_INCREMENT,
  title varchar(255) DEFAULT NULL,
  urgency int DEFAULT NULL,
  reason text DEFAULT NULL,
  witness_user_id bigint DEFAULT NULL,
  customer_name varchar(255) DEFAULT NULL,
  payment_method int DEFAULT NULL,
  invoice_count int DEFAULT NULL,
  total_price decimal(18,2) DEFAULT NULL,
  status int DEFAULT NULL,
  process_instance_id varchar(255) DEFAULT NULL,
  items varchar DEFAULT NULL,
  file_urls varchar DEFAULT NULL,
  creator varchar(64) DEFAULT '',
  create_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updater varchar(64) DEFAULT '',
  update_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  deleted bit NOT NULL DEFAULT 0,
  tenant_id bigint NOT NULL DEFAULT 0,
  PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS oa_overtime_apply (
  id bigint NOT NULL AUTO_INCREMENT,
  title varchar(255) DEFAULT NULL,
  urgency int DEFAULT NULL,
  type int DEFAULT NULL,
  start_time datetime DEFAULT NULL,
  end_time datetime DEFAULT NULL,
  days decimal(10,1) DEFAULT NULL,
  reason text DEFAULT NULL,
  status int DEFAULT NULL,
  process_instance_id varchar(255) DEFAULT NULL,
  creator varchar(64) DEFAULT '',
  create_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updater varchar(64) DEFAULT '',
  update_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  deleted bit NOT NULL DEFAULT 0,
  tenant_id bigint NOT NULL DEFAULT 0,
  PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS oa_leave_apply (
  id bigint NOT NULL AUTO_INCREMENT,
  title varchar(255) DEFAULT NULL,
  urgency int DEFAULT NULL,
  type int DEFAULT NULL,
  start_time datetime DEFAULT NULL,
  end_time datetime DEFAULT NULL,
  days int DEFAULT NULL,
  reason text DEFAULT NULL,
  status int DEFAULT NULL,
  process_instance_id varchar(255) DEFAULT NULL,
  file_urls varchar DEFAULT NULL,
  creator varchar(64) DEFAULT '',
  create_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updater varchar(64) DEFAULT '',
  update_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  deleted bit NOT NULL DEFAULT 0,
  tenant_id bigint NOT NULL DEFAULT 0,
  PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS oa_regular_apply (
  id bigint NOT NULL AUTO_INCREMENT,
  title varchar(255) DEFAULT NULL,
  urgency int DEFAULT NULL,
  start_time datetime DEFAULT NULL,
  end_time datetime DEFAULT NULL,
  days int DEFAULT NULL,
  experience varchar(255) DEFAULT NULL,
  understanding varchar(255) DEFAULT NULL,
  growth varchar(255) DEFAULT NULL,
  deficiency varchar(255) DEFAULT NULL,
  improvement varchar(255) DEFAULT NULL,
  suggestion varchar(255) DEFAULT NULL,
  status int DEFAULT NULL,
  process_instance_id varchar(255) DEFAULT NULL,
  creator varchar(64) DEFAULT '',
  create_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updater varchar(64) DEFAULT '',
  update_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  deleted bit NOT NULL DEFAULT 0,
  tenant_id bigint NOT NULL DEFAULT 0,
  PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS oa_resign_apply (
  id bigint NOT NULL AUTO_INCREMENT,
  title varchar(255) DEFAULT NULL,
  urgency int DEFAULT NULL,
  reason text DEFAULT NULL,
  handover_user_id bigint DEFAULT NULL,
  unfinished_work text DEFAULT NULL,
  has_pending_reimbursement bit DEFAULT NULL,
  suggestion varchar(255) DEFAULT NULL,
  status int DEFAULT NULL,
  process_instance_id varchar(255) DEFAULT NULL,
  creator varchar(64) DEFAULT '',
  create_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updater varchar(64) DEFAULT '',
  update_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  deleted bit NOT NULL DEFAULT 0,
  tenant_id bigint NOT NULL DEFAULT 0,
  PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS oa_official_doc_template (
  "id" bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY,
  "name" varchar(128) NOT NULL,
  "authority_name" varchar(255) NOT NULL,
  "font_size" int NOT NULL,
  "no_prefix" varchar(64) NULL,
  "seal_pic_url" varchar(2048) NULL,
  "separator_type" int NOT NULL,
  "status" int NOT NULL,
  "sort" int NOT NULL,
  "remark" varchar(500) NULL,
  "creator" varchar(64) DEFAULT '',
  "create_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "updater" varchar(64) DEFAULT '',
  "update_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "deleted" bit NOT NULL DEFAULT FALSE,
  "tenant_id" bigint NOT NULL DEFAULT 0,
  PRIMARY KEY ("id")
);

CREATE TABLE IF NOT EXISTS oa_official_doc_send (
  "id" bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY,
  "no" varchar(64) NULL,
  "template_id" bigint NOT NULL,
  "title" varchar(255) NOT NULL,
  "no_prefix" varchar(64) NULL,
  "year" int NULL,
  "sequence" int NULL,
  "document_no" varchar(64) NULL,
  "secrecy_level" int NOT NULL,
  "urgency_level" int NOT NULL,
  "disclosure_type" int NOT NULL,
  "issue_time" timestamp NOT NULL,
  "send_dept_id" bigint NOT NULL,
  "main_dept_ids" varchar(20000) NOT NULL,
  "copy_dept_ids" varchar(20000) NULL,
  "signer_user_id" bigint NULL,
  "content" text NULL,
  "file_urls" varchar(20000) NULL,
  "formal_file_url" varchar(2048) NULL,
  "remark" varchar(500) NULL,
  "status" int NULL,
  "process_instance_id" varchar(64) NULL,
  "creator" varchar(64) DEFAULT '',
  "create_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "updater" varchar(64) DEFAULT '',
  "update_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "deleted" bit NOT NULL DEFAULT FALSE,
  "tenant_id" bigint NOT NULL DEFAULT 0,
  PRIMARY KEY ("id")
);

CREATE TABLE IF NOT EXISTS oa_official_doc_receive (
  "id" bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY,
  "no" varchar(64) NULL,
  "send_id" bigint NULL,
  "receive_type" int NOT NULL,
  "title" varchar(255) NULL,
  "document_no" varchar(64) NULL,
  "secrecy_level" int NULL,
  "urgency_level" int NULL,
  "receive_time" timestamp NULL,
  "receive_dept_id" bigint NOT NULL,
  "handler_user_id" bigint NULL,
  "instruction" text NULL,
  "result" text NULL,
  "deadline_time" timestamp NULL,
  "summary" text NULL,
  "remark" varchar(500) NULL,
  "file_urls" varchar(20000) NULL,
  "formal_file_url" varchar(2048) NULL,
  "handle_status" int NULL,
  "status" int NULL,
  "process_instance_id" varchar(64) NULL,
  "creator" varchar(64) DEFAULT '',
  "create_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "updater" varchar(64) DEFAULT '',
  "update_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "deleted" bit NOT NULL DEFAULT FALSE,
  "tenant_id" bigint NOT NULL DEFAULT 0,
  PRIMARY KEY ("id")
);

-- 出差管理初始化表结构，仅包含已确认的两张单据。

CREATE TABLE IF NOT EXISTS oa_travel_apply (
  id bigint NOT NULL AUTO_INCREMENT,
  no varchar(64) NOT NULL,
  dept_id bigint DEFAULT NULL,
  reason text DEFAULT NULL,
  start_time datetime DEFAULT NULL,
  end_time datetime DEFAULT NULL,
  days int DEFAULT NULL,
  companion varchar(500) DEFAULT NULL,
  estimated_price decimal(18,2) DEFAULT NULL,
  reimburse_status tinyint NOT NULL DEFAULT 0,
  status tinyint NOT NULL DEFAULT -1,
  process_instance_id varchar(64) DEFAULT NULL,
  remark text DEFAULT NULL,
  items varchar DEFAULT NULL,
  file_urls varchar DEFAULT NULL,
  creator varchar(64) DEFAULT '',
  create_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updater varchar(64) DEFAULT '',
  update_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  deleted bit NOT NULL DEFAULT 0,
  tenant_id bigint NOT NULL DEFAULT 0,
  PRIMARY KEY (id),
  KEY travel_apply_idx_no (`tenant_id`, `no`)
) COMMENT='出差申请';

CREATE TABLE IF NOT EXISTS oa_travel_reimbursement (
  id bigint NOT NULL AUTO_INCREMENT,
  no varchar(64) NOT NULL,
  dept_id bigint DEFAULT NULL,
  reason text DEFAULT NULL,
  start_time datetime DEFAULT NULL,
  end_time datetime DEFAULT NULL,
  days int DEFAULT NULL,
  travel_apply_id bigint DEFAULT NULL,
  total_price decimal(18,2) DEFAULT NULL,
  pay_status tinyint NOT NULL DEFAULT 0,
  status tinyint NOT NULL DEFAULT -1,
  process_instance_id varchar(64) DEFAULT NULL,
  remark text DEFAULT NULL,
  items varchar DEFAULT NULL,
  file_urls varchar DEFAULT NULL,
  creator varchar(64) DEFAULT '',
  create_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updater varchar(64) DEFAULT '',
  update_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  deleted bit NOT NULL DEFAULT 0,
  tenant_id bigint NOT NULL DEFAULT 0,
  PRIMARY KEY (id),
  KEY travel_reimbursement_idx_no (`tenant_id`, `no`)
) COMMENT='出差报销';