import {
  BadRequestException,
  Injectable,
  NotFoundException,
} from '@nestjs/common';

import { existsSync, unlinkSync } from 'fs';

import { basename, join } from 'path';

import { PrismaService } from '@/prisma/prisma.service';

import { UpdateMyOrganizationDto } from './dto/update-my-organization.dto';

type MyOrganizationRow = {
  id: number;

  userId: number;
  stakeholderId: number | null;

  name: string | null;
  organizationType: string | null;

  logo: string | null;
  systemLogo: string | null;

  description: string | null;
  coChair: string | null;

  active: boolean;

  createdAt: Date;
  updatedAt: Date;
};

type CurrentUser = {
  id: number;
  email: string;
  name: string | null;
  position: string | null;
  avatar: string | null;
};

type SpecialOrganization = {
  name: string;
  organizationType: string;
  description: string;
};

@Injectable()
export class MyOrganizationService {
  constructor(private readonly prisma: PrismaService) {}

  // ============================================================
  // GET MY ORGANIZATION
  // ============================================================

  async findMyOrganization(userId: number) {
    const organization = await this.resolveMyOrganization(userId);

    return this.getOrganizationResult(organization);
  }

  // ============================================================
  // UPDATE ORGANIZATION INFO
  // ============================================================

  async updateMyOrganization(userId: number, dto: UpdateMyOrganizationDto) {
    const organization = await this.resolveMyOrganization(userId);

    const hasUpdate =
      dto.name !== undefined ||
      dto.description !== undefined ||
      dto.coChair !== undefined;

    if (!hasUpdate) {
      throw new BadRequestException(
        'At least one organization field is required.',
      );
    }

    let nextName = organization.name;

    let nextDescription = organization.description;

    let nextCoChair = organization.coChair;

    if (dto.name !== undefined) {
      const name = dto.name.trim();

      if (!name) {
        throw new BadRequestException('Organization name is required.');
      }

      nextName = name;
    }

    if (dto.description !== undefined) {
      const description = dto.description.trim();

      nextDescription = description.length > 0 ? description : null;
    }

    if (dto.coChair !== undefined) {
      const coChair = dto.coChair.trim();

      nextCoChair = coChair.length > 0 ? coChair : null;
    }

    // ----------------------------------------------------------
    // Update my_organizations
    // ----------------------------------------------------------

    await this.prisma.$executeRaw`
      UPDATE "my_organizations"
      SET
        "name" = ${nextName},
        "description" = ${nextDescription},
        "co_chair" = ${nextCoChair},
        "updated_at" = NOW()
      WHERE "user_id" = ${userId}
    `;

    // ----------------------------------------------------------
    // Ministry / Private Sector
    // Sync to Stakeholder
    // ----------------------------------------------------------

    if (organization.stakeholderId !== null) {
      const stakeholderData: {
        name?: string;
        description?: string | null;
        coChair?: string | null;
      } = {};

      if (dto.name !== undefined && nextName !== null) {
        stakeholderData.name = nextName;
      }

      if (dto.description !== undefined) {
        stakeholderData.description = nextDescription;
      }

      if (dto.coChair !== undefined) {
        stakeholderData.coChair = nextCoChair;
      }

      await this.prisma.stakeholder.update({
        where: {
          id: organization.stakeholderId,
        },

        data: stakeholderData,
      });
    }

    const updated = await this.getMyOrganizationRow(userId);

    if (!updated) {
      throw new NotFoundException('My Organization not found.');
    }

    return this.getOrganizationResult(updated);
  }

  // ============================================================
  // UPDATE ORGANIZATION LOGO
  //
  // MAFF / CDC / CEFP / etc.
  // ============================================================

  async updateLogo(userId: number, logoPath: string) {
    const cleanLogo = logoPath.trim();

    if (!cleanLogo) {
      throw new BadRequestException('Organization logo is required.');
    }

    const organization = await this.resolveMyOrganization(userId);

    let oldLogo = organization.logo;

    // ----------------------------------------------------------
    // Get stakeholder old logo
    // ----------------------------------------------------------

    if (organization.stakeholderId !== null) {
      const stakeholder = await this.prisma.stakeholder.findFirst({
        where: {
          id: organization.stakeholderId,

          deletedAt: null,
        },

        select: {
          id: true,
          logo: true,
        },
      });

      if (stakeholder && stakeholder.logo) {
        oldLogo = stakeholder.logo;
      }
    }

    // ----------------------------------------------------------
    // Update MyOrganization logo
    // ----------------------------------------------------------

    await this.prisma.$executeRaw`
      UPDATE "my_organizations"
      SET
        "logo" = ${cleanLogo},
        "updated_at" = NOW()
      WHERE "user_id" = ${userId}
    `;

    // ----------------------------------------------------------
    // Update stakeholder logo too
    // ----------------------------------------------------------

    if (organization.stakeholderId !== null) {
      await this.prisma.stakeholder.update({
        where: {
          id: organization.stakeholderId,
        },

        data: {
          logo: cleanLogo,
        },
      });
    }

    this.deleteOldLocalFile(
      oldLogo,
      cleanLogo,
      '/uploads/stakeholders/',
      'stakeholders',
    );

    const updated = await this.getMyOrganizationRow(userId);

    if (!updated) {
      throw new NotFoundException('My Organization not found.');
    }

    return this.getOrganizationResult(updated);
  }

