CREATE TABLE People (
  pID SERIAL,
  pName VARCHAR(50),
  pAddress VARCHAR(50),
  pPhoneNumber INTEGER,
  pDOB DATE NOT NULL,
  pDOD DATE DEFAULT NULL,
  PRIMARY KEY (pID)
);

CREATE TABLE Linking (
  lID SERIAl,
  lName VARCHAR (50),
  lType VARCHAR (20),
  lDescription TEXT,
  PRIMARY KEY (lID)
);

CREATE TABLE hasLink (
  HLlID INTEGER,
  HLpID INTEGER,
  PRIMARY KEY (HLlID, HLpID),
  FOREIGN KEY (HLlID) REFERENCES Linking (lID),
  FOREIGN KEY (HLpID) REFERENCES People(pID)
);

CREATE TABLE Member (
  mStartingDate DATE,
  mID INTEGER,
  PRIMARY KEY (mID),
  FOREIGN KEY (mID) REFERENCES People (pID)
);

CREATE TABLE Enemy (
  eID INTEGER,
  PRIMARY KEY (eID),
  FOREIGN KEY (eID) REFERENCES People (pID)
);
/*
CREATE TABLE Opponent(
  NOT NULL
)
*/
CREATE TABLE Appointment(
  aStartDate DATE,
  aEndDate DATE DEFAULT NULL

);

CREATE TABLE Role(
  rID INTEGER,
  rSalary INTEGER,
  rTitle VARCHAR(50),
  PRIMARY KEY (rID)
);

CREATE TABLE In2 (
  --Member in specific Role
  IStartDate DATE,
  IEndDate DATE,
  ImID INTEGER,
  IrID INTEGER,
  PRIMARY KEY (ImID, IrID),
  FOREIGN KEY (ImID) REFERENCES Member (mID),
  FOREIGN KEY (IrID) REFERENCES Role (rID)
);

CREATE TABLE Asset (
  aName VARCHAR(50),
  aUsage VARCHAR(50),
  aDescription TEXT,
  PRIMARY KEY (aName)
);

CREATE TABLE hasAsset (
  HAName VARCHAR(50),
  HAmID INTEGER,
  PRIMARY KEY (HAmID, HAName),
  FOREIGN KEY (HAName) REFERENCES Asset (aName),
  FOREIGN KEY (HAmID) REFERENCES Member (mID)
);

CREATE TABLE Sponsor(
  sID SERIAL,
  sName VARCHAR (50),
  sIndustry VARCHAR (50),
  sAddress VARCHAR (50),
  PRIMARY KEY (sID)
);

CREATE TABLE Grants (
  gAmount FLOAT,
  gDate DATE,
  gPayback VARCHAR (50),
  gsID INTEGER,
  gmID INTEGER,
  FOREIGN KEY (gsID) REFERENCES Sponsor (sID),
  FOREIGN KEY (gmID) REFERENCES Member (mID),
  PRIMARY KEY (gsID, gmID)
);

CREATE TABLE OtherParties (
  opID SERIAL,
  opName VARCHAR(50),
  opCountry VARCHAR(20),
  PRIMARY KEY (opID)
);

CREATE TABLE Monitors (
  momID INTEGER,
  moopID INTEGER,
  PRIMARY KEY (momID, moopID),
  FOREIGN KEY (momID) REFERENCES Member(mID),
  FOREIGN KEY (moopID) REFERENCES OtherParties(opID)
);

CREATE TABLE Review (
  rDate DATE REFERENCES Grants (gDate),
  rGrade INTEGER CHECK (rGrade > 0 AND rGrade <= 10),
  --rID
  PRIMARY KEY (mID)
);