CREATE DATABASE DiskMonitoringSummary;
USE DiskMonitoringSummary;

CREATE TABLE CurrentDiskUsage (
  node CHAR(7),
  INDEX(node),
  role       ENUM( 'undefined', 'unknown', 'b1fits', 
                   'b1jpg', 'b2fits', 
                   'b2jpg', 'bttable', 'bundle', 'catalog', 
                   'fits', 'compfits', 'kernel', 'log', 'logupdate', 
                   'mask', 'mdc', 'mdl', 
                   'miscfits', 'png', 'psf', 'ptn', 'skycell', 'stats',
                   'trace', 'weight', 'other')
        NOT NULL DEFAULT 'undefined',
  INDEX(role),
  stage ENUM( 'undefined', 'unknown', 'raw', 'chip', 'cam', 
              'fake', 'detrend',
              'warp', 'stack', 'diff', 'magic', 'destreak', 
              'dist', 'pub', 'staticsky', 'chipbackground',
              'other' )
        NOT NULL DEFAULT 'undefined',
  INDEX(stage),
  dirname CHAR(3) NOT NULL,
  INDEX(dirname),
  sizes BIGINT(20) UNSIGNED DEFAULT 0,
  counts BIGINT(20) UNSIGNED DEFAULT 0,
  lifetime ENUM('undefined', 'permanent', 
                'ephemeral', 'raw', 
                'unknown') NOT NULL DEFAULT 'undefined',
  INDEX(lifetime),
  last_update TIMESTAMP DEFAULT 0,
  status ENUM('undefined', 'ok', 'deleted', 'error') NOT NULL default 'undefined',
  PRIMARY KEY(node, role, stage, dirname, status)
);

CREATE TABLE DiskUsageTemporary (
  node CHAR(7),
  INDEX(node),
  role       ENUM( 'undefined', 'unknown', 'b1fits', 
                   'b1jpg', 'b2fits', 
                   'b2jpg', 'bttable', 'bundle', 'catalog', 
                   'fits', 'compfits', 'kernel', 'log', 'logupdate', 
                   'mask', 'mdc', 'mdl', 
                   'miscfits', 'png', 'psf', 'ptn', 'skycell', 'stats',
                   'trace', 'weight', 'other')
        NOT NULL DEFAULT 'undefined',
  INDEX(role),
  stage ENUM( 'undefined', 'unknown', 'raw', 'chip', 'cam', 
              'fake', 'detrend',
              'warp', 'stack', 'diff', 'magic', 'destreak', 
              'dist', 'pub', 'staticsky', 'chipbackground',
              'other' )
        NOT NULL DEFAULT 'undefined',
  INDEX(stage),
  dirname CHAR(3) NOT NULL,
  INDEX(dirname),
  sizes BIGINT(20) UNSIGNED DEFAULT 0,
  counts BIGINT(20) UNSIGNED DEFAULT 0,
  lifetime ENUM('undefined', 'permanent', 
                'ephemeral', 'raw', 
                'unknown') NOT NULL DEFAULT 'undefined',
  INDEX(lifetime),
  last_update TIMESTAMP DEFAULT 0,
  status ENUM('undefined', 'ok', 'deleted', 'error') NOT NULL default 'undefined',
  PRIMARY KEY(node, role, stage, dirname, last_update, status)
);

CREATE TABLE DiskUsageHistoryDaily (
  node CHAR(7),
  INDEX(node),
  role       ENUM( 'undefined', 'unknown', 'deleted', 'b1fits', 
                   'b1jpg', 'b2fits', 
                   'b2jpg', 'bttable', 'bundle', 'catalog', 
                   'fits', 'compfits', 'kernel', 'log', 'logupdate', 
                   'mask', 'mdc', 'mdl', 
                   'miscfits', 'png', 'psf', 'ptn', 'skycell', 'stats',
                   'trace', 'weight', 'other')
        NOT NULL DEFAULT 'undefined',
  INDEX(role),
  stage ENUM( 'undefined', 'unknown', 'raw', 'chip', 'cam', 
              'fake', 'detrend',
              'warp', 'stack', 'diff', 'magic', 'destreak', 
              'dist', 'pub', 'staticsky', 'chipbackground',
              'other' )
        NOT NULL DEFAULT 'undefined',
  INDEX(stage),
  dirname CHAR(3) NOT NULL,
  INDEX(dirname),
  sizes BIGINT(20) UNSIGNED DEFAULT 0,
  counts BIGINT(20) UNSIGNED DEFAULT 0,
  lifetime ENUM('undefined', 'permanent', 
                'ephemeral', 'raw', 
                'unknown') NOT NULL DEFAULT 'undefined',
  INDEX(lifetime),
  day DATE DEFAULT '1001-01-01',
  status ENUM('undefined', 'ok', 'deleted', 'error') NOT NULL default 'undefined',
  PRIMARY KEY(node, role, stage, dirname, day, status)
);

CREATE TABLE DiskUsageHistoryWeekly (
  node CHAR(7),
  INDEX(node),
  role       ENUM( 'undefined', 'unknown', 'b1fits', 
                   'b1jpg', 'b2fits', 
                   'b2jpg', 'bttable', 'bundle', 'catalog', 
                   'fits', 'compfits', 'kernel', 'log', 'logupdate', 
                   'mask', 'mdc', 'mdl', 
                   'miscfits', 'png', 'psf', 'ptn', 'skycell', 'stats',
                   'trace', 'weight', 'other')
        NOT NULL DEFAULT 'undefined',
  INDEX(role),
  stage ENUM( 'undefined', 'unknown', 'raw', 'chip', 'cam', 
              'fake', 'detrend',
              'warp', 'stack', 'diff', 'magic', 'destreak', 
              'dist', 'pub', 'staticsky', 'chipbackground',
              'other' )
        NOT NULL DEFAULT 'undefined',
  INDEX(stage),
  dirname CHAR(3) NOT NULL,
  INDEX(dirname),
  sizes BIGINT(20) UNSIGNED DEFAULT 0,
  counts BIGINT(20) UNSIGNED DEFAULT 0,
  lifetime ENUM('undefined', 'permanent', 
                'ephemeral', 'raw', 
                'unknown') NOT NULL DEFAULT 'undefined',
  INDEX(lifetime),
  last_update TIMESTAMP DEFAULT 0,
  week CHAR(8) DEFAULT '1001-01',
  status ENUM('undefined', 'ok', 'deleted', 'error') NOT NULL default 'undefined',
  PRIMARY KEY(node, role, stage, dirname, week, status)
);

CREATE TABLE Misc (
  id INT PRIMARY KEY AUTO_INCREMENT,
  lastHistoryUpdatedDay INT DEFAULT 0,
  lastHistoryUpdatedWeek INT DEFAULT 0
);
