DROP TABLE IF EXISTS "user";

-- DROP SEQUENCE IF EXISTS "user_user_id_seq";
-- CREATE SEQUENCE "user_user_id_seq";


CREATE TABLE "user" (
  "user_id" int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, -- SERIAL PRIMARY KEY
  "user_group_id" int check ("user_group_id" > 0) NOT NULL DEFAULT 1,
  "site_id" int check ("site_id" > 0) NOT NULL DEFAULT 1,
  "username" varchar(60) NOT NULL DEFAULT '',
  "first_name" varchar(32) NOT NULL DEFAULT '',
  "last_name" varchar(32) NOT NULL DEFAULT '',
  "password" varchar(191) NOT NULL DEFAULT '',
  "email" varchar(100) NOT NULL DEFAULT '',
  "phone_number" varchar(32) NOT NULL DEFAULT '',
  "url" varchar(100) NOT NULL DEFAULT '',
  "status" int check ("status" >= 0) NOT NULL DEFAULT 0,
  "display_name" varchar(250) NOT NULL DEFAULT '',
  "avatar" varchar(250) NOT NULL DEFAULT '',
  "cover" varchar(250) NOT NULL DEFAULT '',
  "bio" varchar(250) DEFAULT NULL,
  "token" varchar(32) NOT NULL DEFAULT '',
  "secret" varchar(32) NOT NULL DEFAULT '',
  "subscribe" smallint check ("subscribe" >= 0) NOT NULL DEFAULT 0,
  "last_ip" varchar(40) NOT NULL DEFAULT '',
  "created_at" timestamp(0) NOT NULL DEFAULT now(),
  "updated_at" timestamp(0) NOT NULL DEFAULT now()
  -- PRIMARY KEY("user_id")
);

CREATE INDEX "user_username" ON "user" ("username");
CREATE INDEX "user_email" ON "user" ("email");
CREATE INDEX "user_created_at" ON "user" ("created_at");
SELECT setval('"user_user_id_seq"', 1, true); -- last inserted id by sample data