USE bacula;

DROP TABLE IF EXISTS MAC;
DROP TABLE IF EXISTS Log;
DROP TABLE IF EXISTS Location;
DROP TABLE IF EXISTS LocationLog;

CREATE TABLE Log (
   LogId INTEGER UNSIGNED AUTO_INCREMENT,
   JobId INTEGER UNSIGNED DEFAULT 0 REFERENCES Job,
   Time DATETIME DEFAULT 0,
   LogText BLOB NOT NULL,
   PRIMARY KEY(LogId),
   INDEX (JobId)
   );

CREATE TABLE Location (
   LocationId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
   Location TINYBLOB NOT NULL,
   Cost INTEGER DEFAULT 0,
   Enabled TINYINT,
   PRIMARY KEY(LocationId)
   );

CREATE TABLE LocationLog (
   LocLogId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
   Date DATETIME DEFAULT 0,
   Comment BLOB,
   MediaId INTEGER UNSIGNED DEFAULT 0 REFERENCES Media,
   LocationId INTEGER UNSIGNED DEFAULT 0 REFERENCES Location,
   NewVolStatus ENUM('Full', 'Archive', 'Append', 'Recycle', 'Purged',
    'Read-Only', 'Disabled', 'Error', 'Busy', 'Used', 'Cleaning') NOT NULL,
   NewEnabled TINYINT,
   PRIMARY KEY(LocLogId)
);

ALTER TABLE Media ADD COLUMN MediaTypeId INTEGER UNSIGNED DEFAULT 0 REFERENCES MediaType;
ALTER TABLE Media ADD COLUMN DeviceId INTEGER UNSIGNED DEFAULT 0 REFERENCES Device;
ALTER TABLE Media ADD COLUMN LocationId INTEGER UNSIGNED DEFAULT 0 REFERENCES Location;
ALTER TABLE Media ADD COLUMN RecycleCount INTEGER UNSIGNED DEFAULT 0;
ALTER TABLE Media ADD COLUMN InitialWrite DATETIME DEFAULT 0;
ALTER TABLE Media ADD COLUMN ScratchPoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool;
ALTER TABLE Media ADD COLUMN RecyclePoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool;
ALTER TABLE Media ADD COLUMN Enabled TINYINT DEFAULT 1;
ALTER TABLE Media ADD COLUMN Comment BLOB;

ALTER TABLE JobMedia DROP COLUMN Stripe;

ALTER TABLE Job ADD COLUMN PriorJobId INTEGER UNSIGNED DEFAULT 0 REFERENCES Job;
ALTER TABLE Job ADD COLUMN RealEndTime DATETIME DEFAULT 0;



DELETE FROM Version;
INSERT INTO Version (VersionId) VALUES (10);
