CREATE TABLE %mods%
  ( mod text primary key
  ) ;

CREATE TABLE %roots%
  ( pid  text primary key
  , actv bool NOT NULL DEFAULT FALSE
  ) ;

CREATE TABLE %repos%
  ( id     serial primary key
  , side   text   NOT NULL
  , name   text   NOT NULL
  , ownr   text   NOT NULL
  , dscr   text   NOT NULL DEFAULT ''
  , wtch   bool   NOT NULL DEFAULT FALSE
  , publ   bool   NOT NULL DEFAULT FALSE
  , lock   bool   NOT NULL DEFAULT FALSE
  , nname  text   UNIQUE
  , creat  date   NOT NULL DEFAULT now()
  , unique ( side, ownr, name )
  ) ;

CREATE TABLE %paths%
  ( id  serial primary key
  , rid integer NOT NULL REFERENCES %repos% ( id ) ON DELETE CASCADE
  , path text NOT NULL DEFAULT ''
  , publ bool NOT NULL DEFAULT false
  , unique ( rid, path )
  ) ;

CREATE TABLE %rights%
  ( id  serial primary key
  , pid text NOT NULL
  , rid integer NOT NULL REFERENCES %paths% ( id ) ON DELETE CASCADE
  , mod text NOT NULL REFERENCES %mods% ( mod )
  , man bool NOT NULL default false
  , wat bool NOT NULL default false
  , unique ( pid, rid )
  ) ;

CREATE TABLE %guests%
  ( id     serial primary key
  , login  text NOT NULL
  , passwd text NOT NULL
  , name   text NOT NULL
  , email  text NOT NULL UNIQUE
  , parent text NOT NULL
  , creat  date NOT NULL DEFAULT now()
  , nwmail text
  , nwcode text
  ) ;

CREATE TABLE %groups%
  ( gid  serial primary key
  , name text NOT NULL
  , ownr text NOT NULL
  , creat date NOT NULL DEFAULT now()
  , UNIQUE ( name )
  ) ;

CREATE TABLE %gr_mem%
  ( id  serial primary key
  , pid text NOT NULL
  , gid integer NOT NULL REFERENCES %groups% ( gid ) ON DELETE CASCADE
  , mem bool NOT NULL DEFAULT false
  , man bool NOT NULL DEFAULT false
  , UNIQUE ( pid, gid )
  ) ;

CREATE TABLE %stats%
  ( id   serial primary key
  , rid  integer NOT NULL REFERENCES %repos% ( id ) ON DELETE CASCADE
  , rev  integer NOT NULL
  , size integer NOT NULL
  , date text    NOT NULL
  , time integer NOT NULL
  ) ;

CREATE TABLE %tots%
  ( id    serial primary key
  , size  integer NOT NULL
  , count integer NOT NULL
  , date  text    NOT NULL
  , time  integer NOT NULL
  ) ;

CREATE TABLE %xusers%
  ( id  serial primary key
  , pid text NOT NULL UNIQUE
  ) ;

# inserts -----------------------------------------------

INSERT INTO %mods% ( mod ) VALUES ( 'ro' )  ;
INSERT INTO %mods% ( mod ) VALUES ( 'rw' )  ;
INSERT INTO %mods% ( mod ) VALUES ( 'nx' ) ;

# grants ------------------------------------------------

GRANT INSERT, DELETE ON %roots% TO %db_rupd% ;
GRANT SELECT, UPDATE ON %roots% TO %db_user% ;

GRANT SELECT ON %mods% TO %db_user% ;

GRANT ALL ON %repos% TO %db_user% ;
GRANT ALL ON SEQUENCE %repos%_id_seq TO %db_user% ;

GRANT ALL ON %paths% TO %db_user% ;
GRANT ALL ON SEQUENCE %paths%_id_seq TO %db_user% ;

GRANT ALL ON %rights% TO %db_user% ;
GRANT ALL ON SEQUENCE %rights%_id_seq TO %db_user% ;

GRANT ALL ON %guests% TO %db_user% ;
GRANT ALL ON SEQUENCE %guests%_id_seq TO %db_user% ;

GRANT ALL ON %stats% TO %db_user% ;
GRANT ALL ON %tots%  TO %db_user% ;
GRANT ALL ON SEQUENCE %stats%_id_seq TO %db_user% ;
GRANT ALL ON SEQUENCE %tots%_id_seq TO %db_user% ;

GRANT ALL ON %gr_mem% TO %db_user% ;
GRANT ALL ON SEQUENCE %gr_mem%_id_seq TO %db_user% ;

GRANT ALL ON %groups% TO %db_user% ;
GRANT ALL ON SEQUENCE %groups%_gid_seq TO %db_user% ;

GRANT ALL ON %xusers% TO %db_user% ;
GRANT ALL ON SEQUENCE %xusers%_id_seq TO %db_user% ;

ALTER SEQUENCE %repos%_id_seq OWNED BY %repos%.id ;
ALTER SEQUENCE %paths%_id_seq OWNED BY %paths%.id ;
ALTER SEQUENCE %rights%_id_seq OWNED BY %rights%.id ;
ALTER SEQUENCE %guests%_id_seq OWNED BY %guests%.id ;
ALTER SEQUENCE %stats%_id_seq OWNED BY %stats%.id ;
ALTER SEQUENCE %tots%_id_seq OWNED BY %tots%.id ;
ALTER SEQUENCE %gr_mem%_id_seq OWNED BY %gr_mem%.id ;
ALTER SEQUENCE %groups%_gid_seq OWNED BY %groups%.gid ;
ALTER SEQUENCE %xusers%_id_seq OWNED BY %xusers%.id ;

# CREATE LANGUAGE plpgsql ;

CREATE OR REPLACE FUNCTION add_empty_repo_path()
  RETURNS TRIGGER AS $add_empty_repo_path$
  BEGIN
  INSERT INTO %paths% (rid) VALUES ( NEW.id ) ;
  RETURN NULL ;
  END ; $add_empty_repo_path$ LANGUAGE plpgsql ;

DROP TRIGGER IF EXISTS add_empty_repo_path ON %repos% CASCADE ;

CREATE TRIGGER add_empty_repo_path
  AFTER INSERT ON %repos%
  FOR EACH ROW EXECUTE PROCEDURE add_empty_repo_path() ;

DROP INDEX IF EXISTS %stats%_rid_idx CASCADE ;
CREATE INDEX %stats%_rid_idx ON %stats% ( rid ) ;
