create table prod_file_to_migrate (
       item_uuid VARCHAR2(100) not null,
       version number(10) not null,
       thread_id number(10),
       error_details VARCHAR2(1000),
       u_error_details VARCHAR2(1000),
       filepath      VARCHAR2(500) NOT NULL,
       enc_filepath  VARCHAR2(500) NULL,
       filesize NUMBER(38) default 0 NOT NULL,
       checksum VARCHAR2(1000),
       c_status number(1) default 0,
       status number(1) default 0, --0 for unmigrated, 1 for in progress, 2 for completed, -1 for error
       u_status number(1) default 0, --0 for unmigrated, 1 for in progress, 2 for completed, -1 for error
       src_collection_id VARCHAR2(100),
       collection_id VARCHAR2(100),
       school_reference number(1) default -1 -- -1 for dontcare, 0 for false, 1 for true
);

create unique index file_to_migrate_pk on prod_file_to_migrate (item_uuid, version,filepath);
create unique index prod_file_to_migrate_c_status on prod_file_to_migrate (c_status);
create unique index prod_file_to_migrate_status on prod_file_to_migrate (status);
create unique index prod_file_to_migrate_u_status on prod_file_to_migrate (u_status);
create unique index prod_file_to_migrate_thread_id on prod_file_to_migrate (thread_id);



create table prod_school_file_to_migrate (
       item_uuid VARCHAR2(100) not null,
       version number(10) not null,
       thread_id number(10),
       error_details VARCHAR2(1000),
       u_error_details VARCHAR2(1000),
       filepath      VARCHAR2(500) NOT NULL,
       enc_filepath  VARCHAR2(500) NULL,
       filesize NUMBER(38) default 0 NOT NULL,
       checksum VARCHAR2(1000),
       c_status number(1) default 0,
       status number(1) default 0, --0 for unmigrated, 1 for in progress, 2 for completed, -1 for error
       u_status number(1) default 0, --0 for unmigrated, 1 for in progress, 2 for completed, -1 for error
       src_collection_id VARCHAR2(100),
       collection_id VARCHAR2(100),
       school_reference number(1) default -1 -- -1 for dontcare, 0 for false, 1 for true
);


create unique index prod_school_file_to_migrate_px on prod_school_file_to_migrate (item_uuid, version,filepath);
create unique index prod_school_file_to_migrate_c_status on prod_school_file_to_migrate (c_status);
create unique index prod_school_file_to_migrate_status on prod_school_file_to_migrate (status);
create unique index prod_school_file_to_migrate_u_status on prod_school_file_to_migrate (u_status);
create unique index prod_school_file_to_migrate_thread_id on prod_school_file_to_migrate (thread_id);