-- Web Publication System Complete Database Schema
-- MariaDB/MySQL compatible
-- Combined schema with all migrations for setup.php
-- 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,
    slug VARCHAR(255),
    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 DEFAULT NULL,
    wp_post_id INT NULL DEFAULT NULL,
    INDEX idx_publications_slug (slug),
    INDEX idx_publications_wp_post_id (wp_post_id)
);

-- 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', 'user') DEFAULT 'editor',
    auth_type ENUM('local', 'ldap') DEFAULT 'local',
    ldap_dn VARCHAR(255),
    status ENUM('active', 'inactive') NOT NULL DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_login TIMESTAMP NULL,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_users_role (role),
    INDEX idx_users_status (status)
);

-- 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
);

-- Images table for WYSIWYG editor
CREATE TABLE IF NOT EXISTS images (
    id INT AUTO_INCREMENT PRIMARY KEY,
    filename VARCHAR(255) NOT NULL,
    original_name VARCHAR(255) NOT NULL,
    file_size INT NOT NULL,
    mime_type VARCHAR(100),
    uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    uploaded_by INT,
    INDEX idx_uploaded_at (uploaded_at),
    INDEX idx_uploaded_by (uploaded_by)
);

-- Add foreign key constraint for images table
ALTER TABLE images ADD CONSTRAINT fk_images_user FOREIGN KEY (uploaded_by) REFERENCES users(id) ON DELETE SET NULL;

-- Comments table for publication comments
CREATE TABLE IF NOT EXISTS comments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    publication_id INT NOT NULL,
    parent_id INT NULL DEFAULT NULL,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255),
    content TEXT NOT NULL,
    status ENUM('pending', 'approved', 'rejected') DEFAULT 'pending',
    ip_address VARCHAR(45),
    user_agent TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    admin_reply BOOLEAN DEFAULT FALSE,
    replied_by INT NULL,
    wp_comment_id INT NULL,
    FOREIGN KEY (publication_id) REFERENCES publications(id) ON DELETE CASCADE,
    FOREIGN KEY (parent_id) REFERENCES comments(id) ON DELETE CASCADE,
    FOREIGN KEY (replied_by) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_publication_id (publication_id),
    INDEX idx_parent_id (parent_id),
    INDEX idx_status (status),
    INDEX idx_created_at (created_at),
    INDEX idx_wp_comment_id (wp_comment_id)
);

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

-- 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, slug, content, summary, author, status, published_at) VALUES 
('Welcome to Web Publication System', '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', NOW()),
('Getting Started with PHP', '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', NOW()),
('Database Design Best Practices', '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', NULL);

-- Update existing publications to generate slugs from titles (for any existing data)
UPDATE publications 
SET slug = LOWER(REPLACE(REPLACE(REPLACE(title, '[^a-z0-9 ]', ''), ' ', '-'), '-', '')) 
WHERE slug IS NULL OR slug = '';

-- Update published_at for existing published publications (for any existing data)
UPDATE publications 
SET published_at = created_at 
WHERE status = 'published' AND published_at IS NULL;

-- Update existing users to have default role and status (for any existing data)
UPDATE users SET role = 'admin' WHERE id = 1; -- Make first user admin
UPDATE users SET status = 'active' WHERE status IS NULL;