
-- Create a group for the users created below:
CREATE ROLE yajhfc_user;

-- Users to create: Duplicate this line if you want several users
CREATE USER fax WITH PASSWORD 'fax' IN ROLE yajhfc_user;

-- Example: Create an addiional user named "user" with password "password":
-- CREATE USER user WITH PASSWORD 'password' IN ROLE yajhfc_user;
-- ...


-- Creates tables:
CREATE TABLE Phonebook (
  SerialNr    SERIAL PRIMARY KEY,
  FirstName   VARCHAR(50) NOT NULL,
  Name        VARCHAR(50) NOT NULL,
  Title       VARCHAR(40) NOT NULL,
  JobTitle    VARCHAR(40) NOT NULL,
  Company     VARCHAR(50) NOT NULL,
  Department  VARCHAR(40) NOT NULL,
  Street      VARCHAR(50) NOT NULL,
  Location    VARCHAR(50) NOT NULL,
  ZIPCode     VARCHAR(10) NOT NULL,
  Region      VARCHAR(30) NOT NULL,
  Country     VARCHAR(30) NOT NULL,
  VoiceNumber VARCHAR(50) NOT NULL,
  FaxNumber   VARCHAR(50) NOT NULL,
  EMail       VARCHAR(30) NOT NULL,
  Website     VARCHAR(40) NOT NULL,
  Comments    TEXT
);

CREATE TABLE ReadState (
  Faxname    VARCHAR(20) PRIMARY KEY,
  isRead     BOOLEAN,
  comment    VARCHAR(200),
  last_modified TIMESTAMP
);

-- Grant all users created above full privileges on the table
GRANT ALL PRIVILEGES ON Phonebook, ReadState, phonebook_serialnr_seq TO GROUP yajhfc_user;

