website/setup/database.sql

-- Web Publication System Database Schema
-- MariaDB/MySQL compatible
-- Note: Database creation and selection is handled by setup script

-- Publications table
CREATE TABLE publications (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    content TEXT NOT NULL,
    summary VARCHAR(500),
    author VARCHAR(100) NOT NULL,
    status ENUM('draft', 'published', 'archived') DEFAULT 'draft',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    published_at TIMESTAMP NULL
);

-- Users table for admin authentication with LDAP support
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    password VARCHAR(255),
    email VARCHAR(255),
    role ENUM('admin', 'editor') DEFAULT 'editor',
    auth_type ENUM('local', 'ldap') DEFAULT 'local',
    ldap_dn VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_login TIMESTAMP NULL
);

-- Categories table
CREATE TABLE categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) UNIQUE NOT NULL,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Publication categories junction table
CREATE TABLE publication_categories (
    publication_id INT,
    category_id INT,
    PRIMARY KEY (publication_id, category_id),
    FOREIGN KEY (publication_id) REFERENCES publications(id) ON DELETE CASCADE,
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE CASCADE
);

-- Insert default admin user (password: admin123)
INSERT INTO users (username, password, email, role, auth_type) VALUES 
('admin', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'admin@example.com', 'admin', 'local');

-- Insert sample categories
INSERT INTO categories (name, description) VALUES 
('Technology', 'Articles about technology and programming'),
('News', 'Current events and news articles'),
('Tutorial', 'How-to guides and tutorials'),
('Opinion', 'Editorials and opinion pieces');

-- Insert sample publications
INSERT INTO publications (title, content, summary, author, status) VALUES 
('Welcome to Web Publication System', 'This is a sample publication to demonstrate the system capabilities. You can edit or delete this publication from the admin panel.', 'A welcome message for new users', 'admin', 'published'),
('Getting Started with PHP', 'PHP is a popular server-side scripting language designed for web development. This article covers the basics of getting started with PHP development.', 'Introduction to PHP programming', 'admin', 'published'),
('Database Design Best Practices', 'Good database design is crucial for application performance and maintainability. This article covers essential principles and practices.', 'Tips for designing efficient databases', 'admin', 'draft');