BEGIN;

CREATE TABLE users_be (
  id serial,
  company varchar(255),
  lastname varchar(255) not null,
  firstname varchar(255) not null,
  email varchar(255) not null,
  role integer not null default 1,
  roles varchar(255),
  password char(32),
  store integer,
  created_at datetime not null,
  updated_at datetime,
  deleted_at datetime,
  primary key (id)
) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8 COLLATE=utf8_bin;

CREATE TABLE menus (
  id serial,
  lang char(2),
  slug varchar(255),
  content text,
  published integer not null default 1,
  published_datetime datetime,
  published_datetime2 datetime,
  created_at datetime not null,
  updated_at datetime,
  deleted_at datetime,
  primary key (id)
) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8 COLLATE=utf8_bin;

CREATE TABLE redirects (
  id integer not null auto_increment,
  request_uri varchar(255),
  redirect_uri varchar(255),
  created_at datetime not null,
  updated_at datetime,
  deleted_at datetime,
  primary key (id)
) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8 COLLATE=utf8_bin;

CREATE TABLE pages (
  id serial,
  lang char(2),
  title varchar(1024),
  subtitle varchar(1024),
  hero varchar(255),
  header varchar(255),
  slug varchar(255),
  content text,
  published integer not null default 1,
  published_datetime datetime,
  published_datetime2 datetime,
  seo_title text,
  seo_image varchar(255),
  seo_description text,
  seo_keywords text,
  created_at datetime not null,
  updated_at datetime,
  deleted_at datetime,
  primary key (id)
) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8 COLLATE=utf8_bin;

CREATE TABLE blog_categories (
  id serial,
  lang char(2),
  category_id integer,
  slug varchar(255) not null,
  title varchar(255) not null,
  image_list varchar(255),
  image_cover varchar(255),
  description text,
  color varchar(255),
  published integer not null default 1,
  published_datetime datetime,
  published_datetime2 datetime,
  seo_title text,
  seo_image varchar(255),
  seo_description text,
  seo_keywords text,
  created_at datetime not null,
  updated_at datetime,
  deleted_at datetime,
  primary key (id)
) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8 COLLATE=utf8_bin;

CREATE TABLE blog_articles (
  id serial,
  lang char(2),
  category_id bigint(20) unsigned not null,
  slug varchar(255) not null,
  title varchar(255) not null,
  date date,
  image_list varchar(255),
  image_cover varchar(255),
  video varchar(255),
  author varchar(255),
  description text,
  ingredients text,
  content text,
  images text,
  stores text,
  published integer not null default 1,
  published_datetime datetime,
  published_datetime2 datetime,
  homepage integer,
  focus_magazine integer,
  focus_prodotti integer,
  focus varchar(255),
  requires_login integer,
  related_1 bigint(20),
  related_2 bigint(20),
  related_3 bigint(20),
  seo_title text,
  seo_image varchar(255),
  seo_description text,
  seo_keywords text,
  created_at datetime not null,
  updated_at datetime,
  deleted_at datetime,
  primary key (id),
  foreign key (category_id) references blog_categories (id) on delete cascade on update cascade
) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8 COLLATE=utf8_bin;

CREATE TABLE users (
  id serial,
  uniqid char(13),
  company varchar(255),
  firstname varchar(255),
  lastname varchar(255),
  email varchar(255),
  password char(64),
  address varchar(1024),
  address_no varchar(256),
  city varchar(255),
  province char(2),
  zipcode char(5),
  country varchar(255),
  phone varchar(255),
  mobile varchar(255),
  birthday date,
  gender char(1),
  active integer,
  privacy integer,
  privacy1 integer,
  privacy2 integer,
  privacy3 integer,
  newsletter integer,
  store integer,
  data text,
  card varchar(255),
  created_at datetime not null,
  updated_at datetime,
  deleted_at datetime,
  primary key (id)
) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8 COLLATE=utf8_bin;

CREATE TABLE stores (
  id serial,
  slug varchar(255) not null,
  title varchar(255) not null,
  type integer,
  address varchar(255),
  zipcode char(5),
  city varchar(255),
  province char(2),
  phone varchar(255),
  email varchar(255),
  lat numeric(20,8),
  lng numeric(20,8),
  openings_1 varchar(255),
  openings_2 varchar(255),
  openings_3 varchar(255),
  openings_4 varchar(255),
  openings_5 varchar(255),
  openings_6 varchar(255),
  openings_7 varchar(255),
  services text,
  image_1 varchar(255),
  image_2 varchar(255),
  image_3 varchar(255),
  image_4 varchar(255),
  image_5 varchar(255),
  created_at datetime not null,
  updated_at datetime,
  deleted_at datetime,
  primary key (id)
) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8 COLLATE=utf8_bin;

CREATE TABLE events (
  id serial,
  lang char(2),
  slug varchar(255) not null,
  title varchar(255) not null,
  image varchar(255),
  description text,
  stores text,
  published integer not null default 1,
  published_datetime datetime,
  published_datetime2 datetime,
  created_at datetime not null,
  updated_at datetime,
  deleted_at datetime,
  primary key (id)
) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8 COLLATE=utf8_bin;

CREATE TABLE promotions (
  id serial,
  lang char(2),
  slug varchar(255) not null,
  title varchar(255) not null,
  date date,
  image varchar(255),
  image_small varchar(255),
  description text,
  stores text,
  published integer not null default 1,
  published_datetime datetime,
  published_datetime2 datetime,
  seo_title text,
  seo_image varchar(255),
  seo_description text,
  seo_keywords text,
  created_at datetime not null,
  updated_at datetime,
  deleted_at datetime,
  primary key (id)
) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8 COLLATE=utf8_bin;

