/*
  Databaze neni naplnena cela. Chybi logovani, novinky, konta, prispevky.
*/

/* vlozeni uzivatelu */ 
declare 
  v_jmeno uzivatel.jmeno%TYPE;
  v_prijmeni uzivatel.prijmeni%TYPE;
  v_rc uzivatel.rc%TYPE;
  v_adresa uzivatel.adresa%TYPE;
  v_pokoj uzivatel.pokoj%TYPE;
  v_login uzivatel.login%TYPE;
  v_email uzivatel.email%TYPE;
begin 
  for v_index in 1..200 
  Loop 
    v_jmeno := 'jmeno' || to_char(v_index, '999');
    v_prijmeni := 'prijmeni' || to_char(v_index, '999');
    v_rc := to_char(v_index*333, '000000000');
    v_adresa := 'Thakurova ' || to_char(v_index, '999');
    v_pokoj := to_char( mod(v_index, 54), '99');
    v_login := 'L'||to_char(v_index, '000');
    v_email := v_jmeno || '@ok.cvut.cz';
    INSERT INTO uzivatel(id,jmeno,prijmeni,rc,adresa,pokoj,jiny_kontakt,login)
                VALUES(uzivatel_seq.nextval,v_jmeno, v_prijmeni, v_rc, v_adresa, v_pokoj, v_email, v_login);
  end Loop;
end;

/

/* vlozeni sitovych zarizeni */ 
declare 
  v_aktivni sitove_zarizeni.aktivni%TYPE;
  v_mac sitove_zarizeni.mac_addr%TYPE;
  v_ip sitove_zarizeni.ip_addr%TYPE;
  v_ip6 sitove_zarizeni.ip6_addr%TYPE;
  v_hostname sitove_zarizeni.hostname%TYPE;
  v_zasuvka sitove_zarizeni.zasuvka%TYPE;
  v_popis sitove_zarizeni.popis%TYPE;
  v_min integer;
  v_max integer;
begin 
  SELECT min(id) INTO v_min from uzivatel;
  SELECT max(id) INTO v_max from uzivatel;
  for v_index in v_min..v_max 
  Loop 
    v_aktivni := mod(v_index,2);
    v_mac := '00:20:33:' || to_char(v_index, '9999');
    v_ip := to_char(v_index, '999');
    v_ip6 := 'v6:20:33:' || to_char(v_index, '9999');
    v_hostname := 'host'||to_char( v_index, '099');
    v_zasuvka := to_char(300-v_index, '000');

    INSERT INTO sitove_zarizeni(id,aktivni,mac_addr,ip_addr,ip6_addr,hostname,zasuvka)
                VALUES(sitove_zarizeni_seq.nextval,v_aktivni,v_mac,v_ip,v_ip6,v_hostname,v_zasuvka);
    if mod(v_index,4)>0 then
      INSERT INTO pocitac(sitove_zarizeni_id,uzivatel_id)
                  VALUES(sitove_zarizeni_seq.currval, v_index); -- muzeme, protoze vime, ze lidi je tam tez 200 (1-200)
    end if;
    
  end Loop;
end;

/

/* naplneni tabulek prispevek, typ_konta, typ_clena, typ_prispevku */
INSERT INTO SEMESTR(ID, DATUM_OD, DATUM_DO, POPIS) 
	VALUES (semestr_seq.nextval,TO_DATE('01-OCT-2005','DD-MON-YYYY'),TO_DATE('01-FEB-2006','DD-MON-YYYY'),'ZS-2005/2006');
INSERT INTO SEMESTR(ID, DATUM_OD, DATUM_DO, POPIS) 
	VALUES (semestr_seq.nextval,TO_DATE('02-FEB-2006','DD-MON-YYYY'),TO_DATE('30-SEP-2006','DD-MON-YYYY'),'LS-2005/2006');
INSERT INTO SEMESTR(ID, DATUM_OD, DATUM_DO, POPIS) 
	VALUES (semestr_seq.nextval,TO_DATE('01-OCT-2006','DD-MON-YYYY'),TO_DATE('01-FEB-2007','DD-MON-YYYY'),'ZS-2006/2007');

INSERT INTO TYP_KONTA(ID, NAZEV) 
	VALUES (typ_konta_seq.nextval, 'Databaze');
INSERT INTO TYP_KONTA(ID, NAZEV) 
	VALUES (typ_konta_seq.nextval, 'Tiskarna');
INSERT INTO TYP_KONTA(ID, NAZEV) 
	VALUES (typ_konta_seq.nextval, 'Email');
INSERT INTO TYP_KONTA(ID, NAZEV) 
	VALUES (typ_konta_seq.nextval, 'Shell');
INSERT INTO TYP_KONTA(ID, NAZEV) 
	VALUES (typ_konta_seq.nextval, 'Posilka');

INSERT INTO TYP_CLENA(ID, TYP) VALUES (typ_clena_seq.nextval, 'technik');
INSERT INTO TYP_CLENA(ID, TYP) VALUES (typ_clena_seq.nextval, 'novinkar');
INSERT INTO TYP_CLENA(ID, TYP) VALUES (typ_clena_seq.nextval, 'predseda');

INSERT INTO TYP_PRISPEVKU(ID,VYSE,POPIS,PLATNOST_OD) VALUES (typ_prispevku_seq.nextval,800,'clenstvi v klubu',TO_DATE('01-FEB-2005','DD-MON-YYYY'));
INSERT INTO TYP_PRISPEVKU(ID,VYSE,POPIS,PLATNOST_OD) VALUES (typ_prispevku_seq.nextval,200,'pristup do posilovny',TO_DATE('01-JUN-2005','DD-MON-YYYY'));

