<?php
// ─── INITIALISATION DES TABLES GROUPES ────────────────────────────────────────
// Exécuter UNE SEULE FOIS après init_db.php :
// http://tonsite.com/api/init_groups.php

require 'db.php';

$sqls = [];

// Table groups
$sqls[] = "CREATE TABLE IF NOT EXISTS groups_tbl (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    name        VARCHAR(100) NOT NULL,
    code        VARCHAR(10)  NOT NULL UNIQUE,
    created_by  INT          NOT NULL,
    created_at  TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;";

// Table group_members
$sqls[] = "CREATE TABLE IF NOT EXISTS group_members (
    id         INT AUTO_INCREMENT PRIMARY KEY,
    group_id   INT       NOT NULL,
    user_id    INT       NOT NULL,
    joined_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_group_user (group_id, user_id),
    FOREIGN KEY (group_id) REFERENCES groups_tbl(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id)  REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;";

// Table group_daily_answers
$sqls[] = "CREATE TABLE IF NOT EXISTS group_daily_answers (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    group_id    INT          NOT NULL,
    user_id     INT          NOT NULL,
    day         INT          NOT NULL,
    answer      VARCHAR(255) NOT NULL,
    correct     TINYINT(1)   NOT NULL,
    answered_at TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_group_user_day (group_id, user_id, day),
    FOREIGN KEY (group_id) REFERENCES groups_tbl(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id)  REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;";

// Table group_challenges
$sqls[] = "CREATE TABLE IF NOT EXISTS group_challenges (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    group_id    INT          NOT NULL,
    category    VARCHAR(80)  NOT NULL,
    questions   LONGTEXT     NOT NULL,
    created_by  INT          NOT NULL,
    created_at  TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (group_id)  REFERENCES groups_tbl(id) ON DELETE CASCADE,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;";

// Table group_challenge_results
$sqls[] = "CREATE TABLE IF NOT EXISTS group_challenge_results (
    id           INT AUTO_INCREMENT PRIMARY KEY,
    challenge_id INT       NOT NULL,
    user_id      INT       NOT NULL,
    score        INT       NOT NULL DEFAULT 0,
    good         INT       NOT NULL DEFAULT 0,
    bad          INT       NOT NULL DEFAULT 0,
    answers      LONGTEXT,
    finished_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_challenge_user (challenge_id, user_id),
    FOREIGN KEY (challenge_id) REFERENCES group_challenges(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id)      REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;";

// Table group_chat
$sqls[] = "CREATE TABLE IF NOT EXISTS group_chat (
    id         INT AUTO_INCREMENT PRIMARY KEY,
    group_id   INT          NOT NULL,
    user_id    INT          NOT NULL,
    type       VARCHAR(20)  NOT NULL DEFAULT 'text',
    content    TEXT         NOT NULL,
    sent_at    TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (group_id) REFERENCES groups_tbl(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id)  REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_group_sent (group_id, sent_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;";

$errors = [];
foreach ($sqls as $sql) {
    try {
        $pdo->exec($sql);
    } catch (PDOException $e) {
        $errors[] = $e->getMessage();
    }
}

header('Content-Type: application/json');
if (empty($errors)) {
    echo json_encode(['message' => 'Tables groupes créées avec succès ✅']);
} else {
    http_response_code(500);
    echo json_encode(['message' => 'Erreurs lors de la création', 'errors' => $errors]);
}
