Please Check any errors

--
-- Create Schema

CREATE SCHEMA blog;

-- Table structure for table `user`
--

DROP TABLE IF EXISTS blog.user;
CREATE TABLE blog.user (
  id SERIAL PRIMARY KEY,
  firstName varchar(50) DEFAULT NULL,
  middleName varchar(50) DEFAULT NULL,
  lastName varchar(50) DEFAULT NULL,
  mobile varchar(15) UNIQUE,
  email varchar(50) UNIQUE,
  passwordHash varchar(32) NOT NULL,
  registeredAt timestamp NOT NULL,
  lastLogin timestamp DEFAULT NULL,
  intro text,
  profile text
);


DROP TABLE IF EXISTS blog.post;
CREATE TABLE blog.post (
  id SERIAL PRIMARY KEY,
  -- BIGINT is a data type used to store 8-byte signed integers
  -- from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807
  author_id BIGINT NOT NULL,
  parent_id BIGINT,
  title VARCHAR(75) NOT NULL,
  meta_title VARCHAR(100),
  slug VARCHAR(100) NOT NULL,
  summary TEXT,
  published BOOLEAN NOT NULL DEFAULT FALSE,
  created_at TIMESTAMP NOT NULL,
  updated_at TIMESTAMP,
  published_at TIMESTAMP,
  content TEXT,
  -- adds a foreign key to the parent_id column in the current table, referencing the id column in the post table
  -- This ensures that the values in the parent_id column in the current table always exist in the id column of the post table. 
  CONSTRAINT fk_post_parent FOREIGN KEY (parent_id) REFERENCES post(id),
  CONSTRAINT fk_post_user FOREIGN KEY (author_id) REFERENCES user(id)
);

--
-- Table structure for table `post_meta`
--

DROP TABLE IF EXISTS blog.post_meta;

CREATE TABLE post_meta (
  id SERIAL PRIMARY KEY,
  post_id BIGINT NOT NULL,
  key VARCHAR(50) COLLATE pg_catalog.default NOT NULL,
  content TEXT COLLATE pg_catalog.default,
  CONSTRAINT uq_post_meta UNIQUE (post_id, key),
  CONSTRAINT fk_meta_post FOREIGN KEY (post_id) REFERENCES post(id)
);

--
-- Table structure for table `post_comment`
--

DROP TABLE IF EXISTS blog.post_comment;
CREATE TABLE blog.post_comment (
  id SERIAL PRIMARY KEY,
  post_id BIGINT NOT NULL,
  author_id BIGINT,
  parent_id BIGINT,
  created_at TIMESTAMP NOT NULL,
  updated_at TIMESTAMP,
  content TEXT,
  CONSTRAINT fk_comment_post FOREIGN KEY (post_id) REFERENCES post(id),
  CONSTRAINT fk_comment_user FOREIGN KEY (author_id) REFERENCES user(id),
  CONSTRAINT fk_comment_parent FOREIGN KEY (parent_id) REFERENCES post_comment(id)
);

--
-- Table structure for table `category`
--

DROP TABLE IF EXISTS blog.category;
CREATE TABLE blog.category (
  id SERIAL PRIMARY KEY,
  parent_id BIGINT,
  title VARCHAR(75) NOT NULL,
  meta_title VARCHAR(100),
  slug VARCHAR(100) NOT NULL,
  content TEXT
);

--
-- Table structure for table `post_category`
--

DROP TABLE IF EXISTS blog.post_category;
CREATE TABLE blog.post_category (
  post_id BIGINT NOT NULL,
  category_id BIGINT NOT NULL,
  PRIMARY KEY (post_id, category_id),
  CONSTRAINT fk_pc_post FOREIGN KEY (post_id) REFERENCES post(id),
  CONSTRAINT fk_pc_category FOREIGN KEY (category_id) REFERENCES category(id)
);

--
-- Table structure for table "tag"
--

DROP TABLE IF EXISTS blog.tag;
CREATE TABLE blog.tag (
  id SERIAL PRIMARY KEY,
  title VARCHAR(75) COLLATE pg_catalog.default NOT NULL,
  metaTitle VARCHAR(100) COLLATE pg_catalog.default,
  slug VARCHAR(100) COLLATE pg_catalog.default NOT NULL,
  content TEXT COLLATE pg_catalog.default
) ;

--
-- Table structure for table `post_tag`
--

DROP TABLE IF EXISTS blog.post_tag;
CREATE TABLE blog.post_tag (
  post_id bigint NOT NULL,
  tag_id bigint NOT NULL,
  PRIMARY KEY (post_id,tag_id),
  CONSTRAINT fk_pt_post FOREIGN KEY (post_id) REFERENCES post(id),
  CONSTRAINT fk_pt_tag FOREIGN KEY (tag_id) REFERENCES tag(id)
);
CREATE INDEX idx_pt_tag ON post_tag(tag_id);
CREATE INDEX idx_pt_post ON post_tag(post_id);