CREATE TABLE "public"."users" (
	"id" Serial NOT NULL,
	"username" Character Varying( 200 ) NOT NULL,
	"password" Character Varying( 200 ) NOT NULL,
 PRIMARY KEY ( "id" )
 );

CREATE TABLE "public"."plans" (
	"id" Serial NOT NULL,
	"code" Character Varying( 50 ) NOT NULL,
	"name" Character Varying( 200 ) NOT NULL,
	"description" Text NOT NULL,
	"valid_since" Bigint NOT NULL,
	"valid_to" Bigint NOT NULL,
	"duration" Bigint NOT NULL,
	"speed" Integer NOT NULL,
	"speed_code" Character Varying( 10 ) NOT NULL,
	"download" Integer NOT NULL,
	"download_code" Character Varying( 2 ) NOT NULL,
	"allow_downloads" Character( 1 ) DEFAULT '0' NOT NULL,
 PRIMARY KEY ( "id" )
 );

CREATE TABLE "public"."subscribers" (
	"id" Serial NOT NULL,
	"first_name" Character Varying( 200 ) NOT NULL,
	"last_name" Character Varying( 200 ) NOT NULL,
	"phone" Character Varying( 50 ),
	"email" Character Varying( 100 ),
	"date_since" Bigint NOT NULL,
	"date_to" Bigint NOT NULL,
	"mac" Character Varying( 50 ) NOT NULL,
	"account_type" Character Varying( 2044 ) NOT NULL,
 PRIMARY KEY ( "id" )
 );

ALTER TABLE "public"."subscribers" ADD COLUMN "plans_id" Integer NOT NULL;

ALTER TABLE "public"."subscribers"   ADD CONSTRAINT "lnk_plans_subscribers"   FOREIGN KEY ( "plans_id"	) REFERENCES   "public"."plans"  ( "id" ) MATCH FULL ON UPDATE Cascade;


CREATE TABLE "public"."filters" (
	"id" Serial NOT NULL,
	"name" Character Varying( 200 ) NOT NULL,
 PRIMARY KEY ( "id" )
 );

CREATE TABLE "public"."plans_to_filters" (
	"plans_id" Integer NOT NULL,
	"filters_id" Integer NOT NULL,
	"id" Serial NOT NULL,
 PRIMARY KEY ( "id" )
 );

ALTER TABLE "public"."plans_to_filters"   ADD CONSTRAINT "lnk_plans_MM_filters"   FOREIGN KEY ( "plans_id"	) REFERENCES   "public"."plans"  ( "id" ) MATCH FULL ON UPDATE Cascade;

ALTER TABLE "public"."plans_to_filters"   ADD CONSTRAINT "lnk_filters_MM_plans"   FOREIGN KEY ( "filters_id"	) REFERENCES   "public"."filters"  ( "id" ) MATCH FULL ON UPDATE Cascade;


-- Version 18/05/2018

ALTER TABLE "public"."subscribers" ADD COLUMN "subscribers_id" Integer;

ALTER TABLE "public"."subscribers"   ADD CONSTRAINT "lnk_subscribers_subscribers"   FOREIGN KEY ( "subscribers_id"	) REFERENCES   "public"."subscribers"  ( "id" ) MATCH FULL ON UPDATE Cascade;

ALTER TABLE "public"."plans" ALTER COLUMN "description" DROP NOT NULL;

ALTER TABLE "public"."plans" RENAME COLUMN "allow_downloads" TO "allow_recharges";


CREATE TABLE "public"."menus" (
	"id" Serial NOT NULL,
	"name" Character Varying( 2044 ) NOT NULL,
	"icon" Character Varying( 2044 ),
 PRIMARY KEY ( "id" )
 );

ALTER TABLE "public"."menus" ADD COLUMN "menus_id" Integer;

ALTER TABLE "public"."menus"   ADD CONSTRAINT "lnk_menus_menus"   FOREIGN KEY ( "menus_id"	) REFERENCES   "public"."menus"  ( "id" ) MATCH FULL ON UPDATE Cascade;

CREATE TABLE "public"."profiles" (
	"id" Serial NOT NULL,
	"name" Character Varying( 2044 ) NOT NULL,
 PRIMARY KEY ( "id" )
 );

CREATE TABLE "public"."profiles_to_menus" (
	"profiles_id" Integer NOT NULL,
	"menus_id" Integer NOT NULL,
	"id" Serial NOT NULL,
 PRIMARY KEY ( "id" )
 );

ALTER TABLE "public"."profiles_to_menus"   ADD CONSTRAINT "lnk_profiles_MM_menus"   FOREIGN KEY ( "profiles_id"	) REFERENCES   "public"."profiles"  ( "id" ) MATCH FULL ON UPDATE Cascade;

ALTER TABLE "public"."profiles_to_menus"   ADD CONSTRAINT "lnk_menus_MM_profiles"   FOREIGN KEY ( "menus_id"	) REFERENCES   "public"."menus"  ( "id" ) MATCH FULL ON UPDATE Cascade;

CREATE TABLE "public"."users_to_profiles" (
	"users_id" Integer NOT NULL,
	"profiles_id" Integer NOT NULL,
	"id" Serial NOT NULL,
 PRIMARY KEY ( "id" )
 );

ALTER TABLE "public"."users_to_profiles"   ADD CONSTRAINT "lnk_profiles_MM_users"   FOREIGN KEY ( "profiles_id"	) REFERENCES   "public"."profiles"  ( "id" ) MATCH FULL ON UPDATE Cascade;

ALTER TABLE "public"."users_to_profiles"   ADD CONSTRAINT "lnk_users_MM_profiles"   FOREIGN KEY ( "users_id"	) REFERENCES   "public"."users"  ( "id" ) MATCH FULL ON UPDATE Cascade;
