src/lib/txasupabase.js

import { createClient } from '@supabase/supabase-js';
import crypto from 'node:crypto';

const supabaseUrl = import.meta.env.PUBLIC_SUPABASE_URL || process.env.PUBLIC_SUPABASE_URL;
const supabaseAnonKey = import.meta.env.PUBLIC_SUPABASE_ANON_KEY || process.env.PUBLIC_SUPABASE_ANON_KEY;

if (!supabaseUrl || !supabaseAnonKey) {
  console.warn("Supabase credentials missing! Please configure PUBLIC_SUPABASE_URL and PUBLIC_SUPABASE_ANON_KEY in your .env file.");
}

export const supabase = createClient(supabaseUrl, supabaseAnonKey);

// Kiểm tra kết nối Supabase và in log ra Terminal CLI
if (supabaseUrl && supabaseAnonKey) {
  supabase.from('settings').select('key').limit(1)
    .then(({ error }) => {
      if (error) {
        console.log("\x1b[41m\x1b[37m[Supabase]\x1b[0m \x1b[31m❌ KẾT NỐI THẤT BẠI: " + error.message + "\x1b[0m");
      } else {
        console.log("\x1b[42m\x1b[30m[Supabase]\x1b[0m \x1b[32m✅ KẾT NỐI THÀNH CÔNG tới " + supabaseUrl + "\x1b[0m");
      }
    })
    .catch(err => {
      console.log("\x1b[41m\x1b[37m[Supabase]\x1b[0m \x1b[31m❌ LỖI KẾT NỐI: " + err.message + "\x1b[0m");
    });
}


export class txasupabase {
  static get client() { return supabase; }
  static get supabase() { return supabase; }

  // --- MOVIES ---
  static async getLocalMovies() {
    const { data, error } = await supabase
      .from('movies')
      .select('slug, movie, episodes');
    if (error) {
      console.error('Error fetching movies from Supabase:', error);
      return {};
    }
    const moviesObj = {};
    (data || []).forEach(row => {
      moviesObj[row.slug] = {
        movie: row.movie,
        episodes: row.episodes
      };
    });
    return moviesObj;
  }

  static async saveLocalMovie(slug, movieData) {
    const existing = await this.getMovieBySlug(slug);
    const updatedMovie = {
      ...existing?.movie,
      ...movieData.movie,
      slug,
      modified: { time: new Date().toISOString() }
    };
    const updatedEpisodes = movieData.episodes || existing?.episodes || [];

    // So sánh xem có tập mới không!
    let newEpisodesAdded = [];
    if (existing && existing.episodes && movieData.episodes) {
      for (const newServer of movieData.episodes) {
        const extServer = existing.episodes.find(s => s.server_name === newServer.server_name);
        if (!extServer) {
          newEpisodesAdded.push(...(newServer.server_data || []));
        } else {
          for (const newEp of (newServer.server_data || [])) {
            const hasEp = extServer.server_data.some(e => e.name === newEp.name);
            if (!hasEp) {
              newEpisodesAdded.push(newEp);
            }
          }
        }
      }
    }

    const { data, error } = await supabase
      .from('movies')
      .upsert({
        slug,
        movie: updatedMovie,
        episodes: updatedEpisodes,
        updated_at: new Date().toISOString()
      })
      .select()
      .single();

    if (error) {
      console.error(`Error saving movie ${slug} to Supabase:`, error);
      throw error;
    }

    if (newEpisodesAdded.length > 0) {
      this.notifyNewEpisodes(slug, updatedMovie.name, newEpisodesAdded).catch(err => {
        console.error('[Notification Trigger Error]:', err);
      });
    }

    return {
      movie: data.movie,
      episodes: data.episodes
    };
  }

  static async getMovieBySlug(slug) {
    const { data, error } = await supabase
      .from('movies')
      .select('slug, movie, episodes')
      .eq('slug', slug)
      .maybeSingle();
    if (error) {
      console.error(`Error fetching movie ${slug} from Supabase:`, error);
      return null;
    }
    return data ? { movie: data.movie, episodes: data.episodes } : null;
  }