  // ============================================================
  // UPDATE SYSTEM LOGO
  //
  // Cambodia G-PSF logo in Sidebar/Header
  // ============================================================

  async updateSystemLogo(userId: number, systemLogoPath: string) {
    const cleanSystemLogo = systemLogoPath.trim();

    if (!cleanSystemLogo) {
      throw new BadRequestException('System logo is required.');
    }

    const organization = await this.resolveMyOrganization(userId);

    const oldSystemLogo = organization.systemLogo;

    // ----------------------------------------------------------
    // If this user belongs to a Stakeholder,
    // update all MyOrganization rows of same stakeholder.
    //
    // Example:
    // User 22 MAFF
    // User 35 MAFF
    //
    // Both will see same system logo.
    // ----------------------------------------------------------

    if (organization.stakeholderId !== null) {
      await this.prisma.$executeRaw`
        UPDATE "my_organizations"
        SET
          "system_logo" = ${cleanSystemLogo},
          "updated_at" = NOW()
        WHERE
          "stakeholder_id" =
          ${organization.stakeholderId}
      `;
    } else {
      // CDC / CEFP / CDC G-PSF
      await this.prisma.$executeRaw`
        UPDATE "my_organizations"
        SET
          "system_logo" = ${cleanSystemLogo},
          "updated_at" = NOW()
        WHERE
          "user_id" = ${userId}
      `;
    }

    this.deleteOldLocalFile(
      oldSystemLogo,
      cleanSystemLogo,
      '/uploads/system-logos/',
      'system-logos',
    );

    const updated = await this.getMyOrganizationRow(userId);

    if (!updated) {
      throw new NotFoundException('My Organization not found.');
    }

    return this.getOrganizationResult(updated);
  }

  // ============================================================
  // RESOLVE MY ORGANIZATION
  // ============================================================

  private async resolveMyOrganization(
    userId: number,
  ): Promise<MyOrganizationRow> {
    const user = await this.getCurrentUser(userId);

    // ----------------------------------------------------------
    // Existing My Organization
    // ----------------------------------------------------------

    const existing = await this.getMyOrganizationRow(userId);

    if (existing) {
      return existing;
    }

    // ----------------------------------------------------------
    // Ministry / Private Sector
    // StakeholderUser relation
    // ----------------------------------------------------------

    const stakeholderLinks = await this.prisma.stakeholderUser.findMany({
      where: {
        userId,

        stakeholder: {
          deletedAt: null,

          active: true,
        },
      },

      select: {
        stakeholderId: true,

        stakeholder: {
          select: {
            id: true,

            name: true,

            logo: true,

            description: true,

            coChair: true,

            active: true,

            stakeholderType: {
              select: {
                id: true,

                name: true,
              },
            },
          },
        },
      },

      orderBy: {
        stakeholderId: 'asc',
      },

      take: 2,
    });

    // ----------------------------------------------------------
    // One organization
    // ----------------------------------------------------------

    if (stakeholderLinks.length === 1) {
      const link = stakeholderLinks[0];

      if (!link) {
        throw new NotFoundException('Organization relation not found.');
      }

      const stakeholder = link.stakeholder;

      return this.createMyOrganization({
        userId,

        stakeholderId: stakeholder.id,

        name: stakeholder.name,

        organizationType: stakeholder.stakeholderType?.name ?? 'Organization',

        logo: stakeholder.logo,

        systemLogo: null,

        description: stakeholder.description,

        coChair: stakeholder.coChair,

        active: stakeholder.active,
      });
    }

    // ----------------------------------------------------------
    // Multiple organizations
    // ----------------------------------------------------------

    if (stakeholderLinks.length > 1) {
      throw new BadRequestException(
        'This user belongs to more than one organization.',
      );
    }

    // ----------------------------------------------------------
    // CDC G-PSF / CDC / CEFP
    // ----------------------------------------------------------

    const specialOrganization = this.resolveSpecialOrganization(user);

    if (specialOrganization) {
      return this.createMyOrganization({
        userId,

        stakeholderId: null,

        name: specialOrganization.name,

        organizationType: specialOrganization.organizationType,

        logo: null,

        systemLogo: null,

        description: specialOrganization.description,

        coChair: null,

        active: true,
      });
    }

    throw new NotFoundException(
      'My Organization has not been configured for this user.',
    );
  }

