# MegaBlog Database Schema
# Stack: Laravel + Spatie MediaLibrary + Umami Analytics

## TABLES OVERVIEW
┌─────────────────────────────────────────────────────────────────┐
│  users          → admins, editors, authors                      │
│  continents     → Africa, Europe, Asia...                       │
│  countries      → Nigeria, UK... (belongs to continent)         │
│  categories     → Sports, Entertainment, Tech, News, Politics   │
│  sources        → RSS feed sources (for crawler)                │
│  posts          → articles (crawled or manual)                  │
│  tags           → flexible keywords                             │
│  post_tag       → pivot: post ↔ tag                            │
│  comments       → nested comments per post                      │
│  ad_slots       → defined ad positions on site                  │
│  advertisements → actual ads assigned to slots                  │
│  crawl_logs     → history of each crawler run                   │
└─────────────────────────────────────────────────────────────────┘

═══════════════════════════════════════════════════════════════════
TABLE: users
═══════════════════════════════════════════════════════════════════
id                bigint PK
name              varchar(100)
email             varchar(150) UNIQUE
email_verified_at timestamp NULL
password          varchar(255)
role              enum('super_admin','admin','editor','author','subscriber')  DEFAULT 'subscriber'
avatar            varchar(500) NULL       ← Spatie MediaLibrary handles this
bio               text NULL
is_active         boolean DEFAULT true
last_login_at     timestamp NULL
remember_token    varchar(100) NULL
created_at        timestamp
updated_at        timestamp

═══════════════════════════════════════════════════════════════════
TABLE: continents
═══════════════════════════════════════════════════════════════════
id                tinyint PK
name              varchar(50) UNIQUE      e.g. "Africa"
slug              varchar(50) UNIQUE      e.g. "africa"
emoji             varchar(10)             e.g. "🌍"
color_from        varchar(50)             e.g. "from-amber-500"  (Tailwind gradient)
color_to          varchar(50)             e.g. "to-orange-600"
posts_count       int DEFAULT 0           ← cached counter
created_at        timestamp
updated_at        timestamp

═══════════════════════════════════════════════════════════════════
TABLE: countries
═══════════════════════════════════════════════════════════════════
id                smallint PK
continent_id      tinyint FK → continents.id
name              varchar(100)            e.g. "Nigeria"
slug              varchar(100) UNIQUE     e.g. "nigeria"
flag_emoji        varchar(10)             e.g. "🇳🇬"
is_active         boolean DEFAULT true
posts_count       int DEFAULT 0           ← cached counter
created_at        timestamp
updated_at        timestamp

INDEX: continent_id

═══════════════════════════════════════════════════════════════════
TABLE: categories
═══════════════════════════════════════════════════════════════════
id                tinyint PK
name              varchar(100) UNIQUE     e.g. "Sports"
slug              varchar(100) UNIQUE     e.g. "sports"
description       text NULL
color             varchar(50)             e.g. "text-orange-400"
bg_color          varchar(50)             e.g. "bg-orange-500"
icon              varchar(50) NULL        e.g. "trophy" (lucide icon name)
posts_count       int DEFAULT 0           ← cached counter
sort_order        tinyint DEFAULT 0
created_at        timestamp
updated_at        timestamp

═══════════════════════════════════════════════════════════════════
TABLE: tags
═══════════════════════════════════════════════════════════════════
id                int PK
name              varchar(100) UNIQUE
slug              varchar(100) UNIQUE
posts_count       int DEFAULT 0
created_at        timestamp
updated_at        timestamp

═══════════════════════════════════════════════════════════════════
TABLE: sources  (RSS feed sources for the crawler)
═══════════════════════════════════════════════════════════════════
id                int PK
country_id        smallint FK → countries.id
category_id       tinyint FK → categories.id
name              varchar(150)            e.g. "TechCabal"
rss_url           varchar(500) NULL
website_url       varchar(500) NULL
is_active         boolean DEFAULT true
last_crawled_at   timestamp NULL
last_status       enum('ok','empty','error','blocked') NULL
articles_fetched  int DEFAULT 0           ← total ever fetched
created_at        timestamp
updated_at        timestamp

