DROP TABLE IF EXISTS sys_attachment;

CREATE TABLE sys_attachment (
    id BIGSERIAL PRIMARY KEY,
    source_type VARCHAR(100) NOT NULL,
    source_id BIGINT,
    original_name VARCHAR(255) NOT NULL,
    file_name VARCHAR(255) NOT NULL,
    file_suffix VARCHAR(50),
    content_type VARCHAR(100),
    file_size BIGINT NOT NULL DEFAULT 0,
    storage_type VARCHAR(50) NOT NULL,
    file_path VARCHAR(500) NOT NULL,
    file_url VARCHAR(500),
    version INTEGER NOT NULL DEFAULT 1,
    create_time TIMESTAMP,
    update_time TIMESTAMP,
    remark VARCHAR(500) DEFAULT '',
    create_by VARCHAR(64),
    update_by VARCHAR(64)
);

CREATE INDEX idx_sys_attachment_source_type ON sys_attachment (source_type);
CREATE INDEX idx_sys_attachment_source_id ON sys_attachment (source_id);
CREATE INDEX idx_sys_attachment_storage_type ON sys_attachment (storage_type);
CREATE INDEX idx_sys_attachment_create_time ON sys_attachment (create_time);

COMMENT ON TABLE sys_attachment IS '系统附件表';
COMMENT ON COLUMN sys_attachment.id IS 'ID';
COMMENT ON COLUMN sys_attachment.source_type IS '来源业务类型';
COMMENT ON COLUMN sys_attachment.source_id IS '来源业务ID';
COMMENT ON COLUMN sys_attachment.original_name IS '原始文件名';
COMMENT ON COLUMN sys_attachment.file_name IS '存储文件名';
COMMENT ON COLUMN sys_attachment.file_suffix IS '文件后缀';
COMMENT ON COLUMN sys_attachment.content_type IS '文件MIME类型';
COMMENT ON COLUMN sys_attachment.file_size IS '文件大小(字节)';
COMMENT ON COLUMN sys_attachment.storage_type IS '存储类型';
COMMENT ON COLUMN sys_attachment.file_path IS '文件存储路径';
COMMENT ON COLUMN sys_attachment.file_url IS '文件访问地址';
COMMENT ON COLUMN sys_attachment.version IS '版本';
COMMENT ON COLUMN sys_attachment.create_time IS '创建时间';
COMMENT ON COLUMN sys_attachment.update_time IS '更新时间';
COMMENT ON COLUMN sys_attachment.remark IS '备注';
COMMENT ON COLUMN sys_attachment.create_by IS '创建者';
COMMENT ON COLUMN sys_attachment.update_by IS '更新者';