CREATE DATABASE IF NOT EXISTS gestion_salas CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE gestion_salas;

CREATE TABLE users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    full_name VARCHAR(120) NOT NULL,
    email VARCHAR(150) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    role ENUM('admin','general') NOT NULL DEFAULT 'general',
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE rooms (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL UNIQUE,
    description VARCHAR(255) NULL,
    color VARCHAR(20) NOT NULL DEFAULT '#3788d8',
    allowed_start TIME NOT NULL DEFAULT '08:00:00',
    allowed_end TIME NOT NULL DEFAULT '19:00:00',
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE meetings (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    room_id INT UNSIGNED NOT NULL,
    created_by INT UNSIGNED NOT NULL,
    title VARCHAR(180) NOT NULL,
    description TEXT NULL,
    organizer_email VARCHAR(150) NOT NULL,
    start_datetime DATETIME NOT NULL,
    end_datetime DATETIME NOT NULL,
    recurrence_group CHAR(36) NULL,
    recurrence_type ENUM('none','daily','weekly','monthly') NOT NULL DEFAULT 'none',
    recurrence_interval INT UNSIGNED NOT NULL DEFAULT 1,
    recurrence_until DATE NULL,
    status ENUM('scheduled','cancelled') NOT NULL DEFAULT 'scheduled',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_meetings_room FOREIGN KEY (room_id) REFERENCES rooms(id),
    CONSTRAINT fk_meetings_user FOREIGN KEY (created_by) REFERENCES users(id),
    INDEX idx_room_start_end (room_id, start_datetime, end_datetime),
    INDEX idx_status (status),
    INDEX idx_recurrence_group (recurrence_group)
) ENGINE=InnoDB;

CREATE TABLE meeting_invites (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    meeting_id BIGINT UNSIGNED NOT NULL,
    invite_email VARCHAR(150) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_invites_meeting FOREIGN KEY (meeting_id) REFERENCES meetings(id) ON DELETE CASCADE,
    UNIQUE KEY uq_meeting_invite (meeting_id, invite_email)
) ENGINE=InnoDB;

INSERT INTO users (full_name, email, password_hash, role)
VALUES (
    'Administrador',
    'admin@tudominio.cl',
    '$2y$12$Y4j.A3HaR6aiwSn/4o7Dm.L.5j.TKn/HxJdrE861PPbJZjjbq.npq',
    'admin'
);
-- contraseña inicial: Admin123*

INSERT INTO rooms (name, description, color, allowed_start, allowed_end)
VALUES
('Sala A', 'Sala principal', '#0d6efd', '08:00:00', '18:00:00'),
('Sala B', 'Sala secundaria', '#198754', '08:30:00', '19:00:00');