create database G01_0374;

use G01_0374;

CREATE TABLE IF NOT EXISTS user_tbl(
   id INT UNSIGNED AUTO_INCREMENT,
   name VARCHAR(20) NOT NULL,
   password VARCHAR(100) NULL,
   PRIMARY KEY ( id )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO user_tbl (name,password) VALUES('admin', '123456');

CREATE TABLE IF NOT EXISTS batch_tbl(
   id INT UNSIGNED AUTO_INCREMENT,
   year CHAR(1) NOT NULL,
   begin_time BIGINT NOT NULL,
   end_time BIGINT,
   stat BOOL NOT NULL,
   PRIMARY KEY ( id )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS order_tbl(
   id INT UNSIGNED AUTO_INCREMENT,
   order_num VARCHAR(30) NOT NULL,
   product_idx INT NOT NULL,
   product_name VARCHAR(30) NOT NULL,
   batch VARCHAR(10) NOT NULL,
   team VARCHAR(2) NOT NULL,
   ok INT NOT NULL,
   ng INT NOT NULL,
   targe INT NOT NULL,
   snp INT NOT NULL,
   begin_time BIGINT NOT NULL,
   end_time BIGINT,
   stat BOOL NOT NULL,
   PRIMARY KEY ( id )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS product_tbl(
   id INT UNSIGNED AUTO_INCREMENT,
   product_idx INT NOT NULL,
   product_name VARCHAR(30) NOT NULL,
   17700_name VARCHAR(30) NOT NULL,
   17720_name VARCHAR(30) NOT NULL,
   stat BOOL NOT NULL,
   PRIMARY KEY ( id )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS product_param_tbl(
   id INT UNSIGNED AUTO_INCREMENT,
   product_idx INT NOT NULL,
   product_name VARCHAR(30) NOT NULL,
   param_type INT NOT NULL,
   startuptime VARCHAR(10),
   airtime VARCHAR(10),
   presstime VARCHAR(10),
   testtime VARCHAR(10),
   exhaust VARCHAR(10),
   maxpress VARCHAR(10),
   minpress VARCHAR(10),
   setpress VARCHAR(10),
   capacity VARCHAR(10),
   posthreshold VARCHAR(10),
   pnegthreshold VARCHAR(10),
   PRIMARY KEY ( id )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS product_data_tbl(
   id INT UNSIGNED AUTO_INCREMENT,
   batch VARCHAR(20) NOT NULL,
   serial VARCHAR(20) NOT NULL,
   product_name VARCHAR(30) NOT NULL,
   testok BOOL NOT NULL,
   data_17700 VARCHAR(50) NOT NULL,
   data_17720 VARCHAR(50) NOT NULL,
   team VARCHAR(2) NOT NULL,
   tester VARCHAR(10) NOT NULL,
   begin_time BIGINT NOT NULL,
   end_time BIGINT NOT NULL,
   stat BOOL NOT NULL,
   PRIMARY KEY ( id ),
   INDEX batch_index (batch(12))
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
exit;