  static async resetLocalMovie(slug) {
    const { error } = await supabase
      .from('movies')
      .delete()
      .eq('slug', slug);
    if (error) {
      console.error(`Error resetting movie ${slug} on Supabase:`, error);
      return false;
    }
    return true;
  }

  // --- DELETED MOVIES ---
  static async getDeletedMovies() {
    const { data, error } = await supabase
      .from('deleted_movies')
      .select('slug');
    if (error) {
      console.error('Error fetching deleted movies from Supabase:', error);
      return [];
    }
    return (data || []).map(row => row.slug);
  }

  static async deleteLocalMovie(slug) {
    // 1. Nếu là phim cục bộ tự tạo, xóa khỏi movies
    const movieObj = await this.getMovieBySlug(slug);
    let isLocalOnly = false;
    if (movieObj && movieObj.movie.isLocal) {
      const { error } = await supabase
        .from('movies')
        .delete()
        .eq('slug', slug);
      if (error) console.error('Error deleting local movie from Supabase:', error);
      isLocalOnly = true;
    }

    // 2. Thêm vào deleted_movies để ẩn khỏi API
    const { error: delError } = await supabase
      .from('deleted_movies')
      .upsert({ slug })
      .select();
    if (delError) {
      console.error('Error adding movie to deleted_movies on Supabase:', delError);
    }
    return { success: true, isLocalOnly };
  }

  static async restoreLocalMovie(slug) {
    const { error } = await supabase
      .from('deleted_movies')
      .delete()
      .eq('slug', slug);
    if (error) {
      console.error(`Error restoring movie ${slug} from Supabase:`, error);
      return false;
    }
    return true;
  }

  // --- GENRES ---
  static async getGenres() {
    const { data, error } = await supabase
      .from('genres')
      .select('slug, name')
      .order('name');
    if (error) {
      console.error('Error fetching genres from Supabase:', error);
      return [];
    }
    return data || [];
  }

  static async saveGenre(slug, name) {
    const { error } = await supabase
      .from('genres')
      .upsert({ slug, name });
    if (error) {
      console.error(`Error saving genre ${slug} to Supabase:`, error);
    }
    return await this.getGenres();
  }

  static async deleteGenre(slug) {
    const { error } = await supabase
      .from('genres')
      .delete()
      .eq('slug', slug);
    if (error) {
      console.error(`Error deleting genre ${slug} from Supabase:`, error);
    }
    return await this.getGenres();
  }

  static async bulkDeleteGenres(slugs) {
    const { error } = await supabase
      .from('genres')
      .delete()
      .in('slug', slugs);
    if (error) {
      console.error('Error bulk deleting genres from Supabase:', error);
    }
    return await this.getGenres();
  }

  // --- COUNTRIES ---
  static async getCountries() {
    const { data, error } = await supabase
      .from('countries')
      .select('slug, name')
      .order('name');
    if (error) {
      console.error('Error fetching countries from Supabase:', error);
      return [];
    }
    return data || [];
  }

  static async saveCountry(slug, name) {
    const { error } = await supabase
      .from('countries')
      .upsert({ slug, name });
    if (error) {
      console.error(`Error saving country ${slug} to Supabase:`, error);
    }
    return await this.getCountries();
  }

  static async deleteCountry(slug) {
    const { error } = await supabase
      .from('countries')
      .delete()
      .eq('slug', slug);
    if (error) {
      console.error(`Error deleting country ${slug} from Supabase:`, error);
    }
    return await this.getCountries();
  }

  static async bulkDeleteCountries(slugs) {
    const { error } = await supabase
      .from('countries')
      .delete()
      .in('slug', slugs);
    if (error) {
      console.error('Error bulk deleting countries from Supabase:', error);
    }
    return await this.getCountries();
  }

  // --- ACTORS ---
  static async getActors() {
    const { data, error } = await supabase
      .from('actors')
      .select('name')
      .order('name');
    if (error) {
      console.error('Error fetching actors from Supabase:', error);
      return [];
    }
    return data || [];
  }

