-- ============================================================
--  GetPhDReady - Full Database Schema
--  Import via phpMyAdmin or: mysql -u user -p dbname < database.sql
-- ============================================================
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

CREATE TABLE IF NOT EXISTS categories (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name        VARCHAR(100) NOT NULL,
  slug        VARCHAR(110) NOT NULL UNIQUE,
  description TEXT,
  type        ENUM('article','video','resource','all') DEFAULT 'all',
  sort_order  INT DEFAULT 0,
  created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO categories (name,slug,type,sort_order) VALUES
('Applications','applications','all',1),
('Funding','funding','all',2),
('Research Skills','research-skills','all',3),
('Advisor Relations','advisor-relations','all',4),
('Dissertation','dissertation','all',5),
('Wellness','wellness','all',6),
('Career','career','all',7),
('Faculty Interviews','faculty-interviews','video',8),
('Current Students','current-students','all',9),
('International','international','all',10);

CREATE TABLE IF NOT EXISTS users (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name        VARCHAR(150) NOT NULL,
  email       VARCHAR(191) NOT NULL UNIQUE,
  password    VARCHAR(255) NOT NULL,
  role        ENUM('admin','editor','member') DEFAULT 'member',
  avatar      VARCHAR(255) DEFAULT NULL,
  bio         TEXT,
  is_active   TINYINT(1) DEFAULT 1,
  last_login  TIMESTAMP NULL,
  created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS articles (
  id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id      INT UNSIGNED NOT NULL,
  category_id  INT UNSIGNED NOT NULL,
  title        VARCHAR(255) NOT NULL,
  slug         VARCHAR(270) NOT NULL UNIQUE,
  excerpt      TEXT,
  body         LONGTEXT NOT NULL,
  cover_image  VARCHAR(255) DEFAULT NULL,
  read_time    INT DEFAULT 5,
  audience     ENUM('applicants','current','both') DEFAULT 'both',
  is_featured  TINYINT(1) DEFAULT 0,
  status       ENUM('draft','published','archived') DEFAULT 'draft',
  views        INT UNSIGNED DEFAULT 0,
  published_at TIMESTAMP NULL,
  created_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id)     REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE CASCADE,
  INDEX idx_status (status,published_at),
  INDEX idx_slug   (slug)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS tags (
  id   INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(80) NOT NULL UNIQUE,
  slug VARCHAR(90) NOT NULL UNIQUE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS article_tags (
  article_id INT UNSIGNED NOT NULL,
  tag_id     INT UNSIGNED NOT NULL,
  PRIMARY KEY (article_id,tag_id),
  FOREIGN KEY (article_id) REFERENCES articles(id) ON DELETE CASCADE,
  FOREIGN KEY (tag_id)     REFERENCES tags(id)     ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS videos (
  id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id       INT UNSIGNED NOT NULL,
  category_id   INT UNSIGNED NOT NULL,
  title         VARCHAR(255) NOT NULL,
  slug          VARCHAR(270) NOT NULL UNIQUE,
  description   TEXT,
  video_type    ENUM('youtube','vimeo','upload') DEFAULT 'youtube',
  video_url     VARCHAR(500) NOT NULL,
  thumbnail     VARCHAR(255) DEFAULT NULL,
  duration_sec  INT DEFAULT 0,
  host_name     VARCHAR(150) DEFAULT NULL,
  video_format  ENUM('tutorial','interview','panel','workshop','guide') DEFAULT 'tutorial',
  audience      ENUM('applicants','current','both') DEFAULT 'both',
  is_featured   TINYINT(1) DEFAULT 0,
  status        ENUM('draft','published','archived') DEFAULT 'draft',
  views         INT UNSIGNED DEFAULT 0,
  published_at  TIMESTAMP NULL,
  created_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id)     REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS video_chapters (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  video_id    INT UNSIGNED NOT NULL,
  title       VARCHAR(255) NOT NULL,
  start_sec   INT NOT NULL,
  description TEXT,
  sort_order  INT DEFAULT 0,
  FOREIGN KEY (video_id) REFERENCES videos(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS resources (
  id             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id        INT UNSIGNED NOT NULL,
  category_id    INT UNSIGNED NOT NULL,
  title          VARCHAR(255) NOT NULL,
  slug           VARCHAR(270) NOT NULL UNIQUE,
  description    TEXT,
  file_path      VARCHAR(500) DEFAULT NULL,
  external_url   VARCHAR(500) DEFAULT NULL,
  resource_type  ENUM('pdf','template','checklist','database','guide','other') DEFAULT 'pdf',
  access_type    ENUM('free','premium') DEFAULT 'free',
  icon           VARCHAR(10) DEFAULT '?',
  download_count INT UNSIGNED DEFAULT 0,
  status         ENUM('draft','published','archived') DEFAULT 'draft',
  created_at     TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at     TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id)     REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS comments (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  article_id  INT UNSIGNED NOT NULL,
  user_id     INT UNSIGNED DEFAULT NULL,
  parent_id   INT UNSIGNED DEFAULT NULL,
  guest_name  VARCHAR(150) DEFAULT NULL,
  guest_email VARCHAR(191) DEFAULT NULL,
  body        TEXT NOT NULL,
  status      ENUM('pending','approved','spam') DEFAULT 'pending',
  created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (article_id) REFERENCES articles(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id)    REFERENCES users(id)    ON DELETE SET NULL,
  FOREIGN KEY (parent_id)  REFERENCES comments(id) ON DELETE CASCADE,
  INDEX idx_article_status (article_id,status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS subscribers (
  id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  email        VARCHAR(191) NOT NULL UNIQUE,
  name         VARCHAR(150) DEFAULT NULL,
  status       ENUM('active','unsubscribed','bounced') DEFAULT 'active',
  source       VARCHAR(100) DEFAULT NULL,
  token        VARCHAR(64) NOT NULL,
  confirmed_at TIMESTAMP NULL,
  created_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS contact_messages (
  id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name       VARCHAR(150) NOT NULL,
  email      VARCHAR(191) NOT NULL,
  audience   VARCHAR(100) DEFAULT NULL,
  message    TEXT NOT NULL,
  is_read    TINYINT(1) DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS settings (
  setting_key   VARCHAR(100) PRIMARY KEY,
  setting_value TEXT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO settings (setting_key,setting_value) VALUES
('site_name','GetPhDReady'),
('site_tagline','Your Path to Doctoral Success'),
('site_email','hello@getphdready.com'),
('site_url','https://getphdready.com'),
('posts_per_page','12'),
('allow_comments','1'),
('comment_moderation','1'),
('newsletter_enabled','1'),
('google_analytics',''),
('meta_description','Expert guidance for PhD applicants and current doctoral students.');

SET FOREIGN_KEY_CHECKS = 1;
