-- 新增consultation_method欄位

CREATE OR REPLACE VIEW omo.appointment_customer_view
AS SELECT a.id AS appointment_id,
    a.phone,
    a.email,
    a.contact_type,
    a.gender,
    a.age,
    a.job,
    a.requirement,
    a.communicate_status,
    a.hope_contact_time,
    a.other_requirement,
    a.agent_no,
    a.appointment_date,
    a.customer_id,
    a.consultant_view_time,
    a.consultant_read_time,
    a.contact_time,
    a.status,
    a.last_modified_date,
    c.name,
    a.consultation_method
   FROM appointment a
     LEFT JOIN customer c ON a.customer_id = c.id;
     
     
     
     
-- public.appointment_customer_consultant_view source

-- public.appointment_customer_consultant_view source

CREATE OR REPLACE VIEW omo.appointment_customer_consultant_view
AS SELECT appointment.id AS appointment_id,
    appointment.phone,
    appointment.email,
    appointment.contact_type,
    appointment.gender,
    appointment.age,
    appointment.job,
    appointment.requirement,
    appointment.communicate_status,
    appointment.hope_contact_time,
    appointment.other_requirement,
    appointment.appointment_date,
    appointment.last_modified_date,
    appointment.agent_no,
    appointment.consultant_view_time,
    appointment.consultant_read_time,
    appointment.contact_time,
    appointment.status,
    consultant.name AS consultant_name,
    consultant.phone_number AS consultant_phone_number,
    consultant.email AS consultant_email,
    consultant.role AS consultant_role,
    customer.name AS customer_name,
    appointment.consultation_method as consultation_method
   FROM appointment
     LEFT JOIN consultant ON appointment.agent_no::text = consultant.agent_no::text
     LEFT JOIN customer ON appointment.customer_id = customer.id;