  static async saveActor(name, originalName = null) {
    const cleanName = name.trim();
    if (originalName && originalName.trim().toLowerCase() !== cleanName.toLowerCase()) {
      const { error: delError } = await supabase
        .from('actors')
        .delete()
        .eq('name', originalName.trim());
      if (delError) console.error('Error deleting renamed actor on Supabase:', delError);
    }
    const { error } = await supabase
      .from('actors')
      .upsert({ name: cleanName });
    if (error) {
      console.error(`Error saving actor ${cleanName} to Supabase:`, error);
    }
    return await this.getActors();
  }

  static async deleteActor(name) {
    const { error } = await supabase
      .from('actors')
      .delete()
      .eq('name', name);
    if (error) {
      console.error(`Error deleting actor ${name} from Supabase:`, error);
    }
    return await this.getActors();
  }

  static async bulkDeleteActors(names) {
    const { error } = await supabase
      .from('actors')
      .delete()
      .in('name', names);
    if (error) {
      console.error('Error bulk deleting actors from Supabase:', error);
    }
    return await this.getActors();
  }

  // --- SERVERS ---
  static async getServers() {
    const { data, error } = await supabase
      .from('servers')
      .select('name')
      .order('name');
    if (error) {
      console.error('Error fetching servers from Supabase:', error);
      return [];
    }
    return data || [];
  }

  static async saveServer(name, originalName = null) {
    const cleanName = name.trim();
    if (originalName && originalName.trim().toLowerCase() !== cleanName.toLowerCase()) {
      const { error: delError } = await supabase
        .from('servers')
        .delete()
        .eq('name', originalName.trim());
      if (delError) console.error('Error deleting renamed server on Supabase:', delError);
    }
    const { error } = await supabase
      .from('servers')
      .upsert({ name: cleanName });
    if (error) {
      console.error(`Error saving server ${cleanName} to Supabase:`, error);
    }
    return await this.getServers();
  }

  static async deleteServer(name) {
    const { error } = await supabase
      .from('servers')
      .delete()
      .eq('name', name);
    if (error) {
      console.error(`Error deleting server ${name} from Supabase:`, error);
    }
    return await this.getServers();
  }

  static async bulkDeleteServers(names) {
    const { error } = await supabase
      .from('servers')
      .delete()
      .in('name', names);
    if (error) {
      console.error('Error bulk deleting servers from Supabase:', error);
    }
    return await this.getServers();
  }

  // --- AUTOMATIC SYNC FROM MOVIES DATA ---
  static async syncFromMovies(type) {
    const moviesObj = await this.getLocalMovies();
    const allMovies = Object.values(moviesObj);
    
    if (type === 'servers') {
      const serversList = await this.getServers();
      const serverSet = new Set(serversList.map(s => s.name.toLowerCase()));
      const extracted = new Set();
      
      for (const item of allMovies) {
        if (item.episodes) {
          for (const epGroup of item.episodes) {
            if (epGroup.server_name) {
              extracted.add(epGroup.server_name.trim());
            }
          }
        }
      }
      
      const newServers = [];
      for (const sName of extracted) {
        if (!serverSet.has(sName.toLowerCase())) {
          newServers.push({ name: sName });
          serverSet.add(sName.toLowerCase());
        }
      }
      
      if (newServers.length > 0) {
        const { error } = await supabase.from('servers').insert(newServers);
        if (error) console.error('Error syncing servers to Supabase:', error);
      }
      return await this.getServers();
    }
    
    if (type === 'genres') {
      const genresList = await this.getGenres();
      const genreMap = new Map(genresList.map(g => [g.slug, g.name]));
      const newGenres = [];
      
      for (const item of allMovies) {
        const cats = item.movie.category || [];
        for (const cat of cats) {
          if (cat.slug && cat.name) {
            if (!genreMap.has(cat.slug)) {
              newGenres.push({ slug: cat.slug, name: cat.name });
              genreMap.set(cat.slug, cat.name);
            }
          }
        }
      }
      
      if (newGenres.length > 0) {
        const { error } = await supabase.from('genres').insert(newGenres);
        if (error) console.error('Error syncing genres to Supabase:', error);
      }
      return await this.getGenres();
    }
    
    if (type === 'countries') {
      const countriesList = await this.getCountries();
      const countryMap = new Map(countriesList.map(c => [c.slug, c.name]));
      const newCountries = [];
      
      for (const item of allMovies) {
        const countries = item.movie.country || [];
        for (const c of countries) {
          if (c.slug && c.name) {
            if (!countryMap.has(c.slug)) {
              newCountries.push({ slug: c.slug, name: c.name });
              countryMap.set(c.slug, c.name);
            }
          }
        }
      }
      
      if (newCountries.length > 0) {
        const { error } = await supabase.from('countries').insert(newCountries);
        if (error) console.error('Error syncing countries to Supabase:', error);
      }
      return await this.getCountries();
    }
    
    if (type === 'actors') {
      const actorsList = await this.getActors();
      const actorSet = new Set(actorsList.map(a => a.name.toLowerCase()));
      const extracted = new Set();
      
      for (const item of allMovies) {
        const actors = item.movie.actor || [];
        for (const actor of actors) {
          if (actor && typeof actor === 'string') {
            extracted.add(actor.trim());
          }
        }
      }
      
      const newActors = [];
      for (const actorName of extracted) {
        if (!actorSet.has(actorName.toLowerCase())) {
          newActors.push({ name: actorName });
          actorSet.add(actorName.toLowerCase());
        }
      }
      
      if (newActors.length > 0) {
        const { error } = await supabase.from('actors').insert(newActors);
        if (error) console.error('Error syncing actors to Supabase:', error);
      }
      return await this.getActors();
    }
    
    return [];
  }

