DROP VIEW view_quote;
CREATE VIEW view_quote
AS
SELECT
    q.id AS id,
    q.reference,
    it.title,
    q.insurance_code,
    q.product_code,
    if (q.product_code = 'TP', '3rd Party',
        if(q.product_code = 'TL', 'Total Loss',
           if(q.product_code = 'CUST', 'Make Your Own',
              if(q.product_code = 'COMP', 'Comprehensive',
                 if(q.product_code = 'SV', 'Silver',
                    if(q.product_code = 'GD', 'Gold',
                       if(q.product_code = 'PT', 'Platinum',
                          if(q.product_code = 'S1', 'Basic',
                             if(q.product_code = 'S2', 'Standard',
                                if(q.product_code = 'S3', 'Deluxe',
                                   if(q.product_code = 'S4', 'Platinum',
                                      if(q.product_code = 'BA', 'Basic',
                                         if(q.product_code = 'DL', 'Deluxe',
                                            if(q.product_code = 'BA', 'Basic',
                                               if(q.product_code = 'DL', 'Deluxe',
                                                  if(q.product_code = 'PT', 'Platinum',''

                                                      )
                                                   )
                                                )
                                             )
                                          )
                                       )
                                    )
                                 )
                              )
                           )
                        )
                     )
                  )
               )
            )
        ) AS product,
    date_format(str_to_date(q.policy_start_date, '%d/%m/%Y'), '%Y-%m-%d') AS policy_start_date,
    DATE_ADD(date_format(str_to_date(q.policy_start_date, '%d/%m/%Y'), '%Y-%m-%d'), INTERVAL 1 YEAR ) AS policy_end_date,
    DATEDIFF(DATE_ADD(date_format(str_to_date(q.policy_start_date, '%d/%m/%Y'), '%Y-%m-%d'), INTERVAL 1 YEAR ), CURDATE()) days_diff,
    if((DATEDIFF(DATE_ADD(date_format(str_to_date(q.policy_start_date, '%d/%m/%Y'), '%Y-%m-%d'), INTERVAL 1 YEAR),
                 CURDATE()) < 0 &&
        DATEDIFF(DATE_ADD(date_format(str_to_date(q.policy_start_date, '%d/%m/%Y'), '%Y-%m-%d'), INTERVAL 1 YEAR),
                 CURDATE()) > -14 && q.status = 'PAID'), 'Renew',
       if((DATEDIFF(DATE_ADD(date_format(str_to_date(q.policy_start_date, '%d/%m/%Y'), '%Y-%m-%d'), INTERVAL 1 YEAR),
                    CURDATE()) < 14 &&
           DATEDIFF(DATE_ADD(date_format(str_to_date(q.policy_start_date, '%d/%m/%Y'), '%Y-%m-%d'), INTERVAL 1 YEAR),
                    CURDATE()) > 0 && q.status = 'PAID'), 'Pending',
          if((DATEDIFF(DATE_ADD(date_format(str_to_date(q.policy_start_date, '%d/%m/%Y'), '%Y-%m-%d'), INTERVAL 1 YEAR),
                       CURDATE()) < -14 && q.status = 'PAID'), 'Expired',
             if((q.status = 'PENDING' || q.status = 'NOTPAID'), 'Quote', 'Failed'
                 )
              )
           )
        ) AS renewal_status,
    q.user_id,
    q.customer_name,
    q.customer_email,
    q.customer_telephone,
    q.customer_address,
    q.customer_nic,
    q.status,
    q.insured_value,
    q.gross_premium,
    q.policy_fee,
    q.stamp_duty,
    q.cess,
    q.nbt,
    q.vat,
    q.road_safety,
    q.total_tax,
    q.total_activation_fee,
    q.paid_amount,
    if (q.status = 'PAID', q.response_date, '' ) AS paid_date,
    vt.vehicle_type,
    vt.id AS vehicle_type_id,
    q.make_id AS vehicle_make_id,
    q.fuel,
    q.fuel_type,
    q.make,
    q.model,
    q.model_id AS vehicle_model_id,
    q.mode AS policy_mode,
    q.days AS subscription,
    q.activated_extensions AS extension_packs,
    q.activated_plans AS cover_plans_ids,
    q.deductible_id AS deductible_id,
    q.yom,
    q.vehicle_number,
    q.registration_date,
    q.chassis_number,
    q.engine_number,
    q.cc,
    q.pet_name,
    q.pet_dob,
    q.pet_category,
    q.pet_age_text,
    q.pet_age,
    q.pet_breed,
    q.pet_gender,
    q.pa_occupation,
    q.pa_age,
    q.pa_dob,
    q.pa_monthly_income,
    q.pa_nominee_name,
    q.pa_nominee_relationship,
    q.travel_area,
    q.travel_type,
    q.departure_date,
    q.return_date,
    q.imei_number,
    q.phone_make_id,
    q.phone_model_id,
    q.phone_make,
    q.phone_model,
    q.no_claim_bonus,
    q.is_admin_activate_renew,
    q.renew_note,
    q.document_paths,
    q.agent_code,
    CONCAT_WS(" ",q.reference,it.title,q.customer_name,q.customer_telephone,q.customer_email,q.customer_nic,vt.vehicle_type,q.fuel,q.make,q.model,q.vehicle_number,q.chassis_number,q.engine_number,
              q.cc,q.pet_category,q.pet_name,q.pet_category,q.pet_breed,q.pet_gender,q.pa_occupation,q.travel_type,
              q.travel_area,q.internal_quotation_no,q.internal_reference_code,q.imei_number,q.phone_model,q.phone_make
        ) AS quick_search
FROM
    quotes AS q
        LEFT JOIN vehicle_types AS vt ON q.vehicle_type_id = vt.id
        LEFT JOIN core_insurance_types AS it ON q.insurance_code = it.code
  WHERE q.status = 'PAID';

SELECT * FROM view_quote;