USE forecast;

CREATE TABLE company
(
  company_id   VARCHAR(2)   NOT NULL,
  name         VARCHAR(20)  NOT NULL,
  area         VARCHAR(20)  NOT NULL,
  offshore     VARCHAR(1)   NOT NULL,
  bud_cost     NUMERIC(7,2)     NULL,
  bud_mark_up  NUMERIC(3,2) NOT NULL,
  bud_rev      NUMERIC(7,2)     NULL,
  bud_date     DATE             NULL,
  bud_time     TIME             NULL,
  fcast_cost   NUMERIC(7,2)     NULL,
  fcast_markup NUMERIC(3,2) NOT NULL,
  fcast_rev    NUMERIC(7,2)     NULL,
  fcast_date   DATE             NULL,
  fcast_time   TIME             NULL,
  CONSTRAINT pk_company PRIMARY KEY( company_id ) 
)
ENGINE = InnoDB;

CREATE TABLE department
(
  dep_id       VARCHAR(2)   NOT NULL,
  name         VARCHAR(20)  NOT NULL, 
  com_id       VARCHAR(2)   NOT NULL, 
  bud_hrs      NUMERIC(5)   NOT NULL,  
  bud_rate     NUMERIC(7,2) NOT NULL,        
  bud_cost     NUMERIC(7,2)     NULL,
  bud_rev      NUMERIC(7,2)     NULL,
  bud_date     DATE             NULL,
  bud_time     TIME             NULL,
  fcast_hrs    NUMERIC(5)   NOT NULL,  
  fcast_rate   NUMERIC(7,2) NOT NULL,        
  fcast_cost   NUMERIC(7,2)     NULL,
  fcast_markup NUMERIC(3,2) NOT NULL,
  fcast_rev    NUMERIC(7,2)     NULL,
  fcast_date   DATE             NULL,
  fcast_time   TIME             NULL,
  CONSTRAINT pk_department PRIMARY KEY( dep_id )
)
ENGINE = InnoDB;

CREATE INDEX department_inx1 ON department ( com_id );

ALTER TABLE department ADD CONSTRAINT fk_department_company FOREIGN KEY ( com_id )
            REFERENCES company( company_id ) ON DELETE RESTRICT ON UPDATE RESTRICT;

DELIMITER $

CREATE PROCEDURE p_reset_data()
BEGIN

set FOREIGN_KEY_CHECKS=0;

DELETE FROM department;
DELETE FROM company;

/* populate company */
/*                          company_id  name                  area      offshore bud_cost bud_mark_up bud_rev  bud_date      bud_time fcast_cost fcast_mark_up fcast_rev fcast_date    fcast_time */
INSERT INTO company VALUES( 'c1',       'codal ltd',          'UK',     'N',     10,      1.1,        10,      '2001-04-01', '09:00', 0.0,       1.5,          0.0,      '2001-05-01', '09:01' );
INSERT INTO company VALUES( 'c2',       'codal services ltd', 'Europe', 'Y',     10,      2.1,        10,      '2001-04-01', '21:00', 0.0,       2.5,          0.0,      '2001-05-01', '21:01' );

/* populate department */
/*                             dep_id  name          com_id bud_hrs bud_rate bud_cost bud_rev bud_date      bud_time fcast_hrs fcast_rate fcast_cost fcast_markup fcast_rev fcast_date    fcast_time */
INSERT INTO department VALUES( 'd1',   'sales',      'c1',  10,     10.1,    1000.1,  1500.1, '2001-04-01', '09:00', 10,       10.1,      1000.1,    1.5,         1500.1,   '2001-04-01', '09:00' );
INSERT INTO department VALUES( 'd2',   'accounts',   'c1',  20,     20.1,    2000.1,  2500.1, '2001-04-01', '09:00', 20,       20.1,      2000.1,    1.5,         2500.1,   '2001-04-01', '09:00' );
INSERT INTO department VALUES( 'd3',   'purchasing', 'c1',  30,     30.1,    3000.1,  3500.1, '2001-04-01', '09:00', 30,       30.1,      3000.1,    1.5,         3500.1,   '2001-04-01', '09:00' );
INSERT INTO department VALUES( 'd4',   'consulting', 'c1',  40,     40.1,    4000.1,  4500.1, '2001-04-01', '09:00', 40,       40.1,      4000.1,    1.5,         4500.1,   '2001-04-01', '09:00' );
INSERT INTO department VALUES( 'd5',   'accounts',   'c1',  50,     50.1,    5000.1,  5500.1, '2001-04-01', '09:00', 50,       50.1,      5000.1,    1.5,         5500.1,   '2001-04-01', '09:00' );
INSERT INTO department VALUES( 'd6',   'marketing',  'c1',  60,     60.1,    6000.1,  6500.1, '2001-04-01', '09:00', 60,       60.1,      6000.1,    1.5,         6500.1,   '2001-04-01', '09:00' );
INSERT INTO department VALUES( 'd7',   'admin',      'c2',  70,     70.1,    7000.1,  7000.1, '2001-04-01', '09:00', 70,       70.1,      7000.1,    1.5,         7000.1,   '2001-04-01', '09:00' );
INSERT INTO department VALUES( 'd8',   'logistics',  'c2',  80,     80.1,    8000.1,  8000.1, '2001-04-01', '09:00', 80,       80.1,      8000.1,    1.5,         8000.1,   '2001-04-01', '09:00' );
INSERT INTO department VALUES( 'd9',   'accounts',   'c2',  90,     90.1,    9000.1,  9000.1, '2001-04-01', '09:00', 90,       90.1,      9000.1,    1.5,         9000.1,   '2001-04-01', '09:00' );

set FOREIGN_KEY_CHECKS=1;

END$

DELIMITER ;

CALL p_reset_data();