  // --- USERS ---
  static async getUsers() {
    const { data, error } = await supabase
      .from('users')
      .select('*');
    if (error) {
      console.error('Error fetching users from Supabase:', error);
      return [];
    }
    return data || [];
  }

  static async getUserByUsername(username) {
    const { data, error } = await supabase
      .from('users')
      .select('*')
      .eq('username', username)
      .maybeSingle();
    if (error) {
      console.error(`Error fetching user ${username} from Supabase:`, error);
      return null;
    }
    return data;
  }

  static async getUserById(id) {
    const { data, error } = await supabase
      .from('users')
      .select('*')
      .eq('id', id)
      .maybeSingle();
    if (error) {
      console.error(`Error fetching user by ID ${id} from Supabase:`, error);
      return null;
    }
    return data;
  }

  static getGravatarUrl(email) {
    if (!email) return 'https://www.gravatar.com/avatar/00000000000000000000000000000000?d=mp';
    const hash = crypto.createHash('md5').update(email.trim().toLowerCase()).digest('hex');
    return `https://www.gravatar.com/avatar/${hash}?d=mp`;
  }

  static async saveUser(user) {
    const defaultAvatar = user.email ? this.getGravatarUrl(user.email) : 'https://www.gravatar.com/avatar/00000000000000000000000000000000?d=mp';
    const { data, error } = await supabase
      .from('users')
      .upsert({
        username: user.username,
        id: user.id,
        email: user.email,
        password: user.password,
        province: user.province || '',
        ward: user.ward || '',
        role: user.role || 'user',
        favorites: user.favorites || [],
        history: user.history || [],
        avatar_url: user.avatar_url || defaultAvatar,
        created_at: user.created_at || user.createdAt || new Date().toISOString()
      })
      .select()
      .single();
    if (error) {
      console.error(`Error saving user ${user.username} to Supabase:`, error);
      throw error;
    }
    return data;
  }

  // --- RATINGS ---
  static async getRatingBySlug(slug) {
    const { data, error } = await supabase
      .from('ratings')
      .select('*')
      .eq('slug', slug)
      .maybeSingle();
    if (error) {
      console.error(`Error fetching rating for ${slug}:`, error);
      return null;
    }
    return data;
  }

  static async saveRating(slug, count, totalScore, userRatings, ipRatings = {}) {
    const { data, error } = await supabase
      .from('ratings')
      .upsert({
        slug,
        count,
        total_score: totalScore,
        user_ratings: userRatings,
        ip_ratings: ipRatings,
        updated_at: new Date().toISOString()
      })
      .select()
      .single();
    if (error) {
      console.error(`Error saving rating for ${slug}:`, error);
      throw error;
    }
    return data;
  }

