-- Dr. Udyan Arya Portfolio Website
-- Articles Module Schema

CREATE TABLE IF NOT EXISTS categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    slug VARCHAR(120) NOT NULL UNIQUE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO categories (name, slug) VALUES
('Sanskrit', 'sanskrit'),
('Education', 'education'),
('Vedas', 'vedas'),
('Culture', 'culture'),
('Motivation', 'motivation'),
('Youth', 'youth'),
('Society', 'society'),
('Teachers', 'teachers'),
('Students', 'students');

CREATE TABLE IF NOT EXISTS articles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    slug VARCHAR(280) NOT NULL UNIQUE,
    content LONGTEXT NOT NULL,
    excerpt VARCHAR(400) DEFAULT NULL,
    featured_image VARCHAR(255) DEFAULT NULL,
    author_name VARCHAR(150) DEFAULT 'Dr. Udyan Arya',
    category_id INT DEFAULT NULL,
    reading_time INT DEFAULT 3,
    status ENUM('draft','published') DEFAULT 'published',
    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
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS admins (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(100) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- No default admin row is inserted here on purpose (a hard-coded hash in a public
-- schema file is a security risk). Run database/setup-admin.php once after import
-- to create your first admin login, then delete that file.