CREATE TABLE NodeStats (
   IPAddress  VARCHAR(20),
   BlocksUploaded  INT,
   BlocksDownloaded INT,
   RequestsReceived INT,
   RequestsSent INT,
   RequestsServed INT,
   RequestsFailed INT,
   DistrosUploaded  INT,
   DistrosDownloaded INT,
   PRIMARY KEY (IPAddress),
 );


CREATE TABLE Nodes  VOLATILE(
   IPAddress  VARCHAR(20),
   firstSeen    DATETIME,
   lastSeen    DATETIME,
   PRIMARY KEY (IPAddress),
   FOREIGN KEY (IPAddress) REFERENCES NodeStats
);


CREATE TABLE Distros (
    DistroID  INT,    -- may change to VARCHAR if desired
    DistroName  VARCHAR(64),
    NFiles  INT,
    Size   INT,
    PRIMARY KEY(DistroID)
)


CREATE TABLE Files (
   FileId      INT,
   DistroId  INT,
   FileName  VARCHAR(128),
   Size  INT,
   Blocks INT,
   PRIMARY KEY(FileId),
   FOREIGN KEY(DistroId) REFERENCES (Distros)
);










CREATE TABLE Availability VOLATILE (
    NodeId  VARCHAR(20),
    DistroId  INT,
    FileId  INT,
    BlockID  INT,
    TimeStamp DATETIME,
    PRIMARY KEY(FileId, BlockId, NodeId),
    FOREIGN KEY(NodeId) REFERENCES Nodes,
    FOREIGN KEY(NodeId) REFERNCES NodeStats,
   FOREIGN KEY (DistroId) REFERENCES Distros,
   FOREIGN KEY (FileId) REFERENCES Files
);


CREATE TABLE LocalAvailability  (
   DistroID  INT,
  FileId INT,
  BlockId INT,
  TimeStamp DATETIME,
   PRIMARY KEY(FileId, BlockId),
   FOREIGN KEY (DistroId) REFERENCES Distros,
   FOREIGN KEY (FileId) REFERENCES Files
);




CREATE TABLE Downloads(
   DistroID INT,
   StartTime DATETIME,
   Status VARCHAR(10),    -- ENUM, but we do not have ENUMs
   EndTime DATETIME,
   Success INT
);
























CREATE TABLE OutgoingRequests VOLATILE (
  NodeID VARCHAR(20),
  DistroID INT,
  FileID INT,
  BlockID INT,
  TimeStamp DATETIME,
  Status VARCHAR(10),
  ArchivalTime DATETIME,
  PRIMARY KEY(FileID, BlockId, NodeID, TimeStamp),
  FOREIGN KEY(NodeID) REFERENCES Nodes,
  FOREIGN KEY(NodeID) REFERENCES NodeStats,
  FOREIGN KEY(DistroID) REFERENCES Distros,
  FOREIGN KEY(FileID) REFERENCES Files
);






CREATE TABLE IncomingRequests VOLATILE (
  NodeID VARCHAR(20),
  DistroID INT,
  FileID INT,
  BlockID INT,
  TimeStamp DATETIME,
  Status VARCHAR(10),
  ArchivalTime DATETIME,
  PRIMARY KEY(FileID, BlockId, NodeID, TimeStamp),
  FOREIGN KEY(NodeID) REFERENCES Nodes,
  FOREIGN KEY(NodeID) REFERENCES NodeStats,
  FOREIGN KEY(DistroID) REFERENCES Distros,
  FOREIGN KEY(FileID) REFERENCES Files
);