DROP DATABASE bemyeye;

-- Create role 'bemyeye' password 'eyebemy'
CREATE ROLE bemyeye LOGIN ENCRYPTED PASSWORD 'md52c8adeec0b5f56e9b0e637c616c7fc3e'
   VALID UNTIL 'infinity';
ALTER ROLE test_b PASSWORD 'eyebemy';

-- Create DB and set as current
CREATE DATABASE bemyeye
  WITH OWNER = bemyeye
       ENCODING = 'UTF8'
       CONNECTION LIMIT = -1;
\c bemyeye

CREATE SCHEMA bemyeye
  AUTHORIZATION bemyeye;

CREATE TABLE bemyeye.user (
    id serial not null PRIMARY KEY,
    name character varying(100) not null,
    login character varying(25) not null,
    email character varying(25) not null,
    pass character varying(160) not null,
    account integer not null default 10,
    phone integer,
    status integer not null default 2,
    login_count integer not null default 0,
    audit_dc timestamp default current_timestamp,
    audit_la timestamp default null,
    CONSTRAINT user_login UNIQUE (login)
);
CREATE INDEX user_phone ON bemyeye.user (phone);

-- Add user: 'admin', pass: 'admin1'
INSERT INTO bemyeye.user VALUES
(1, 'Administrator', 'admin', 'admin@bemyeye.pl', '58b5444cf1b6253a4317fe12daff411a78bda0a95279b1d5768ebf5ca60829e78da944e8a9160a0b6d428cb213e813525a72650dac67b88879394ff624da482f', 1, 0);

CREATE TABLE bemyeye.stream (
    id serial not null PRIMARY KEY,
    uuid character varying(50) not null,
    owner_id integer not null REFERENCES bemyeye.user (id) ON DELETE CASCADE,
    name character varying(100) not null,
    phone integer not null,
    imei character varying(50) not null,
    operator character varying(50) not null,
    device_vendor character varying(50) not null,
    device_model character varying(50) not null,
    os_version character varying(25) not null,
    transmision_id integer,
    status integer not null default 0,
    audit_dc timestamp default current_timestamp,
    audit_la timestamp default null,
    CONSTRAINT stream_uuid UNIQUE (uuid)
);
CREATE INDEX stream_phone ON bemyeye.stream (phone);

CREATE TABLE bemyeye.transmision (
    id serial not null PRIMARY KEY,
    stream_id integer not null REFERENCES bemyeye.stream (id),
    gps_x double precision,
    gps_y double precision,
    orientation_pitch double precision,
    orientation_roll double precision,
    orientation_yaw double precision,
    audit_dc timestamp default current_timestamp,
    audit_la timestamp
);
ALTER TABLE bemyeye.stream
    ADD FOREIGN KEY (transmision_id) REFERENCES bemyeye.transmision (id);

CREATE TABLE bemyeye.stream_access (
    id serial PRIMARY KEY,
    stream_id integer not null REFERENCES bemyeye.stream (id) ON DELETE CASCADE,
    viewer_id integer REFERENCES bemyeye.user (id),
    widget integer not null,
    audit_dc timestamp default current_timestamp
);
CREATE INDEX stream_access_stream_viewer ON bemyeye.stream_access (stream_id, viewer_id);

CREATE TABLE bemyeye.viewing (
    id serial not null PRIMARY KEY,
    stream_id integer not null REFERENCES bemyeye.stream (id),
    viewer_id integer REFERENCES bemyeye.user (id),
    ip character varying(32),
    os character varying(50),
    browser character varying(50) NOT NULL,
    refresh_count integer not null default 0,
    audit_dc timestamp default current_timestamp
);
CREATE INDEX viewing_stream_viewer ON bemyeye.viewing (stream_id, viewer_id);

ALTER TABLE bemyeye.user OWNER TO bemyeye;
ALTER TABLE bemyeye.stream OWNER TO bemyeye;
ALTER TABLE bemyeye.transmision OWNER TO bemyeye;
ALTER TABLE bemyeye.stream_access OWNER TO bemyeye;
ALTER TABLE bemyeye.viewing OWNER TO bemyeye;
