-- drop table states;
create table states (
 state_id               smallint not null primary key,
 state_name             varchar(64) not null,
 state_img_tag          varchar(255)
);
insert into states values (0,'Open',null);
insert into states values (1,'Waiting',null);
insert into states values (2,'Closed',null);
insert into states values (3,'Shelved',null);

-- drop table users;
create table users (
 user_id                bigint not null auto_increment primary key,
 user_name              varchar(32),
 user_password_md5      varchar(32),
 user_is_admin          smallint not null default 0,
 user_email             varchar(128),
 user_notify_newticket  smallint not null default 0,
 user_notify_assign     smallint not null default 0,
 user_notify_ownticket  smallint not null default 0,
 user_notify_unassigned smallint not null default 0,
 user_auth              bigint
);

-- drop table tickets;
create table tickets (
 ticket_id              bigint not null auto_increment primary key,
 ticket_auth            bigint not null,
 ticket_state_id        smallint not null references states.state_id,
 ticket_assigned_to     bigint references users.user_id,
 ticket_timestamp       timestamp,
 ticket_account_login   varchar(32),
 ticket_account_domain  varchar(128),
 ticket_author_name     varchar(128),
 ticket_author_email    varchar(128),
 ticket_author_phone    varchar(64),
 ticket_author_notify   smallint not null default 0,
 ticket_date_created    datetime not null,
 index(ticket_timestamp),
 index(ticket_state_id)
);

-- drop table notes;
create table notes (
 note_id                bigint not null auto_increment primary key,
 note_ticket_id         bigint not null references tickets.ticket_id,
 note_user_id           bigint references users.user_id,
 note_author_email      varchar(128),
 note_author_phone      varchar(32),
 note_is_public         smallint not null default 0,
 note_date_created      datetime not null,
 note_remote_addr       varchar(16),
 note_longtext          longtext,
 index(note_ticket_id),
 index(note_user_id)
);