DELIMITER //

USE defaultdb//

DROP VIEW IF EXISTS `vw_notify_users`//

CREATE VIEW vw_notify_users AS
    select j.tenant_id, null job_application_id, j.id job_id, null person_id, 'Job Created', u.name, u.email from jobs j left join users u on u.id = j.created_by where j.created_by is not null
    UNION
    select j.tenant_id, null job_application_id, j.id job_id, null person_id, 'Job Sourcer', u.name, u.email from jobs j left join users u on u.id = j.sourcer_id where j.sourcer_id is not null
    UNION
    select j.tenant_id, null job_application_id, j.id job_id, null person_id, 'Job Recruiter', u.name, u.email from jobs j left join users u on u.id = j.recruiter_id where j.sourcer_id is not null
    UNION
    select j.tenant_id, null job_application_id, j.id job_id, null person_id, 'Job Hiring Manager', u.name, u.email from jobs j left join users u on u.id = j.hiring_manager_id where j.sourcer_id is not null
    UNION
    select j.tenant_id, null job_application_id, j.id job_id, null person_id, 'Job Created', u.name, u.email from jobs j left join users u on u.id = j.created_by where j.created_by is not null
    UNION
    select j.tenant_id, null job_application_id, j.job_id job_id, null person_id, 'Job Favorites', u.name, u.email from favorites j left join users u on u.id = j.created_by where j.job_id is not null
    UNION
    select j.tenant_id, j.id job_application_id, j.job_id job_id, null person_id, 'Application Created', u.name, u.email from job_applications j left join users u on u.id = j.created_by where j.created_by is not null
    UNION
    select j.tenant_id, j.id job_application_id, j.job_id job_id, null person_id, 'Application Sourcer', u.name, u.email from job_applications j left join users u on u.id = j.sourcer_id where j.sourcer_id is not null
    UNION
    select j.tenant_id, j.id job_application_id, j.job_id job_id, null person_id, 'Application Recruiter', u.name, u.email from job_applications j left join users u on u.id = j.recruiter_id where j.recruiter_id is not null
    UNION
    select j.tenant_id, j.id job_application_id, j.job_id job_id, null person_id, 'Application Hiring Manager', u.name, u.email from job_applications j left join users u on u.id = j.hiring_manager_id where j.hiring_manager_id is not null
    UNION
    select j.tenant_id, j.id job_application_id, j.job_id job_id, null person_id, 'Candidate Favorites', u.name, u.email from job_applications j left join favorites as f on j.person_id = f.person_id left join users u on u.id = f.created_by where f.created_by is not null
//    

DROP VIEW IF EXISTS `vw_candidate_newsletter_email`//

CREATE VIEW vw_candidate_newsletter_email AS
    (select 'Article' type, id, created_at, title, description, wp_media_url image_url, wp_post_url url
    from articles
    where wp_id is not null
    and length(description) > 375
    and wp_post_url is not null
    order by id desc
    limit 0,5)
    union
    (select 'Job' type, j.id, j.created_at, j.title, j.summary, o.profile_photo, 'https://app.gigminds.com/jobs/'||j.id url
    from jobs j, organization o
    where j.org_id = o.id
    and j.status = 'Active'
    and j.source = 'Gigminds'
    and j.tenant_id is not null
    order by j.id desc limit 0,3)
    union
    (select 'Article' type, id, created_at, title, description, wp_media_url, wp_post_url
    from articles
    where wp_id is not null
    and length(description) > 375
    and wp_post_url is not null
    order by id desc
    limit 6,5)
//

DELIMITER ;
