CREATE TABLE IF NOT EXISTS report_assets (
id CHAR(36) PRIMARY KEY,
tenant_id CHAR(36) NOT NULL,
report_id CHAR(36) NOT NULL,
report_version INT NOT NULL,
asset_kind VARCHAR(24) NOT NULL,
file_name VARCHAR(180) NOT NULL,
mime_type VARCHAR(100) NOT NULL,
byte_size INT UNSIGNED NOT NULL,
sha256 CHAR(64) NOT NULL,
storage_key VARCHAR(255) NOT NULL,
created_by CHAR(36) NULL,
created_at DATETIME NOT NULL,
deleted_at DATETIME NULL,
content_deleted_at DATETIME NULL,
UNIQUE KEY report_assets_tenant_id (tenant_id, id),
UNIQUE KEY report_assets_storage_key (storage_key),
INDEX report_assets_report (tenant_id, report_id, report_version),
CONSTRAINT report_assets_version_fk FOREIGN KEY (report_id, tenant_id, report_version) REFERENCES report_versions(report_id, tenant_id, version)
);
CREATE TABLE IF NOT EXISTS report_asset_uploads (
id CHAR(36) PRIMARY KEY,
asset_id CHAR(36) NOT NULL,
tenant_id CHAR(36) NOT NULL,
report_id CHAR(36) NOT NULL,
report_version INT NOT NULL,
asset_kind VARCHAR(24) NOT NULL,
file_name VARCHAR(180) NOT NULL,
mime_type VARCHAR(100) NOT NULL,
byte_size INT UNSIGNED NOT NULL,
sha256 CHAR(64) NOT NULL,
received_bytes INT UNSIGNED NOT NULL DEFAULT 0,
temp_storage_key VARCHAR(255) NOT NULL,
created_by CHAR(36) NULL,
created_at DATETIME NOT NULL,
expires_at DATETIME NOT NULL,
UNIQUE KEY report_asset_uploads_tenant_asset (tenant_id, asset_id),
UNIQUE KEY report_asset_uploads_temp_key (temp_storage_key),
CONSTRAINT report_asset_uploads_version_fk FOREIGN KEY (report_id, tenant_id, report_version) REFERENCES report_versions(report_id, tenant_id, version)
);
CREATE TABLE IF NOT EXISTS report_asset_audit_events (
id CHAR(36) PRIMARY KEY,
asset_id CHAR(36) NOT NULL,
tenant_id CHAR(36) NOT NULL,
report_id CHAR(36) NOT NULL,
event_type VARCHAR(32) NOT NULL,
sha256 CHAR(64) NOT NULL,
actor_user_id CHAR(36) NULL,
created_at DATETIME NOT NULL,
INDEX report_asset_audit_report (tenant_id, report_id, created_at),
CONSTRAINT report_asset_audit_asset_fk FOREIGN KEY (tenant_id, asset_id) REFERENCES report_assets(tenant_id, id),
CONSTRAINT report_asset_audit_report_fk FOREIGN KEY (tenant_id, report_id) REFERENCES reports(tenant_id, id)
);
