IF OBJECT_ID('dbo.conversion_job_files', 'U') IS NOT NULL
BEGIN
    DROP TABLE dbo.conversion_job_files;
END
GO

IF OBJECT_ID('dbo.conversion_jobs', 'U') IS NOT NULL
BEGIN
    DROP TABLE dbo.conversion_jobs;
END
GO

CREATE TABLE dbo.conversion_jobs (
    id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    job_uuid UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID(),
    conversion_type VARCHAR(50) NOT NULL,
    status VARCHAR(20) NOT NULL,
    message NVARCHAR(MAX) NULL,
    primary_output_file_id INT NULL,
    created_at DATETIME2(0) NOT NULL DEFAULT SYSUTCDATETIME(),
    finished_at DATETIME2(0) NULL
);
GO

CREATE TABLE dbo.conversion_job_files (
    id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    job_id INT NOT NULL,
    file_role VARCHAR(20) NOT NULL,
    sort_order INT NOT NULL DEFAULT 0,
    original_name NVARCHAR(255) NOT NULL,
    stored_name NVARCHAR(255) NOT NULL,
    mime_type NVARCHAR(150) NOT NULL,
    file_ext NVARCHAR(20) NOT NULL,
    file_size BIGINT NOT NULL,
    file_data VARBINARY(MAX) NOT NULL,
    created_at DATETIME2(0) NOT NULL DEFAULT SYSUTCDATETIME(),
    CONSTRAINT FK_conversion_job_files_job
        FOREIGN KEY (job_id) REFERENCES dbo.conversion_jobs(id)
        ON DELETE CASCADE
);
GO

CREATE INDEX IX_conversion_jobs_created_at ON dbo.conversion_jobs(created_at DESC);
GO

CREATE INDEX IX_conversion_jobs_status ON dbo.conversion_jobs(status);
GO

CREATE INDEX IX_conversion_job_files_job_role ON dbo.conversion_job_files(job_id, file_role, sort_order);
GO