-- 1. Tabla surveys
CREATE TABLE surveys (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL UNIQUE,
    is_active BOOLEAN DEFAULT TRUE,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- 2. Tabla features (depende de surveys)
CREATE TABLE features (
    id INT AUTO_INCREMENT PRIMARY KEY,
    survey_id INT NOT NULL,
    name VARCHAR(255) NOT NULL,
    position INT NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (survey_id) REFERENCES surveys(id)
);

-- 3. Tabla projects (depende de surveys)
CREATE TABLE projects (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL UNIQUE,
    slug VARCHAR(255),
    survey_id INT,
    start_date DATE,
    end_date DATE,
    show_results ENUM('after_end_survey', 'send_by_email', 'show_by_admin') DEFAULT 'show_by_admin',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (survey_id) REFERENCES surveys(id)
);

-- 5. Tabla options
CREATE TABLE options (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    position INT NOT NULL,
    temperament_id INT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    -- Si temperament_id es FK, se puede agregar luego
);

-- 6. Tabla questions (depende de features)
CREATE TABLE questions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name TEXT NOT NULL,
    feature_id INT NOT NULL,
    option_id INT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (feature_id) REFERENCES features(id),
    FOREIGN KEY (option_id) REFERENCES options(id)
);

-- 7. Tabla peoples (depende de projects)
CREATE TABLE peoples (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    project_id INT,
    phone VARCHAR(20),
    email VARCHAR(255) NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (project_id) REFERENCES projects(id),
    UNIQUE KEY idx_unique_email_project (email, project_id)
);

-- 8. Tabla temperaments
CREATE TABLE temperaments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL UNIQUE,
    description TEXT,
    color VARCHAR(7) DEFAULT '#007bff',
    is_active BOOLEAN DEFAULT TRUE,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- 9. Tabla recommendations
CREATE TABLE recommendations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    temperaments_orders JSON NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- 10. Tabla answers (depende de projects, peoples, options, questions)
CREATE TABLE answers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    project_id INT,
    people_id INT,
    option_id INT,
    question_id INT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (project_id) REFERENCES projects(id),
    FOREIGN KEY (people_id) REFERENCES peoples(id),
    FOREIGN KEY (option_id) REFERENCES options(id),
    FOREIGN KEY (question_id) REFERENCES questions(id)
);

-- Agregar foreign key a options para temperament_id
ALTER TABLE options ADD FOREIGN KEY (temperament_id) REFERENCES temperaments(id);

-- 12. Tabla settings (configuración del sistema)
CREATE TABLE settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL DEFAULT 'Survey Manager',
    logo VARCHAR(500) DEFAULT NULL,
    description TEXT DEFAULT NULL,
    tags TEXT DEFAULT NULL,
    time_zone VARCHAR(50) NOT NULL DEFAULT 'America/New_York',
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Migración: agregar option_id a questions si no existe (para instalaciones previas)
-- Nota: Estos comandos pueden fallar si la columna ya existe; ejecútalos manualmente según tu entorno.
-- ALTER TABLE questions ADD COLUMN option_id INT NULL;
-- ALTER TABLE questions ADD FOREIGN KEY (option_id) REFERENCES options(id);

-- Insertar datos iniciales
INSERT INTO temperaments (name, description, color) VALUES 
('Sanguine', 'Optimistic and social', '#ff6b6b'),
('Choleric', 'Ambitious and leader-like', '#4ecdc4'),
('Melancholic', 'Analytical and detail-oriented', '#45b7d1'),
('Phlegmatic', 'Relaxed and peaceful', '#96ceb4');

-- Insertar configuración por defecto
INSERT INTO settings (title, description, tags, time_zone) VALUES
('Survey Manager', 'A professional administrative panel for managing surveys and analyzing temperament profiles', 'surveys,temperament,analysis,admin', 'America/New_York');

-- Migración para bases de datos existentes:
-- ALTER TABLE users ADD COLUMN lang VARCHAR(5) NOT NULL DEFAULT 'es' AFTER role;
