Salary Market

use salarymarket;

INSERT INTO company_reports_authorization (companyId, report_generated, report_authorized)

SELECT 

    c.id AS companyId,

    COUNT(cr.id) AS report_generated,

    CASE 

        WHEN c.subscription_type = 'Advanced' THEN 'Illimited'

        ELSE '1'

    END AS report_authorized

FROM company c

LEFT JOIN company_reports cr ON cr.companyId = c.id

WHERE c.id NOT IN (

    SELECT companyId FROM company_reports_authorization

)

GROUP BY c.id, c.subscription_type;