drop table if exists user_charts;
drop table if exists charts;
drop table if exists chart_data;
drop table if exists attachments;
drop table if exists news;
drop table if exists soft_target_types;
drop table if exists attackers;
drop table if exists related_attacks;
drop table if exists attacks;
drop table if exists users;
drop table if exists roles;
drop table if exists map_settings;

create table roles(
  id bigserial primary key,
  name varchar(100) not null unique,
  label varchar(100) not null
);
alter sequence roles_id_seq restart;
insert into roles(id, name, label) values
(DEFAULT, 'ROLE_USER', 'Registrovaný uživatel'),
(DEFAULT, 'ROLE_ANALYST', 'Analytik'),
(DEFAULT, 'ROLE_ADMIN', 'Správce');

create table users(
  id bigserial primary key,
  email varchar(300) not null unique,
  password varchar(60) not null,
  active boolean default false not null,
  first_name varchar(100) not null,
  last_name varchar(100) not null,
  company varchar(100),
  position varchar(100),
  role bigint default 1 references roles(id)
);
alter sequence users_id_seq restart;
insert into users(email, password, active, first_name, last_name, company, position, role) values
('d.lapkova@stpi.cz', '$2a$10$eRRQggmwT5Xy./V/zy4bouM2iLLfHRgCMuoj4ygs25dVIL6wy2ake', true, 'Dora', 'Lapková', 'STPI', 'Ředitelka institutu', 3);

create table attacks(
  id bigserial primary key,
  country varchar(50) not null,
  city varchar(50) not null,
  date date not null,
  time time,
  peak time,
  latitude real,
  longitude real,
  soft_target_description varchar(200),
  terror varchar(20),
  epicenter varchar(20),
  modus_operandi varchar(20),
  modus_operandi_detail varchar(200),
  entrance_security varchar(20),
  terrorist_organization varchar(200),
  attackers_count integer,
  deaths integer,
  injured integer,
  potential_victims integer,
  note varchar(1000),
  link varchar(1000)
);
alter sequence attacks_id_seq restart;

create table related_attacks(
  id bigserial primary key,
  attack_id bigint references attacks(id),
  related_attack_id bigint references attacks(id) on delete cascade,
  note varchar(200),
  unique(attack_id, related_attack_id)
);
alter sequence related_attacks_id_seq restart;

create table soft_target_types(
  id bigserial primary key,
  attack_id bigint references attacks(id),
  soft_target_type varchar(30) not null,
  soft_target_type_detail varchar(250),
  unique (attack_id, soft_target_type)
);
alter sequence soft_target_types_id_seq restart;

create table attackers(
  id bigserial primary key,
  attack_id bigint references attacks(id),
  name varchar(100) not null,
  gender varchar(10),
  age smallint,
  residence varchar(60),
  attack_locality varchar(16),
  origin varchar(500)
);
alter sequence attackers_id_seq restart;

create table news(
  id bigserial primary key,
  title varchar(500) not null,
  text varchar(10000) not null,
  author_id bigint references users(id),
  created timestamp default current_timestamp not null
);
alter sequence news_id_seq restart;

create table attachments(
  id bigserial primary key,
  new_id bigint references news(id),
  file_name varchar(500) not null,
  file_type varchar(10000) not null,
  data bytea not null,
  created timestamp default current_timestamp not null
);
alter sequence attachments_id_seq restart;

create table map_settings(
  id bigserial primary key,
  center_latitude real not null,
  center_longitude real not null,
  zoom real not null
);
alter sequence map_settings_id_seq restart;

create table chart_data(
  id bigserial primary key,
  x varchar(30),
  y varchar(30),
  z varchar(30),
  x_interval integer
);
alter sequence chart_data_id_seq restart;

create table charts(
  id bigserial primary key,
  author_id bigint references users(id),
  title varchar(100) not null,
  from_date date not null,
  to_date date not null,
  type varchar(20) not null,
  public_chart boolean default false not null,
  chart_data_id bigint references chart_data(id) on delete cascade
);
alter sequence charts_id_seq restart;

create table user_charts(
  id bigserial primary key,
  user_id  bigint references users(id) on delete cascade,
  chart_id bigint references charts(id) on delete cascade,
  unique (user_id, chart_id)
);
alter sequence user_charts_id_seq restart;