  // ============================================================
  // CURRENT USER
  // ============================================================

  private async getCurrentUser(userId: number): Promise<CurrentUser> {
    const user = await this.prisma.user.findFirst({
      where: {
        id: userId,

        deletedAt: null,

        isActive: true,
      },

      select: {
        id: true,

        email: true,

        name: true,

        position: true,

        avatar: true,
      },
    });

    if (!user) {
      throw new NotFoundException('User not found or inactive.');
    }

    return {
      id: user.id,

      email: user.email,

      name: user.name,

      position: user.position,

      avatar: user.avatar,
    };
  }

  // ============================================================
  // SPECIAL ORGANIZATIONS
  // ============================================================

  private resolveSpecialOrganization(
    user: CurrentUser,
  ): SpecialOrganization | null {
    const email = user.email.trim().toLowerCase();

    const position = String(user.position ?? '')
      .trim()
      .toLowerCase();

    // ----------------------------------------------------------
    // CDC G-PSF
    // ----------------------------------------------------------

    if (email === 'cdcgpsf@gmail.com' || position.includes('cdc g-psf')) {
      return {
        name: 'CDC G-PSF Secretariat',

        organizationType: 'CDC G-PSF',

        description: 'CDC G-PSF Secretariat',
      };
    }

    // ----------------------------------------------------------
    // CEFP
    // ----------------------------------------------------------

    if (email === 'cefp@gmail.com' || position.includes('cefp')) {
      return {
        name: 'CEFP Secretariat',

        organizationType: 'CEFP',

        description: 'CEFP Secretariat',
      };
    }

    // ----------------------------------------------------------
    // CDC
    // ----------------------------------------------------------

    if (email === 'cdc@gmail.com' || position.includes('cdc secretariat')) {
      return {
        name: 'CDC Secretariat',

        organizationType: 'CDC',

        description: 'CDC Secretariat',
      };
    }

    return null;
  }

  // ============================================================
  // GET MY ORGANIZATION ROW
  // ============================================================

  private async getMyOrganizationRow(
    userId: number,
  ): Promise<MyOrganizationRow | null> {
    const rows = await this.prisma.$queryRaw<MyOrganizationRow[]>`
        SELECT
          "id",

          "user_id"
            AS "userId",

          "stakeholder_id"
            AS "stakeholderId",

          "name",

          "organization_type"
            AS "organizationType",

          "logo",

          "system_logo"
            AS "systemLogo",

          "description",

          "co_chair"
            AS "coChair",

          "active",

          "created_at"
            AS "createdAt",

          "updated_at"
            AS "updatedAt"

        FROM
          "my_organizations"

        WHERE
          "user_id" = ${userId}

        LIMIT 1
      `;

    const organization = rows[0];

    if (!organization) {
      return null;
    }

    return organization;
  }

  // ============================================================
  // CREATE MY ORGANIZATION
  // ============================================================

  private async createMyOrganization(data: {
    userId: number;
    stakeholderId: number | null;

    name: string;
    organizationType: string;

    logo: string | null;
    systemLogo: string | null;

    description: string | null;
    coChair: string | null;

    active: boolean;
  }): Promise<MyOrganizationRow> {
    const rows = await this.prisma.$queryRaw<MyOrganizationRow[]>`
        INSERT INTO "my_organizations"
        (
          "user_id",
          "stakeholder_id",

          "name",
          "organization_type",

          "logo",
          "system_logo",

          "description",
          "co_chair",
          "active",

          "created_at",
          "updated_at"
        )

        VALUES
        (
          ${data.userId},
          ${data.stakeholderId},

          ${data.name},
          ${data.organizationType},

          ${data.logo},
          ${data.systemLogo},

          ${data.description},
          ${data.coChair},
          ${data.active},

          NOW(),
          NOW()
        )

        ON CONFLICT ("user_id")
        DO UPDATE SET
          "stakeholder_id" =
            EXCLUDED."stakeholder_id",

          "name" =
            EXCLUDED."name",

          "organization_type" =
            EXCLUDED."organization_type",

          "updated_at" =
            NOW()

        RETURNING
          "id",

          "user_id"
            AS "userId",

          "stakeholder_id"
            AS "stakeholderId",

          "name",

          "organization_type"
            AS "organizationType",

          "logo",

          "system_logo"
            AS "systemLogo",

          "description",

          "co_chair"
            AS "coChair",

          "active",

          "created_at"
            AS "createdAt",

          "updated_at"
            AS "updatedAt"
      `;

    const organization = rows[0];

    if (!organization) {
      throw new BadRequestException('Unable to create My Organization.');
    }

    return organization;
  }

