-- Estructura de tablas para preguntas y pools (PostgreSQL)

CREATE TABLE IF NOT EXISTS question_pools (
    pool_id VARCHAR(32) PRIMARY KEY,
    name VARCHAR(128) NOT NULL,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS questions (
    question_id VARCHAR(32) PRIMARY KEY,
    user_id INTEGER NOT NULL,
    type VARCHAR(16) NOT NULL,
    content TEXT NOT NULL,
    media JSONB,
    score NUMERIC(5,2) DEFAULT 1.0,
    options JSONB,
    explanation TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE TABLE IF NOT EXISTS question_pool_questions (
    question_id VARCHAR(32) NOT NULL,
    pool_id VARCHAR(32) NOT NULL,
    PRIMARY KEY (question_id, pool_id),
    FOREIGN KEY (question_id) REFERENCES questions(question_id),
    FOREIGN KEY (pool_id) REFERENCES question_pools(pool_id)
);

-- Trigger para actualizar updated_at
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
   NEW.updated_at = now();
   RETURN NEW;
END;
$$ language 'plpgsql';

DROP TRIGGER IF EXISTS update_questions_updated_at ON questions;
CREATE TRIGGER update_questions_updated_at BEFORE UPDATE
ON questions FOR EACH ROW EXECUTE PROCEDURE update_updated_at_column();
