execute ibeblock
AS
begin
  CNT=10000;
  delete from customer_history;
  delete from orderline;
  delete from orders;

  delete from inventory;

  select first 1 id from product order by id into :i;
  update product set common_product_id=:i;

  delete from product where id>:i;
  delete from product;

  delete from customer;

  select gen_id(id,-gen_id(id,0)) from rdb$database into i;

  c=0;
  while (c<cnt) do
  begin
    c=c+1;
    xid=gen_id(id,1);

    i=getrandom(676)+1;
    select txt from tmp_fn where id=:i into :xfirstname;

    i=getrandom(1086)+1;
    select txt from tmp_ln where id=:i into :xlastname;

    i=getrandom(4710)+1;
    select txt from tmp_adr where id=:i into :xaddress1;

    xaddress2='-';

    xzip=getrandom(90000)+10000;

    xcountry='USA';

    i=getrandom(3234)+1;
    select txt from tmp_city where id=:i into :xcity;

    i=getrandom(51)+1;
    select txt from tmp_state where id=:i into :xstate;

    xREGION=getrandom(10)+1;

    xemail=xfirstname||'.'||xlastname||'@yahoo.com';

    t=getrandom(900)+100;
    xphone='1('||t;
    t=getrandom(900)+100;
    xphone=xphone||')'||t;
    t=getrandom(9000)+1000;
    xphone=xphone||'-'||t;

    xCREDITCARDTYPE=getrandom(4)+1;

    t=getrandom(9000)+1000;
    xCREDITCARD=t;
    t=getrandom(9000)+1000;
    xCREDITCARD=t||' '||xCREDITCARD;
    t=getrandom(9000)+1000;
    xCREDITCARD=t||' '||xCREDITCARD;
    t=getrandom(9000)+1000;
    xCREDITCARD=t||' '||xCREDITCARD;

    t=extract(year from current_date)+getrandom(5)+1;
    xCREDITCARDEXPIRATION=t;
    t=getrandom(12)+1;
    xCREDITCARDEXPIRATION=t||'/'||xCREDITCARDEXPIRATION;

    xusername=xfirstname||'.'||xlastname;
    xusername=xusername||(cnt+xid);

    xpasswd=getrandom(900000)+100000;

    xage=getrandom(60)+15;

    xincome=1000*(getrandom(90)+10);

    i=getrandom(2)+1;
    if (i=1) then xgender='M'; else xgender='F';

    INSERT INTO CUSTOMER(ID, FIRSTNAME, LASTNAME, ADDRESS1, ADDRESS2, CITY, STATE, ZIP, COUNTRY, REGION, EMAIL, PHONE, CREDITCARDTYPE, CREDITCARD, CREDITCARDEXPIRATION, USERNAME, PASSWD, AGE, INCOME, GENDER)
    VALUES (:xID, :xFIRSTNAME, :xLASTNAME, :xADDRESS1, :xADDRESS2, :xCITY, :xSTATE, :xZIP, :xCOUNTRY, :xREGION, :xEMAIL, :xPHONE, :xCREDITCARDTYPE, :xCREDITCARD, :xCREDITCARDEXPIRATION, :xUSERNAME, :xPASSWD, :xAGE, :xINCOME, :xGENDER);
  end
  commit;
  
  select gen_id(id,(-gen_id(id,0)+:cnt)) from rdb$database into i;

  c=0;
  while (c<cnt) do
  begin
    c=c+1;
    xid=gen_id(id,1);
    xcategory_id=getrandom(16)+1;

    i=getrandom(1000)+1;
    select txt from tmp_titleword where id=:i into :t;
    xtitle=t;

    i=getrandom(1000)+1;
    select txt from tmp_titleword where id=:i into :t;
    xtitle=t||' '||xtitle;

    i=getrandom(1000)+1;
    select txt from tmp_titleword where id=:i into :t;
    xtitle=t||' '||xtitle;


    i=getrandom(200)+1;
    select txt from tmp_fn2 where id=:i into :t;
    xactor=t;
    i=getrandom(200)+1;
    select txt from tmp_ln2 where id=:i into :t;
    xactor=xactor||' '||t||', ';

    i=getrandom(200)+1;
    select txt from tmp_fn2 where id=:i into :t;
    xactor=xactor||' '||t;
    i=getrandom(200)+1;
    select txt from tmp_ln2 where id=:i into :t;
    xactor=xactor||' '||t;

    xprice=getrandom(40)+10+.90;

    xspecial=getrandom(500)+1;
    if (xspecial>490) then xspecial=1; else xspecial=0;

    xcommon_product_id=getrandom((xid-:cnt)/10)+1+:cnt;

    INSERT INTO PRODUCT(ID, CATEGORY_ID, TITLE, ACTOR, PRICE, SPECIAL, COMMON_PRODUCT_ID)
    VALUES (:xID, :xCATEGORY_ID, :xTITLE, :xACTOR, :xPRICE, :xSPECIAL, :xCOMMON_PRODUCT_ID);

    INSERT INTO INVENTORY(PRODUCT_ID, QUAN_IN_STOCK, SALES)
    VALUES (:xID, (100+getrandom(500)+cast(:cnt/100 as integer)), 0);
  end
  commit;

  i=cnt;

  select gen_id(id,-gen_id(id,0)+:cnt+:cnt) from rdb$database into i;

  while (i>0) do
  begin
    xid=gen_id(id,1);
    xORDERDATE=current_date-(i/cnt*730)-getrandom(3);
    xcustomer_id=getrandom(:cnt)+1;
    xNETAMOUNT=0;
    xtax=0;
    xTOTALAMOUNT=0;
    INSERT INTO ORDERS(ID, ORDERDATE, CUSTOMER_ID, NETAMOUNT, TAX, TOTALAMOUNT)
    VALUES (:xID, :xORDERDATE, :xCUSTOMER_ID, :xNETAMOUNT, :xTAX, :xTOTALAMOUNT);

    ix=getrandom(5)+1;
    while (ix>0) do
    begin

      yid=gen_id(id,1);
      yorders_id=xid;

      yproduct_id=getrandom(:cnt)+1+:cnt;
      select count(*) from orderline o where o.orders_id=:xid and o.product_id=:yproduct_id into :iy;
      while (iy>0) do
      begin
        yproduct_id=getrandom(:cnt)+1+:cnt;
        select count(*) from orderline o where o.orders_id=:xid and o.product_id=:yproduct_id into :iy;
      end
      yquantity=getrandom(3)+1;
      yorderdate=xorderdate;

      INSERT INTO ORDERLINE(ID, ORDERS_ID, PRODUCT_ID, QUANTITY, ORDERDATE)
      VALUES (:yID, :yORDERS_ID, :yPRODUCT_ID, :yQUANTITY, :yORDERDATE);


      select product.price from product where product.id=:yproduct_id into pp;
      xNETAMOUNT=xNETAMOUNT+(:yquantity*:pp);

      ix=ix-1;
    end

    xTAX=xNETAMOUNT*.16;
    xTOTALAMOUNT=xNETAMOUNT+xtax;
    UPDATE ORDERS SET NETAMOUNT = :xNETAMOUNT,TAX = :xTAX, TOTALAMOUNT = :xTOTALAMOUNT
    WHERE (ID = :xID);


    i=i-1;
  end
  commit;
end
