
-- Drop and create employee table
create table employee (
    empno      int not null,
    ename      character varying(10),
    job        character varying(9),
    mgr        int,
    hiredate   timestamp,
    sal        int,
    comm       int,
    deptno     numeric(2,0),
    sido_nm    character varying(100)
);
insert into employee
select i,
       chr(65+mod(i,26))||':text'||'nm',
       case when mod(i,10000)=0 then 'president'
            when mod(i,1000)=0 then 'manager'
            when mod(i,3)=0 then 'salesman'
            when mod(i,3)=1 then 'analyst'
            else 'clerk'
       end,
       case when mod(i,10000)=0 then null
            when mod(i,1000)=1 then 10000
            when i > 9000 then 1000
            else ceiling((i+1000)/1000)*1000
       end,
       '20240930'::date,
       trunc(random()*1000),
       mod(i,12)+1,
       case when mod(i,3)=0 then 'jeonbuk'
            when mod(i,3)=1 then 'kangwon'
            else 'chungnam'
       end
from generate_series(1,10000) a(i);
alter table employee add constraint employee_pk primary key (empno);

-- Drop and create customer table
create table customer (
    cust_no         bigint not null,
    cust_nm         character varying(100),
    register_date   timestamp,
    register_dt     varchar(8),
    cust_status_cd  varchar(1),
    register_channel_cd varchar(1),
    cust_age        smallint,
    active_yn       varchar(1),
    sigungu_cd      varchar(5),
    sido_cd         varchar(2)
);
insert into customer
select i,
       chr(65+mod(i,26))||':text'||'cust_nm',
       '20240930'::date - mod(i,10000),
       to_char('20240930'::date - mod(i,10000), 'yyyymmdd'),
       mod(i,5)+1,
       mod(i,3)+1,
       trunc(random()*100)+1,
       case when mod(i,22)=0 then 'n' else 'y' end,
       case mod(i,6)
            when 0 then '11007'
            when 1 then '11006'
            when 2 then '11005'
            when 3 then '11004'
            when 4 then '11003'
            else '11002'
       end,
       case mod(i,3)
            when 0 then '01'
            when 1 then '02'
            else '03'
       end
from generate_series(1,1000000) a(i);
alter table customer add constraint customer_pk primary key (cust_no);

-- Drop and create com_code
create table com_code (
    group_cd varchar(10) not null,
    cd       varchar(10) not null,
    cd_nm    varchar(100) not null
);
insert into com_code values
('g1','11001','seoul'), ('g1','11002','pusan'), ('g1','11003','incheon'), ('g1','11004','daegu'),
('g1','11005','jaeju'), ('g1','11006','uleung'), ('g1','11007','etc'),
('g2','1','infant'), ('g2','2','child'), ('g2','3','adolescent'), ('g2','4','adult'), ('g2','5','senior'),
('g3','01','jeonbuk'), ('g3','02','kangwon'), ('g3','03','chungnam'),
('g4','1','주문신청'), ('g4','2','주문신청반려'), ('g4','3','주문취소'), ('g4','4','주문취소');
alter table com_code add constraint com_code_pk primary key (group_cd, cd);



-- Drop and create product table
create table product (
    prod_id       varchar(10) not null,
    prod_nm       varchar(100) not null,
    regist_empno  int
);
insert into product
select 'prod'||i::text, 'prod_nm'||i::text, i*10
from generate_series(1,200) a(i);
alter table product add constraint product_pk primary key (prod_id);

-- Drop and create online_order table
create table online_order (
    ord_no        bigint not null,
    cust_no       int not null,
    ord_date      date not null,
    ord_dt        varchar(8) not null,
    ord_status_cd varchar(1) not null,
    comment       varchar(100)
);
insert into online_order
select i,
       mod(i,1000000),
       '20241015'::date - mod(i,1000),
       to_char('20241015'::date - mod(i,1000), 'yyyymmdd'),
       (mod(i,4)+1)::text,
       lpad('x',100,'x')
from generate_series(1,2000000,2) a(i);
alter table online_order add constraint online_order_pk primary key (ord_no);

-- Drop and create offline_order table
create table offline_order (
    ord_no        bigint not null,
    cust_no       int not null,
    ord_date      date not null,
    ord_dt        varchar(8) not null,
    ord_status_cd varchar(1) not null,
    empno         int,
    comment       varchar(100)
);
insert into offline_order
select i,
       mod(i,1000000),
       '20241015'::date - mod(i,1000),
       to_char('20241015'::date - mod(i,1000), 'yyyymmdd'),
       (mod(i/2,4)+1)::text,
       mod(i,10000)+1,
       lpad('y',100,'y')