INDEX: country_id, category_id, is_active

═══════════════════════════════════════════════════════════════════
TABLE: posts
═══════════════════════════════════════════════════════════════════
id                bigint PK
ulid              char(26) UNIQUE         ← public-facing ID (no sequential guessing)
source_id         int FK → sources.id NULL       (NULL = manually written)
author_id         bigint FK → users.id NULL      (NULL = crawled/auto)
continent_id      tinyint FK → continents.id
country_id        smallint FK → countries.id
category_id       tinyint FK → categories.id

title             varchar(500)
slug              varchar(600) UNIQUE
excerpt           text NULL
content           longtext
original_content  longtext NULL           ← content before AI rewrite
featured_image    varchar(1000) NULL      ← Spatie OR direct URL from crawler

status            enum('draft','published','pending','archived') DEFAULT 'draft'
is_rewritten      boolean DEFAULT false   ← was it AI rewritten?
is_featured       boolean DEFAULT false   ← pin to hero section
is_breaking       boolean DEFAULT false   ← show in breaking ticker

source_url        varchar(1000) NULL      ← original article URL
source_name       varchar(200) NULL       ← e.g. "TechCabal"

reading_time      tinyint DEFAULT 1       ← minutes (auto-calculated)
views_count       bigint DEFAULT 0        ← cached from analytics
comments_count    int DEFAULT 0           ← cached counter

meta_title        varchar(200) NULL       ← SEO override
meta_description  varchar(300) NULL       ← SEO override

published_at      timestamp NULL
created_at        timestamp
updated_at        timestamp
deleted_at        timestamp NULL          ← soft delete

INDEX: status, published_at
INDEX: continent_id, country_id, category_id
INDEX: is_featured, is_breaking
FULLTEXT: title, excerpt, content

═══════════════════════════════════════════════════════════════════
TABLE: post_tag  (pivot)
═══════════════════════════════════════════════════════════════════
post_id           bigint FK → posts.id
tag_id            int FK → tags.id
PRIMARY KEY (post_id, tag_id)

═══════════════════════════════════════════════════════════════════
TABLE: comments
═══════════════════════════════════════════════════════════════════
id                bigint PK
post_id           bigint FK → posts.id
user_id           bigint FK → users.id NULL   (NULL = guest)
parent_id         bigint FK → comments.id NULL (NULL = top-level)

author_name       varchar(100) NULL       ← for guest comments
author_email      varchar(150) NULL       ← for guest comments
author_ip         varchar(45) NULL        ← for spam detection

content           text
status            enum('pending','approved','spam') DEFAULT 'pending'
likes_count       int DEFAULT 0

created_at        timestamp
updated_at        timestamp
deleted_at        timestamp NULL

INDEX: post_id, status
INDEX: parent_id

═══════════════════════════════════════════════════════════════════
TABLE: ad_slots  (defined positions on the site)
═══════════════════════════════════════════════════════════════════
id                smallint PK
name              varchar(100)            e.g. "Homepage Hero Banner"
slug              varchar(100) UNIQUE     e.g. "homepage-hero"
position          enum(
                    'header',
                    'homepage_hero',
                    'homepage_mid',
                    'sidebar_top',
                    'sidebar_mid',
                    'sidebar_bottom',
                    'in_content',        ← injected between paragraphs
                    'between_posts',
                    'footer',
                    'mobile_sticky'
                  )
description       varchar(300) NULL
width             smallint NULL           ← pixels
height            smallint NULL           ← pixels
max_ads           tinyint DEFAULT 1       ← how many ads can run here at once
is_active         boolean DEFAULT true
created_at        timestamp
updated_at        timestamp

