USE administrador_citas;

-- Creación de la tabla reviews
-- DROP TABLE IF EXISTS `reviews`; -- Yo eliminaria la tabla existente y la crearía de nuevo
CREATE TABLE IF NOT EXISTS `reviews` (
    `id_review` INT AUTO_INCREMENT PRIMARY KEY,
    `product_id` INT NULL,
    `service_id` INT NULL,
    `employee_id` INT NULL,
    `IS_COMPANY_REVIEW` BOOLEAN DEFAULT FALSE,
    `company_id` INT NOT NULL,
    `id_user` INT NOT NULL, -- ID del usuario que deja la reseña
    `title` VARCHAR(255),
    `content` TEXT NOT NULL,
    `rating` DECIMAL(2,1) NOT NULL CHECK (`rating` BETWEEN 1.0 AND 5.0),
    `verified` BOOLEAN DEFAULT FALSE, -- Verificación si el usuario ha usado el producto/servicio
    `business_reply` TEXT, -- Respuesta opcional del proveedor
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    -- Definición de FOREIGN KEY
    FOREIGN KEY (`id_user`) REFERENCES users(`id_user`),
    FOREIGN KEY (`product_id`) REFERENCES products(`id_product`),
    FOREIGN KEY (`service_id`) REFERENCES services(`id_service`),
    FOREIGN KEY (`employee_id`) REFERENCES employees(`id_employee`),
    FOREIGN KEY (`company_id`) REFERENCES companies(`id_company`),
    
    -- Columna generada para la combinación única
    `combination_key` VARCHAR(255) GENERATED ALWAYS AS (
        CONCAT(
            IFNULL(`product_id`, '0'), '|',
            IFNULL(`service_id`, '0'), '|',
            IFNULL(`employee_id`, '0'), '|',
            IF(`IS_COMPANY_REVIEW`, '1', '0'), '|',
            `company_id`,'|',
            `id_user`
        )
    ) STORED,
    
    -- Restricción UNIQUE sobre la columna generada
    UNIQUE KEY `unique_combination_key` (`combination_key`),
    
    -- Restricción CHECK para asegurar que solo uno de los campos esté presente
    CHECK (
        (CASE WHEN `product_id` IS NOT NULL THEN 1 ELSE 0 END) +
        (CASE WHEN `service_id` IS NOT NULL THEN 1 ELSE 0 END) +
        (CASE WHEN `employee_id` IS NOT NULL THEN 1 ELSE 0 END) +
        (CASE WHEN `IS_COMPANY_REVIEW` = TRUE THEN 1 ELSE 0 END)
        = 1
    )
);

-- Creación de la tabla review_reactions
-- DROP TABLE IF EXISTS `review_reactions`;
CREATE TABLE IF NOT EXISTS `review_reactions` (
    `id_reaction` INT AUTO_INCREMENT PRIMARY KEY,
    `id_review` INT NOT NULL,
    `id_user` INT NOT NULL, -- ID del usuario que reacciona
    `company_id` INT NOT NULL, 
    `reaction_type` ENUM('like', 'dislike') NOT NULL, -- Tipo de reacción
    `created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`id_review`) REFERENCES reviews(`id_review`),
    FOREIGN KEY (`id_user`) REFERENCES users(`id_user`),
    FOREIGN KEY (`company_id`) REFERENCES companies(`id_company`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Creación de la tabla review_media
-- DROP TABLE IF EXISTS `review_media`;
CREATE TABLE IF NOT EXISTS `review_media` (
    `id_media` INT AUTO_INCREMENT PRIMARY KEY,
    `id_review` INT NOT NULL, -- ID de la reseña
    `company_id` INT NOT NULL,
    `media_url` VARCHAR(255) NOT NULL, -- URL de la imagen o video
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`id_review`) REFERENCES reviews(`id_review`),
    FOREIGN KEY (`company_id`) REFERENCES companies(`id_company`)
);

-- Creación de la tabla favorites
-- DROP TABLE IF EXISTS `favorites`;
CREATE TABLE IF NOT EXISTS `favorites` (
    `id_user` INT NOT NULL,
    `id_service` INT NOT NULL, -- ID del servicio marcado como favorito
    `company_id` INT NOT NULL,
    PRIMARY KEY (`id_user`, `id_service`),
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`id_user`) REFERENCES `users` (`id_user`),
    FOREIGN KEY (`id_service`) REFERENCES `services` (`id_service`),
    FOREIGN KEY (`company_id`) REFERENCES `companies` (`id_company`)
);

-- Creación de la tabla users
-- DROP TABLE IF EXISTS `users`; -- Yo eliminaria la tabla existente y la crearía de nuevo
CREATE TABLE IF NOT EXISTS users (
    id_user INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(45) NOT NULL UNIQUE,
    name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    phone VARCHAR(15) NOT NULL UNIQUE,
    email VARCHAR(255) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL, -- Contraseña encriptada
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Añadir averageRating y reviewCount a las tablas de servicios, productos, empleados, empresas
ALTER TABLE services
ADD COLUMN averageRating DECIMAL(3,2) DEFAULT 0.0,
ADD COLUMN reviewCount INT DEFAULT 0;

ALTER TABLE products
ADD COLUMN averageRating DECIMAL(3,2) DEFAULT 0.0,
ADD COLUMN reviewCount INT DEFAULT 0;

ALTER TABLE employees
ADD COLUMN averageRating DECIMAL(3,2) DEFAULT 0.0,
ADD COLUMN reviewCount INT DEFAULT 0;

ALTER TABLE companies
ADD COLUMN averageRating DECIMAL(3,2) DEFAULT 0.0,
ADD COLUMN reviewCount INT DEFAULT 0;
