import { Injectable, Logger } from '@nestjs/common';
import { InjectRepository } from '@nestjs/typeorm';
import { Repository } from 'typeorm';
import { PostEntity } from './post.entity';

@Injectable()
export class PostService {
  private readonly logger = new Logger(PostService.name);

  constructor(
    @InjectRepository(PostEntity, 'wordpressDataSource')
    private postRepository: Repository<PostEntity>,
  ) {}

  // -------------------------------------------
  // EXISTING METHODS – unchanged
  // -------------------------------------------

  async getAllPostsAndAttachments(
    page: number = 1,
    take: number = 10,
    search?: string,
  ): Promise<{ data: any[]; total: number; currentPage: number }> {
    const skip = (page - 1) * take;
    const searchValue = search ? `%${search}%` : null;

    // Reusable WHERE fragments
    const attachWhere = `
    post_type = 'attachment'
    AND (post_mime_type = 'application/pdf' OR post_mime_type LIKE 'image/%')
    ${search ? `AND (post_title LIKE ? OR post_name LIKE ? OR guid LIKE ?)` : ''}
  `;

    const postWhere = `
    p.post_type = 'post'
    AND p.post_status = 'publish'
    ${search ? `AND (p.post_title LIKE ? OR p.post_name LIKE ?)` : ''}
  `;

    // Correlated subquery picks ONE attachment per post (matches your getFileUrlForPost logic)
    // -> guarantees one row per post, no LEFT JOIN multiplication
    const buildPostSelect = (table: string, source: string) => `
    SELECT p.ID, p.post_title AS postTitle, p.post_name AS postName,
           COALESCE(
             (SELECT a.guid FROM ${table} a
              WHERE a.post_parent = p.ID AND a.post_type = 'attachment'
                AND (a.post_mime_type = 'application/pdf' OR a.post_mime_type LIKE 'image/%')
              ORDER BY a.ID DESC LIMIT 1),
             p.guid
           ) AS guid,
           COALESCE(
             (SELECT a.post_mime_type FROM ${table} a
              WHERE a.post_parent = p.ID AND a.post_type = 'attachment'
                AND (a.post_mime_type = 'application/pdf' OR a.post_mime_type LIKE 'image/%')
              ORDER BY a.ID DESC LIMIT 1),
             ''
           ) AS postMimeType,
           p.post_type AS postType, p.post_date AS postDate, p.post_content AS postContent,
           '${source}' AS source
    FROM ${table} p
    WHERE ${postWhere}
  `;

    const buildAttachSelect = (table: string, source: string) => `
    SELECT ID, post_title AS postTitle, post_name AS postName, guid,
           post_mime_type AS postMimeType, post_type AS postType,
           post_date AS postDate, post_content AS postContent,
           '${source}' AS source
    FROM ${table}
    WHERE ${attachWhere}
  `;

    const unionSql = `
    ${buildAttachSelect('wp_posts', 'wp_attach')}
    UNION ALL
    ${buildPostSelect('wp_posts', 'wp_post')}
    UNION ALL
    ${buildAttachSelect('njgi_posts', 'njgi_attach')}
    UNION ALL
    ${buildPostSelect('njgi_posts', 'njgi_post')}
  `;

    // Pagination + COUNT applied to the FULL combined set
    const dataQuery = `
    SELECT * FROM ( ${unionSql} ) combined
    ORDER BY postDate DESC
    LIMIT ? OFFSET ?
  `;

    const countQuery = `SELECT COUNT(*) AS total FROM ( ${unionSql} ) combined`;

    const attachParams = search ? [searchValue, searchValue, searchValue] : [];
    const postParams = search ? [searchValue, searchValue] : [];

    // Param order MUST match the ? order in unionSql:
    // wp_attach -> wp_post -> njgi_attach -> njgi_post
    const unionParams = [
      ...attachParams,
      ...postParams,
      ...attachParams,
      ...postParams,
    ];

    const [rows, countResult] = await Promise.all([
      this.postRepository.query(dataQuery, [...unionParams, take, skip]),
      this.postRepository.query(countQuery, unionParams),
    ]);

    // Dedupe by source+ID (wp_posts and njgi_posts have independent ID sequences)
    const seen = new Set<string>();
    const unique = rows.filter((p: any) => {
      const key = `${p.source}-${p.ID}`;
      if (seen.has(key)) return false;
      seen.add(key);
      return true;
    });

    const total = Number(countResult[0]?.total ?? 0);

    this.logger.log(
      `Fetched posts → page: ${page}, limit: ${take}, search: ${search || 'none'}, total: ${total}`,
    );

    return {
      data: unique,
      total,
      currentPage: page,
    };
  }
  // ... inside PostService class

