DROP TABLE IF EXISTS continents;

CREATE TABLE continents (
  ContinentCode TEXT PRIMARY KEY,
  ContinentName TEXT,
  GeoNameId INTEGER
);

DROP TABLE IF EXISTS countries;

CREATE TABLE countries (
  ISO TEXT PRIMARY KEY,
  ISONumeric TEXT,
  CountryName TEXT,
  Capital TEXT,
  CityCode INTEGER,
  Area TEXT,
  Population INTEGER,
  Continent TEXT,
  TopLevelDomain TEXT,
  CurrencyCode TEXT,
  CurrencyName TEXT,
  PhoneCountryCode TEXT,
  Languages TEXT,
  Neighbours TEXT,
  CountryDescription TEXT
);

DROP TABLE IF EXISTS cities;

CREATE TABLE cities (
  CityCode INTEGER PRIMARY KEY,
  AsciiName TEXT,
  CountryCodeISO TEXT,
  Latitude REAL,
  Longitude REAL,
  Population INTEGER,
  Elevation INTEGER,
  TimeZone TEXT
);

DROP TABLE IF EXISTS languages;

CREATE TABLE languages (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT,
  iso TEXT
);

DROP TABLE IF EXISTS users;

CREATE TABLE users (
  UserID INTEGER PRIMARY KEY,
  FirstName TEXT,
  LastName TEXT,
  Address TEXT,
  City TEXT,
  Region TEXT,
  Country TEXT,
  Postal TEXT,
  Phone TEXT,
  Email TEXT,
  Privacy TEXT
);

DROP TABLE IF EXISTS userslogin;

CREATE TABLE userslogin (
  UserID INTEGER PRIMARY KEY AUTOINCREMENT,
  UserName TEXT,
  Password TEXT,
  Salt TEXT,
  Password_sha256 TEXT,
  State INTEGER,
  DateJoined TEXT,
  DateLastModified TEXT
);

DROP TABLE IF EXISTS imagedetails;

CREATE TABLE imagedetails (
  ImageID INTEGER PRIMARY KEY AUTOINCREMENT,
  UserID INTEGER DEFAULT 0,
  Title TEXT,
  Description TEXT,
  Latitude REAL,
  Longitude REAL,
  CityCode INTEGER,
  CountryCodeISO TEXT,
  ContinentCode TEXT,
  Path TEXT,
  Exif TEXT,
  ActualCreator TEXT,
  CreatorURL TEXT,
  SourceURL TEXT,
  Colors TEXT
);

DROP TABLE IF EXISTS imagerating;

CREATE TABLE imagerating (
  ImageRatingID INTEGER PRIMARY KEY AUTOINCREMENT,
  ImageID INTEGER,
  Rating INTEGER
);

DROP TABLE IF EXISTS posts;

CREATE TABLE posts (
  PostID INTEGER PRIMARY KEY AUTOINCREMENT,
  UserID INTEGER,
  MainPostImage INTEGER DEFAULT 0,
  Title TEXT,
  Message TEXT,
  PostTime TEXT
);

DROP TABLE IF EXISTS postimages;

CREATE TABLE postimages (
  ImageID INTEGER NOT NULL,
  PostID INTEGER NOT NULL,
  PRIMARY KEY (ImageID, PostID)
);