  // --- MOVIE VIEWS & SUGGESTIONS ---
  static async incrementViews(slug, ip = '127.0.0.1') {
    try {
      const { data: existing, error: fetchError } = await supabase
        .from('movie_views')
        .select('views, ip_log')
        .eq('slug', slug)
        .maybeSingle();

      if (fetchError) {
        console.error(`Error fetching views for ${slug}:`, fetchError);
        return null;
      }

      let views = existing?.views || 0;
      let ipLog = existing?.ip_log || {};
      
      const now = new Date();
      const lastViewTimeStr = ipLog[ip];
      
      if (lastViewTimeStr) {
        const lastViewTime = new Date(lastViewTimeStr);
        const diffMs = now.getTime() - lastViewTime.getTime();
        const diffHours = diffMs / (1000 * 60 * 60);
        
        // Nếu IP này đã xem phim này trong vòng 24 giờ qua, không tính thêm lượt xem
        if (diffHours < 24) {
          return existing;
        }
      }

      // Tăng lượt xem lên 1
      views += 1;
      
      // Ghi nhận thời gian xem của IP này
      ipLog[ip] = now.toISOString();

      // Dọn dẹp ipLog: chỉ giữ lại các địa chỉ IP đã xem trong vòng 24 giờ qua để tiết kiệm dung lượng JSON
      const cleanIpLog = {};
      const limitMs = 24 * 60 * 60 * 1000;
      
      Object.entries(ipLog).forEach(([key, val]) => {
        if (typeof val === 'string') {
          const t = new Date(val).getTime();
          if (now.getTime() - t < limitMs) {
            cleanIpLog[key] = val;
          }
        }
      });

      const { data, error } = await supabase
        .from('movie_views')
        .upsert({
          slug,
          views,
          ip_log: cleanIpLog,
          updated_at: now.toISOString()
        })
        .select()
        .single();

      if (error) {
        console.error(`Error incrementing views for ${slug}:`, error);
        return null;
      }

      return data;
    } catch (e) {
      console.error(`Error in incrementViews for ${slug}:`, e);
      return null;
    }
  }

  static async getTopViewedMovies(limit = 10) {
    try {
      const { data, error } = await supabase
        .from('movie_views')
        .select('slug, views')
        .order('views', { ascending: false })
        .limit(limit);

      if (error) {
        console.error('Error fetching top viewed movies:', error);
        return [];
      }
      
      if (!data || !data.length) return [];

      const slugs = data.map(d => d.slug);
      const { data: movies, error: moviesError } = await supabase
        .from('movies')
        .select('slug, movie')
        .in('slug', slugs);

      if (moviesError) {
        console.error('Error fetching movie details for top viewed:', moviesError);
        return [];
      }

      return data
        .map(v => {
          const m = movies.find(x => x.slug === v.slug);
          if (!m) return null;
          return {
            ...m.movie,
            views: v.views
          };
        })
        .filter(Boolean);
    } catch (e) {
      console.error('Error in getTopViewedMovies:', e);
      return [];
    }
  }

  static async getTopRatedMovies(limit = 10) {
    try {
      const { data, error } = await supabase
        .from('ratings')
        .select('slug, count, total_score');

      if (error) {
        console.error('Error fetching ratings for top rated:', error);
        return [];
      }

      if (!data || !data.length) return [];

      const sorted = data
        .map(r => ({
          slug: r.slug,
          avg: r.count > 0 ? (Number(r.total_score) / r.count) : 0,
          count: r.count
        }))
        .sort((a, b) => b.avg - a.avg || b.count - a.count)
        .slice(0, limit);

      const slugs = sorted.map(s => s.slug);
      const { data: movies, error: moviesError } = await supabase
        .from('movies')
        .select('slug, movie')
        .in('slug', slugs);

      if (moviesError) {
        console.error('Error fetching movie details for top rated:', moviesError);
        return [];
      }

      return sorted
        .map(s => {
          const m = movies.find(x => x.slug === s.slug);
          if (!m) return null;
          return {
            ...m.movie,
            rating: s.avg.toFixed(1),
            vote_count: s.count
          };
        })
        .filter(Boolean);
    } catch (e) {
      console.error('Error in getTopRatedMovies:', e);
      return [];
    }
  }