═══════════════════════════════════════════════════════════════════
TABLE: advertisements
═══════════════════════════════════════════════════════════════════
id                int PK
ad_slot_id        smallint FK → ad_slots.id
title             varchar(200)
type              enum('image','html_code','google_adsense') DEFAULT 'image'

image_url         varchar(1000) NULL      ← for type=image
link_url          varchar(1000) NULL      ← click destination
ad_code           text NULL               ← for type=html_code or adsense

advertiser_name   varchar(150) NULL
advertiser_email  varchar(150) NULL
price_per_day     decimal(10,2) NULL

start_date        date NULL
end_date          date NULL
is_active         boolean DEFAULT true

impressions_count bigint DEFAULT 0        ← views
clicks_count      bigint DEFAULT 0        ← clicks
ctr               decimal(5,4) DEFAULT 0  ← click-through rate (auto-updated)

created_at        timestamp
updated_at        timestamp

INDEX: ad_slot_id, is_active, start_date, end_date

═══════════════════════════════════════════════════════════════════
TABLE: crawl_logs
═══════════════════════════════════════════════════════════════════
id                bigint PK
source_id         int FK → sources.id
status            enum('success','empty','error','blocked')
articles_found    smallint DEFAULT 0
articles_saved    smallint DEFAULT 0
articles_skipped  smallint DEFAULT 0      ← duplicates
error_message     text NULL
started_at        timestamp
finished_at       timestamp NULL
duration_seconds  smallint NULL

INDEX: source_id, started_at

═══════════════════════════════════════════════════════════════════
TABLE: subscribers  (newsletter / email list)
═══════════════════════════════════════════════════════════════════
id                bigint PK
email             varchar(150) UNIQUE
name              varchar(100) NULL
status            enum('pending','confirmed','unsubscribed') DEFAULT 'pending'
confirmation_token varchar(100) NULL
confirmed_at      timestamp NULL
unsubscribed_at   timestamp NULL
source            enum('homepage','post','popup','api') DEFAULT 'homepage'
ip_address        varchar(45) NULL
created_at        timestamp
updated_at        timestamp

INDEX: status, email

═══════════════════════════════════════════════════════════════════
TABLE: newsletter_campaigns
═══════════════════════════════════════════════════════════════════
id                int PK
title             varchar(200)
subject           varchar(200)
content           longtext
status            enum('draft','scheduled','sending','sent') DEFAULT 'draft'
total_recipients  int DEFAULT 0
sent_count        int DEFAULT 0
open_count        int DEFAULT 0
click_count       int DEFAULT 0
scheduled_at      timestamp NULL
sent_at           timestamp NULL
created_by        bigint FK → users.id
created_at        timestamp
updated_at        timestamp

═══════════════════════════════════════════════════════════════════
TABLE: bookmarks  (users saving posts)
═══════════════════════════════════════════════════════════════════
id                bigint PK
user_id           bigint FK → users.id
post_id           bigint FK → posts.id
created_at        timestamp

UNIQUE: (user_id, post_id)
INDEX: user_id

═══════════════════════════════════════════════════════════════════
TABLE: post_likes
═══════════════════════════════════════════════════════════════════
id                bigint PK
post_id           bigint FK → posts.id
user_id           bigint FK → users.id NULL
ip_address        varchar(45) NULL         ← for guest likes
created_at        timestamp

UNIQUE: (post_id, user_id)
UNIQUE: (post_id, ip_address)             ← prevent guest duplicates
INDEX: post_id

═══════════════════════════════════════════════════════════════════
TABLE: post_views  (granular view tracking for trending/popular)
═══════════════════════════════════════════════════════════════════
id                bigint PK
post_id           bigint FK → posts.id
session_id        varchar(100) NULL        ← anonymous session
user_id           bigint FK → users.id NULL
ip_hash           varchar(64) NULL         ← hashed IP for privacy
country_code      char(2) NULL             ← e.g. "NG", "US"
device_type       enum('desktop','mobile','tablet') NULL
referrer          varchar(500) NULL
utm_source        varchar(100) NULL
utm_medium        varchar(100) NULL
utm_campaign      varchar(100) NULL
created_at        timestamp