  // ============================================================
  // RESPONSE
  // ============================================================

  private async getOrganizationResult(myOrganization: MyOrganizationRow) {
    const user = await this.getCurrentUser(myOrganization.userId);

    // ----------------------------------------------------------
    // CDC / CEFP / CDC G-PSF
    // ----------------------------------------------------------

    if (myOrganization.stakeholderId === null) {
      return {
        id: myOrganization.id,

        userId: myOrganization.userId,

        stakeholderId: null,

        // SYSTEM LOGO
        systemLogo: myOrganization.systemLogo,

        organization: {
          id: null,

          name: myOrganization.name,

          // ORGANIZATION LOGO
          logo: myOrganization.logo,

          description: myOrganization.description,

          coChair: myOrganization.coChair,

          active: myOrganization.active,

          stakeholderTypeId: null,

          stakeholderType: {
            id: null,

            name: myOrganization.organizationType,
          },

          relatedStakeholderId: null,

          relatedStakeholder: null,

          users: [
            {
              id: user.id,

              email: user.email,

              name: user.name,

              position: user.position,

              avatar: user.avatar,
            },
          ],

          createdAt: myOrganization.createdAt,

          updatedAt: myOrganization.updatedAt,
        },
      };
    }

    // ----------------------------------------------------------
    // Ministry / Private Sector
    // ----------------------------------------------------------

    const stakeholder = await this.prisma.stakeholder.findFirst({
      where: {
        id: myOrganization.stakeholderId,

        deletedAt: null,
      },

      select: {
        id: true,

        name: true,

        logo: true,

        description: true,

        coChair: true,

        active: true,

        stakeholderTypeId: true,

        stakeholderType: {
          select: {
            id: true,

            name: true,
          },
        },

        relatedStakeholderId: true,

        relatedStakeholder: {
          select: {
            id: true,

            name: true,

            logo: true,

            description: true,

            coChair: true,

            active: true,
          },
        },

        users: {
          select: {
            user: {
              select: {
                id: true,

                email: true,

                name: true,

                position: true,

                avatar: true,
              },
            },
          },
        },

        createdAt: true,

        updatedAt: true,
      },
    });

    if (!stakeholder) {
      throw new NotFoundException('Organization not found.');
    }

    const users = stakeholder.users.map((item) => ({
      id: item.user.id,

      email: item.user.email,

      name: item.user.name,

      position: item.user.position,

      avatar: item.user.avatar,
    }));

    return {
      id: myOrganization.id,

      userId: myOrganization.userId,

      stakeholderId: myOrganization.stakeholderId,

      // ========================================================
      // SYSTEM LOGO
      // ========================================================

      systemLogo: myOrganization.systemLogo,

      organization: {
        id: stakeholder.id,

        name: myOrganization.name ?? stakeholder.name,

        // ======================================================
        // ORGANIZATION LOGO
        // ======================================================

        logo: myOrganization.logo ?? stakeholder.logo,

        description: myOrganization.description ?? stakeholder.description,

        coChair: myOrganization.coChair ?? stakeholder.coChair,

        active: myOrganization.active,

        stakeholderTypeId: stakeholder.stakeholderTypeId,

        stakeholderType: stakeholder.stakeholderType,

        relatedStakeholderId: stakeholder.relatedStakeholderId,

        relatedStakeholder: stakeholder.relatedStakeholder,

        users,

        createdAt: myOrganization.createdAt,

        updatedAt: myOrganization.updatedAt,
      },
    };
  }

  // ============================================================
  // DELETE OLD LOCAL FILE
  // ============================================================

  private deleteOldLocalFile(
    oldPath: string | null,
    newPath: string,
    allowedPrefix: string,
    folder: string,
  ): void {
    if (!oldPath) {
      return;
    }

    if (oldPath === newPath) {
      return;
    }

    const normalized = oldPath.replace(/\\/g, '/');

    if (!normalized.startsWith(allowedPrefix)) {
      return;
    }

    const fileName = basename(normalized);

    const fullPath = join(process.cwd(), 'uploads', folder, fileName);

    try {
      if (existsSync(fullPath)) {
        unlinkSync(fullPath);
      }
    } catch (error: unknown) {
      console.warn('Unable to delete old logo:', error);
    }
  }
}