import { 
  users, movies, 
  type User, type InsertUser, 
  type Movie, type InsertMovie 
} from "@shared/schema";
import session from "express-session";
import { db } from './db';
import { eq, like, SQL, sql, or } from "drizzle-orm";
import connectPg from "connect-pg-simple";
import { pool } from './db';

// Define the search parameters
interface SearchParams {
  query?: string;
  genre?: string;
  language?: string;
  vjName?: string;
}

// Extend the storage interface with movie-related methods
export interface IStorage {
  // User methods
  getUser(id: number): Promise<User | undefined>;
  getUserByUsername(username: string): Promise<User | undefined>;
  getUserByEmail(email: string): Promise<User | undefined>;
  createUser(user: InsertUser): Promise<User>;
  getAllUsers(): Promise<User[]>;
  deleteUser(id: number): Promise<boolean>;
  
  // Movie methods
  getAllMovies(): Promise<Movie[]>;
  getTranslatedMovies(): Promise<Movie[]>;
  getNonTranslatedMovies(): Promise<Movie[]>;
  getMovie(id: number): Promise<Movie | undefined>;
  createMovie(movie: InsertMovie): Promise<Movie>;
  updateMovie(id: number, movie: InsertMovie): Promise<Movie | undefined>;
  deleteMovie(id: number): Promise<boolean>;
  searchMovies(params: SearchParams): Promise<Movie[]>;
  incrementMovieViews(id: number): Promise<Movie | undefined>;
  incrementMovieDownloads(id: number): Promise<Movie | undefined>;
  
  // Session store
  sessionStore: session.SessionStore;
}

// PostgreSQL session store
const PostgresSessionStore = connectPg(session);

export class DatabaseStorage implements IStorage {
  sessionStore: session.SessionStore;

  constructor() {
    // Create PostgreSQL-based session store
    this.sessionStore = new PostgresSessionStore({ 
      pool, 
      createTableIfMissing: true 
    });
    
    // Initialize database if needed
    this.initializeDatabase();
  }

  // Initialize database with sample data if empty
  private async initializeDatabase() {
    // Check if we need to initialize the database
    const existingUsers = await db.select().from(users).limit(1);
    if (existingUsers.length === 0) {
      console.log("Initializing database with sample data...");
      
      // Create admin user
      await this.createUser({
        username: "admin",
        password: "password123", // This will be hashed by the auth.ts
        email: "admin@moviestream.com",
        isAdmin: true,
      });
      
      // Add sample movies
      await this.createSampleMovies();
    }
  }

  // User methods
  async getUser(id: number): Promise<User | undefined> {
    const result = await db
      .select()
      .from(users)
      .where(eq(users.id, id))
      .limit(1);
    
    return result[0];
  }

  async getUserByUsername(username: string): Promise<User | undefined> {
    const result = await db
      .select()
      .from(users)
      .where(eq(users.username, username))
      .limit(1);
    
    return result[0];
  }

  async getUserByEmail(email: string): Promise<User | undefined> {
    const result = await db
      .select()
      .from(users)
      .where(eq(users.email, email))
      .limit(1);
    
    return result[0];
  }

  async createUser(insertUser: InsertUser): Promise<User> {
    const result = await db
      .insert(users)
      .values(insertUser)
      .returning();
    
    return result[0];
  }

  async getAllUsers(): Promise<User[]> {
    return await db.select().from(users);
  }

  async deleteUser(id: number): Promise<boolean> {
    const result = await db
      .delete(users)
      .where(eq(users.id, id))
      .returning({ id: users.id });
    
    return result.length > 0;
  }

  // Movie methods
  async getAllMovies(): Promise<Movie[]> {
    return await db.select().from(movies);
  }

  async getTranslatedMovies(): Promise<Movie[]> {
    return await db
      .select()
      .from(movies)
      .where(eq(movies.isTranslated, true));
  }

  async getNonTranslatedMovies(): Promise<Movie[]> {
    return await db
      .select()
      .from(movies)
      .where(eq(movies.isTranslated, false));
  }

  async getMovie(id: number): Promise<Movie | undefined> {
    const result = await db
      .select()
      .from(movies)
      .where(eq(movies.id, id))
      .limit(1);
    
    return result[0];
  }

  async createMovie(insertMovie: InsertMovie): Promise<Movie> {
    const now = new Date();
    const movieWithDefaults = {
      ...insertMovie,
      views: 0,
      downloads: 0,
      createdAt: now
    };
    
    const result = await db
      .insert(movies)
      .values(movieWithDefaults)
      .returning();
    
    return result[0];
  }

  async updateMovie(id: number, updateMovie: InsertMovie): Promise<Movie | undefined> {
    const result = await db
      .update(movies)
      .set(updateMovie)
      .where(eq(movies.id, id))
      .returning();
    
    return result[0];
  }

  async deleteMovie(id: number): Promise<boolean> {
    const result = await db
      .delete(movies)
      .where(eq(movies.id, id))
      .returning({ id: movies.id });
    
    return result.length > 0;
  }

