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

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;
-- 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');
