CREATE DATABASE xixi;

/**
 * accounts
 */
CREATE TABLE accounts (
  id bigserial UNIQUE NOT NULL,
  -- UNIQUE is needed
  -- http://www.postgresql.org/docs/9.5/static/datatype-numeric.html#DATATYPE-SERIAL
  -- 
  username varchar(100) UNIQUE NOT NULL,
  -- /^[A-Za-z][\w-]{1,31}$/
  email varchar(100) UNIQUE NOT NULL,
  phone varchar(32) UNIQUE,
  password varchar(100) NOT NULL,
  -- /^[\w-@*#]{6,32}$/
  created_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
  
  community_id serial,
  
  location_id serial NOT NULL,

  
  PRIMARY KEY (id),
  FOREIGN KEY (location_id) REFERENCES locations(id),
  FOREIGN KEY (community_id) REFERENCES communities(id),
);

/**
 * communities
 */
CREATE TABLE communities (
  id serial UNIQUE NOT NULL,
  
  location_id serial UNIQUE NOT NULL,
  
  
  PRIMARY KEY (id),
  FOREIGN KEY (location_id) REFERENCES locations(id)
);

/**
 * locations
 */
CREATE TABLE locations (
  id serial UNIQUE NOT NULL,
  --country,
  --province,
  --city,
  --latitude,
  --longitude,
  PRIMARY KEY (id)
);


/**
 * parents,
 */
CREATE TABLE parents (
  account_id bigserial UNIQUE,
  
  -- other parent-related infos
  
  FOREIGN KEY (account_id) REFERENCES accounts(id) ON DELETE CASCADE
  --CONSTRANT parent_account_id FOREIGN KEY (account_id) REFERENCES account(id) ON DELETE CASCADE
);

/**
 * experts
 */
CREATE TABLE experts (
  account_id bigserial UNIQUE,
  
  -- other experts-related infos
  
  FOREIGN KEY (account_id) REFERENCES accounts(id) ON DELETE CASCADE
);