CREATE TABLE authors (
  id BIGSERIAL PRIMARY KEY,
  pen_name VARCHAR(255) NOT NULL,
  slug VARCHAR(255) UNIQUE NOT NULL,
  bio TEXT,
  avatar_url TEXT,
  country VARCHAR(100),
  contact_email VARCHAR(255),
  zalo VARCHAR(100),
  status VARCHAR(30) DEFAULT 'active',
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE categories (
  id BIGSERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  slug VARCHAR(120) UNIQUE NOT NULL,
  description TEXT,
  sort_order INT DEFAULT 0,
  is_active BOOLEAN DEFAULT TRUE,
  created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE tags (
  id BIGSERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  slug VARCHAR(120) UNIQUE NOT NULL,
  created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE books (
  id BIGSERIAL PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  slug VARCHAR(255) UNIQUE NOT NULL,
  original_title VARCHAR(255),
  author_id BIGINT REFERENCES authors(id),
  description TEXT,
  cover_url TEXT,
  language VARCHAR(20) DEFAULT 'vi',
  status VARCHAR(30) DEFAULT 'ongoing',
  source_type VARCHAR(50) DEFAULT 'original',
  copyright_status VARCHAR(50) DEFAULT 'authorized',
  word_count INT DEFAULT 0,
  chapter_count INT DEFAULT 0,
  view_count BIGINT DEFAULT 0,
  favorite_count BIGINT DEFAULT 0,
  rating_avg NUMERIC(3,2) DEFAULT 0,
  rating_count INT DEFAULT 0,
  is_featured BOOLEAN DEFAULT FALSE,
  is_published BOOLEAN DEFAULT TRUE,
  published_at TIMESTAMP,
  latest_chapter_id BIGINT,
  latest_chapter_title VARCHAR(255),
  latest_chapter_at TIMESTAMP,
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE book_categories (
  book_id BIGINT REFERENCES books(id) ON DELETE CASCADE,
  category_id BIGINT REFERENCES categories(id) ON DELETE CASCADE,
  PRIMARY KEY (book_id, category_id)
);

CREATE TABLE book_tags (
  book_id BIGINT REFERENCES books(id) ON DELETE CASCADE,
  tag_id BIGINT REFERENCES tags(id) ON DELETE CASCADE,
  PRIMARY KEY (book_id, tag_id)
);

CREATE TABLE chapters (
  id BIGSERIAL PRIMARY KEY,
  book_id BIGINT NOT NULL REFERENCES books(id) ON DELETE CASCADE,
  title VARCHAR(255) NOT NULL,
  slug VARCHAR(255) NOT NULL,
  chapter_number INT NOT NULL,
  content TEXT NOT NULL,
  word_count INT DEFAULT 0,
  view_count BIGINT DEFAULT 0,
  is_vip BOOLEAN DEFAULT FALSE,
  is_published BOOLEAN DEFAULT TRUE,
  published_at TIMESTAMP,
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW(),
  UNIQUE (book_id, slug),
  UNIQUE (book_id, chapter_number)
);

CREATE TABLE users (
  id BIGSERIAL PRIMARY KEY,
  email VARCHAR(255) UNIQUE,
  password_hash TEXT,
  display_name VARCHAR(100),
  avatar_url TEXT,
  role VARCHAR(30) DEFAULT 'reader',
  status VARCHAR(30) DEFAULT 'active',
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE bookshelf_items (
  id BIGSERIAL PRIMARY KEY,
  user_id BIGINT REFERENCES users(id) ON DELETE CASCADE,
  book_id BIGINT REFERENCES books(id) ON DELETE CASCADE,
  last_chapter_id BIGINT REFERENCES chapters(id),
  last_read_at TIMESTAMP DEFAULT NOW(),
  created_at TIMESTAMP DEFAULT NOW(),
  UNIQUE (user_id, book_id)
);

CREATE TABLE reading_history (
  id BIGSERIAL PRIMARY KEY,
  user_id BIGINT REFERENCES users(id) ON DELETE CASCADE,
  book_id BIGINT REFERENCES books(id) ON DELETE CASCADE,
  chapter_id BIGINT REFERENCES chapters(id) ON DELETE CASCADE,
  progress_percent NUMERIC(5,2) DEFAULT 0,
  read_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE ads (
  id BIGSERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  placement VARCHAR(100) NOT NULL,
  device VARCHAR(30) DEFAULT 'all',
  ad_code TEXT,
  is_active BOOLEAN DEFAULT TRUE,
  start_at TIMESTAMP,
  end_at TIMESTAMP,
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE content_reports (
  id BIGSERIAL PRIMARY KEY,
  book_id BIGINT REFERENCES books(id),
  chapter_id BIGINT REFERENCES chapters(id),
  reporter_name VARCHAR(255),
  reporter_email VARCHAR(255),
  reason VARCHAR(100),
  message TEXT,
  evidence_url TEXT,
  status VARCHAR(30) DEFAULT 'pending',
  admin_note TEXT,
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE daily_book_stats (
  id BIGSERIAL PRIMARY KEY,
  book_id BIGINT REFERENCES books(id) ON DELETE CASCADE,
  stat_date DATE NOT NULL,
  views BIGINT DEFAULT 0,
  unique_readers BIGINT DEFAULT 0,
  favorites BIGINT DEFAULT 0,
  ad_impressions BIGINT DEFAULT 0,
  ad_revenue NUMERIC(12,4) DEFAULT 0,
  UNIQUE (book_id, stat_date)
);

CREATE TABLE redirects (
  id BIGSERIAL PRIMARY KEY,
  old_path TEXT NOT NULL UNIQUE,
  new_path TEXT NOT NULL,
  status_code INT DEFAULT 301,
  is_active BOOLEAN DEFAULT TRUE,
  created_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_books_slug ON books(slug);
CREATE INDEX idx_books_status ON books(status);
CREATE INDEX idx_books_latest_chapter_at ON books(latest_chapter_at DESC);
CREATE INDEX idx_books_view_count ON books(view_count DESC);
CREATE INDEX idx_chapters_book_id ON chapters(book_id);
CREATE INDEX idx_chapters_book_number ON chapters(book_id, chapter_number);
CREATE INDEX idx_daily_book_stats_date ON daily_book_stats(stat_date);
