
-----------------------------------------------------------------------------
-- expedition
-----------------------------------------------------------------------------

DROP TABLE "expedition" CASCADE;


CREATE TABLE "expedition"
(
	"id" serial  NOT NULL,
	"title" VARCHAR(256),
	"updated_at" TIMESTAMP,
	"duration" VARCHAR(256),
	"region" VARCHAR(256),
	"continent_id" INTEGER,
	"is_active" BOOLEAN default 'f',
	"is_on_main" BOOLEAN default 'f',
	"ord" INTEGER default 1,
	"col_left_group_id" INTEGER,
	"col_right_group_id" INTEGER,
	"photo_list_normal" INTEGER,
	"photo_list_sepia" INTEGER,
	"photo_top" INTEGER,
	"bg_lat" VARCHAR(16),
	"bg_lng" VARCHAR(16),
	"bg_zoom" VARCHAR(16),
	"payment_misc" VARCHAR(4096),
	"is_temp" BOOLEAN default 't',
	"map_image" INTEGER,
	"map_big_image" INTEGER,
	"map_code" VARCHAR(4096),
	"gallery_id" INTEGER,
	"thumb_info" VARCHAR(255),
	PRIMARY KEY ("id")
);

COMMENT ON TABLE "expedition" IS '';


SET search_path TO public;
-----------------------------------------------------------------------------
-- expedition_type
-----------------------------------------------------------------------------

DROP TABLE "expedition_type" CASCADE;


CREATE TABLE "expedition_type"
(
	"id" serial  NOT NULL,
	"title" VARCHAR(256),
	"short" VARCHAR(16),
	PRIMARY KEY ("id")
);

COMMENT ON TABLE "expedition_type" IS '';


SET search_path TO public;
-----------------------------------------------------------------------------
-- expedition_details
-----------------------------------------------------------------------------

DROP TABLE "expedition_details" CASCADE;


CREATE TABLE "expedition_details"
(
	"id" serial  NOT NULL,
	"number" VARCHAR(256),
	"date_beginning" DATE,
	"date_finish" DATE,
	"date_descr" VARCHAR(4096),
	"price" DOUBLE PRECISION,
	"price_cur_id" INTEGER,
	"flight_price_range" VARCHAR(4096),
	"flight_price_cur_id" INTEGER,
	"expedition_id" INTEGER,
	PRIMARY KEY ("id")
);

COMMENT ON TABLE "expedition_details" IS '';


SET search_path TO public;
-----------------------------------------------------------------------------
-- expedition_expedition_type
-----------------------------------------------------------------------------

DROP TABLE "expedition_expedition_type" CASCADE;


CREATE TABLE "expedition_expedition_type"
(
	"expedition_id" INTEGER  NOT NULL,
	"expedition_type_id" INTEGER  NOT NULL,
	PRIMARY KEY ("expedition_id","expedition_type_id")
);

COMMENT ON TABLE "expedition_expedition_type" IS '';


SET search_path TO public;
-----------------------------------------------------------------------------
-- expedition_reports
-----------------------------------------------------------------------------

DROP TABLE "expedition_reports" CASCADE;


CREATE TABLE "expedition_reports"
(
	"id" serial  NOT NULL,
	"content" VARCHAR(4096),
	"report_group_id" INTEGER  NOT NULL,
	"gallery_id" INTEGER,
	"expedition_id" INTEGER  NOT NULL,
	"show_button" BOOLEAN default 'f',
	"photo_top" INTEGER,
	PRIMARY KEY ("id")
);

COMMENT ON TABLE "expedition_reports" IS '';


SET search_path TO public;
-----------------------------------------------------------------------------
-- static_text
-----------------------------------------------------------------------------

DROP TABLE "static_text" CASCADE;


CREATE TABLE "static_text"
(
	"id" serial  NOT NULL,
	"title" VARCHAR(256),
	"content" VARCHAR(16384),
	"is_standalone" BOOLEAN default 'f',
	"stripped_title" VARCHAR(256),
	"ord" INTEGER,
	"static_text_group_id" INTEGER,
	"author" VARCHAR(256),
	PRIMARY KEY ("id")
);

COMMENT ON TABLE "static_text" IS '';


SET search_path TO public;
-----------------------------------------------------------------------------
-- static_text_group
-----------------------------------------------------------------------------

DROP TABLE "static_text_group" CASCADE;


CREATE TABLE "static_text_group"
(
	"id" serial  NOT NULL,
	"title" VARCHAR(256),
	"is_standalone" BOOLEAN default 'f',
	PRIMARY KEY ("id")
);

COMMENT ON TABLE "static_text_group" IS '';


SET search_path TO public;
-----------------------------------------------------------------------------
-- feed
-----------------------------------------------------------------------------

