CREATE DATABASE if not exists punchun_database
	CHARACTER SET utf8
	DEFAULT CHARACTER SET utf8
	COLLATE utf8_general_ci
	DEFAULT COLLATE utf8_general_ci
	;
use punchun_database;
create table if not exists punchun_db_food
(
   foodID int unsigned not null auto_increment primary key,
   food_title char (50),
   food_description varchar(255),
   food_ingredient varchar(170),
   food_seasoning varchar(500),
   food_methods varchar(500), 
   food_small_image varchar(50),
   food_large_image varchar(50),
   footCatID int unsigned not null references punchun_db_foodCatID(foodCatID),
   festID int unsigned not null references punchun_db_festival(festID),
   langID int unsigned not null references punchun_db_language(langID),
   refDate date not null
) type=InnoDB;

create table if not exists punchun_db_festival
(
   festID int unsigned not null auto_increment primary key,
   title_eng char (50) not null,
   title_big5 char (50) not null,
   fest_date date not null,
   refDate date not null
) type=InnoDB;

create table if not exists punchun_db_foodCat
(
   foodCatID int unsigned not null auto_increment primary key,
   title char (50) not null,
   langID int unsigned not null references language(langID),
   refDate date not null
) type=InnoDB;
create table if not exists punchun_db_language
(
   langCatID int unsigned not null auto_increment primary key,
   title char (50) not null,
   refDate date not null
) type=InnoDB;
create table if not exists punchun_db_news
(
  	newsID int unsigned not null auto_increment primary key,
  	news_header varchar(100), 
  	news_date date not null,
	news_description varchar (250) not null,
	news_image char (50) not null,
	news_asset varchar(50),
   	langID int unsigned not null references language(langID),
  	refDate date not null
) type=InnoDB;
create table if not exists punchun_db_product
(
  	prodID int unsigned not null auto_increment primary key,
  	prod_header varchar(50), 
	prod_description varchar(255),
	prod_ingredient varchar(255),
	prod_capacity varchar (255),
	prod_small_image char (50) not null,
   	langID int unsigned not null references language(langID),
	prodCatID int unsigned not null references punchun_db_prodCat(prodCatID),

  	refDate date not null
) type=InnoDB;
create table if not exists punchun_db_prodCat
(
   prodCatID int unsigned not null auto_increment primary key,
   title char (50) not null,
   langID int unsigned not null references language(langID),
   refDate date not null
) type=InnoDB;
create table if not exists punchun_db_retailer
(
  	retID int unsigned not null auto_increment primary key,
  	ret_header varchar(50), 
	ret_add varchar(255),
   	langID int unsigned not null references language(langID),
	loctID int unsigned not null references punchun_db_retailer_location(loctID),

  	refDate date not null
) type=InnoDB;
create table if not exists punchun_db_retailer_location
(
   loctID int unsigned not null auto_increment primary key,
   title_eng char (50) not null,
   title_big5 char (50) not null,
   refDate date not null
) type=InnoDB;
create table if not exists admin
(
  userName char(16) not null primary key,
  password char(40) not null
)type=InnoDB;

grant select, insert, update, delete
on punchun_database.*
to admin_punchun@localhost identified by '96112112';