-- backend_upgrade.sql
-- Import this AFTER your existing database.sql, or import schema_full.sql for everything together.

CREATE TABLE IF NOT EXISTS cms_admin_users (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  username VARCHAR(80) NOT NULL,
  email VARCHAR(255) DEFAULT NULL,
  password_hash VARCHAR(255) NOT NULL,
  role ENUM('admin','editor') NOT NULL DEFAULT 'admin',
  status ENUM('active','disabled') NOT NULL DEFAULT 'active',
  last_login_at DATETIME DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_cms_admin_username (username),
  KEY idx_cms_admin_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS cms_quotes (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  page_id INT UNSIGNED DEFAULT NULL,
  quote_text TEXT NOT NULL,
  quote_label VARCHAR(120) DEFAULT NULL,
  status ENUM('draft','published','archived') NOT NULL DEFAULT 'published',
  is_featured TINYINT(1) NOT NULL DEFAULT 1,
  display_order INT NOT NULL DEFAULT 0,
  published_at DATETIME DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_cms_quotes_page (page_id),
  KEY idx_cms_quotes_status (status),
  KEY idx_cms_quotes_featured (is_featured),
  KEY idx_cms_quotes_order (display_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS cms_uploads (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  original_name VARCHAR(255) NOT NULL,
  stored_name VARCHAR(255) NOT NULL,
  public_path VARCHAR(500) NOT NULL,
  mime_type VARCHAR(120) NOT NULL,
  file_size INT UNSIGNED NOT NULL DEFAULT 0,
  uploaded_by INT UNSIGNED DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_cms_uploads_created (created_at),
  KEY idx_cms_uploads_user (uploaded_by)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Optional seed quote. The homepage also falls back to cms_pages if cms_quotes is empty.
INSERT INTO cms_quotes (page_id, quote_text, quote_label, status, is_featured, display_order, published_at)
SELECT id, excerpt, topic, 'published', 1, 10, published_at
FROM cms_pages
WHERE status = 'published'
  AND page_type = 'article'
  AND excerpt IS NOT NULL
  AND excerpt <> ''
  AND NOT EXISTS (SELECT 1 FROM cms_quotes LIMIT 1)
ORDER BY COALESCE(published_at, created_at) DESC
LIMIT 5;


-- Optional: adds an on/off switch for featured images on page.php.
-- If this column already exists, ignore the duplicate-column error.
ALTER TABLE cms_pages
  ADD COLUMN show_featured_image TINYINT(1) NOT NULL DEFAULT 1
  AFTER featured_image_caption;