DROP TABLE "feed" CASCADE;


CREATE TABLE "feed"
(
	"id" serial  NOT NULL,
	"link" VARCHAR(256),
	"content" VARCHAR(16384),
	"date" TIMESTAMP,
	"obtained" TIMESTAMP,
	"feed_type_id" INTEGER  NOT NULL,
	"media_link" VARCHAR(512),
	"visible" BOOLEAN default 't',
	PRIMARY KEY ("id")
);

COMMENT ON TABLE "feed" IS '';


SET search_path TO public;
-----------------------------------------------------------------------------
-- file
-----------------------------------------------------------------------------

DROP TABLE "file" CASCADE;


CREATE TABLE "file"
(
	"id" serial  NOT NULL,
	"name" VARCHAR(256),
	"assoc_files" VARCHAR(1024),
	"caption" VARCHAR(256),
	"comment" VARCHAR(256),
	"mime" VARCHAR(256),
	"imxs" INTEGER,
	"imys" INTEGER,
	PRIMARY KEY ("id")
);

COMMENT ON TABLE "file" IS '';


SET search_path TO public;
-----------------------------------------------------------------------------
-- gallery
-----------------------------------------------------------------------------

DROP TABLE "gallery" CASCADE;


CREATE TABLE "gallery"
(
	"id" serial  NOT NULL,
	"created_at" TIMESTAMP,
	"title" VARCHAR(256),
	"ord" VARCHAR(1024),
	PRIMARY KEY ("id")
);

COMMENT ON TABLE "gallery" IS '';


SET search_path TO public;
-----------------------------------------------------------------------------
-- gallery_file
-----------------------------------------------------------------------------

DROP TABLE "gallery_file" CASCADE;


CREATE TABLE "gallery_file"
(
	"gallery_id" INTEGER  NOT NULL,
	"file_id" INTEGER  NOT NULL,
	PRIMARY KEY ("gallery_id","file_id")
);

COMMENT ON TABLE "gallery_file" IS '';


SET search_path TO public;
-----------------------------------------------------------------------------
-- continent
-----------------------------------------------------------------------------

DROP TABLE "continent" CASCADE;


CREATE TABLE "continent"
(
	"id" serial  NOT NULL,
	"title" VARCHAR(256),
	"bg_lat" VARCHAR(16),
	"bg_lng" VARCHAR(16),
	"bg_zoom" VARCHAR(16),
	PRIMARY KEY ("id")
);

COMMENT ON TABLE "continent" IS '';


SET search_path TO public;
-----------------------------------------------------------------------------
-- currency
-----------------------------------------------------------------------------

DROP TABLE "currency" CASCADE;


CREATE TABLE "currency"
(
	"id" serial  NOT NULL,
	"title" VARCHAR(32),
	"exchange" FLOAT,
	PRIMARY KEY ("id")
);

COMMENT ON TABLE "currency" IS '';


SET search_path TO public;
-----------------------------------------------------------------------------
-- feed_type
-----------------------------------------------------------------------------

DROP TABLE "feed_type" CASCADE;


CREATE TABLE "feed_type"
(
	"id" serial  NOT NULL,
	"short" VARCHAR(8),
	PRIMARY KEY ("id")
);

COMMENT ON TABLE "feed_type" IS '';


SET search_path TO public;
-----------------------------------------------------------------------------
-- newsletter_subscriptions
-----------------------------------------------------------------------------

DROP TABLE "newsletter_subscriptions" CASCADE;


CREATE TABLE "newsletter_subscriptions"
(
	"id" serial  NOT NULL,
	"email" VARCHAR(128),
	"confirmation_hash" VARCHAR(128)  NOT NULL,
	"unsubscribe_hash" VARCHAR(40)  NOT NULL,
	"is_confirmed" BOOLEAN default 'f',
	PRIMARY KEY ("id"),
	CONSTRAINT "newsletter_subscriptions_U_1" UNIQUE ("email")
);

COMMENT ON TABLE "newsletter_subscriptions" IS '';


SET search_path TO public;
-----------------------------------------------------------------------------
-- contact
-----------------------------------------------------------------------------

DROP TABLE "contact" CASCADE;


CREATE TABLE "contact"
(
	"id" serial  NOT NULL,
	"subject" VARCHAR(100),
	"date" VARCHAR(100),
	"name" VARCHAR(100),
	"email" VARCHAR(100),
	"phone" VARCHAR(50),
	"message" VARCHAR(4000),
	"created_at" TIMESTAMP,
	PRIMARY KEY ("id")
);

COMMENT ON TABLE "contact" IS '';


SET search_path TO public;
-----------------------------------------------------------------------------
-- setting
-----------------------------------------------------------------------------