/

/* vytvoreni aktivnich clenu, adminu */

INSERT INTO AKTIVNI_CLEN(UZIVATEL_ID)
  SELECT ID FROM UZIVATEL WHERE mod(ID,20)=0;

INSERT INTO ADMIN(AKTIVNI_CLEN_UZIVATEL_ID)
  SELECT UZIVATEL_ID FROM AKTIVNI_CLEN WHERE mod(UZIVATEL_ID,30)=0;

/

/* vyplneni ostatni_sz */
INSERT INTO OSTATNI_SZ(SITOVE_ZARIZENI_ID, TYP)
  SELECT ID, 'server' TYP FROM SITOVE_ZARIZENI WHERE mod(ID,4)=0 AND ID < ((SELECT max(ID) FROM SITOVE_ZARIZENI)-100);
INSERT INTO OSTATNI_SZ(SITOVE_ZARIZENI_ID, TYP)
  SELECT ID, 'switch' TYP FROM SITOVE_ZARIZENI WHERE mod(ID,4)=0 AND ID > ((SELECT max(ID) FROM SITOVE_ZARIZENI)-100);

/

/* vyplneni sluzeb */
declare
  CURSOR c_sz is SELECT * from OSTATNI_SZ where TYP like 'server';
  v_nazev sluzba.nazev%TYPE;
begin
  for v_radek in c_sz
  loop
    v_nazev := 'sluzba'||c_sz%ROWCOUNT;
    INSERT INTO SLUZBA(ID,OSTATNI_SZ_SITOVE_ZARIZENI_ID,NAZEV)
      VALUES(sluzba_seq.nextval,v_radek.SITOVE_ZARIZENI_ID,v_nazev);
    exit when c_sz%ROWCOUNT = 20;	--vime, ze je serveru 25 -> nejake budou bez sluzeb
  end loop;
end;

/

/* vlozeni vazeb admin -> sluzba */
declare
  CURSOR c_sluzba is SELECT * from SLUZBA;
  CURSOR c_admin is SELECT * from podvok1.ADMIN;
  v_admin podvok1.admin%ROWTYPE;
  v_index integer;
begin
  v_index := 0;
  open c_admin;
  for v_radek in c_sluzba
  loop
    if mod(v_index,3)=0 then
      fetch c_admin into v_admin;
    end if;
    exit when c_admin%NOTFOUND;
    
    INSERT INTO ADMIN_has_SLUZBA(ADMIN_ID,SLUZBA_ID, SLUZBA_SZ_ID)
      VALUES(v_admin.aktivni_clen_uzivatel_id,v_radek.id,v_radek.ostatni_sz_sitove_zarizeni_id);
    v_index := v_index + 1;
  end loop;
  close c_admin;
end;

/

/* vlozeni vazeb admin -> ostatni_sz */
declare
  CURSOR c_sluzba is SELECT * from OSTATNI_SZ;
  CURSOR c_admin is SELECT * from ADMIN;
  v_admin admin%ROWTYPE;
  v_index integer;
begin
  v_index := 0;
  open c_admin;
  for v_radek in c_sluzba
  loop
    if mod(v_index,5)=0 then
      fetch c_admin into v_admin;
    end if;
    exit when c_admin%NOTFOUND;
    
    INSERT INTO ADMIN_has_OSTATNI(ADMIN_ID,OSTATNI_SZ_SITOVE_ZARIZENI_ID)
      VALUES(v_admin.aktivni_clen_uzivatel_id,v_radek.sitove_zarizeni_id);
    v_index := v_index + 1;
  end loop;
  close c_admin;
end;

/

/* pridani trestu */
declare
  CURSOR c_uzivatel is SELECT * from UZIVATEL WHERE ID > (SELECT max(ID) FROM UZIVATEL)-50;
  CURSOR c_admin is SELECT * from ADMIN;
  v_admin admin%ROWTYPE;
  v_index integer;
begin
  v_index := 0;
  open c_admin;
  for v_radek in c_uzivatel
  loop
    if mod(v_index,5)=0 then
      fetch c_admin into v_admin;
    end if;
    exit when c_admin%NOTFOUND;
    
    INSERT INTO TREST(ID,ADMIN_ID,UZIVATEL_ID,DATUM,DATUM_OD,DATUM_DO,DUVOD,TYP)
      VALUES(trest_seq.nextval,v_admin.aktivni_clen_uzivatel_id,v_radek.id,
             Add_months(SYSDATE,-v_index),
             Add_months(SYSDATE,-v_index),
             Add_months(SYSDATE,-v_index+2),
             'sdili fillmy',
             'odpojeni od site'
      );
    v_index := v_index + 1;
  end loop;
  close c_admin;
end;

/

/* vlozeni vazeb aktivni_clen <-> typ_clena */
declare
  CURSOR c_ac is SELECT * from AKTIVNI_CLEN;
  CURSOR c_tc is SELECT * from TYP_CLENA where TYP<>'predseda'; --predsedu tady neuvazujeme
  v_tc typ_clena%ROWTYPE;
  v_index integer;
begin
  v_index := 0;
  open c_tc;
  for v_radek in c_ac
  loop
    if mod(v_index,5)=0 then
      fetch c_tc into v_tc;
    end if;
    exit when c_tc%NOTFOUND;
    
    INSERT INTO AKTIVNI_CLEN_has_TYP_CLENA(AKTIVNI_CLEN_UZIVATEL_ID,TYP_CLENA_ID)
      VALUES(v_radek.uzivatel_id,v_tc.id);
    v_index := v_index + 1;
  end loop;
  close c_tc;
end;

/

commit;
