# 设备台账
create table `product-inventory-management`.device_repair
(
    id                 bigint auto_increment
        primary key,
    device_ledger_id   varchar(255) not null comment '设备台账id',
    repair_time        timestamp    null comment '报修日期',
    repair_name        varchar(255) not null comment '报修人',
    remark             varchar(255) not null comment '故障现象',
    maintenance_name   varchar      not null comment '维修人',
    maintenance_time   timestamp    null comment '维修时间',
    maintenance_result varchar(255) null comment '维修结果',
    status             int default 0 comment '状态',
    create_time        datetime     null comment '录入时间',
    update_time        datetime     null comment '更新时间',
    create_user        varchar(255) not null comment '录入人',
    update_user        varchar(255) not null comment '更新人',
    tenant_id          bigint       not null comment '租户id'
);

# 设备维修
create table `product-inventory-management`.device_ledger
(
    id                           bigint auto_increment
        primary key,
    device_name                  varchar(255) not null comment '设备名称',
    device_model                 varchar(255) not null comment '规格型号',
    supplier_name                varchar(255) not null comment '供应商',
    unit                         varchar(255) not null comment '单位',
    number                       decimal      not null comment '数量',
    tax_including_price_unit     decimal      not null comment '含税单价',
    tax_including_price_total    decimal      not null comment '含税总价',
    tax_rate                     decimal      not null comment '税率',
    un_tax_including_price_total decimal      not null comment '不含税总价',
    create_time                  timestamp    null comment '录入时间',
    update_time                  timestamp    null comment '更新时间',
    create_user                  varchar(255) not null comment '录入人',
    update_user                  varchar(255) not null comment '更新人',
    tenant_id                    bigint       not null comment '租户id'
);

create table `product-inventory-management`.device_maintenance
(
    id                 bigint auto_increment
        primary key,
    device_ledger_id   varchar(255) not null comment '设备台账id',
    maintenance__plan_time        timestamp    null comment '计划保养日期',
    maintenance_actually_name   varchar(255)      not null comment '实际保养人',
    maintenance_actually_time   timestamp    null comment '实际保养日期',
    maintenance_result int not null  default 0 comment '保养结果 0 维修 1 完好',
    status             int not null  default 0 comment '状态 0 待保养 1 完结',
    create_time        datetime     null comment '录入时间',
    update_time        datetime     null comment '更新时间',
    create_user        varchar(255) not null comment '录入人',
    update_user        varchar(255) not null comment '更新人',
    tenant_id          bigint       not null comment '租户id'
);


alter table purchase_ledger
    add payment_method varchar(255) null;
alter table sales_ledger
    add payment_method varchar(255) null;


alter table payment_registration
    modify sale_ledger_id bigint default 0 null comment '销售台账id';


alter table ticket_registration
    add enter_date timestamp null;

alter table product_record
    add 列_name int null comment 'sale_ledger_projectI_id';

# 添加设备名称和规格型号
ALTER TABLE `product-inventory-management`.`device_repair`
    ADD COLUMN `device_name` varchar(255) NULL AFTER `tenant_id`,
    ADD COLUMN `device_model` varchar(255) NULL AFTER `device_name`;

ALTER TABLE `product-inventory-management`.`device_maintenance`
    ADD COLUMN `device_name` varchar(255) NULL AFTER `tenant_id`,
    ADD COLUMN `device_model` varchar(255) NULL AFTER `device_name`;



DROP TABLE IF EXISTS storage_attachment;
CREATE TABLE storage_attachment (
                                    id BIGINT AUTO_INCREMENT PRIMARY KEY,
                                    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
                                    update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
                                    deleted BIGINT DEFAULT 0 NOT NULL,
                                    record_type SMALLINT DEFAULT 0 NOT NULL,
                                    record_id BIGINT DEFAULT 0 NOT NULL,
                                    name VARCHAR(100) DEFAULT '' NOT NULL,
                                    storage_blob_id BIGINT DEFAULT 0 NOT NULL
) COMMENT='通用文件上传的附件信息';

ALTER TABLE storage_attachment
    COMMENT '通用文件上传的附件信息';

ALTER TABLE storage_attachment MODIFY record_type SMALLINT COMMENT '关联的记录类型';
ALTER TABLE storage_attachment MODIFY record_id BIGINT COMMENT '关联的记录id';
ALTER TABLE storage_attachment MODIFY name VARCHAR(100) COMMENT '名称, 如: file, avatar (区分同一条记录不同类型的附件)';
ALTER TABLE storage_attachment MODIFY storage_blob_id BIGINT COMMENT '关联storage_blob记录id';

CREATE INDEX idx_storage_attachment_on_record
    ON storage_attachment (record_type, record_id);


CREATE TABLE storage_blob (
                              id BIGINT AUTO_INCREMENT PRIMARY KEY,
                              create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
                              key_name VARCHAR(150) DEFAULT '' NOT NULL,
                              content_type VARCHAR(100) DEFAULT '' NOT NULL,
                              original_filename VARCHAR(255) DEFAULT '' NOT NULL,
                              bucket_filename VARCHAR(255) DEFAULT '' NOT NULL,
                              bucket_name VARCHAR(255) DEFAULT '' NOT NULL,
                              byte_size BIGINT DEFAULT 0 NOT NULL,
                              UNIQUE (key_name)
) COMMENT='通用文件上传的附件信息';

ALTER TABLE storage_blob
    COMMENT '通用文件上传的附件信息';

ALTER TABLE storage_blob MODIFY key_name VARCHAR(150) COMMENT '资源id';
ALTER TABLE storage_blob MODIFY content_type VARCHAR(100) COMMENT '资源类型，例如JPG图片的资源类型为image/jpg';
ALTER TABLE storage_blob MODIFY original_filename VARCHAR(255) COMMENT '原文件名称';
ALTER TABLE storage_blob MODIFY bucket_filename VARCHAR(255) COMMENT '存储桶中文件名';
ALTER TABLE storage_blob MODIFY bucket_name VARCHAR(255) COMMENT '存储桶名';
ALTER TABLE storage_blob MODIFY byte_size BIGINT COMMENT '资源尺寸(字节)';