from generate_series(2,2000000,2) a(i);
alter table offline_order add constraint offline_order_pk primary key (ord_no);

-- Drop and create ord_item table
create table ord_item (
    ord_no       bigint not null,
    prod_id      varchar(10) not null,
    unit_price   int not null,
    quantity     int not null,
    on_off_code  varchar(2) not null,
    oder_comment varchar(100)
);
insert into ord_item
select a.ord_no,
       'prod'||(mod(a.ord_no,200)+1)::text,
       trunc(100*random())+1,
       trunc(10*random())+1,
       case when mod(a.ord_no,2)=0 then '01' else '02' end,
       lpad('c',100,'y')
from (
    select ord_no from online_order
    union all
    select ord_no from offline_order
) a;
alter table ord_item add constraint ord_item_pk primary key (ord_no, prod_id);



-- Drop and create employee_hist table
create table employee_hist (
    operation     character varying(10) not null,
    empno         int not null,
    ename         character varying(10),
    sal           int,
    update_date   timestamp
);
alter table employee_hist add constraint pk_e_hist primary key (operation, empno);

-- Drop and create err_log table
create table err_log (
    ora_err_number int,
    ora_err_msg    varchar(2000),
    operation      varchar(10),
    empno          int,
    sal            int,
    err_date       timestamp
);

-- Drop and create bizpl_sale_sum table
create table bizpl_sale_sum (
    bizpl_id  varchar(10) not null,
    prod_id   varchar(10) not null,
    sale_date date not null,
    sale_amt  int not null,
    comment   varchar(100)
);
insert into bizpl_sale_sum
select 'bizpl'||p.prod_id, p.prod_id, b.sale_date, random()*1000000, null
from product p,
     generate_series('20240101'::date, '20241231'::date, interval '1 day') b(sale_date);
alter table bizpl_sale_sum add constraint bizpl_sale_sum_pk PRIMARY KEY (bizpl_id, prod_id, sale_date);

-- Drop and create sale_goal table
create table sale_goal (
    prod_id     varchar(10) not null,
    sale_month  varchar(6) not null,
    sale_amt    int not null,
    comment     varchar(100),
    CONSTRAINT sale_goal_pk PRIMARY KEY (prod_id, sale_month)
);
insert into sale_goal
select p.prod_id, to_char(a.i, 'yyyymm'), trunc(random()*1000000), md5(random()::text)
from product p,
     generate_series('20230101'::date, '20251201'::date, interval '1 month') a(i);

-- Partitioned table: sale_p
create table sale_p (
    bizpl_id  varchar(10) not null,
    prod_id   varchar(10) not null,
    sale_date date not null,
    sale_amt  int not null,
    comment   varchar(100)
) partition by range(sale_date);

-- Create partitions for sale_p (monthly for year 2024)
create table sale_p_202401 partition of sale_p for values from ('2024-01-01') to ('2024-02-01');
create table sale_p_202402 partition of sale_p for values from ('2024-02-01') to ('2024-03-01');
create table sale_p_202403 partition of sale_p for values from ('2024-03-01') to ('2024-04-01');
create table sale_p_202404 partition of sale_p for values from ('2024-04-01') to ('2024-05-01');
create table sale_p_202405 partition of sale_p for values from ('2024-05-01') to ('2024-06-01');
create table sale_p_202406 partition of sale_p for values from ('2024-06-01') to ('2024-07-01');
create table sale_p_202407 partition of sale_p for values from ('2024-07-01') to ('2024-08-01');
create table sale_p_202408 partition of sale_p for values from ('2024-08-01') to ('2024-09-01');
create table sale_p_202409 partition of sale_p for values from ('2024-09-01') to ('2024-10-01');
create table sale_p_202410 partition of sale_p for values from ('2024-10-01') to ('2024-11-01');
create table sale_p_202411 partition of sale_p for values from ('2024-11-01') to ('2024-12-01');
create table sale_p_202412 partition of sale_p for values from ('2024-12-01') to ('2025-01-01');

-- Insert sample data into partitioned sale_p
insert into sale_p
select 'bizpl'||p.prod_id, p.prod_id, b.sale_date, random()*1000000, null
from product p,
     generate_series('20240101'::date, '20241231'::date, interval '1 day') b(sale_date);
alter table sale_p add constraint sales_p_pk PRIMARY KEY (bizpl_id, prod_id, sale_date);