CREATE TABLE products_categories (
  id serial,
  lang char(2),
  slug varchar(255) not null,
  title varchar(255) not null,
  image_list varchar(255),
  image_cover varchar(255),
  description text,
  published integer not null default 1,
  published_datetime datetime,
  published_datetime2 datetime,
  seo_title text,
  seo_image varchar(255),
  seo_description text,
  seo_keywords text,
  created_at datetime not null,
  updated_at datetime,
  deleted_at datetime,
  primary key (id)
) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8 COLLATE=utf8_bin;

CREATE TABLE products (
  id serial,
  lang char(2),
  category_id bigint(20) unsigned not null,
  promotion_id bigint(20),
  slug varchar(255) not null,
  title varchar(255) not null,
  image_list varchar(255),
  image_cover varchar(255),
  description text,
  price numeric(20,2),
  price_old numeric(20,2),
  price_discount integer,
  images text,
  published integer not null default 1,
  published_datetime datetime,
  published_datetime2 datetime,
  seo_title text,
  seo_image varchar(255),
  seo_description text,
  seo_keywords text,
  created_at datetime not null,
  updated_at datetime,
  deleted_at datetime,
  primary key (id),
  foreign key (category_id) references products_categories (id) on delete cascade on update cascade
) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8 COLLATE=utf8_bin;

CREATE TABLE banners (
  id serial,
  title varchar(255),
  image varchar(255),
  navigator varchar(255),
  link varchar(1024),
  category integer,
  sorting integer,
  published integer,
  date_begin date,
  date_end date,
  created_at datetime not null,
  updated_at datetime,
  deleted_at datetime,
  primary key (id)
) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8 COLLATE=utf8_bin;

CREATE TABLE users_favourites (
  id serial,
  user_id bigint(20) unsigned not null,
  content_type varchar(255),
  content_id integer,
  created_at datetime not null,
  updated_at datetime,
  deleted_at datetime,
  primary key (id),
  foreign key (user_id) references users (id) on delete cascade on update cascade
) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8 COLLATE=utf8_bin;

CREATE TABLE users_shopping_list (
  id serial,
  user_id bigint(20) unsigned not null,
  title varchar(255),
  created_at datetime not null,
  updated_at datetime,
  deleted_at datetime,
  primary key (id),
  foreign key (user_id) references users (id) on delete cascade on update cascade
) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8 COLLATE=utf8_bin;

CREATE TABLE search (
  id serial,
  id_type varchar(255),
  id_ext integer,
  url varchar(255),
  title varchar(255),
  body text,
  created_at datetime,
  updated_at datetime,
  deleted_at datetime,
  primary key (id),
  FULLTEXT (title, body)
) ENGINE=MyISAM;

CREATE TABLE flyers (
  id serial,
  slug varchar(255) not null,
  title varchar(255) not null,
  source varchar(255),
  code varchar(255),
  thumb varchar(255),
  type varchar(255),
  stores text,
  line integer,
  pdf varchar(255),
  link varchar(255),
  display integer,
  flipping_book varchar(255),
  date_publishing date,
  date_begin date,
  date_end date,
  created_at datetime not null,
  updated_at datetime,
  deleted_at datetime,
  primary key (id)
) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8 COLLATE=utf8_bin;

CREATE TABLE flyers_products (
  id serial,
  flyer_id bigint(20) unsigned not null,
  category varchar(255),
  subcategory varchar(255),
  code varchar(255),
  title varchar(255),
  brand varchar(255),
  description text,
  offer_price numeric(20,2),
  offer_price_old numeric(20,2),
  offer_discount integer,
  offer_multi varchar(255),
  offer_type char(2),
  notes varchar(255),
  ean varchar(255),
  stores varchar(1024),
  stores_card varchar(1024),
  label varchar(255),
  label2 varchar(255),
  date_begin date,
  date_end date,
  image_thumb varchar(255),
  image_full varchar(255),
  below_cost integer,
  homepage integer,
  created_at datetime not null,
  updated_at datetime,
  deleted_at datetime,
  primary key (id),
  foreign key (flyer_id) references flyers (id) on delete cascade on update cascade
) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8 COLLATE=utf8_bin;

CREATE TABLE wslog (
  id serial,
  card varchar(255),
  request text,
  response text,
  created_at datetime not null,
  primary key (id)
) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8 COLLATE=utf8_bin;

CREATE TABLE beacons (
  id serial,
  name varchar(255),
  uuid varchar(255),
  identifier varchar(255),
  major integer,
  minor integer,
  event_enter_region text,
  event_exit_region text,
  event_did_range text,
  created_at datetime not null,
  updated_at datetime,
  deleted_at datetime,
  primary key (id)
) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8 COLLATE=utf8_bin;

CREATE TABLE notifications (
  id serial,
  title varchar(255),
  target integer,
  target_beacon_id integer,
  target_filter varchar(255),
  target_datetime datetime,
  target_status integer,
  logo varchar(256),
  header varchar(256),
  body text,
  message varchar(1024),
  data text,
  status integer,
  created_at datetime not null,
  updated_at datetime,
  deleted_at datetime,
  primary key (id)
) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8 COLLATE=utf8_bin;

COMMIT;
