-- ============================================================
-- SCHPOUNTZI — Schéma initial (utf8mb4, InnoDB)
-- Conventions : id CHAR(36) UUID v4, timestamps UTC
-- ============================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ---------- Utilisateurs & auth ----------

CREATE TABLE IF NOT EXISTS users (
    id            CHAR(36)      NOT NULL PRIMARY KEY,
    first_name    VARCHAR(60)   NOT NULL,
    last_name     VARCHAR(60)   NOT NULL,
    username      VARCHAR(30)   NOT NULL UNIQUE,
    email         VARCHAR(190)  NOT NULL UNIQUE,
    password_hash VARCHAR(255)  NOT NULL,
    avatar_url    VARCHAR(255)  NULL,
    created_at    DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at    DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS auth_sessions (
    id                  CHAR(36)     NOT NULL PRIMARY KEY,
    user_id             CHAR(36)     NOT NULL,
    access_token_hash   CHAR(64)     NOT NULL,
    refresh_token_hash  CHAR(64)     NOT NULL,
    access_expires_at   DATETIME     NOT NULL,
    refresh_expires_at  DATETIME     NOT NULL,
    revoked_at          DATETIME     NULL,
    created_at          DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_session_refresh (refresh_token_hash),
    KEY idx_session_access (access_token_hash),
    KEY idx_session_user (user_id),
    CONSTRAINT fk_session_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS rate_limits (
    bucket      VARCHAR(190) NOT NULL PRIMARY KEY,
    attempts    INT UNSIGNED NOT NULL DEFAULT 0,
    reset_at    DATETIME     NOT NULL,
    KEY idx_rl_reset (reset_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------- Social ----------

CREATE TABLE IF NOT EXISTS friendships (
    id           CHAR(36) NOT NULL PRIMARY KEY,
    requester_id CHAR(36) NOT NULL,
    addressee_id CHAR(36) NOT NULL,
    status       ENUM('pending','accepted','declined') NOT NULL DEFAULT 'pending',
    created_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_friendship_pair (requester_id, addressee_id),
    CONSTRAINT fk_friend_requester FOREIGN KEY (requester_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_friend_addressee FOREIGN KEY (addressee_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `groups` (
    id         CHAR(36)     NOT NULL PRIMARY KEY,
    name       VARCHAR(80)  NOT NULL,
    image_url  VARCHAR(255) NULL,
    created_by CHAR(36)     NOT NULL,
    created_at DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_group_creator FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS group_members (
    group_id   CHAR(36) NOT NULL,
    user_id    CHAR(36) NOT NULL,
    role       ENUM('admin','member') NOT NULL DEFAULT 'member',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (group_id, user_id),
    CONSTRAINT fk_gm_group FOREIGN KEY (group_id) REFERENCES `groups`(id) ON DELETE CASCADE,
    CONSTRAINT fk_gm_user  FOREIGN KEY (user_id)  REFERENCES users(id)   ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------- Événements (Schpountzis) ----------

CREATE TABLE IF NOT EXISTS events (
    id          CHAR(36)      NOT NULL PRIMARY KEY,
    title       VARCHAR(120)  NOT NULL,
    description TEXT          NULL,
    category    VARCHAR(30)   NOT NULL DEFAULT 'other',
    image_url   VARCHAR(255)  NULL,
    location    VARCHAR(120)  NULL,
    address     VARCHAR(255)  NULL,
    organizer_id CHAR(36)     NOT NULL,
    starts_at   DATETIME      NULL,            -- NULL = vote des dates en cours
    invite_code VARCHAR(12)   NOT NULL UNIQUE, -- lien public /i/{code}
    modules     JSON          NOT NULL,        -- ["chat","polls","items","expenses","photos"]
    status      ENUM('draft','upcoming','past','cancelled') NOT NULL DEFAULT 'upcoming',
    created_at  DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at  DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    KEY idx_events_organizer (organizer_id),
    KEY idx_events_starts (starts_at),
    CONSTRAINT fk_event_organizer FOREIGN KEY (organizer_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS event_members (
    event_id   CHAR(36) NOT NULL,
    user_id    CHAR(36) NOT NULL,
    rsvp       ENUM('going','maybe','declined','pending') NOT NULL DEFAULT 'pending',
    role       ENUM('organizer','guest') NOT NULL DEFAULT 'guest',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (event_id, user_id),
    KEY idx_em_user (user_id),
    CONSTRAINT fk_em_event FOREIGN KEY (event_id) REFERENCES events(id) ON DELETE CASCADE,
    CONSTRAINT fk_em_user  FOREIGN KEY (user_id)  REFERENCES users(id)  ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS event_date_options (
    id         CHAR(36) NOT NULL PRIMARY KEY,
    event_id   CHAR(36) NOT NULL,
    starts_at  DATETIME NOT NULL,
    created_by CHAR(36) NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_edo_event (event_id),
    CONSTRAINT fk_edo_event FOREIGN KEY (event_id)   REFERENCES events(id) ON DELETE CASCADE,
    CONSTRAINT fk_edo_user  FOREIGN KEY (created_by) REFERENCES users(id)  ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS event_date_votes (
    date_option_id CHAR(36) NOT NULL,
    user_id        CHAR(36) NOT NULL,
    status         ENUM('going','maybe','declined') NOT NULL,
    created_at     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (date_option_id, user_id),
    CONSTRAINT fk_edv_option FOREIGN KEY (date_option_id) REFERENCES event_date_options(id) ON DELETE CASCADE,
    CONSTRAINT fk_edv_user   FOREIGN KEY (user_id)        REFERENCES users(id)             ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS user_availability (
    id         CHAR(36) NOT NULL PRIMARY KEY,
    user_id    CHAR(36) NOT NULL,
    day        DATE     NOT NULL,
    status     ENUM('available','maybe','unavailable') NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_avail (user_id, day),
    CONSTRAINT fk_avail_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------- Modules ----------

CREATE TABLE IF NOT EXISTS messages (
    id         CHAR(36)   NOT NULL PRIMARY KEY,
    event_id   CHAR(36)   NOT NULL,
    user_id    CHAR(36)   NOT NULL,
    body       TEXT       NOT NULL,
    created_at DATETIME   NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_msg_event (event_id, created_at),
    CONSTRAINT fk_msg_event FOREIGN KEY (event_id) REFERENCES events(id) ON DELETE CASCADE,
    CONSTRAINT fk_msg_user  FOREIGN KEY (user_id)  REFERENCES users(id)  ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS polls (
    id         CHAR(36)     NOT NULL PRIMARY KEY,
    event_id   CHAR(36)     NOT NULL,
    question   VARCHAR(200) NOT NULL,
    multiple   TINYINT(1)   NOT NULL DEFAULT 0,
    created_by CHAR(36)     NOT NULL,
    created_at DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_poll_event (event_id),
    CONSTRAINT fk_poll_event FOREIGN KEY (event_id)   REFERENCES events(id) ON DELETE CASCADE,
    CONSTRAINT fk_poll_user  FOREIGN KEY (created_by) REFERENCES users(id)  ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS poll_options (
    id      CHAR(36)     NOT NULL PRIMARY KEY,
    poll_id CHAR(36)     NOT NULL,
    label   VARCHAR(120) NOT NULL,
    CONSTRAINT fk_po_poll FOREIGN KEY (poll_id) REFERENCES polls(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS poll_votes (
    option_id  CHAR(36) NOT NULL,
    user_id    CHAR(36) NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (option_id, user_id),
    CONSTRAINT fk_pv_option FOREIGN KEY (option_id) REFERENCES poll_options(id) ON DELETE CASCADE,
    CONSTRAINT fk_pv_user   FOREIGN KEY (user_id)   REFERENCES users(id)        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS event_items (
    id         CHAR(36)     NOT NULL PRIMARY KEY,
    event_id   CHAR(36)     NOT NULL,
    label      VARCHAR(120) NOT NULL,
    emoji      VARCHAR(8)   NULL,
    claimed_by CHAR(36)     NULL,
    created_at DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    KEY idx_items_event (event_id),
    CONSTRAINT fk_item_event   FOREIGN KEY (event_id)   REFERENCES events(id) ON DELETE CASCADE,
    CONSTRAINT fk_item_claimed FOREIGN KEY (claimed_by) REFERENCES users(id)  ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS expenses (
    id         CHAR(36)      NOT NULL PRIMARY KEY,
    event_id   CHAR(36)      NOT NULL,
    title      VARCHAR(120)  NOT NULL,
    amount     DECIMAL(10,2) NOT NULL,
    paid_by    CHAR(36)      NOT NULL,
    created_at DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_exp_event (event_id),
    CONSTRAINT fk_exp_event FOREIGN KEY (event_id) REFERENCES events(id) ON DELETE CASCADE,
    CONSTRAINT fk_exp_payer FOREIGN KEY (paid_by)  REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS expense_participants (
    expense_id CHAR(36)      NOT NULL,
    user_id    CHAR(36)      NOT NULL,
    share      DECIMAL(10,2) NOT NULL,
    PRIMARY KEY (expense_id, user_id),
    CONSTRAINT fk_ep_expense FOREIGN KEY (expense_id) REFERENCES expenses(id) ON DELETE CASCADE,
    CONSTRAINT fk_ep_user    FOREIGN KEY (user_id)    REFERENCES users(id)   ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS event_photos (
    id          CHAR(36)     NOT NULL PRIMARY KEY,
    event_id    CHAR(36)     NOT NULL,
    path        VARCHAR(255) NOT NULL,  -- ex: "events/<uuid>/ab12cd.webp"
    uploaded_by CHAR(36)     NOT NULL,
    created_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_photo_event (event_id),
    CONSTRAINT fk_photo_event FOREIGN KEY (event_id)    REFERENCES events(id) ON DELETE CASCADE,
    CONSTRAINT fk_photo_user  FOREIGN KEY (uploaded_by) REFERENCES users(id)  ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------- Notifications & push ----------

CREATE TABLE IF NOT EXISTS notifications (
    id         CHAR(36)     NOT NULL PRIMARY KEY,
    user_id    CHAR(36)     NOT NULL,
    title      VARCHAR(160) NOT NULL,
    body       VARCHAR(255) NULL,
    type       VARCHAR(40)  NULL,
    event_id   CHAR(36)     NULL,
    read_at    DATETIME     NULL,
    created_at DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_notif_user (user_id, read_at),
    CONSTRAINT fk_notif_user  FOREIGN KEY (user_id)  REFERENCES users(id)  ON DELETE CASCADE,
    CONSTRAINT fk_notif_event FOREIGN KEY (event_id) REFERENCES events(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS push_tokens (
    id         CHAR(36)     NOT NULL PRIMARY KEY,
    user_id    CHAR(36)     NOT NULL,
    token      VARCHAR(120) NOT NULL UNIQUE,  -- Expo push token
    platform   ENUM('ios','android') NOT NULL,
    created_at DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    KEY idx_push_user (user_id),
    CONSTRAINT fk_push_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------- Suivi des migrations ----------

CREATE TABLE IF NOT EXISTS migrations (
    id         INT UNSIGNED  NOT NULL AUTO_INCREMENT PRIMARY KEY,
    filename   VARCHAR(190)  NOT NULL UNIQUE,
    applied_at DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;