  static async getMostFavoritedMovies(limit = 10) {
    try {
      const { data: users, error } = await supabase
        .from('users')
        .select('favorites');

      if (error) {
        console.error('Error fetching users for favorites count:', error);
        return [];
      }

      const counts = {};
      users?.forEach(u => {
        const favs = u.favorites || [];
        if (Array.isArray(favs)) {
          favs.forEach(f => {
            if (f.slug) {
              counts[f.slug] = (counts[f.slug] || 0) + 1;
            }
          });
        }
      });

      const sortedSlugs = Object.entries(counts)
        .sort((a, b) => b[1] - a[1])
        .slice(0, limit)
        .map(entry => entry[0]);

      if (sortedSlugs.length === 0) return [];

      const { data: movies, error: moviesError } = await supabase
        .from('movies')
        .select('slug, movie')
        .in('slug', sortedSlugs);

      if (moviesError) {
        console.error('Error fetching movie details for most favorited:', moviesError);
        return [];
      }

      return sortedSlugs
        .map(slug => {
          const m = movies.find(x => x.slug === slug);
          if (!m) return null;
          return {
            ...m.movie,
            favorite_count: counts[slug]
          };
        })
        .filter(Boolean);
    } catch (e) {
      console.error('Error in getMostFavoritedMovies:', e);
      return [];
    }
  }

  // --- SETTINGS & EMAIL LOGS ---
  static async getSiteUrl(request = null) {
    let url = await this.getSetting('site_url');
    if (!url) {
      if (process.env.SITE_URL) {
        url = process.env.SITE_URL;
      } else if (process.env.VERCEL_URL) {
        url = `https://${process.env.VERCEL_URL}`;
      } else if (request) {
        try {
          const host = request.headers.get('host') || request.url ? new URL(request.url).host : null;
          const proto = request.headers.get('x-forwarded-proto') || 'http';
          url = host ? `${proto}://${host}` : 'http://localhost:4321';
        } catch (e) {
          url = 'http://localhost:4321';
        }
      } else {
        url = 'http://localhost:4321';
      }
    }
    if (url.endsWith('/')) {
      url = url.slice(0, -1);
    }
    return url;
  }

  static async getSettings() {
    const { data, error } = await supabase
      .from('settings')
      .select('*');
    if (error) {
      console.error('Error fetching settings:', error);
      return {};
    }
    const settings = {};
    (data || []).forEach(item => {
      settings[item.key] = item.value;
    });
    return settings;
  }

  static async getSetting(key) {
    const { data, error } = await supabase
      .from('settings')
      .select('value')
      .eq('key', key)
      .maybeSingle();
    if (error) {
      console.error(`Error fetching setting ${key}:`, error);
      return null;
    }
    return data ? data.value : null;
  }

  static async saveSetting(key, value) {
    const { data, error } = await supabase
      .from('settings')
      .upsert({
        key,
        value,
        updated_at: new Date().toISOString()
      })
      .select()
      .single();
    if (error) {
      console.error(`Error saving setting ${key}:`, error);
      throw error;
    }
    return data;
  }

  static async saveSettings(settingsObj) {
    const upsertData = Object.entries(settingsObj).map(([key, value]) => ({
      key,
      value,
      updated_at: new Date().toISOString()
    }));
    const { data, error } = await supabase
      .from('settings')
      .upsert(upsertData);
    if (error) {
      console.error('Error saving multiple settings:', error);
      throw error;
    }
    return data;
  }

  static async getEmailLogs() {
    const { data, error } = await supabase
      .from('email_logs')
      .select('*')
      .order('created_at', { ascending: false });
    if (error) {
      console.error('Error fetching email logs:', error);
      return [];
    }
    return data;
  }

  static async saveEmailLog(toEmail, subject, body, templateName, status, errorMessage = null) {
    const { data, error } = await supabase
      .from('email_logs')
      .insert({
        to_email: toEmail,
        subject,
        body,
        template_name: templateName,
        status,
        error_message: errorMessage,
        created_at: new Date().toISOString()
      })
      .select()
      .single();
    if (error) {
      console.error('Error saving email log:', error);
      throw error;
    }
    return data;
  }

