-- Guardem les dades de la connexió actual
SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT;
SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS;
SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION;
SET NAMES utf8mb4;
SET @OLD_TIME_ZONE=@@TIME_ZONE;
SET TIME_ZONE='+00:00';
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO';
SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0;

SET FOREIGN_KEY_CHECKS=0;

DROP TABLE IF EXISTS WF_WORLD_REGIONS;
DROP TABLE IF EXISTS WF_SPOKEN_LANGUAGES;
DROP TABLE IF EXISTS WF_LANGUAGES;
DROP TABLE IF EXISTS WF_CURRENCIES;
DROP TABLE IF EXISTS WF_COUNTRIES;
create table WF_COUNTRIES
(
  COUNTRY_ID             INT not null,
  REGION_ID              INT,
  COUNTRY_NAME           VARCHAR(200),
  LOCATION               VARCHAR(200),
  CAPITOL                VARCHAR(200),
  AREA                   VARCHAR(200),
  COASTLINE              VARCHAR(200),
  LOWEST_ELEVATION       VARCHAR(100),
  LOWEST_ELEVATION_NAME  VARCHAR(200),
  HIGHEST_ELEVATION      VARCHAR(100),
  HIGHEST_ELEVATION_NAME VARCHAR(200),
  DATE_OF_INDEPENDENCE   VARCHAR(200),
  NATIONAL_HOLIDAY_NAME  VARCHAR(200),
  NATIONAL_HOLIDAY_DATE  VARCHAR(200),
  POPULATION             VARCHAR(200),
  POPULATION_GROWTH_RATE VARCHAR(100),
  LIFE_EXPECT_AT_BIRTH   VARCHAR(200),
  MEDIAN_AGE             VARCHAR(200),
  AIRPORTS               VARCHAR(200),
  CLIMATE                VARCHAR(200),
  FIPS_ID                VARCHAR(200),
  INTERNET_EXTENSION     VARCHAR(200),
  FLAG                   BLOB,
  CURRENCY_CODE          INT,
  EXTERNAL_FLAG          BLOB
);
alter table WF_COUNTRIES
  add constraint COUNTRY_ID primary key (COUNTRY_ID);

create table WF_CURRENCIES
(
  CURRENCY_CODE INT not null,
  CURRENCY_NAME VARCHAR(200),
  COMMENTS      VARCHAR(200)
);
alter table WF_CURRENCIES
  add constraint CURRENCY_CODE primary key (CURRENCY_CODE);
  
create table WF_LANGUAGES
(
  LANGUAGE_ID   INT not null,
  LANGUAGE_NAME VARCHAR(200)
);
alter table WF_LANGUAGES
  add constraint LANGUAGE_ID primary key (LANGUAGE_ID);

create table WF_SPOKEN_LANGUAGES
(
  COUNTRY_ID  INT not null,
  LANGUAGE_ID INT not null,
  OFFICIAL    VARCHAR(200),
  COMMENTS    VARCHAR(200)
);
alter table WF_SPOKEN_LANGUAGES
  add constraint COUNTRY_LANGUAGE_ID primary key (COUNTRY_ID,   LANGUAGE_ID);

create table WF_WORLD_REGIONS
(
  REGION_ID   INT not null,
  REGION_NAME VARCHAR(200)
);
alter table WF_WORLD_REGIONS
  add constraint REGION_ID primary key (REGION_ID);

-- Restaurem les dades de la connexió actual
SET FOREIGN_KEY_CHECKS=1;

SET TIME_ZONE=@OLD_TIME_ZONE;
SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT;
SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS;
SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION;
SET SQL_NOTES=@OLD_SQL_NOTES;