DROP TABLE "setting" CASCADE;


CREATE TABLE "setting"
(
	"name" VARCHAR(32)  NOT NULL,
	"value" VARCHAR(256),
	PRIMARY KEY ("name"),
	CONSTRAINT "setting_U_1" UNIQUE ("name")
);

COMMENT ON TABLE "setting" IS '';


SET search_path TO public;
ALTER TABLE "expedition" ADD CONSTRAINT "expedition_FK_1" FOREIGN KEY ("continent_id") REFERENCES "continent" ("id");

ALTER TABLE "expedition" ADD CONSTRAINT "expedition_FK_2" FOREIGN KEY ("col_left_group_id") REFERENCES "static_text_group" ("id");

ALTER TABLE "expedition" ADD CONSTRAINT "expedition_FK_3" FOREIGN KEY ("col_right_group_id") REFERENCES "static_text_group" ("id");

ALTER TABLE "expedition" ADD CONSTRAINT "expedition_FK_4" FOREIGN KEY ("photo_list_normal") REFERENCES "file" ("id") ON DELETE SET NULL;

ALTER TABLE "expedition" ADD CONSTRAINT "expedition_FK_5" FOREIGN KEY ("photo_list_sepia") REFERENCES "file" ("id") ON DELETE SET NULL;

ALTER TABLE "expedition" ADD CONSTRAINT "expedition_FK_6" FOREIGN KEY ("photo_top") REFERENCES "file" ("id") ON DELETE SET NULL;

ALTER TABLE "expedition" ADD CONSTRAINT "expedition_FK_7" FOREIGN KEY ("map_image") REFERENCES "file" ("id") ON DELETE SET NULL;

ALTER TABLE "expedition" ADD CONSTRAINT "expedition_FK_8" FOREIGN KEY ("map_big_image") REFERENCES "file" ("id") ON DELETE SET NULL;

ALTER TABLE "expedition" ADD CONSTRAINT "expedition_FK_9" FOREIGN KEY ("gallery_id") REFERENCES "gallery" ("id") ON DELETE SET NULL;

ALTER TABLE "expedition_details" ADD CONSTRAINT "expedition_details_FK_1" FOREIGN KEY ("price_cur_id") REFERENCES "currency" ("id");

ALTER TABLE "expedition_details" ADD CONSTRAINT "expedition_details_FK_2" FOREIGN KEY ("flight_price_cur_id") REFERENCES "currency" ("id");

ALTER TABLE "expedition_details" ADD CONSTRAINT "expedition_details_FK_3" FOREIGN KEY ("expedition_id") REFERENCES "expedition" ("id") ON DELETE CASCADE;

ALTER TABLE "expedition_expedition_type" ADD CONSTRAINT "expedition_expedition_type_FK_1" FOREIGN KEY ("expedition_id") REFERENCES "expedition" ("id") ON DELETE CASCADE;

ALTER TABLE "expedition_expedition_type" ADD CONSTRAINT "expedition_expedition_type_FK_2" FOREIGN KEY ("expedition_type_id") REFERENCES "expedition_type" ("id") ON DELETE CASCADE;

ALTER TABLE "expedition_reports" ADD CONSTRAINT "expedition_reports_FK_1" FOREIGN KEY ("report_group_id") REFERENCES "static_text_group" ("id") ON DELETE CASCADE;

ALTER TABLE "expedition_reports" ADD CONSTRAINT "expedition_reports_FK_2" FOREIGN KEY ("gallery_id") REFERENCES "gallery" ("id") ON DELETE SET NULL;

ALTER TABLE "expedition_reports" ADD CONSTRAINT "expedition_reports_FK_3" FOREIGN KEY ("expedition_id") REFERENCES "expedition" ("id") ON DELETE CASCADE;

ALTER TABLE "expedition_reports" ADD CONSTRAINT "expedition_reports_FK_4" FOREIGN KEY ("photo_top") REFERENCES "file" ("id") ON DELETE SET NULL;

ALTER TABLE "static_text" ADD CONSTRAINT "static_text_FK_1" FOREIGN KEY ("static_text_group_id") REFERENCES "static_text_group" ("id");

ALTER TABLE "feed" ADD CONSTRAINT "feed_FK_1" FOREIGN KEY ("feed_type_id") REFERENCES "feed_type" ("id");

ALTER TABLE "gallery_file" ADD CONSTRAINT "gallery_file_FK_1" FOREIGN KEY ("gallery_id") REFERENCES "gallery" ("id") ON DELETE CASCADE;

ALTER TABLE "gallery_file" ADD CONSTRAINT "gallery_file_FK_2" FOREIGN KEY ("file_id") REFERENCES "file" ("id") ON DELETE CASCADE;
