CREATE DATABASE GSP;

USE GSP;

CREATE TABLE location (
  location_id INT NOT NULL PRIMARY KEY,
  address VARCHAR(45) NULL,
  city VARCHAR(45) NULL,
  state CHAR(2) NULL,
  zip INT(5) NULL,
  lat VARCHAR(45) NULL,
  longi VARCHAR(45) NULL)
ENGINE = InnoDB;

CREATE TABLE social_worker(
  sw_id INT NOT NULL PRIMARY KEY,
  first_name VARCHAR(45) NULL,
  last_name VARCHAR(45) NULL,
  organization_id VARCHAR(45) NULL,
  location_id INT NULL,
   INDEX (location_id),
    FOREIGN KEY (location_id)
    REFERENCES location(location_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;

CREATE TABLE donor(
  donor_id INT NOT NULL,
  first_name VARCHAR(45) NULL,
  last_name VARCHAR(45) NULL,
  radius VARCHAR(45) NULL,
  location_id INT NULL,
  phone VARCHAR(45) NULL,
  email VARCHAR(45) NULL,
  PRIMARY KEY (donor_id),
  INDEX (location_id),
    FOREIGN KEY (location_id)
    REFERENCES location(location_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;

CREATE  TABLE sw_donor (
  `sw_donor_id` INT NOT NULL AUTO_INCREMENT ,
  `sw_id` INT NOT NULL ,
  `donor_id` INT NOT NULL ,
  PRIMARY KEY (sw_donor_id, sw_id, donor_id) ,
  INDEX (sw_id) ,
    FOREIGN KEY (sw_id)
    REFERENCES social_worker(sw_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
 INDEX (donor_id) ,
    FOREIGN KEY (donor_id)
    REFERENCES donor(donor_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;

CREATE  TABLE client (
  `client_id` INT NOT NULL ,
  `first_name` VARCHAR(45) NULL ,
  `last_name` VARCHAR(45) NULL ,
  `organization_id` VARCHAR(45) NULL ,
  `age` VARCHAR(45) NULL ,
  `sw_id` INT NOT NULL ,
  `num_family_members` VARCHAR(45) NULL ,
  `things_desired_id` VARCHAR(45) NULL ,
  `location_id` INT NULL ,
  PRIMARY KEY (client_id, sw_id) ,
  INDEX (sw_id) ,
    FOREIGN KEY (sw_id)
    REFERENCES social_worker(sw_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
 INDEX (location_id) ,
    FOREIGN KEY (location_id)
    REFERENCES location(location_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;

CREATE  TABLE donation (
  `donation_id` INT NOT NULL ,
  `desctiption` VARCHAR(45) NULL ,
  `category_id` INT NULL ,
  PRIMARY KEY (donation_id) )
ENGINE = InnoDB;

CREATE  TABLE donation_link (
  `donation_donor` INT NOT NULL ,
  `donation_id` INT NOT NULL ,
  `donor_id` INT NOT NULL ,
  PRIMARY KEY (donation_donor, donor_id, donation_id) ,
  INDEX (donation_id) ,
    FOREIGN KEY (donation_id)
    REFERENCES donation(donation_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  INDEX(donor_id) ,
    FOREIGN KEY (donor_id)
    REFERENCES donor(donor_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;

CREATE  TABLE desired (
  `desired_id` INT NOT NULL ,
  `donation_id` INT NOT NULL ,
  `client_id` INT NOT NULL ,
  PRIMARY KEY (desired_id, donation_id, client_id) ,
  INDEX (donation_id) ,
    FOREIGN KEY (donation_id)
    REFERENCES donation(donation_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
 INDEX(client_id) ,
    FOREIGN KEY (client_id)
    REFERENCES client(client_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;

CREATE  TABLE organization (
  `organization_id` INT NOT NULL ,
  `client_id` INT NULL ,
  `sw_id` INT NULL ,
  `name` VARCHAR(45) NULL ,
  `location_id` INT NULL ,
  PRIMARY KEY (organization_id) ,
  INDEX (client_id) ,
    FOREIGN KEY (client_id)
    REFERENCES client(client_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
 INDEX (sw_id) ,
    FOREIGN KEY (sw_id)
    REFERENCES social_worker(sw_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  INDEX(location_id) ,
    FOREIGN KEY (location_id)
    REFERENCES location(location_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;