INDEX: post_id, created_at
INDEX: session_id

═══════════════════════════════════════════════════════════════════
TABLE: pages  (static pages: About, Contact, Privacy Policy etc.)
═══════════════════════════════════════════════════════════════════
id                int PK
title             varchar(300)
slug              varchar(300) UNIQUE
content           longtext
meta_title        varchar(200) NULL
meta_description  varchar(300) NULL
status            enum('draft','published') DEFAULT 'draft'
show_in_menu      boolean DEFAULT false
sort_order        smallint DEFAULT 0
created_by        bigint FK → users.id NULL
created_at        timestamp
updated_at        timestamp

═══════════════════════════════════════════════════════════════════
TABLE: menus
═══════════════════════════════════════════════════════════════════
id                smallint PK
name              varchar(100)             e.g. "Header Nav", "Footer Nav"
slug              varchar(100) UNIQUE      e.g. "header", "footer"
created_at        timestamp
updated_at        timestamp

═══════════════════════════════════════════════════════════════════
TABLE: menu_items
═══════════════════════════════════════════════════════════════════
id                int PK
menu_id           smallint FK → menus.id
parent_id         int FK → menu_items.id NULL   ← nested items
label             varchar(100)
url               varchar(500)
target            enum('_self','_blank') DEFAULT '_self'
icon              varchar(50) NULL
sort_order        smallint DEFAULT 0
created_at        timestamp
updated_at        timestamp

INDEX: menu_id, parent_id

═══════════════════════════════════════════════════════════════════
TABLE: site_settings  (key/value store for admin config)
═══════════════════════════════════════════════════════════════════
id                int PK
key               varchar(100) UNIQUE
value             text NULL
type              enum('string','boolean','integer','json') DEFAULT 'string'
group             varchar(50) DEFAULT 'general'   ← general/seo/social/email/crawler
description       varchar(300) NULL
updated_by        bigint FK → users.id NULL
created_at        timestamp
updated_at        timestamp

EXAMPLE ROWS:
  site_name           → "MegaBlog"
  site_tagline        → "Global News"
  logo_url            → "https://..."
  posts_per_page      → "12"
  crawler_interval    → "60"   (minutes)
  auto_publish        → "false"
  twitter_handle      → "@megablog"
  facebook_url        → "https://..."
  google_adsense_id   → "ca-pub-xxxx"
  smtp_host           → "smtp.mailgun.org"
  maintenance_mode    → "false"

═══════════════════════════════════════════════════════════════════
TABLE: notifications  (in-app admin notifications)
═══════════════════════════════════════════════════════════════════
id                bigint PK  (UUID recommended)
user_id           bigint FK → users.id
type              varchar(100)   e.g. "NewComment", "CrawlerFailed", "NewSubscriber"
title             varchar(200)
body              text NULL
data              json NULL       ← extra context (post_id, comment_id etc.)
read_at           timestamp NULL
created_at        timestamp

INDEX: user_id, read_at

═══════════════════════════════════════════════════════════════════
TABLE: redirects  (manage 301/302 redirects from admin)
═══════════════════════════════════════════════════════════════════
id                int PK
from_path         varchar(500) UNIQUE    e.g. "/old-post-slug"
to_path           varchar(500)           e.g. "/new-post-slug"
type              smallint DEFAULT 301   ← 301 permanent, 302 temporary
hit_count         int DEFAULT 0
is_active         boolean DEFAULT true
created_at        timestamp
updated_at        timestamp

═══════════════════════════════════════════════════════════════════
TABLE: api_keys  (for crawler + external access auth)
═══════════════════════════════════════════════════════════════════
id                int PK
user_id           bigint FK → users.id
name              varchar(100)            e.g. "Python Crawler", "Mobile App"
key               varchar(64) UNIQUE      ← hashed token
permissions       json                    e.g. ["posts:write", "posts:read"]
last_used_at      timestamp NULL
expires_at        timestamp NULL
is_active         boolean DEFAULT true
created_at        timestamp
updated_at        timestamp

