CREATE TABLE IF NOT EXISTS schema_migrations (
    migration VARCHAR(190) PRIMARY KEY,
    applied_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE users (
    id CHAR(36) PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    email VARCHAR(190) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    role ENUM('super', 'operator') NOT NULL DEFAULT 'operator',
    active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE events (
    id CHAR(36) PRIMARY KEY,
    name VARCHAR(190) NOT NULL,
    slug VARCHAR(190) NOT NULL UNIQUE,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    country_code CHAR(2) NOT NULL DEFAULT 'UY',
    timezone VARCHAR(80) NOT NULL DEFAULT 'America/Montevideo',
    website_url VARCHAR(1000) NULL,
    venue_address VARCHAR(500) NULL,
    map_link VARCHAR(1000) NULL,
    map_embed_url TEXT NULL,
    primary_color CHAR(7) NOT NULL DEFAULT '#ffd709',
    secondary_color CHAR(7) NOT NULL DEFAULT '#e2e2e2',
    background_color CHAR(7) NOT NULL DEFAULT '#0e0e0e',
    text_on_primary CHAR(7) NOT NULL DEFAULT '#000000',
    logo_url VARCHAR(1000) NULL,
    favicon_url VARCHAR(1000) NULL,
    share_image_url VARCHAR(1000) NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    networking_enabled TINYINT(1) NOT NULL DEFAULT 0,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT chk_event_dates CHECK (end_date >= start_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE event_users (
    event_id CHAR(36) NOT NULL,
    user_id CHAR(36) NOT NULL,
    PRIMARY KEY (event_id, user_id),
    CONSTRAINT fk_event_users_event FOREIGN KEY (event_id) REFERENCES events(id) ON DELETE CASCADE,
    CONSTRAINT fk_event_users_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE event_days (
    id CHAR(36) PRIMARY KEY,
    event_id CHAR(36) NOT NULL,
    day_number SMALLINT UNSIGNED NOT NULL,
    label VARCHAR(100) NOT NULL,
    event_date DATE NOT NULL,
    sort_order INT NOT NULL DEFAULT 0,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_event_day_number (event_id, day_number),
    UNIQUE KEY uq_event_day_date (event_id, event_date),
    CONSTRAINT fk_days_event FOREIGN KEY (event_id) REFERENCES events(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE rooms (
    id CHAR(36) PRIMARY KEY,
    day_id CHAR(36) NOT NULL,
    name VARCHAR(150) NOT NULL,
    sort_order INT NOT NULL DEFAULT 0,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_room_name_day (day_id, name),
    CONSTRAINT fk_rooms_day FOREIGN KEY (day_id) REFERENCES event_days(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE activity_types (
    code VARCHAR(40) PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    color CHAR(7) NOT NULL,
    icon VARCHAR(50) NULL,
    expandable TINYINT(1) NOT NULL DEFAULT 1,
    sort_order INT NOT NULL DEFAULT 0,
    active TINYINT(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO activity_types (code, name, color, icon, expandable, sort_order) VALUES
('CHARLA', 'Charla', '#e2e2e2', 'mic', 1, 10),
('APERTURA', 'Apertura', '#ffd709', 'celebration', 1, 20),
('PAUSA', 'Pausa', '#454747', 'coffee', 0, 30),
('CIERRE', 'Cierre', '#ffd709', 'flag', 1, 40),
('PREMIACION', 'Premiación', '#f59e0b', 'trophy', 1, 50),
('RECORRIDA', 'Recorrida', '#43b97f', 'directions_walk', 1, 60),
('FIESTA', 'Fiesta', '#8b5cf6', 'music_note', 1, 70)
ON DUPLICATE KEY UPDATE name = VALUES(name), color = VALUES(color), icon = VALUES(icon);

CREATE TABLE activities (
    id CHAR(36) PRIMARY KEY,
    room_id CHAR(36) NOT NULL,
    activity_type VARCHAR(40) NOT NULL,
    type_label VARCHAR(150) NULL,
    start_time TIME NOT NULL,
    end_time TIME NOT NULL,
    ends_next_day TINYINT(1) NOT NULL DEFAULT 0,
    title VARCHAR(255) NOT NULL,
    subtitle VARCHAR(500) NULL,
    description TEXT NULL,
    country_code_override CHAR(2) NULL,
    tags_json JSON NULL,
    sort_order INT NOT NULL DEFAULT 0,
    is_published TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    KEY idx_activity_room_order (room_id, sort_order),
    KEY idx_activity_times (start_time, end_time),
    CONSTRAINT fk_activities_room FOREIGN KEY (room_id) REFERENCES rooms(id) ON DELETE CASCADE,
    CONSTRAINT fk_activities_type FOREIGN KEY (activity_type) REFERENCES activity_types(code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE speakers (
    id CHAR(36) PRIMARY KEY,
    event_id CHAR(36) NOT NULL,
    name VARCHAR(190) NOT NULL,
    position VARCHAR(255) NULL,
    institution VARCHAR(255) NULL,
    country_code CHAR(2) NULL,
    bio TEXT NULL,
    photo_url VARCHAR(1000) NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    KEY idx_speaker_event_name (event_id, name),
    CONSTRAINT fk_speakers_event FOREIGN KEY (event_id) REFERENCES events(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE activity_speakers (
    activity_id CHAR(36) NOT NULL,
    speaker_id CHAR(36) NOT NULL,
    sort_order INT NOT NULL DEFAULT 0,
    PRIMARY KEY (activity_id, speaker_id),
    CONSTRAINT fk_activity_speakers_activity FOREIGN KEY (activity_id) REFERENCES activities(id) ON DELETE CASCADE,
    CONSTRAINT fk_activity_speakers_speaker FOREIGN KEY (speaker_id) REFERENCES speakers(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE sponsors (
    id CHAR(36) PRIMARY KEY,
    event_id CHAR(36) NOT NULL,
    name VARCHAR(190) NOT NULL,
    logo_url VARCHAR(1000) NULL,
    external_url VARCHAR(1000) NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    KEY idx_sponsor_event_name (event_id, name),
    CONSTRAINT fk_sponsors_event FOREIGN KEY (event_id) REFERENCES events(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE activity_sponsors (
    activity_id CHAR(36) NOT NULL,
    sponsor_id CHAR(36) NOT NULL,
    sort_order INT NOT NULL DEFAULT 0,
    PRIMARY KEY (activity_id, sponsor_id),
    CONSTRAINT fk_activity_sponsors_activity FOREIGN KEY (activity_id) REFERENCES activities(id) ON DELETE CASCADE,
    CONSTRAINT fk_activity_sponsors_sponsor FOREIGN KEY (sponsor_id) REFERENCES sponsors(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE networking_settings (
    event_id CHAR(36) PRIMARY KEY,
    name VARCHAR(190) NOT NULL,
    slug VARCHAR(190) NOT NULL UNIQUE,
    detail TEXT NULL,
    confirmation_required TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_networking_event FOREIGN KEY (event_id) REFERENCES events(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE exhibitors (
    id CHAR(36) PRIMARY KEY,
    event_id CHAR(36) NOT NULL,
    name VARCHAR(190) NOT NULL,
    company VARCHAR(190) NOT NULL,
    description TEXT NULL,
    image_url VARCHAR(1000) NULL,
    email VARCHAR(190) NOT NULL,
    phone VARCHAR(80) NULL,
    password_hash VARCHAR(255) NULL,
    visible TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_exhibitor_event_email (event_id, email),
    CONSTRAINT fk_exhibitors_event FOREIGN KEY (event_id) REFERENCES events(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE meeting_requests (
    id CHAR(36) PRIMARY KEY,
    event_id CHAR(36) NOT NULL,
    exhibitor_id CHAR(36) NOT NULL,
    visitor_name VARCHAR(190) NOT NULL,
    visitor_email VARCHAR(190) NOT NULL,
    visitor_cedula VARCHAR(80) NULL,
    visitor_position VARCHAR(190) NULL,
    visitor_phone VARCHAR(80) NULL,
    visitor_company VARCHAR(190) NULL,
    contact_reason VARCHAR(500) NOT NULL,
    contact_comment TEXT NULL,
    confirmation_token CHAR(64) NULL UNIQUE,
    confirmed TINYINT(1) NOT NULL DEFAULT 0,
    confirmed_at DATETIME NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_requests_event FOREIGN KEY (event_id) REFERENCES events(id) ON DELETE CASCADE,
    CONSTRAINT fk_requests_exhibitor FOREIGN KEY (exhibitor_id) REFERENCES exhibitors(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE audit_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id CHAR(36) NULL,
    event_id CHAR(36) NULL,
    action VARCHAR(100) NOT NULL,
    entity_type VARCHAR(80) NOT NULL,
    entity_id VARCHAR(80) NULL,
    metadata_json JSON NULL,
    ip_address VARCHAR(45) NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_audit_event_created (event_id, created_at),
    CONSTRAINT fk_audit_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    CONSTRAINT fk_audit_event FOREIGN KEY (event_id) REFERENCES events(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;