  /**
   * Try to get real file URL (PDF or image) for a given post
   */
  async getFileUrlForPost(post: any): Promise<string> {
    const parentId = post.ID;

    // 1) Try to find a PDF or image attachment associated with this post
    const attachments: any[] = await this.postRepository.query(
      `SELECT ID, guid, post_mime_type
     FROM wp_posts
     WHERE post_parent = ? AND post_type = 'attachment'
       AND (post_mime_type = 'application/pdf' OR post_mime_type LIKE 'image/%')
     ORDER BY ID DESC
     LIMIT 1`,
      [parentId],
    );

    if (attachments.length > 0) {
      const attach = attachments[0];
      console.log(`✔️ Found attachment for post ${parentId} → ${attach.guid}`);
      return attach.guid;
    }

    // 2) Fallback: try to parse HTML content for .pdf (or image) link
    if (post.post_content) {
      const match = post.post_content.match(
        /https?:\/\/[^"']+\\.(pdf|jpg|jpeg|png|gif|webp)/i,
      );
      if (match) {
        console.log(
          `✔️ Found file link in content for post ${parentId} → ${match[0]}`,
        );
        return match[0];
      }
    }

    // 3) Fallback: use guid (not ideal, but ensures something returns)
    console.warn(
      `⚠️ No attachment or direct file link for post ${parentId}. Using guid fallback: ${post.guid}`,
    );
    return post.guid;
  }
  async getPostBySlug(slug: string, site: 'intl' | 'syst' = 'intl') {
    try {
      console.log(`🔹 Searching for slug "${slug}" in site "${site}"`);

      const result = await this.postRepository.query(
        `
      SELECT ID, post_title AS postTitle, post_name AS postName, guid,
             post_mime_type AS postMimeType, post_type AS postType,
             post_date AS postDate, post_content AS postContent
      FROM wp_posts
      WHERE post_name = ?

      UNION ALL

      SELECT ID, post_title AS postTitle, post_name AS postName, guid,
             post_mime_type AS postMimeType, post_type AS postType,
             post_date AS postDate, post_content AS postContent
      FROM njgi_posts
      WHERE post_name = ?

      LIMIT 1
      `,
        [slug, slug],
      );

      console.log('🔹 Slug query result:', result);
      return result.length ? result[0] : null;
    } catch (err) {
      console.error('❌ Error in getPostBySlug:', err);
      throw err;
    }
  }

  // -------------------------------------------
  // NEW METHOD — supports old QR format page_id=14926
  // -------------------------------------------
  async getPostByPageId(pageId: string) {
    try {
      console.log(`🔹 Searching for page ID "${pageId}"`);

      const result = await this.postRepository.query(
        `
      SELECT ID, post_title AS postTitle, post_name AS postName, guid,
             post_mime_type AS postMimeType, post_type AS postType,
             post_date AS postDate, post_content AS postContent
      FROM wp_posts
      WHERE ID = ?

      UNION ALL

      SELECT ID, post_title AS postTitle, post_name AS postName, guid,
             post_mime_type AS postMimeType, post_type AS postType,
             post_date AS postDate, post_content AS postContent
      FROM njgi_posts
      WHERE ID = ?

      LIMIT 1
      `,
        [pageId, pageId],
      );

      console.log('🔹 Page ID query result:', result);
      return result.length ? result[0] : null;
    } catch (err) {
      console.error('❌ Error in getPostByPageId:', err);
      throw err;
    }
  }
}