--
-- Conver PostgreSQL to  MySQL
--
CREATE DATABASE IF NOT EXISTS northwind;

---
--- drop tables
---
DROP TABLE IF EXISTS customer_customer_demo;
DROP TABLE IF EXISTS customer_demographics;
DROP TABLE IF EXISTS employee_territories;
DROP TABLE IF EXISTS order_details;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS customers;
DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS shippers;
DROP TABLE IF EXISTS suppliers;
DROP TABLE IF EXISTS territories;
DROP TABLE IF EXISTS us_states;
DROP TABLE IF EXISTS categories;
DROP TABLE IF EXISTS region;
DROP TABLE IF EXISTS employees;

--
-- Name: categories; 
--
CREATE TABLE categories (
    category_id smallint NOT NULL,
    category_name varchar(15) NOT NULL,
    description text,
    picture blob
);

--
-- Name: customer_customer_demo;
--
CREATE TABLE customer_customer_demo (
    customer_id varchar(100) NOT NULL,
    customer_type_id varchar(100) NOT NULL
);


--
-- Name: customer_demographics; 
--
CREATE TABLE customer_demographics (
    customer_type_id varchar(100) NOT NULL,
    customer_desc text
);

--
-- Name: customers; 
--
CREATE TABLE customers (
    customer_id varchar(100) NOT NULL,
    company_name varchar(40) NOT NULL,
    contact_name varchar(30),
    contact_title varchar(30),
    address varchar(60),
    city varchar(15),
    region varchar(15),
    postal_code varchar(10),
    country varchar(15),
    phone varchar(24),
    fax varchar(24)
);

--
-- Name: employees; 
--
CREATE TABLE employees (
    employee_id smallint NOT NULL,
    last_name varchar(20) NOT NULL,
    first_name varchar(10) NOT NULL,
    title varchar(30),
    title_of_courtesy varchar(25),
    birth_date date,
    hire_date date,
    address varchar(60),
    city varchar(15),
    region varchar(15),
    postal_code varchar(10),
    country varchar(15),
    home_phone varchar(24),
    extension varchar(4),
    photo blob,
    notes text,
    reports_to smallint,
    photo_path varchar(255)
);

--
-- Name: employee_territories; 
--
CREATE TABLE employee_territories (
    employee_id smallint NOT NULL,
    territory_id varchar(20) NOT NULL
);

--
-- Name: order_details; 
--
CREATE TABLE order_details (
    order_id smallint NOT NULL,
    product_id smallint NOT NULL,
    unit_price real NOT NULL,
    quantity smallint NOT NULL,
    discount real NOT NULL
);

--
-- Name: orders; 
--
CREATE TABLE orders (
    order_id smallint NOT NULL,
    customer_id varchar(100),
    employee_id smallint,
    order_date date,
    required_date date,
    shipped_date date,
    ship_via smallint,
    freight real,
    ship_name varchar(40),
    ship_address varchar(60),
    ship_city varchar(15),
    ship_region varchar(15),
    ship_postal_code varchar(10),
    ship_country varchar(15)
);

--
-- Name: products; 
--
CREATE TABLE products (
    product_id smallint NOT NULL,
    product_name varchar(40) NOT NULL,
    supplier_id smallint,
    category_id smallint,
    quantity_per_unit varchar(20),
    unit_price real,
    units_in_stock smallint,
    units_on_order smallint,
    reorder_level smallint,
    discontinued integer NOT NULL
);

--
-- Name: region; 
--
CREATE TABLE region (
    region_id smallint NOT NULL,
    region_description varchar(100) NOT NULL
);

--
-- Name: shippers; 
--
CREATE TABLE shippers (
    shipper_id smallint NOT NULL,
    company_name varchar(40) NOT NULL,
    phone varchar(24)
);


--
-- Name: suppliers; 
--
CREATE TABLE suppliers (
    supplier_id smallint NOT NULL,
    company_name varchar(40) NOT NULL,
    contact_name varchar(30),
    contact_title varchar(30),
    address varchar(60),
    city varchar(15),
    region varchar(15),
    postal_code varchar(10),
    country varchar(15),
    phone varchar(24),
    fax varchar(24),
    homepage text
);

--
-- Name: territories; 
--
CREATE TABLE territories (
    territory_id varchar(20) NOT NULL,
    territory_description varchar(100) NOT NULL,
    region_id smallint NOT NULL
);

--
-- Name: us_states;  
--
CREATE TABLE us_states (
    state_id smallint NOT NULL,
    state_name varchar(100),
    state_abbr varchar(2),
    state_region varchar(50)
);

--
-- Name: pk_categories; 
--
ALTER TABLE categories
    ADD CONSTRAINT  PRIMARY KEY pk_categories(category_id);

--
-- Name: pk_customer_customer_demo; 
--
ALTER TABLE customer_customer_demo
    ADD PRIMARY KEY pk_customer_customer_demo(customer_id, customer_type_id);

--
-- Name: pk_customer_demographics;
--
ALTER TABLE customer_demographics
    ADD PRIMARY KEY pk_customer_demographics(customer_type_id);

--
-- Name: pk_customers;
--
ALTER TABLE customers
    ADD PRIMARY KEY pk_customers(customer_id);

--
-- Name: pk_employees;  
--
ALTER TABLE employees
    ADD  PRIMARY KEY pk_employees(employee_id);

--
-- Name: pk_employee_territories; 
--
ALTER TABLE employee_territories
    ADD  PRIMARY KEY pk_employee_territories(employee_id, territory_id);

--
-- Name: pk_order_details;
--
ALTER TABLE order_details
    ADD  PRIMARY KEY pk_order_details(order_id, product_id);

--
-- Name: pk_orders; 
--
ALTER TABLE orders
    ADD PRIMARY KEY pk_orders(order_id);

--
-- Name: pk_products; 
--
ALTER TABLE products
    ADD  PRIMARY KEY pk_products(product_id);

--
-- Name: pk_region; 
--
ALTER TABLE region
    ADD PRIMARY KEY  pk_region(region_id);


--
-- Name: pk_shippers; 
--
ALTER TABLE shippers
    ADD PRIMARY KEY pk_shippers(shipper_id);

--
-- Name: pk_suppliers; 
--
ALTER TABLE suppliers
    ADD  PRIMARY KEY pk_suppliers(supplier_id);

--
-- Name: pk_territories; 
--
ALTER TABLE territories
    ADD  PRIMARY KEY pk_territories(territory_id);

--
-- Name: pk_usstates;  
--
ALTER TABLE us_states
    ADD  PRIMARY KEY pk_usstates(state_id);