-- =====================================================================
-- estertics - Database Schema and Seed Data
-- Personal Storytelling Website
-- Designed for MySQL / MariaDB (InnoDB, utf8mb4_unicode_ci)
-- =====================================================================

SET FOREIGN_KEY_CHECKS = 0;

DROP TABLE IF EXISTS login_attempts;
DROP TABLE IF EXISTS settings;
DROP TABLE IF EXISTS homepage_gallery;
DROP TABLE IF EXISTS homepage_sections;
DROP TABLE IF EXISTS media;
DROP TABLE IF EXISTS article_tags;
DROP TABLE IF EXISTS tags;
DROP TABLE IF EXISTS articles;
DROP TABLE IF EXISTS categories;
DROP TABLE IF EXISTS admins;

SET FOREIGN_KEY_CHECKS = 1;

-- ---------------------------------------------------------------------
-- Table: admins
-- Stores administrative user credentials and login timestamps
-- ---------------------------------------------------------------------
CREATE TABLE admins (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    last_login DATETIME NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- Table: categories
-- Article categories with slug and display sorting
-- ---------------------------------------------------------------------
CREATE TABLE categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    slug VARCHAR(120) NOT NULL UNIQUE,
    description TEXT NULL,
    sort_order INT DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- Table: articles
-- Editorial articles, essays, and stories
-- ---------------------------------------------------------------------
CREATE TABLE articles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    slug VARCHAR(280) NOT NULL UNIQUE,
    excerpt TEXT NULL,
    content LONGTEXT NOT NULL,
    cover_image VARCHAR(255) NULL,
    category_id INT NULL,
    status ENUM('draft','published') DEFAULT 'draft',
    is_featured TINYINT(1) DEFAULT 0,
    is_pinned TINYINT(1) DEFAULT 0,
    meta_title VARCHAR(255) NULL,
    meta_description TEXT NULL,
    published_at DATETIME NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL,
    INDEX idx_status_published (status, published_at),
    INDEX idx_featured (is_featured),
    INDEX idx_pinned (is_pinned)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- Table: tags
-- Content tagging for cross-referencing topics
-- ---------------------------------------------------------------------
CREATE TABLE tags (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    slug VARCHAR(60) NOT NULL UNIQUE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- Table: article_tags
-- Many-to-many relationship table between articles and tags
-- ---------------------------------------------------------------------
CREATE TABLE article_tags (
    article_id INT NOT NULL,
    tag_id INT 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 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- Table: media
-- Uploaded image registry with file metadata and alt text
-- ---------------------------------------------------------------------
CREATE TABLE media (
    id INT AUTO_INCREMENT PRIMARY KEY,
    filename VARCHAR(255) NOT NULL,
    original_name VARCHAR(255) NOT NULL,
    mime_type VARCHAR(50) NOT NULL,
    file_size INT UNSIGNED NOT NULL,
    alt_text VARCHAR(255) NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- Table: homepage_sections
-- Dynamic homepage content blocks (Hero, About, Callout, etc.)
-- ---------------------------------------------------------------------
CREATE TABLE homepage_sections (
    id INT AUTO_INCREMENT PRIMARY KEY,
    section_key VARCHAR(50) NOT NULL UNIQUE,
    title VARCHAR(255) NULL,
    subtitle TEXT NULL,
    content LONGTEXT NULL,
    image VARCHAR(255) NULL,
    link VARCHAR(500) NULL,
    link_text VARCHAR(100) NULL,
    extra_data JSON NULL,
    sort_order INT DEFAULT 0,
    is_active TINYINT(1) DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- Table: homepage_gallery
-- Visual gallery / photo grid for homepage
-- ---------------------------------------------------------------------
CREATE TABLE homepage_gallery (
    id INT AUTO_INCREMENT PRIMARY KEY,
    image VARCHAR(255) NOT NULL,
    caption VARCHAR(255) NULL,
    link VARCHAR(500) NULL,
    sort_order INT DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- Table: settings
-- Global key-value configuration and metadata
-- ---------------------------------------------------------------------
CREATE TABLE settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    setting_key VARCHAR(100) NOT NULL UNIQUE,
    setting_value TEXT NULL,
    setting_group VARCHAR(50) DEFAULT 'general'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- Table: login_attempts
-- Rate-limiting and brute force prevention log
-- ---------------------------------------------------------------------
CREATE TABLE login_attempts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    ip_address VARCHAR(45) NOT NULL,
    attempted_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_ip_time (ip_address, attempted_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================================
-- SEED DATA
-- =====================================================================

-- ---------------------------------------------------------------------
-- Admin Default Account
-- IMPORTANT: Default credentials are:
-- Username: admin
-- Password: password
-- PLEASE CHANGE THIS PASSWORD IMMEDIATELY AFTER INITIAL SETUP!
-- ---------------------------------------------------------------------
INSERT INTO admins (username, email, password_hash, created_at)
VALUES (
    'admin',
    'admin@estertics.com',
    '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi',
    NOW()
);

-- ---------------------------------------------------------------------
-- Default Categories
-- ---------------------------------------------------------------------
INSERT INTO categories (name, slug, description, sort_order) VALUES
('Stories', 'stories', 'Personal narratives, journeys, and lived experiences.', 1),
('Essays', 'essays', 'Reflective writings, perspectives, and critical observations.', 2),
('Life', 'life', 'Everyday moments, mindfulness, and lifestyle reflections.', 3),
('Culture', 'culture', 'Art, literature, music, and cultural curiosities.', 4);

-- ---------------------------------------------------------------------
-- Default Homepage Sections
-- ---------------------------------------------------------------------
INSERT INTO homepage_sections (section_key, title, subtitle, content, sort_order, is_active) VALUES
(
    'hero',
    'estertics',
    'Stories worth telling. Moments worth remembering.',
    NULL,
    1,
    1
),
(
    'about',
    'About',
    NULL,
    'A personal space for stories, essays, and moments that shape who we are. Every story deserves to be told, every moment deserves to be remembered.',
    2,
    1
);

-- ---------------------------------------------------------------------
-- Default Settings
-- ---------------------------------------------------------------------
INSERT INTO settings (setting_key, setting_value, setting_group) VALUES
('site_name', 'estertics', 'general'),
('site_description', 'Stories worth telling', 'general'),
('site_tagline', 'Stories worth telling. Moments worth remembering.', 'general'),
('social_instagram', '', 'social'),
('social_twitter', '', 'social'),
('social_facebook', '', 'social'),
('social_spotify', '', 'social'),
('footer_text', 'A personal storytelling space', 'general'),
('copyright_text', '© 2026 estertics. All Rights Reserved.', 'general');