create view CertificationExpiryStatus (EmployeeId, Name, Surname, DateOfBirth, Active, CertificationId,
    CertificationType, AirplaneModelId, AirplaneModel, ExpirationDate, DaysUntilExpiration, IsExpired) as
    select employee.id, employee.name, employee.surname,
           employee.dateofbirth, employee.active, flightcertification.id, flightcertification.certificationtype,
           airplanemodel.id, airplanemodel.model, flightcertification.expirationdate,
           expirationdate - current_date,
            expirationdate < current_date
    from employee
    join flightcertification on flightcertification.airlineemployeeid = employee.id
    join airplanemodel on flightcertification.airplanemodelid = airplanemodel.id

    union all

    select employee.id, employee.name, employee.surname,
           employee.dateofbirth, employee.active,repaircertification.id,'Mechanic',
           airplanemodel.id, airplanemodel.model, repaircertification.expirationdate,
            expirationdate - current_date,
            expirationdate < current_date
    from employee
    join repaircertification on repaircertification.mechanicid = employee.id
    join airplanemodel on repaircertification.airplanemodelid = airplanemodel.id

    union all

    select employee.id, employee.name, employee.surname,
           employee.dateofbirth, employee.active,medicalcheckup.id,'Medical Checkup',
           null, null, medicalcheckup.expirationdate,
            expirationdate - current_date,
            expirationdate < current_date
    from employee
    join medicalcheckup on medicalcheckup.employeeid = employee.id;