-- Schéma de la base de données SQLite pour Framed Tracker

-- Table des utilisateurs
CREATE TABLE users (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  username TEXT UNIQUE NOT NULL,
  password TEXT NOT NULL,           -- Mot de passe hashé avec bcrypt
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Index sur le nom d'utilisateur pour accélérer les recherches
CREATE UNIQUE INDEX idx_users_username ON users(username);

-- Table des scores
CREATE TABLE scores (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id INTEGER NOT NULL,         -- Référence à l'utilisateur
  date TEXT NOT NULL,               -- Date au format YYYY-MM-DD
  game_type TEXT NOT NULL,          -- "daily" ou "one"
  game_number INTEGER,              -- Numéro optionnel du jeu
  score INTEGER NOT NULL,           -- Score entre 1 et 6
  comment TEXT,                     -- Commentaire optionnel sur le jeu
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  
  -- Contrainte de clé étrangère pour assurer l'intégrité référentielle
  FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
);

-- Index sur user_id pour accélérer les recherches par utilisateur
CREATE INDEX idx_scores_user_id ON scores(user_id);

-- Index sur game_type pour accélérer les filtres par type de jeu
CREATE INDEX idx_scores_game_type ON scores(game_type);

-- Index composite pour les recherches fréquentes
CREATE INDEX idx_scores_user_game_type ON scores(user_id, game_type);

-- Vue pour les statistiques globales par utilisateur
CREATE VIEW user_stats AS
SELECT 
  user_id,
  COUNT(*) AS total_games,
  AVG(score) AS average_score,
  MIN(score) AS best_score
FROM scores
GROUP BY user_id;

-- Vue pour les statistiques par type de jeu et utilisateur
CREATE VIEW user_game_type_stats AS
SELECT 
  user_id,
  game_type,
  COUNT(*) AS total_games,
  AVG(score) AS average_score,
  MIN(score) AS best_score
FROM scores
GROUP BY user_id, game_type;

-- Vue pour le classement général
CREATE VIEW leaderboard AS
SELECT 
  u.id,
  u.username,
  COUNT(s.id) AS total_games,
  AVG(s.score) AS average_score,
  MIN(s.score) AS best_score
FROM users u
JOIN scores s ON u.id = s.user_id
GROUP BY u.id
ORDER BY average_score ASC, best_score ASC;

-- Vue pour le classement par type de jeu
CREATE VIEW leaderboard_by_type AS
SELECT 
  u.id,
  u.username,
  s.game_type,
  COUNT(s.id) AS total_games,
  AVG(s.score) AS average_score,
  MIN(s.score) AS best_score
FROM users u
JOIN scores s ON u.id = s.user_id
GROUP BY u.id, s.game_type
ORDER BY s.game_type, average_score ASC, best_score ASC;

-- Exemple d'insertion d'utilisateur (dans une application réelle, le mot de passe serait hashé)
-- INSERT INTO users (username, password) VALUES ('joueur1', 'motdepasse_hashed');

-- Exemple d'insertion de score
-- INSERT INTO scores (user_id, date, game_type, game_number, score) VALUES (1, '2025-03-05', 'daily', 1090, 3);