147 lines
4.0 KiB
SQL
147 lines
4.0 KiB
SQL
CREATE TABLE users(
|
|
"id" serial primary key not null,
|
|
"email" varchar unique,
|
|
"username" varchar unique not null,
|
|
"password" varchar not null,
|
|
"avatar_picture" varchar,
|
|
"google_sign_in_payload" varchar,
|
|
"banned_at" timestamp,
|
|
"banned_until" timestamp,
|
|
"ban_reason" varchar,
|
|
"is_permaban" boolean default(false),
|
|
"is_admin" boolean default(false),
|
|
"is_critics" boolean default(false),
|
|
"is_verified" boolean default(false),
|
|
"is_active" boolean default(true),
|
|
"social_media" jsonb,
|
|
"created_at" timestamp default(now()),
|
|
"updated_at" timestamp default(now())
|
|
);
|
|
|
|
CREATE TABLE client_ips(
|
|
"id" serial primary key not null,
|
|
"ipv4" varchar(15) not null,
|
|
"ipv6" varchar(40),
|
|
"banned_at" timestamp,
|
|
"banned_until" timestamp,
|
|
"reason" varchar,
|
|
"is_permaban" boolean,
|
|
"created_at" timestamp default(now()),
|
|
"updated_at" timestamp default(now())
|
|
);
|
|
|
|
|
|
CREATE TYPE user_reports_type as ENUM(
|
|
'comments',
|
|
'reviews',
|
|
'locations',
|
|
'users',
|
|
'stories'
|
|
);
|
|
|
|
CREATE TABLE user_reports(
|
|
"id" serial primary key not null,
|
|
"message" text not null,
|
|
"date" timestamp not null,
|
|
"report_target" integer not null,
|
|
"report_type" user_reports_type not null,
|
|
"submitted_by" integer references "users"("id") not null,
|
|
"created_at" timestamp default(now()),
|
|
"updated_at" timestamp default(now())
|
|
);
|
|
|
|
|
|
CREATE TABLE regions(
|
|
"id" serial primary key not null,
|
|
"region_name" varchar not null,
|
|
"created_at" timestamp default(now()),
|
|
"updated_at" timestamp default(now())
|
|
);
|
|
|
|
CREATE TABLE provinces(
|
|
"id" serial primary key not null,
|
|
"province_name" varchar not null,
|
|
"region_id" smallint references "regions"("id") not null,
|
|
"created_at" timestamp default(now()),
|
|
"updated_at" timestamp default(now())
|
|
);
|
|
|
|
CREATE TABLE regencies(
|
|
"id" serial primary key not null,
|
|
"regency_name" varchar not null,
|
|
"province_id" smallint references "provinces"("id") not null,
|
|
"created_at" timestamp default(now()),
|
|
"updated_at" timestamp default(now())
|
|
);
|
|
|
|
CREATE TYPE location_type AS ENUM(
|
|
'beach',
|
|
'amusement park',
|
|
'culinary',
|
|
'hiking / camping',
|
|
'other'
|
|
);
|
|
|
|
CREATE TABLE locations(
|
|
"id" serial primary key not null,
|
|
"address" varchar not null,
|
|
"name" varchar unique not null,
|
|
"google_maps_link" varchar,
|
|
"location_type" location_type not null,
|
|
"submitted_by" integer references "users"("id") not null,
|
|
"total_visited" integer,
|
|
"thumbnail" varchar,
|
|
"regency_id" smallint references "regencies"("id") not null,
|
|
"is_deleted" boolean not null,
|
|
"created_at" timestamp default(now()),
|
|
"updated_at" timestamp default(now())
|
|
);
|
|
|
|
CREATE TABLE tags (
|
|
"id" serial primary key not null,
|
|
"name" varchar(50) not null,
|
|
"submitted_by" integer references "users"("id") not null,
|
|
"target_id" integer not null, -- location_id, story_id
|
|
"tags_type" varchar(20) not null, -- locations, stories
|
|
"approved_by" integer references "users"("id")
|
|
);
|
|
|
|
CREATE TABLE location_images (
|
|
"id" serial primary key not null,
|
|
"url" varchar not null,
|
|
"location_id" integer references "locations"("id") not null,
|
|
"uploaded_by" integer references "users"("id"),
|
|
"created_at" timestamp default(now()),
|
|
"updated_at" timestamp default(now())
|
|
);
|
|
|
|
CREATE TABLE reviews (
|
|
"id" serial primary key not null,
|
|
"submitted_by" integer references "users"("id") not null,
|
|
"comments" text not null,
|
|
"score" smallint not null,
|
|
"is_from_critic" boolean not null,
|
|
"cost_approx" integer,
|
|
"is_hided" boolean not null default(false), -- if comments violate TOS just hide the reviews
|
|
"location_id" integer references "locations"("id") not null,
|
|
"created_at" timestamp default(now()),
|
|
"updated_at" timestamp default(now())
|
|
);
|
|
|
|
CREATE TYPE comment_type AS ENUM(
|
|
'stories',
|
|
'news',
|
|
'reviews',
|
|
'locations'
|
|
);
|
|
|
|
CREATE TABLE comments(
|
|
"id" serial primary key not null,
|
|
"submitted_by" integer not null,
|
|
"comment_on" integer not null,
|
|
"comment_type" comment_type not null,
|
|
"reply_to" integer,
|
|
"is_hide" boolean,
|
|
"created_at" timestamp default(now()),
|
|
"updated_at" timestamp default(now())
|
|
); |