import { ViewColumn, ViewEntity } from "typeorm";

@ViewEntity({
  expression: `SELECT 
    posMaster.id,
    posMaster.posMasterNoPrefix,
    posMaster.posMasterNo,
    posMaster.posMasterNoSuffix,
    posMaster.orgRevisionId,
    posMaster.orgRootId,
    posMaster.orgChild1Id,
    posMaster.orgChild2Id,
    posMaster.orgChild3Id,
    posMaster.orgChild4Id,
    posMaster.current_holderId,
    profile.id as profileId,
    profile.prefix,
    profile.firstName,
    profile.lastName,
    profile.citizenId,
    profile.position,
    profile.amount,
    profile.dateRetire,
    profile.birthDate,
    orgRoot.id as rootId,
    orgRoot.orgRootShortName,
    orgRoot.orgRootOrder,
    orgRoot.orgRootName,
    orgChild1.id as child1Id,
    orgChild1.orgChild1ShortName,
    orgChild1.orgChild1Order,
    orgChild1.orgChild1Name,
    orgChild2.id as child2Id,
    orgChild2.orgChild2ShortName,
    orgChild2.orgChild2Order,
    orgChild2.orgChild2Name,
    orgChild3.id as child3Id,
    orgChild3.orgChild3ShortName,
    orgChild3.orgChild3Order,
    orgChild3.orgChild3Name,
    orgChild4.id as child4Id,
    orgChild4.orgChild4ShortName,
    orgChild4.orgChild4Order,
    orgChild4.orgChild4Name,
    position.id as positionId,
    position.positionIsSelected,
    position.posExecutiveId as positionPosExecutiveId,
    position.isSpecial,
    position.positionExecutiveField,
    position.positionArea,
    posExecutive.id as posExecutiveId,
    posExecutive.posExecutiveName,
    profileDiscipline.id as profileDisciplineId,
    profileDiscipline.date as disCriplineDate,
    profileLeave.id as profileLeaveId,
    profileAssessment.id as profileAssessmentId,
    profileAssessment.pointSum,
    posLevel.id as posLevelId,
    posLevel.posLevelName,
	  posType.id as posTypeId,
    posType.posTypeName
    FROM 
        posMaster
    LEFT JOIN 
        profile ON posMaster.current_holderId = profile.id
    LEFT JOIN 
        orgRoot ON posMaster.orgRootId = orgRoot.id
    LEFT JOIN 
        orgChild1 ON posMaster.orgChild1Id = orgChild1.id
    LEFT JOIN 
        orgChild2 ON posMaster.orgChild2Id = orgChild2.id
    LEFT JOIN 
        orgChild3 ON posMaster.orgChild3Id = orgChild3.id
    LEFT JOIN 
        orgChild4 ON posMaster.orgChild4Id = orgChild4.id
    LEFT JOIN 
        position ON posMaster.id = position.posMasterId
    LEFT JOIN 
        posExecutive ON position.posExecutiveId = posExecutive.id
    LEFT JOIN (
        SELECT *
        FROM (
            SELECT 
                pd.*,
                ROW_NUMBER() OVER (
                    PARTITION BY profileId 
                    ORDER BY date DESC, id DESC
                ) AS rn
            FROM profileDiscipline pd
        ) t
        WHERE rn = 1
    ) AS profileDiscipline ON profileDiscipline.profileId = profile.id
    LEFT JOIN (
    SELECT pl1.*
    FROM profileLeave pl1
    INNER JOIN (
            SELECT profileId, MAX(createdAt) AS maxDate
            FROM profileLeave
            GROUP BY profileId
        ) pl2 ON pl1.profileId = pl2.profileId
            AND pl1.createdAt = pl2.maxDate
    ) AS profileLeave 
    ON profileLeave.profileId = posMaster.current_holderId
    LEFT JOIN (
    SELECT pa1.*
    FROM profileAssessment pa1
    INNER JOIN (
        SELECT profileId, MAX(createdAt) AS maxDate
        FROM profileAssessment
        GROUP BY profileId
    ) pa2 ON pa1.profileId = pa2.profileId
            AND pa1.createdAt = pa2.maxDate
    ) AS profileAssessment 
     ON profileAssessment.profileId = profile.id
    LEFT JOIN 
        posLevel ON profile.posLevelId = posLevel.id
    LEFT JOIN 
        posType ON profile.posTypeId = posType.id
    WHERE 	
      posMaster.current_holderId IS NOT NULL
    ORDER BY 
        profile.citizenId ASC
    `,
})

export class viewPosMaster {
  @ViewColumn()
  id: string;
  @ViewColumn()
  posMasterNoPrefix: string;
  @ViewColumn()
  posMasterNo: number;
  @ViewColumn()
  posMasterNoSuffix: string;
  @ViewColumn()
  orgRevisionId: string;
  @ViewColumn()
  orgRootId: string;
  @ViewColumn()
  orgChild1Id: string;
  @ViewColumn()
  orgChild2Id: string;
  @ViewColumn()
  orgChild3Id: string;
  @ViewColumn()
  orgChild4Id: string;
  @ViewColumn()
  current_holderId: string;
  @ViewColumn()
  profileId: string;
  @ViewColumn()
  prefix: string;
  @ViewColumn()
  firstName: string;
  @ViewColumn()
  lastName: string;
  @ViewColumn()
  citizenId: number;
  @ViewColumn()
  position: string;
  @ViewColumn()
  amount: number;
  @ViewColumn()
  dateRetire: Date;
  @ViewColumn()
  birthDate: Date;
  @ViewColumn()
  rootId: string;
  @ViewColumn()
  orgRootShortName: string;
  @ViewColumn()
  orgRootOrder: string;
  @ViewColumn()
  orgRootName: string;
  @ViewColumn()
  child1Id: string;
  @ViewColumn()
  orgChild1ShortName: string;
  @ViewColumn()
  orgChild1Order: string;
  @ViewColumn()
  orgChild1Name: string;
  @ViewColumn()
  child2Id: string;
  @ViewColumn()
  orgChild2ShortName: string;
  @ViewColumn()
  orgChild2Order: string;
  @ViewColumn()
  orgChild2Name: string;
  @ViewColumn()
  child3Id: string;
  @ViewColumn()
  orgChild3ShortName: string;
  @ViewColumn()
  orgChild3Order: string;
  @ViewColumn()
  orgChild3Name: string;
  @ViewColumn()
  child4Id: string;
  @ViewColumn()
  orgChild4ShortName: string;
  @ViewColumn()
  orgChild4Order: string;
  @ViewColumn()
  orgChild4Name: string;
  @ViewColumn()
  positionId: string;
  @ViewColumn()
  positionIsSelected: boolean;
  @ViewColumn()
  positionPosExecutiveId: string;
  @ViewColumn()
  isSpecial: boolean;
  @ViewColumn()
  positionExecutiveField: string;
  @ViewColumn()
  positionArea: string;
  @ViewColumn()
  posExecutiveId: string;
  @ViewColumn()
  posExecutiveName: string;
  @ViewColumn()
  profileDisciplineId: string;
  @ViewColumn()
  disCriplineDate: Date;
  @ViewColumn()
  profileLeaveId: string;
  @ViewColumn()
  profileAssessmentId: string;
  @ViewColumn()
  pointSum: number;
  @ViewColumn()
  posLevelId: string;
  @ViewColumn()
  posLevelName: string;
  @ViewColumn()
  posTypeId: string;
  @ViewColumn()
  posTypeName: string;
}
