-- Reservation Database Setup

-- create database users CHARACTER SET = utf8 COLLATE = utf8_bin;
-- create user 'stay_user'@'localhost' IDENTIFIED BY 'password';
use users;

create table adminuser  (
  ausername varchar(16) primary key,
  aemail varchar(100) not null,
  atype ENUM('administrator', 'finance', 'registered') NOT NULL DEFAULT 'registered',
  astatus ENUM('staying', 'active', 'inactive', 'inapplicable') NOT NULL DEFAULT 'active',
  afullname varchar(100),
  aphone varchar(100),
  amobile varchar(100),
  aaddress1 varchar(100),
  aaddress2 varchar(100),
  asuburb varchar(100),
  acity varchar(100),
  aastate varchar(100),
  acountry varchar(100),
  apostcode varchar(20)
);

create table user  (
  username varchar(16) primary key,
  passwd char(40) not null,
  email varchar(100) not null,
  utype ENUM('administrator', 'finance', 'registered') NOT NULL DEFAULT 'registered',
  ustatus ENUM('staying', 'active', 'inactive', 'inapplicable') NOT NULL DEFAULT 'active',
  ucdate timestamp not null default now(),
  umdate timestamp,
  fullname varchar(100),
  phone varchar(100),
  mobile varchar(100),
  address1 varchar(100),
  address2 varchar(100),
  suburb varchar(100),
  city varchar(100),
  astate varchar(100),
  country varchar(100),
  postcode varchar(20),
  ucomment varchar(100)
);

create table room (
  id MEDIUMINT NOT NULL AUTO_INCREMENT,
  roomnumber MEDIUMINT NOT NULL DEFAULT 0,
  rname varchar(100) NOT NULL,
  sstatus ENUM('renovating', 'active', 'inactive') NOT NULL DEFAULT 'active',
  rusername varchar(16),
  rate double NOT NULL DEFAULT 0.0,
  description varchar(100) NOT NULL,
  reduction double NOT NULL DEFAULT 0.0,
  rcomment varchar(100),
  PRIMARY KEY (id),
  index (roomnumber)
);

create table payment (
  xid MEDIUMINT NOT NULL AUTO_INCREMENT,
  rid MEDIUMINT NOT NULL,
  pusername varchar(16) NOT NULL,
  pname varchar(100) NOT NULL,
  rdate timestamp not null default now(),
  udate timestamp,
  cstatus ENUM('validated', 'received', 'accepted', 'requested', 'rejected') NOT NULL DEFAULT 'requested',
  payment double NOT NULL DEFAULT 0.0,
  receiptno MEDIUMINT NOT NULL DEFAULT 0,
  PRIMARY KEY (xid),
  index (rid),
  index (pusername),
  index (pname),
  index (rdate)
);

create table stay (
  id MEDIUMINT NOT NULL AUTO_INCREMENT,
  susername varchar(16) not null,
  rid MEDIUMINT not null,
  stay_URL varchar(255) not null,
  sstatus ENUM('finalized', 'downpayment', 'confirmed', 'booked', 'quote') NOT NULL DEFAULT 'quote',
  sdate timestamp not null default now(),
  edate timestamp,
  days double,
  stay_new_URL varchar(255),
  spasswd char(40),
  scomment varchar(100),
  PRIMARY KEY (id),
  index (susername),
  index (rid),
  index (stay_URL)
);

grant select, insert, update, delete
on users.*
to stay_user@localhost identified by 'password';