-- =============================================
-- 文件管理系统 - MySQL 初始化脚本
-- 字符集: utf8mb4
-- =============================================

CREATE DATABASE IF NOT EXISTS seeyon_fs DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE seeyon_fs;

-- ========== 用户与权限 ==========

CREATE TABLE IF NOT EXISTS fs_user (
    id              BIGINT PRIMARY KEY,
    username        VARCHAR(64) UNIQUE NOT NULL,
    password        VARCHAR(256) NOT NULL,
    email           VARCHAR(128),
    nickname        VARCHAR(64),
    avatar          VARCHAR(512),
    role            VARCHAR(32) DEFAULT 'user',
    dept_id         BIGINT DEFAULT 0,
    status          TINYINT DEFAULT 1,
    storage_quota   BIGINT DEFAULT 10737418240,
    used_storage    BIGINT DEFAULT 0,
    must_change_pwd TINYINT DEFAULT 0,
    created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at      DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS fs_role (
    id           BIGINT PRIMARY KEY,
    name         VARCHAR(64) UNIQUE NOT NULL,
    display_name VARCHAR(64) NOT NULL,
    description  VARCHAR(256),
    is_system    TINYINT DEFAULT 0,
    status       TINYINT DEFAULT 1,
    created_at   DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at   DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS fs_permission (
    id          BIGINT PRIMARY KEY,
    code        VARCHAR(64) UNIQUE NOT NULL,
    name        VARCHAR(64) NOT NULL,
    resource    VARCHAR(32) NOT NULL,
    action      VARCHAR(32) NOT NULL,
    description VARCHAR(256)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS fs_role_permission (
    role_id       BIGINT NOT NULL,
    permission_id BIGINT NOT NULL,
    PRIMARY KEY (role_id, permission_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS fs_user_role (
    user_id BIGINT NOT NULL,
    role_id BIGINT NOT NULL,
    PRIMARY KEY (user_id, role_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========== 组织架构 ==========

CREATE TABLE IF NOT EXISTS fs_department (
    id         BIGINT PRIMARY KEY,
    name       VARCHAR(128) NOT NULL,
    code       VARCHAR(64) UNIQUE,
    parent_id  BIGINT DEFAULT 0,
    path       VARCHAR(1024) NOT NULL,
    leader_id  BIGINT DEFAULT 0,
    sort_order INT DEFAULT 0,
    status     TINYINT DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========== SSO 单点登录 ==========

CREATE TABLE IF NOT EXISTS fs_sso_provider (
    id           BIGINT PRIMARY KEY,
    name         VARCHAR(64) NOT NULL,
    type         VARCHAR(32) NOT NULL,
    display_name VARCHAR(64) NOT NULL,
    icon         VARCHAR(512),
    config       TEXT,
    auto_create  TINYINT DEFAULT 1,
    default_role VARCHAR(32) DEFAULT 'user',
    status       TINYINT DEFAULT 1,
    sort_order   INT DEFAULT 0,
    created_at   DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at   DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS fs_sso_session (
    id            BIGINT PRIMARY KEY,
    user_id       BIGINT NOT NULL,
    provider_id   BIGINT NOT NULL,
    external_id   VARCHAR(256) NOT NULL,
    access_token  VARCHAR(1024),
    refresh_token VARCHAR(1024),
    expires_at    DATETIME,
    last_login_at DATETIME,
    created_at    DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user (user_id),
    INDEX idx_provider_external (provider_id, external_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========== 文件管理 ==========

CREATE TABLE IF NOT EXISTS fs_folder (
    id         BIGINT PRIMARY KEY,
    biz_id     VARCHAR(128) DEFAULT '',
    name       VARCHAR(256) NOT NULL,
    parent_id  BIGINT DEFAULT 0,
    owner_id   BIGINT NOT NULL,
    path       VARCHAR(1024) NOT NULL,
    status     TINYINT DEFAULT 1,
    version    INT DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_parent (parent_id),
    INDEX idx_owner (owner_id),
    INDEX idx_biz (biz_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS fs_file (
    id               BIGINT PRIMARY KEY,
    biz_id           VARCHAR(128) DEFAULT '',
    name             VARCHAR(256) NOT NULL,
    extension        VARCHAR(32),
    folder_id        BIGINT NOT NULL,
    owner_id         BIGINT NOT NULL,
    size             BIGINT DEFAULT 0,
    md5              VARCHAR(32),
    sha256           VARCHAR(64),
    storage_key      VARCHAR(512) NOT NULL,
    storage_policy_id BIGINT DEFAULT 1,
    mime_type        VARCHAR(128),
    is_favorite      TINYINT DEFAULT 0,
    status           TINYINT DEFAULT 1,
    download_count   INT DEFAULT 0,
    version          INT DEFAULT 1,
    created_at       DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at       DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_biz (biz_id),
    INDEX idx_folder (folder_id),
    INDEX idx_owner (owner_id),
    INDEX idx_md5 (md5),
    INDEX idx_policy (storage_policy_id),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS fs_file_permission (
    id         BIGINT PRIMARY KEY,
    file_id    BIGINT NOT NULL,
    user_id    BIGINT DEFAULT 0,
    role_id    BIGINT DEFAULT 0,
    perm_level VARCHAR(16) DEFAULT 'read',
    grant_by   BIGINT NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_fp_user_file (user_id, file_id),
    INDEX idx_fp_role_file (role_id, file_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS fs_file_association (
    id         BIGINT PRIMARY KEY,
    biz_id     VARCHAR(128) NOT NULL,
    biz_type   VARCHAR(64) NOT NULL,
    file_id    BIGINT NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_biz (biz_id, biz_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS fs_share (
    id             BIGINT PRIMARY KEY,
    file_id        BIGINT NOT NULL,
    owner_id       BIGINT NOT NULL,
    share_code     VARCHAR(32) UNIQUE NOT NULL,
    password       VARCHAR(64),
    expire_at      DATETIME,
    max_download   INT DEFAULT 0,
    download_count INT DEFAULT 0,
    allow_preview  TINYINT DEFAULT 1,
    allow_download TINYINT DEFAULT 1,
    status         TINYINT DEFAULT 1,
    created_at     DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========== 存储策略 ==========

CREATE TABLE IF NOT EXISTS fs_storage_policy (
    id          BIGINT PRIMARY KEY,
    name        VARCHAR(64) NOT NULL,
    type        VARCHAR(32) NOT NULL,
    policy_type VARCHAR(32) DEFAULT 'hot',
    config      TEXT,
    status      TINYINT DEFAULT 1,
    created_at  DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS fs_storage_assignment (
    id            BIGINT PRIMARY KEY,
    policy_id     BIGINT NOT NULL,
    user_id       BIGINT DEFAULT 0,
    group_id      BIGINT DEFAULT 0,
    is_default    TINYINT DEFAULT 0,
    priority      INT DEFAULT 0,
    storage_quota BIGINT DEFAULT 0,
    access_mode   VARCHAR(32),
    status        TINYINT DEFAULT 1,
    created_at    DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at    DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_policy (policy_id),
    INDEX idx_user (user_id),
    INDEX idx_group (group_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========== 开放平台 ==========

CREATE TABLE IF NOT EXISTS fs_app (
    id         BIGINT PRIMARY KEY,
    app_id     VARCHAR(64) UNIQUE NOT NULL,
    app_secret VARCHAR(128) NOT NULL,
    app_name   VARCHAR(128) NOT NULL,
    status     TINYINT DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS fs_app_user (
    id         BIGINT PRIMARY KEY,
    app_id     BIGINT NOT NULL,
    user_id    BIGINT NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_app (app_id),
    INDEX idx_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========== 分片上传 ==========

CREATE TABLE IF NOT EXISTS fs_upload_session (
    id             VARCHAR(64) PRIMARY KEY,
    file_id        BIGINT DEFAULT 0,
    file_name      VARCHAR(256) NOT NULL,
    file_size      BIGINT NOT NULL,
    chunk_size     BIGINT NOT NULL,
    total_chunks   INT NOT NULL,
    uploaded_chunks TEXT,
    md5            VARCHAR(32),
    sha256         VARCHAR(64),
    folder_id      BIGINT NOT NULL,
    owner_id       BIGINT NOT NULL,
    policy_id      BIGINT DEFAULT 1,
    status         TINYINT DEFAULT 1,
    temp_dir       VARCHAR(512),
    created_at     DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at     DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========== 用户会话(多端登录) ==========

CREATE TABLE IF NOT EXISTS fs_user_session (
    id         BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id    BIGINT NOT NULL,
    token      VARCHAR(512) NOT NULL,
    device     VARCHAR(128),
    ip         VARCHAR(64),
    expires_at DATETIME NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user (user_id),
    INDEX idx_expires (expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========== 操作日志 ==========

CREATE TABLE IF NOT EXISTS fs_operation_log (
    id         BIGINT PRIMARY KEY,
    user_id    BIGINT DEFAULT 0,
    username   VARCHAR(64),
    action     VARCHAR(32) NOT NULL,
    resource   VARCHAR(32),
    detail     VARCHAR(512),
    ip         VARCHAR(64),
    user_agent VARCHAR(256),
    status     INT DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user (user_id),
    INDEX idx_action (action),
    INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========== 备份 ==========

CREATE TABLE IF NOT EXISTS fs_backup_policy (
    id            BIGINT PRIMARY KEY,
    name          VARCHAR(128) NOT NULL,
    backup_type   VARCHAR(32) NOT NULL,
    target_type   VARCHAR(32) NOT NULL,
    target_config TEXT,
    include_db    TINYINT DEFAULT 1,
    include_files TINYINT DEFAULT 1,
    cron_expr     VARCHAR(64),
    max_backups   INT DEFAULT 7,
    status        TINYINT DEFAULT 1,
    last_run      DATETIME,
    created_at    DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS fs_backup_log (
    id          BIGINT PRIMARY KEY,
    policy_id   BIGINT NOT NULL,
    policy_name VARCHAR(128),
    backup_type VARCHAR(32),
    status      VARCHAR(32) DEFAULT 'running',
    file_path   VARCHAR(512),
    file_size   BIGINT DEFAULT 0,
    db_size     BIGINT DEFAULT 0,
    files_count INT DEFAULT 0,
    error_msg   VARCHAR(512),
    started_at  DATETIME,
    finished_at DATETIME,
    duration    INT DEFAULT 0,
    created_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_policy (policy_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;