-- Forum Database Schema

-- Users table (if you don't have one already)
CREATE TABLE IF NOT EXISTS yogapwa_users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    is_admin TINYINT(1) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Questions table
CREATE TABLE IF NOT EXISTS yogapwa_forum_questions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    title VARCHAR(255) NOT NULL,
    description TEXT NOT NULL,
    upvotes INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES yogapwa_users(id) ON DELETE CASCADE,
    INDEX idx_upvotes (upvotes),
    INDEX idx_created (created_at)
);

-- Answers table
CREATE TABLE IF NOT EXISTS yogapwa_forum_answers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    question_id INT NOT NULL,
    user_id INT NOT NULL,
    answer TEXT NOT NULL,
    favorites INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (question_id) REFERENCES yogapwa_forum_questions(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES yogapwa_users(id) ON DELETE CASCADE,
    INDEX idx_question (question_id),
    INDEX idx_favorites (favorites)
);

-- Question upvotes tracking (to prevent duplicate votes)
CREATE TABLE IF NOT EXISTS yogapwa_forum_question_upvotes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    question_id INT NOT NULL,
    user_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (question_id) REFERENCES yogapwa_forum_questions(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES yogapwa_users(id) ON DELETE CASCADE,
    UNIQUE KEY unique_vote (question_id, user_id)
);

-- Answer favorites tracking
CREATE TABLE IF NOT EXISTS yogapwa_forum_answer_favorites (
    id INT AUTO_INCREMENT PRIMARY KEY,
    answer_id INT NOT NULL,
    user_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (answer_id) REFERENCES yogapwa_forum_answers(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES yogapwa_users(id) ON DELETE CASCADE,
    UNIQUE KEY unique_favorite (answer_id, user_id)
);