INDEX: key, is_active

═══════════════════════════════════════════════════════════════════
TABLE: social_accounts  (OAuth login: Google, Facebook, Twitter)
═══════════════════════════════════════════════════════════════════
id                int PK
user_id           bigint FK → users.id
provider          enum('google','facebook','twitter','github')
provider_id       varchar(200)
provider_token    text NULL
provider_refresh  text NULL
avatar_url        varchar(500) NULL
created_at        timestamp
updated_at        timestamp

UNIQUE: (provider, provider_id)
INDEX: user_id

═══════════════════════════════════════════════════════════════════
TABLE: ad_impressions  (detailed ad tracking)
═══════════════════════════════════════════════════════════════════
id                bigint PK
advertisement_id  int FK → advertisements.id
type              enum('impression','click')
post_id           bigint FK → posts.id NULL   ← where it was shown
session_id        varchar(100) NULL
ip_hash           varchar(64) NULL
country_code      char(2) NULL
device_type       enum('desktop','mobile','tablet') NULL
created_at        timestamp

INDEX: advertisement_id, type, created_at

═══════════════════════════════════════════════════════════════════
TABLE: contact_messages
═══════════════════════════════════════════════════════════════════
id                int PK
name              varchar(100)
email             varchar(150)
subject           varchar(200) NULL
message           text
type              enum('general','advertise','partnership','report') DEFAULT 'general'
status            enum('unread','read','replied','archived') DEFAULT 'unread'
ip_address        varchar(45) NULL
replied_at        timestamp NULL
replied_by        bigint FK → users.id NULL
created_at        timestamp
updated_at        timestamp

INDEX: status, type

═══════════════════════════════════════════════════════════════════
ANALYTICS → Use Umami (self-hosted, open source)
═══════════════════════════════════════════════════════════════════
DO NOT build analytics from scratch.
Use Umami: https://umami.is

Why Umami:
  ✓ Self-hosted Docker container
  ✓ No cookies — GDPR compliant
  ✓ Works with Next.js (one script tag)
  ✓ Has REST API → pull stats into your Laravel admin
  ✓ Tracks: page views, unique visitors, referrers,
            countries, devices, browsers, UTM params
  ✓ Real-time dashboard

Add to Next.js layout.tsx:
  <Script src="https://analytics.yourdomain.com/script.js"
          data-website-id="YOUR_WEBSITE_ID" />

Pull stats via Umami API into Laravel admin:
  GET /api/websites/{id}/stats
  GET /api/websites/{id}/pageviews
  GET /api/websites/{id}/events

═══════════════════════════════════════════════════════════════════
SPATIE PACKAGES TO USE
═══════════════════════════════════════════════════════════════════
spatie/laravel-medialibrary   → featured images, user avatars
spatie/laravel-permission     → user roles (admin/editor/author)
spatie/laravel-sluggable      → auto-generate slugs
spatie/laravel-tags           → tag management
spatie/laravel-activitylog    → audit log (who edited what)

═══════════════════════════════════════════════════════════════════
RELATIONSHIPS SUMMARY
═══════════════════════════════════════════════════════════════════

Continent  hasMany  Countries
Country    belongsTo Continent
Country    hasMany  Posts
Country    hasMany  Sources

Category   hasMany  Posts
Category   hasMany  Sources

Source     hasMany  Posts
Source     hasMany  CrawlLogs
Source     belongsTo Country
Source     belongsTo Category

Post       belongsTo Continent
Post       belongsTo Country
Post       belongsTo Category
Post       belongsTo Source (nullable)
Post       belongsTo User/Author (nullable)
Post       hasMany  Comments
Post       belongsToMany Tags
Post       hasOne   Media (Spatie - featured image)

AdSlot     hasMany  Advertisements
