-- Wydarzenia / Podróże służbowe – schema MariaDB 10.5+
-- Uruchom: mysql -u root -p wydarzenia_db < database/schema.sql

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- --------------------------------------------------------
-- Tabela: people
-- --------------------------------------------------------
DROP TABLE IF EXISTS event_participants;
DROP TABLE IF EXISTS events;
DROP TABLE IF EXISTS people;

CREATE TABLE people (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(150) NOT NULL,
    email VARCHAR(150) NULL,
    department VARCHAR(120) NULL,
    active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    PRIMARY KEY (id),
    KEY idx_people_active (active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Tabela: events
-- --------------------------------------------------------
CREATE TABLE events (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    title VARCHAR(200) NOT NULL,
    type ENUM('BUSINESS_TRIP','EVENT','TRAINING','OTHER') NOT NULL DEFAULT 'EVENT',
    date_from DATETIME NOT NULL,
    date_to DATETIME NOT NULL,
    location VARCHAR(200) NULL,
    description TEXT NULL,
    tags JSON NULL,
    created_by BIGINT UNSIGNED NULL,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    PRIMARY KEY (id),
    KEY idx_events_date_from (date_from),
    KEY idx_events_date_to (date_to),
    KEY idx_events_type (type),
    FULLTEXT KEY ft_events_title (title),
    CONSTRAINT chk_date_to_after_from CHECK (date_to >= date_from)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Tabela: event_participants (N:M)
-- --------------------------------------------------------
CREATE TABLE event_participants (
    event_id BIGINT UNSIGNED NOT NULL,
    person_id BIGINT UNSIGNED NOT NULL,
    PRIMARY KEY (event_id, person_id),
    KEY idx_ep_person (person_id),
    CONSTRAINT fk_ep_event FOREIGN KEY (event_id) REFERENCES events (id) ON DELETE CASCADE,
    CONSTRAINT fk_ep_person FOREIGN KEY (person_id) REFERENCES people (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;