  static async notifyNewEpisodes(slug, movieName, newEpisodes = []) {
    try {
      const { data: users, error } = await supabase
        .from('users')
        .select('id, username, email, favorites');
        
      if (error) throw error;
      
      const interestedUsers = (users || []).filter(u => 
        Array.isArray(u.favorites) && u.favorites.some(f => f.slug === slug)
      );
      
      if (interestedUsers.length === 0) return;
      
      const settings = await this.getSettings();
      if (!settings.smtp_user || !settings.smtp_pass) {
        console.log(`[Notification Engine]: SMTP not configured. Skipping notifications for ${movieName}.`);
        return;
      }
      
      console.log(`[Notification Engine]: Sending new episode notifications for ${movieName} to ${interestedUsers.length} users...`);
      
      const episodeListHtml = newEpisodes.map(ep => `<li>Tập ${ep.name || ep.filename || ep.episode || 'mới'}</li>`).join('');
      
      for (const u of interestedUsers) {
        if (!u.email) continue;
        
        try {
          await this.sendMail({
            to: u.email,
            subject: `[TPHIMX] Có tập mới phim "${movieName}" nè bạn ơi!`,
            templateName: 'new_episode',
            placeholders: {
              username: u.username,
              movie_name: movieName,
              movie_slug: slug,
              episode_list: episodeListHtml
            }
          });
          console.log(`[Notification Engine]: Notification sent to ${u.email}`);
        } catch (mailErr) {
          console.error(`[Notification Engine]: Failed to send notification to ${u.email}:`, mailErr);
        }
      }
    } catch (err) {
      console.error('[Notification Engine Error]:', err);
    }
  }

  static async sendMail({ to, subject, templateName, placeholders = {}, smtpConfigOverride = null }) {
    try {
      const settings = smtpConfigOverride || await this.getSettings();
      const host = settings.smtp_host || settings.smtpHost || 'smtp.gmail.com';
      const port = Number(settings.smtp_port || settings.smtpPort) || 465;
      const secure = (settings.smtp_secure || settings.smtpSecure) === 'SSL' || (settings.smtp_secure || settings.smtpSecure) === 'TLS' || port === 465;
      const user = settings.smtp_user || settings.smtpUser || '';
      const pass = settings.smtp_pass || settings.smtpPass || '';
      const fromEmail = settings.smtp_from_email || settings.smtpFromEmail || 'no-reply@tphimx.com';
      const fromName = settings.smtp_from_name || settings.smtpFromName || 'TPHIMX';

      if (!user || !pass) {
        throw new Error('SMTP credentials are not configured.');
      }

      // Import nodemailer and fs dynamically
      const nodemailer = await import('nodemailer');
      const fs = await import('node:fs/promises');
      const path = await import('node:path');

      // Load template
      const templatePath = path.join(process.cwd(), 'src/templates/email', `${templateName}.html`);
      let htmlContent = '';
      try {
        htmlContent = await fs.readFile(templatePath, 'utf-8');
      } catch (err) {
        console.error(`Template ${templateName} not found at ${templatePath}, falling back to default styling.`);
        htmlContent = `<h1>${subject}</h1><p>Welcome to TPHIMX.</p>`;
      }

      // Replace placeholders
      Object.entries(placeholders).forEach(([key, val]) => {
        const regex = new RegExp(`{{\\s*${key}\\s*}}`, 'g');
        htmlContent = htmlContent.replace(regex, val);
      });

      // Also dynamically replace site_url, site_name and copyright_year from configurations if present
      const siteUrl = settings.site_url || 'http://localhost:4321';
      const siteName = settings.site_name || 'TPHIMX';
      const currentYear = new Date().getFullYear();
      const copyrightYear = currentYear > 2026 ? `2026 - ${currentYear}` : '2026';

      htmlContent = htmlContent.replace(/{{\s*site_url\s*}}/g, siteUrl);
      htmlContent = htmlContent.replace(/{{\s*site_name\s*}}/g, siteName);
      htmlContent = htmlContent.replace(/{{\s*copyright_year\s*}}/g, copyrightYear);

      // Create transporter
      const transporter = nodemailer.default.createTransport({
        host,
        port,
        secure,
        auth: {
          user,
          pass,
        },
        tls: {
          rejectUnauthorized: false
        }
      });

      // Send mail
      const info = await transporter.sendMail({
        from: `"${fromName}" <${fromEmail}>`,
        to,
        subject,
        html: htmlContent
      });

      console.log('Message sent: %s', info.messageId);

      // Log success to DB
      await this.saveEmailLog(to, subject, htmlContent, templateName, 'sent');
      return { success: true, messageId: info.messageId };
    } catch (error) {
      console.error('Error sending email:', error);
      // Log failure to DB
      try {
        await this.saveEmailLog(to, subject, `[Gửi thất bại] ${error.message}`, templateName, 'failed', error.message);
      } catch (logErr) {
        console.error('Failed to log email failure:', logErr);
      }
      throw error;
    }
  }