  async searchMovies(params: SearchParams): Promise<Movie[]> {
    let query = db.select().from(movies);
    const conditions: SQL[] = [];
    
    if (params.query) {
      const searchPattern = `%${params.query}%`;
      conditions.push(
        or(
          like(movies.title, searchPattern),
          like(movies.description, searchPattern),
          like(sql`COALESCE(${movies.vjName}, '')`, searchPattern)
        )
      );
    }
    
    if (params.genre) {
      conditions.push(eq(movies.genre, params.genre));
    }
    
    if (params.language) {
      conditions.push(eq(movies.language, params.language));
    }
    
    if (params.vjName) {
      conditions.push(like(sql`COALESCE(${movies.vjName}, '')`, `%${params.vjName}%`));
    }
    
    if (conditions.length > 0) {
      // Combine all conditions with AND
      let combinedCondition = conditions[0];
      for (let i = 1; i < conditions.length; i++) {
        combinedCondition = sql`${combinedCondition} AND ${conditions[i]}`;
      }
      query = query.where(combinedCondition);
    }
    
    return await query;
  }

  async incrementMovieViews(id: number): Promise<Movie | undefined> {
    const result = await db
      .update(movies)
      .set({
        views: sql`${movies.views} + 1`
      })
      .where(eq(movies.id, id))
      .returning();
    
    return result[0];
  }

  async incrementMovieDownloads(id: number): Promise<Movie | undefined> {
    const result = await db
      .update(movies)
      .set({
        downloads: sql`${movies.downloads} + 1`
      })
      .where(eq(movies.id, id))
      .returning();
    
    return result[0];
  }

  // Helper to create sample movies
  private async createSampleMovies() {
    // Translated movies
    await this.createMovie({
      title: "Avengers: Endgame",
      description: "After Thanos, an intergalactic warlord, disintegrates half of the universe, the Avengers must reunite and assemble again to reinvigorate their trounced allies and restore balance.",
      posterUrl: "https://images.unsplash.com/photo-1536440136628-849c177e76a1?ixlib=rb-1.2.1&auto=format&fit=crop&w=500&h=750&q=80",
      videoUrl: "https://www.example.com/videos/avengers-endgame.mp4",
      releaseYear: 2019,
      duration: 181,
      language: "English",
      genre: "Action",
      isTranslated: true,
      vjName: "John Smith"
    });

    await this.createMovie({
      title: "The Dark Knight",
      description: "When the menace known as the Joker wreaks havoc and chaos on the people of Gotham, Batman must accept one of the greatest psychological and physical tests of his ability to fight injustice.",
      posterUrl: "https://images.unsplash.com/photo-1616530940355-351fabd9524b?ixlib=rb-1.2.1&auto=format&fit=crop&w=500&h=750&q=80",
      videoUrl: "https://www.example.com/videos/dark-knight.mp4",
      releaseYear: 2008,
      duration: 152,
      language: "English",
      genre: "Thriller",
      isTranslated: true,
      vjName: "Mike Johnson"
    });

    await this.createMovie({
      title: "Inception",
      description: "A thief who steals corporate secrets through the use of dream-sharing technology is given the inverse task of planting an idea into the mind of a C.E.O.",
      posterUrl: "https://images.unsplash.com/photo-1485846234645-a62644f84728?ixlib=rb-1.2.1&auto=format&fit=crop&w=500&h=750&q=80",
      videoUrl: "https://www.example.com/videos/inception.mp4",
      releaseYear: 2010,
      duration: 148,
      language: "English",
      genre: "Sci-Fi",
      isTranslated: true,
      vjName: "Sarah Lee"
    });
    
    // Non-translated movies
    await this.createMovie({
      title: "Parasite",
      description: "Greed and class discrimination threaten the newly formed symbiotic relationship between the wealthy Park family and the destitute Kim clan.",
      posterUrl: "https://images.unsplash.com/photo-1517604931442-7e0c8ed2963c?ixlib=rb-1.2.1&auto=format&fit=crop&w=500&h=750&q=80",
      videoUrl: "https://www.example.com/videos/parasite.mp4",
      releaseYear: 2019,
      duration: 132,
      language: "Korean",
      genre: "Thriller",
      isTranslated: false,
    });

    await this.createMovie({
      title: "Train to Busan",
      description: "While a zombie virus breaks out in South Korea, passengers struggle to survive on the train from Seoul to Busan.",
      posterUrl: "https://images.unsplash.com/photo-1626814026160-2237a95fc5a0?ixlib=rb-1.2.1&auto=format&fit=crop&w=500&h=750&q=80",
      videoUrl: "https://www.example.com/videos/train-to-busan.mp4",
      releaseYear: 2016,
      duration: 118,
      language: "Korean",
      genre: "Horror",
      isTranslated: false,
    });

    await this.createMovie({
      title: "Your Name",
      description: "Two strangers find themselves linked in a bizarre way. When a connection forms, will distance be the only thing to keep them apart?",
      posterUrl: "https://images.unsplash.com/photo-1598899134739-24c46f58b8c0?ixlib=rb-1.2.1&auto=format&fit=crop&w=500&h=750&q=80",
      videoUrl: "https://www.example.com/videos/your-name.mp4",
      releaseYear: 2016,
      duration: 106,
      language: "Japanese",
      genre: "Animation",
      isTranslated: false,
    });
  }
}

export const storage = new DatabaseStorage();