  // --- ZALO ACCESS ---
  static async getZaloAccessByToken(token) {
    const { data, error } = await supabase
      .from('zalo_access')
      .select('*')
      .eq('browser_token', token)
      .maybeSingle();
    if (error) {
      console.error(`Error fetching Zalo access for token ${token}:`, error);
      return null;
    }
    return data;
  }

  static async createZaloAccessRequest(token, zaloNickname) {
    const { data, error } = await supabase
      .from('zalo_access')
      .upsert({
        browser_token: token,
        zalo_nickname: zaloNickname,
        status: 'pending',
        updated_at: new Date().toISOString()
      }, { onConflict: 'browser_token' })
      .select()
      .single();
    if (error) {
      console.error(`Error creating Zalo access request for token ${token}:`, error);
      throw error;
    }
    return data;
  }

  static async updateZaloAccessStatus(id, status) {
    const { data, error } = await supabase
      .from('zalo_access')
      .update({
        status,
        updated_at: new Date().toISOString()
      })
      .eq('id', id)
      .select()
      .single();
    if (error) {
      console.error(`Error updating Zalo access status for ${id}:`, error);
      throw error;
    }
    return data;
  }

  static async getAllZaloAccessRequests() {
    const { data, error } = await supabase
      .from('zalo_access')
      .select('*')
      .order('created_at', { ascending: false });
    if (error) {
      console.error('Error fetching Zalo access requests:', error);
      return [];
    }
    return data;
  }

  static async getZaloAccessRequests() {
    return await this.getAllZaloAccessRequests();
  }

  // --- REPORTS ---
  static async createReport(movieSlug, movieName, episodeName, errorType, description) {
    const { data, error } = await supabase
      .from('reports')
      .insert({
        movie_slug: movieSlug,
        movie_name: movieName,
        episode_name: episodeName,
        error_type: errorType,
        description,
        status: 'pending',
        created_at: new Date().toISOString()
      })
      .select()
      .single();
    if (error) {
      console.error('Error creating report:', error);
      throw error;
    }
    return data;
  }

  static async getAllReports() {
    const { data, error } = await supabase
      .from('reports')
      .select('*')
      .order('created_at', { ascending: false });
    if (error) {
      console.error('Error fetching reports:', error);
      return [];
    }
    return data;
  }

  static async getReports() {
    return await this.getAllReports();
  }

  static async updateReportStatus(id, status) {
    const { data, error } = await supabase
      .from('reports')
      .update({ status })
      .eq('id', id)
      .select()
      .single();
    if (error) {
      console.error(`Error updating report status for ${id}:`, error);
      throw error;
    }
    return data;
  }

  // --- LOGS ---
  static async createLog(action, level, message, details = null) {
    const { data, error } = await supabase
      .from('logs')
      .insert({
        action,
        level,
        message,
        details: typeof details === 'object' ? JSON.stringify(details) : details,
        created_at: new Date().toISOString()
      })
      .select()
      .single();
    if (error) {
      console.error('Error creating log:', error);
    }
    return data;
  }

  static async getAllLogs() {
    const { data, error } = await supabase
      .from('logs')
      .select('*')
      .order('created_at', { ascending: false });
    if (error) {
      console.error('Error fetching logs:', error);
      return [];
    }
    return data;
  }
}