CREATE FUNCTION public.assign_ibh_number() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
      begin
        IF NEW.isinhouse = true and (new.invoice_number  is null or new.invoice_number  = 'ih') THEN
           UPDATE counters
	SET count = count + 1
	where shopid = (select shopid from jobs where jobs.id = new.jobid)
	AND countertype = 'ihbnum'
	RETURNING concat(prefix,count) into new.invoice_number;
        END IF;
        RETURN NEW;
      END;
  $$;
CREATE FUNCTION public.assign_payment_number() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN
	IF (new.paymentnum IS NULL
		OR new.paymentnum = 'pnew') THEN
	UPDATE
		counters
	SET
		count = count + 1
	WHERE
		shopid = (
			SELECT
				shopid
			FROM
				jobs
			WHERE
				jobs.id = new.jobid)
			AND countertype = 'paymentnum'
		RETURNING
			concat(prefix, count) INTO new.paymentnum;
END IF;
	RETURN NEW;
END;
$$;
CREATE FUNCTION public.assign_ro_number() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
      begin
        IF NEW.converted = true and (new.ro_number is null or new.ro_number = '') THEN
           UPDATE counters
	SET count = count + 1 where shopid=new.shopid AND countertype = 'ronum'
	RETURNING concat(prefix,count) into new.ro_number;
        END IF;
        RETURN NEW;
      END;
  $$;
CREATE FUNCTION public.audit_trigger() RETURNS trigger
    LANGUAGE plpgsql SECURITY DEFINER
    AS $$
 DECLARE
 shopid uuid ;
 email text;
        BEGIN
 select b.id, u.email INTO shopid, email from users u join associations a on u.email = a.useremail join bodyshops b on b.id = a.shopid where u.authid = current_setting('hasura.user', 't')::jsonb->>'x-hasura-user-id' and a.active = true;
                IF      TG_OP = 'INSERT'
                THEN
                        INSERT INTO public.audit_trail (tabname, schemaname, operation, new_val, recordid, bodyshopid, useremail)
                                VALUES (TG_RELNAME, TG_TABLE_SCHEMA, TG_OP, row_to_json(NEW), NEW.id, shopid, email);
                        RETURN NEW;
                ELSIF   TG_OP = 'UPDATE'
                THEN
                        INSERT INTO public.audit_trail (tabname, schemaname, operation, old_val, new_val, recordid, bodyshopid, useremail)
                                VALUES (TG_RELNAME, TG_TABLE_SCHEMA, TG_OP,
                                      json_diff(to_jsonb(OLD), to_jsonb(NEW))   ,  json_diff(to_jsonb(NEW), to_jsonb(OLD)), OLD.id,  shopid, email);
                        RETURN NEW;
                ELSIF   TG_OP = 'DELETE'
                THEN
                        INSERT INTO public.audit_trail (tabname, schemaname, operation, old_val, recordid, bodyshopid, useremail)
                                VALUES (TG_RELNAME, TG_TABLE_SCHEMA, TG_OP, row_to_json(OLD), OLD.ID,  shopid, email);
                        RETURN OLD;
                END IF;
        END;
$$;
CREATE FUNCTION public.json_diff(l jsonb, r jsonb) RETURNS jsonb
    LANGUAGE sql
    AS $$
    SELECT jsonb_object_agg(a.key, a.value) FROM
        ( SELECT key, value FROM jsonb_each(l) ) a LEFT OUTER JOIN
        ( SELECT key, value FROM jsonb_each(r) ) b ON a.key = b.key
    WHERE a.value != b.value OR b.key IS NULL;
$$;
CREATE TABLE public.bills (
    id uuid DEFAULT public.gen_random_uuid() NOT NULL,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL,
    vendorid uuid NOT NULL,
    jobid uuid NOT NULL,
    date date DEFAULT now() NOT NULL,
    due_date date,
    exported boolean DEFAULT false NOT NULL,
    exported_at timestamp with time zone,
    is_credit_memo boolean DEFAULT false NOT NULL,
    total numeric DEFAULT 0 NOT NULL,
    invoice_number text NOT NULL,
    federal_tax_rate numeric DEFAULT 0 NOT NULL,
    state_tax_rate numeric DEFAULT 0 NOT NULL,
    local_tax_rate numeric DEFAULT 0 NOT NULL,
    isinhouse boolean DEFAULT false NOT NULL
);
CREATE FUNCTION public.search_bills(search text) RETURNS SETOF public.bills
    LANGUAGE plpgsql STABLE
    AS $$
BEGIN
  if search = '' then
    return query select * from bills ;
  else 
    return query SELECT
  bills.*
FROM
  bills, vendors
WHERE
bills.vendorid= vendors.id and 
  (search <% (invoice_number)
  OR
  search <% (name)
  )
  ;
  end if;
	END
$$;
CREATE TABLE public.cccontracts (
    id uuid DEFAULT public.gen_random_uuid() NOT NULL,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL,
    agreementnumber integer NOT NULL,
    courtesycarid uuid NOT NULL,
    jobid uuid NOT NULL,
    status text DEFAULT 'Reserved'::text NOT NULL,
    start timestamp with time zone,
    scheduledreturn timestamp with time zone,
    actualreturn timestamp with time zone,
    kmstart numeric NOT NULL,
    kmend numeric,
    driver_dlnumber text,
    driver_dlexpiry date,
    driver_dlst text,
    driver_fn text NOT NULL,
    driver_ln text NOT NULL,
    driver_addr1 text,
    driver_addr2 text,
    driver_city text,
    driver_state text,
    driver_zip text,
    driver_ph1 text,
    driver_dob date,
    contract_date date DEFAULT now() NOT NULL,
    dailyrate numeric,
    actax numeric,
    dailyfreekm integer,
    refuelcharge numeric,
    excesskmrate numeric,
    cleanupcharge numeric,
    damagewaiver numeric,
    federaltax numeric,
    statetax numeric,
    localtax numeric,
    coverage numeric,
    fuelout numeric DEFAULT 100 NOT NULL,
    fuelin numeric DEFAULT 0 NOT NULL,
    damage text
);
CREATE FUNCTION public.search_cccontracts(search text) RETURNS SETOF public.cccontracts
    LANGUAGE plpgsql STABLE
    AS $$
BEGIN
	IF search = '' THEN
		RETURN query
		SELECT
			*
		FROM
			cccontracts c;
	ELSE
		RETURN query
		SELECT
			contracts.*
		FROM
		courtesycars cars,
			jobs jobs,
			cccontracts contracts
		WHERE (jobs.ro_number ILIKE '%' || search || '%'
			OR jobs.ownr_fn ILIKE '%' || search || '%'
			OR jobs.ownr_ln ILIKE '%' || search || '%'
			OR jobs.ownr_co_nm ILIKE '%' || search || '%'
			OR (cast(contracts.agreementnumber as text)) ILIKE '%' || search || '%'
			OR contracts.driver_fn ILIKE '%' || search || '%'
			OR contracts.driver_ln ILIKE '%' || search || '%'
			OR cars.fleetnumber ILIKE '%' || search || '%'
			OR cars.make ILIKE '%' || search || '%'
			OR cars.model ILIKE '%' || search || '%')
			AND contracts.jobid = jobs.id
			AND contracts.courtesycarid = cars.id;
	END IF;
END
$$;
CREATE TABLE public.exportlog (
    id uuid DEFAULT public.gen_random_uuid() NOT NULL,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL,
    jobid uuid,
    billid uuid,
    paymentid uuid,
    successful boolean DEFAULT false NOT NULL,
    message text,
    bodyshopid uuid NOT NULL,
    useremail text NOT NULL
);
CREATE FUNCTION public.search_exportlog(search text) RETURNS SETOF public.exportlog
    LANGUAGE plpgsql STABLE
    AS $$ BEGIN IF search = '' THEN RETURN query
SELECT
  *
FROM
  exportlog e;
  ELSE RETURN query
SELECT
  e.*
FROM
  exportlog e
  LEFT JOIN jobs j on j.id = e.jobid
LEFT JOIN payments p 
    ON p.id = e.paymentid
LEFT JOIN bills b
    ON e.billid = b.id
WHERE
  (
    j.ro_number ILIKE '%' || search || '%'
    OR b.invoice_number ILIKE '%' || search || '%'
    OR p.paymentnum ILIKE '%' || search || '%'
    OR e.useremail ILIKE '%' || search || '%'
  )
  AND (e.jobid = j.id
  or e.paymentid = p.id
 or e.billid = b.id)
;
END IF;
END $$;
CREATE TABLE public.jobs (
    id uuid DEFAULT public.gen_random_uuid() NOT NULL,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL,
    shopid uuid NOT NULL,
    ro_number text,
    ownerid uuid,
    vehicleid uuid,
    labor_rate_id text,
    labor_rate_desc text,
    rate_lab numeric,
    rate_lad numeric,
    rate_lae numeric,
    rate_lar numeric,
    rate_las numeric,
    rate_laf numeric,
    rate_lam numeric,
    rate_lag numeric,
    rate_lau numeric,
    rate_la1 numeric,
    rate_la2 numeric,
    rate_la3 numeric,
    rate_la4 numeric,
    rate_mapa numeric,
    rate_mash numeric,
    rate_mahw numeric,
    rate_ma2s numeric,
    rate_ma3s numeric,
    rate_ma2t numeric,
    rate_mabl numeric,
    rate_macs numeric,
    rate_matd numeric,
    federal_tax_rate numeric,
    state_tax_rate numeric,
    local_tax_rate numeric,
    est_co_nm text,
    est_addr1 text,
    est_addr2 text,
    est_city text,
    est_st text,
    est_zip text,
    est_ctry text,
    est_ph1 text,
    est_ea text,
    est_ct_ln text,
    est_ct_fn text,
    scheduled_in timestamp with time zone,
    actual_in timestamp with time zone,
    scheduled_completion timestamp with time zone,
    actual_completion timestamp with time zone,
    scheduled_delivery timestamp with time zone,
    actual_delivery timestamp with time zone,
    regie_number text,
    invoice_date date,
    inproduction boolean DEFAULT false NOT NULL,
    ins_co_id text,
    ins_co_nm text,
    ins_addr1 text,
    ins_addr2 text,
    ins_city text,
    ins_st text,
    ins_zip text,
    ins_ctry text,
    ins_ph1 text,
    ins_ph1x text,
    ins_ph2 text,
    ins_ph2x text,
    ins_fax text,
    ins_faxx text,
    ins_ct_ln text,
    ins_ct_fn text,
    ins_title text,
    ins_ct_ph text,
    ins_ct_phx text,
    ins_ea text,
    ins_memo text,
    policy_no text,
    ded_amt numeric,
    ded_status text,
    asgn_no text,
    asgn_date date,
    asgn_type text,
    clm_no text,
    clm_ofc_id text,
    date_estimated date,
    date_open timestamp with time zone,
    date_scheduled timestamp with time zone,
    date_invoiced timestamp with time zone,
    date_exported timestamp with time zone,
    clm_total numeric DEFAULT 0,
    owner_owing numeric,
    converted boolean DEFAULT false NOT NULL,
    ciecaid text,
    loss_date date,
    clm_ofc_nm text,
    clm_addr1 text,
    clm_addr2 text,
    clm_city text,
    clm_st text,
    clm_zip text,
    clm_ctry text,
    clm_ph1 text,
    clm_ph1x text,
    clm_ph2 text,
    clm_ph2x text,
    clm_fax text,
    clm_faxx text,
    clm_ct_ln text,
    clm_ct_fn text,
    clm_title text,
    clm_ct_ph text,
    clm_ct_phx text,
    clm_ea text,
    payee_nms text,
    pay_type text,
    pay_date date,
    pay_chknm text,
    pay_amt numeric,
    agt_co_id text,
    agt_co_nm text,
    agt_addr1 text,
    agt_addr2 text,
    agt_city text,
    agt_st text,
    agt_zip text,
    agt_ctry text,
    agt_ph1 text,
    agt_ph1x text,
    agt_ph2 text,
    agt_ph2x text,
    agt_fax text,
    agt_faxx text,
    agt_ct_ln text,
    agt_ct_fn text,
    agt_ct_ph text,
    agt_ct_phx text,
    agt_ea text,
    agt_lic_no text,
    loss_type text,
    loss_desc text,
    theft_ind boolean DEFAULT false,
    cat_no text,
    tlos_ind boolean DEFAULT false,
    cust_pr text,
    insd_ln text,
    insd_fn text,
    insd_title text,
    insd_co_nm text,
    insd_addr1 text,
    insd_addr2 text,
    insd_city text,
    insd_st text,
    insd_zip text,
    insd_ctry text,
    insd_ph1 text,
    insd_ph1x text,
    insd_ph2 text,
    insd_ph2x text,
    insd_fax text,
    insd_faxx text,
    insd_ea text,
    ownr_ln text,
    ownr_fn text,
    ownr_title text,
    ownr_co_nm text,
    ownr_addr1 text,
    ownr_addr2 text,
    ownr_city text,
    ownr_st text,
    ownr_zip text,
    ownr_ctry text,
    ownr_ph1 text,
    ownr_ph1x text,
    ownr_ph2 text,
    ownr_ph2x text,
    ownr_fax text,
    ownr_faxx text,
    ownr_ea text,
    area_of_damage jsonb,
    loss_cat text,
    special_coverage_policy boolean DEFAULT false NOT NULL,
    po_number text,
    unit_number text,
    kmin integer,
    kmout integer,
    referral_source text,
    selling_dealer text,
    servicing_dealer text,
    servicing_dealer_contact text,
    selling_dealer_contact text,
    depreciation_taxes numeric,
    other_amount_payable numeric,
    towing_payable numeric,
    storage_payable numeric,
    adjustment_bottom_line numeric,
    tax_pstthr numeric,
    tax_tow_rt numeric,
    tax_sub_rt numeric,
    tax_paint_mat_rt numeric,
    tax_levies_rt numeric,
    tax_prethr numeric,
    tax_thramt numeric,
    tax_str_rt numeric,
    tax_lbr_rt numeric,
    adj_g_disc numeric,
    adj_towdis numeric,
    adj_strdis numeric,
    tax_predis numeric,
    rate_laa numeric,
    status text DEFAULT 'Open'::text NOT NULL,
    cieca_stl jsonb,
    g_bett_amt numeric DEFAULT 0,
    cieca_ttl jsonb,
    plate_no text,
    plate_st text,
    v_vin text,
    v_model_yr text,
    v_model_desc text,
    v_make_desc text,
    v_color text,
    parts_tax_rates jsonb,
    job_totals jsonb,
    production_vars jsonb,
    intakechecklist jsonb,
    invoice_allocation jsonb,
    kanbanparent text,
    employee_body uuid,
    employee_refinish uuid,
    employee_prep uuid,
    tax_registration_number text,
    class text,
    category text,
    deliverchecklist jsonb,
    tax_shop_mat_rt numeric,
    queued_for_parts boolean DEFAULT false NOT NULL,
    ca_gst_registrant boolean DEFAULT false,
    alt_transport text,
    lbr_adjustments jsonb DEFAULT jsonb_build_object() NOT NULL,
    voided boolean DEFAULT false NOT NULL,
    towin boolean DEFAULT false NOT NULL,
    driveable boolean DEFAULT false NOT NULL,
    ca_bc_pvrt numeric,
    ca_customer_gst numeric DEFAULT 0,
    employee_csr uuid
);
COMMENT ON COLUMN public.jobs.tax_pstthr IS 'Parts Tax Rate';
COMMENT ON COLUMN public.jobs.tax_tow_rt IS 'Towing Tax Rate';
COMMENT ON COLUMN public.jobs.tax_sub_rt IS 'Sublet Tax Rate';
CREATE FUNCTION public.search_jobs(search text) RETURNS SETOF public.jobs
    LANGUAGE plpgsql STABLE
    AS $$ BEGIN if search = '' then return query
select *
from jobs j;
else return query
SELECT *
FROM jobs j2
WHERE ro_number ILIKE '%' || search || '%'
    or ownr_fn ILIKE '%' || search || '%'
    or ownr_ln ILIKE '%' || search || '%'
    or ownr_co_nm ILIKE '%' || search || '%'
    or clm_no ILIKE '%' || search || '%'
    or v_make_desc ILIKE '%' || search || '%'
    or v_model_desc ILIKE '%' || search || '%'
    OR plate_no ILIKE '%' || search || '%'
ORDER BY ro_number ILIKE '%' || search || '%'
    OR NULL,
    ownr_ln ILIKE '%' || search || '%'
    OR null,
    ownr_co_nm ILIKE '%' || search || '%'
    OR NULL,
    ownr_fn ILIKE '%' || search || '%'
    OR NULL,
    clm_no ILIKE '%' || search || '%'
    OR NULL,
    v_make_desc ILIKE '%' || search || '%'
    OR NULL,
    v_model_desc ILIKE '%' || search || '%'
    OR NULL,
    plate_no ILIKE '%' || search || '%'
    OR NULL;
end if;
END $$;
CREATE TABLE public.owners (
    id uuid DEFAULT public.gen_random_uuid() NOT NULL,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL,
    ownr_fn text,
    ownr_ln text,
    ownr_addr1 text,
    ownr_addr2 text,
    ownr_city text,
    ownr_st text,
    ownr_zip text,
    ownr_ctry text,
    ownr_ea text,
    ownr_ph1 text,
    preferred_contact text,
    allow_text_message boolean DEFAULT false NOT NULL,
    shopid uuid NOT NULL,
    ownr_ph2 text,
    ownr_co_nm text,
    ownr_title text,
    accountingid bigint NOT NULL
);
CREATE FUNCTION public.search_owners(search text) RETURNS SETOF public.owners
    LANGUAGE plpgsql STABLE
    AS $$
BEGIN
	IF search = '' THEN
		RETURN query
		SELECT
			*
		FROM
			owners;
	ELSE
		RETURN query
		SELECT
			*
		FROM
			owners
		WHERE (ownr_fn || ' ' || ownr_ln)
		ILIKE '%' || search || '%'
			OR ownr_ln ILIKE '%' || search || '%'
			OR ownr_fn ILIKE '%' || search || '%'
			OR ownr_fn ILIKE '%' || search || '%'
			OR ownr_co_nm ILIKE '%' || search || '%'
			OR ownr_ph1 ILIKE '%' || search || '%'
			OR ownr_addr1 ILIKE '%' || search || '%'
		ORDER BY
			(ownr_fn || ' ' || ownr_ln)
			ILIKE '%' || search || '%'
			OR NULL,
			ownr_ln ILIKE '%' || search || '%'
			OR NULL,
			ownr_fn ILIKE '%' || search || '%'
			OR NULL,
			ownr_co_nm ILIKE '%' || search || '%'
			OR NULL,
			ownr_fn ILIKE '%' || search || '%'
			OR NULL,
			ownr_ph1 ILIKE '%' || search || '%'
			OR NULL,
			ownr_addr1 ILIKE '%' || search || '%'
			OR NULL;
	END IF;
END
$$;
CREATE TABLE public.payments (
    id uuid DEFAULT public.gen_random_uuid() NOT NULL,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL,
    jobid uuid NOT NULL,
    amount numeric NOT NULL,
    transactionid text,
    memo text,
    stripeid text,
    payer text,
    exportedat timestamp with time zone,
    type text,
    paymentnum text,
    date date
);
CREATE FUNCTION public.search_payments(search text) RETURNS SETOF public.payments
    LANGUAGE plpgsql STABLE
    AS $$
BEGIN
  if search = '' then
    return query select * from payments ;
  else 
    return query SELECT
  p.*
FROM
  payments p, jobs j
WHERE
p.jobid = j.id AND
(
search <% j.ownr_fn OR
search <% j.ownr_ln OR
search <% j.ownr_co_nm OR
search <% j.ro_number OR
  search <% (p.payer) OR
  search <% (p.transactionid) OR
  search <% (p.memo));
  end if;
	END
$$;
CREATE TABLE public.phonebook (
    id uuid DEFAULT public.gen_random_uuid() NOT NULL,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL,
    bodyshopid uuid NOT NULL,
    firstname text,
    lastname text,
    phone1 text,
    email text,
    address1 text,
    address2 text,
    city text,
    state text,
    zip text,
    country text,
    company text,
    phone2 text,
    fax text,
    category text
);
CREATE FUNCTION public.search_phonebook(search text) RETURNS SETOF public.phonebook
    LANGUAGE plpgsql STABLE
    AS $$
BEGIN
	IF search = '' THEN
		RETURN query
		SELECT
			*
		FROM
			phonebook;
	ELSE
		RETURN query
		SELECT
			phonebook.*
		FROM
			phonebook
		WHERE  firstname ILIKE '%' || search || '%'
			OR lastname ILIKE '%' || search || '%'
			OR company ILIKE '%' || search || '%'
			OR address1 ILIKE '%' || search || '%'
			OR phone1 ILIKE '%' || search || '%'
			OR phone2 ILIKE '%' || search || '%'
			OR email ILIKE '%' || search || '%'
			OR category ILIKE '%' || search || '%'
		ORDER BY
			firstname ILIKE '%' || search || '%'
			OR NULL,
			lastname ILIKE '%' || search || '%'
			OR NULL,
			company ILIKE '%' || search || '%'
			OR NULL,
			address1 ILIKE '%' || search || '%'
			OR NULL,
			phone1 ILIKE '%' || search || '%'
			OR NULL,
			phone2 ILIKE '%' || search || '%'
			OR NULL,
			email ILIKE '%' || search || '%'
			OR NULL,
			category ILIKE '%' || search || '%'
			OR NULL;
	END IF;
END
$$;
CREATE TABLE public.vehicles (
    id uuid DEFAULT public.gen_random_uuid() NOT NULL,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL,
    v_vin text NOT NULL,
    v_make_desc text,
    v_model_desc text,
    v_model_yr text,
    v_color text,
    v_paint_codes jsonb,
    v_bstyle text,
    v_engine text,
    shopid uuid NOT NULL,
    db_v_code text,
    plate_no text,
    plate_st text,
    v_cond text,
    v_prod_dt text,
    v_type text,
    v_trimcode text,
    trim_color text,
    v_mldgcode text,
    v_options text,
    v_tone text,
    v_stage text,
    v_makecode text
);
CREATE FUNCTION public.search_vehicles(search text) RETURNS SETOF public.vehicles
    LANGUAGE plpgsql STABLE
    AS $$
BEGIN
  if search = '' then
    return query select * from vehicles ;
  else 
    return query 
    SELECT *
FROM vehicles
WHERE v_vin ILIKE '%' || search || '%'
    or plate_no ILIKE '%' || search || '%'
    or v_make_desc ILIKE '%' || search || '%'
    or v_model_desc ILIKE '%' || search || '%'
    ORDER BY v_vin ILIKE '%' || search || '%'
    OR NULL,
    plate_no ILIKE '%' || search || '%'
    OR NULL,
        v_model_desc ILIKE '%' || search || '%'
    OR NULL,
    v_make_desc ILIKE '%' || search || '%'
    OR null;
  end if;
	END
$$;
CREATE FUNCTION public.set_current_timestamp_updated_at() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
  _new record;
BEGIN
  _new := NEW;
  _new."updated_at" = NOW();
  RETURN _new;
END;
$$;
CREATE FUNCTION public.update_conversation_on_message() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN
    UPDATE conversations SET updated_at = now() WHERE id = NEW."conversationid";
    RETURN NEW;   
END;
$$;
CREATE TABLE public.allocations (
    id uuid DEFAULT public.gen_random_uuid() NOT NULL,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL,
    joblineid uuid NOT NULL,
    employeeid uuid NOT NULL,
    hours numeric DEFAULT 0 NOT NULL
);
CREATE TABLE public.appointments (
    id uuid DEFAULT public.gen_random_uuid() NOT NULL,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL,
    jobid uuid,
    start timestamp with time zone NOT NULL,
    "end" timestamp with time zone NOT NULL,
    canceled boolean DEFAULT false NOT NULL,
    arrived boolean DEFAULT false NOT NULL,
    isintake boolean DEFAULT true NOT NULL,
    bodyshopid uuid NOT NULL,
    title text,
    block boolean DEFAULT false NOT NULL,
    color text
);
CREATE TABLE public.associations (
    id uuid DEFAULT public.gen_random_uuid() NOT NULL,
    shopid uuid NOT NULL,
    useremail text NOT NULL,
    active boolean DEFAULT true NOT NULL,
    authlevel integer DEFAULT 0 NOT NULL,
    default_prod_list_view text
);
CREATE TABLE public.audit_trail (
    id integer NOT NULL,
    created timestamp without time zone DEFAULT now(),
    schemaname text,
    tabname text,
    operation text,
    recordid uuid,
    new_val json,
    old_val json,
    useremail text,
    bodyshopid uuid
);
CREATE SEQUENCE public.audit_trail_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;
ALTER SEQUENCE public.audit_trail_id_seq OWNED BY public.audit_trail.id;
CREATE TABLE public.available_jobs (
    id uuid DEFAULT public.gen_random_uuid() NOT NULL,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL,
    uploaded_by text NOT NULL,
    cieca_id text NOT NULL,
    bodyshopid uuid NOT NULL,
    est_data jsonb NOT NULL,
    issupplement boolean DEFAULT false NOT NULL,
    jobid uuid,
    supplement_number integer,
    ownr_name text,
    vehicle_info text,
    clm_amt numeric,
    clm_no text,
    source_system text,
    ins_co_nm text
);
CREATE TABLE public.billlines (
    id uuid DEFAULT public.gen_random_uuid() NOT NULL,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL,
    billid uuid NOT NULL,
    line_desc text,
    actual_price numeric DEFAULT 0 NOT NULL,
    actual_cost numeric DEFAULT 0 NOT NULL,
    cost_center text NOT NULL,
    joblineid uuid,
    applicable_taxes jsonb,
    quantity integer DEFAULT 1 NOT NULL,
    deductedfromlbr boolean DEFAULT false NOT NULL,
    lbr_adjustment jsonb
);
CREATE TABLE public.bodyshops (
    id uuid DEFAULT public.gen_random_uuid() NOT NULL,
    shopname text NOT NULL,
    created_at timestamp with time zone DEFAULT now(),
    updated_at timestamp with time zone DEFAULT now(),
    address1 text,
    address2 text,
    city text,
    state text,
    zip_post text,
    country text,
    email text,
    federal_tax_id text,
    insurance_vendor_id text,
    state_tax_id text,
    md_ro_statuses jsonb,
    region_config text DEFAULT 'CA_BC'::text NOT NULL,
    md_order_statuses jsonb,
    md_responsibility_centers jsonb,
    messagingservicesid text,
    shoprates jsonb,
    template_header text,
    textid text,
    production_config jsonb,
    bill_tax_rates jsonb,
    inhousevendorid uuid,
    intakechecklist jsonb DEFAULT jsonb_build_object(),
    accountingconfig jsonb,
    appt_length integer DEFAULT 60 NOT NULL,
    stripe_acct_id text,
    ssbuckets jsonb DEFAULT jsonb_build_array(),
    scoreboard_target jsonb DEFAULT jsonb_build_object(),
    md_referral_sources jsonb DEFAULT jsonb_build_array() NOT NULL,
    md_messaging_presets jsonb DEFAULT jsonb_build_array() NOT NULL,
    speedprint jsonb DEFAULT jsonb_build_array(),
    md_parts_locations jsonb DEFAULT jsonb_build_array(),
    md_notes_presets jsonb DEFAULT jsonb_build_array() NOT NULL,
    md_rbac jsonb,
    prodtargethrs numeric DEFAULT 1 NOT NULL,
    md_classes jsonb DEFAULT jsonb_build_array(),
    md_categories jsonb DEFAULT jsonb_build_array(),
    md_ins_cos jsonb DEFAULT jsonb_build_array(),
    enforce_class boolean DEFAULT false NOT NULL,
    md_labor_rates jsonb DEFAULT jsonb_build_array(),
    deliverchecklist jsonb DEFAULT jsonb_build_object(),
    phone text,
    target_touchtime numeric DEFAULT 3.5,
    appt_colors jsonb DEFAULT jsonb_build_array(),
    appt_alt_transport jsonb DEFAULT jsonb_build_array(),
    schedule_start_time timestamp with time zone,
    schedule_end_time timestamp with time zone,
    imexshopid text,
    default_adjustment_rate numeric DEFAULT 0,
    workingdays jsonb DEFAULT jsonb_build_object() NOT NULL,
    use_fippa boolean DEFAULT false NOT NULL,
    md_payment_types jsonb DEFAULT jsonb_build_array() NOT NULL,
    md_hour_split jsonb DEFAULT jsonb_build_object() NOT NULL,
    autohouseid text,
    logo_img_path jsonb DEFAULT jsonb_build_object(),
    sub_status text DEFAULT 'active'::text NOT NULL,
    jobsizelimit integer DEFAULT 26214400 NOT NULL,
    md_ccc_rates jsonb DEFAULT jsonb_build_array(),
    enforce_referral boolean DEFAULT false NOT NULL
);
CREATE SEQUENCE public.cccontract_agreementnumber_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;
ALTER SEQUENCE public.cccontract_agreementnumber_seq OWNED BY public.cccontracts.agreementnumber;
CREATE TABLE public.conversations (
    id uuid DEFAULT public.gen_random_uuid() NOT NULL,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL,
    bodyshopid uuid NOT NULL,
    phone_num text NOT NULL
);
CREATE TABLE public.counters (
    id uuid DEFAULT public.gen_random_uuid() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL,
    shopid uuid NOT NULL,
    countertype text NOT NULL,
    prefix text,
    count integer DEFAULT 1 NOT NULL
);
CREATE TABLE public.courtesycars (
    id uuid DEFAULT public.gen_random_uuid() NOT NULL,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL,
    bodyshopid uuid NOT NULL,
    make text NOT NULL,
    model text NOT NULL,
    year text NOT NULL,
    plate text NOT NULL,
    color text NOT NULL,
    vin text NOT NULL,
    fleetnumber text,
    purchasedate date,
    servicestartdate date,
    serviceenddate date,
    leaseenddate date,
    status text DEFAULT 'Available'::text NOT NULL,
    nextservicekm numeric DEFAULT 0 NOT NULL,
    nextservicedate date,
    damage text,
    notes text,
    fuel numeric DEFAULT 100 NOT NULL,
    registrationexpires date,
    insuranceexpires date,
    dailycost numeric DEFAULT 0 NOT NULL,
    mileage numeric DEFAULT 0 NOT NULL
);
CREATE TABLE public.csi (
    id uuid DEFAULT public.gen_random_uuid() NOT NULL,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL,
    jobid uuid NOT NULL,
    valid boolean DEFAULT true NOT NULL,
    relateddata jsonb,
    bodyshopid uuid NOT NULL,
    validuntil date,
    response jsonb,
    questionset uuid,
    completedon timestamp with time zone
);
CREATE TABLE public.csiquestions (
    id uuid DEFAULT public.gen_random_uuid() NOT NULL,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL,
    bodyshopid uuid NOT NULL,
    current boolean DEFAULT true NOT NULL,
    config jsonb
);
CREATE TABLE public.documents (
    id uuid DEFAULT public.gen_random_uuid() NOT NULL,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL,
    uploaded_by text NOT NULL,
    jobid uuid,
    name text,
    key text DEFAULT '0'::text NOT NULL,
    billid uuid,
    type text,
    extension text,
    bodyshopid uuid,
    size integer DEFAULT 0 NOT NULL
);
CREATE TABLE public.employees (
    id uuid DEFAULT public.gen_random_uuid() NOT NULL,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL,
    first_name text NOT NULL,
    last_name text NOT NULL,
    employee_number text,
    shopid uuid NOT NULL,
    active boolean DEFAULT true NOT NULL,
    hire_date date,
    termination_date date,
    base_rate numeric DEFAULT 0 NOT NULL,
    flat_rate boolean DEFAULT false NOT NULL,
    pin text,
    user_email text,
    rates jsonb DEFAULT jsonb_build_array() NOT NULL
);
CREATE TABLE public.ioevents (
    id bigint NOT NULL,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    operationname text NOT NULL,
    "time" numeric,
    variables jsonb,
    dbevent boolean DEFAULT false NOT NULL
);
CREATE SEQUENCE public.ioevents_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;
ALTER SEQUENCE public.ioevents_id_seq OWNED BY public.ioevents.id;
CREATE TABLE public.job_conversations (
    jobid uuid NOT NULL,
    conversationid uuid NOT NULL,
    id uuid DEFAULT public.gen_random_uuid() NOT NULL
);
CREATE TABLE public.joblines (
    id uuid DEFAULT public.gen_random_uuid() NOT NULL,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL,
    jobid uuid NOT NULL,
    unq_seq integer,
    line_ind text,
    line_desc text,
    part_type text,
    oem_partno text,
    est_seq integer,
    db_ref text,
    line_ref numeric,
    tax_part boolean DEFAULT true NOT NULL,
    db_price numeric,
    act_price numeric,
    part_qty integer,
    alt_partno text,
    mod_lbr_ty text,
    db_hrs numeric,
    mod_lb_hrs numeric,
    lbr_op text,
    lbr_amt numeric,
    glass_flag boolean,
    price_inc boolean,
    alt_part_i boolean,
    price_j boolean,
    cert_part boolean,
    alt_co_id text,
    alt_overrd boolean,
    alt_partm text,
    prt_dsmk_p numeric,
    prt_dsmk_m numeric,
    lbr_inc boolean,
    lbr_hrs_j boolean,
    lbr_typ_j boolean,
    lbr_op_j boolean,
    paint_stg integer,
    paint_tone integer,
    lbr_tax boolean,
    misc_amt numeric,
    misc_sublt boolean,
    misc_tax boolean,
    bett_type text,
    bett_pctg numeric,
    bett_amt numeric,
    bett_tax boolean,
    op_code_desc text,
    status text,
    removed boolean DEFAULT false NOT NULL,
    line_no integer,
    notes text,
    location text,
    profitcenter_labor text,
    profitcenter_part text,
    sublet_ignored boolean DEFAULT false NOT NULL,
    sublet_completed boolean DEFAULT false NOT NULL,
    manual_line boolean DEFAULT false NOT NULL
);
CREATE VIEW public.joblines_status AS
 SELECT j.jobid,
    j.status,
    count(1) AS count,
    j.part_type
   FROM public.joblines j
  WHERE ((j.part_type IS NOT NULL) AND (j.part_type <> 'PAE'::text))
  GROUP BY j.jobid, j.status, j.part_type;
CREATE TABLE public.masterdata (
    key text NOT NULL,
    value text NOT NULL
);
CREATE TABLE public.messages (
    id uuid DEFAULT public.gen_random_uuid() NOT NULL,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL,
    msid text NOT NULL,
    conversationid uuid NOT NULL,
    text text,
    image boolean DEFAULT false NOT NULL,
    isoutbound boolean DEFAULT false NOT NULL,
    status text DEFAULT 'posted'::text NOT NULL,
    read boolean DEFAULT false NOT NULL,
    userid text,
    image_path jsonb
);
CREATE TABLE public.notes (
    id uuid DEFAULT public.gen_random_uuid() NOT NULL,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL,
    jobid uuid NOT NULL,
    text text,
    created_by text NOT NULL,
    critical boolean DEFAULT false NOT NULL,
    private boolean DEFAULT false NOT NULL,
    audit boolean DEFAULT false NOT NULL
);
CREATE SEQUENCE public.owners_accountingid_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;
ALTER SEQUENCE public.owners_accountingid_seq OWNED BY public.owners.accountingid;
CREATE TABLE public.parts_order_lines (
    id uuid DEFAULT public.gen_random_uuid() NOT NULL,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL,
    orderid uuid NOT NULL,
    job_line_id uuid,
    line_desc text,
    oem_partno text,
    db_price numeric,
    act_price numeric,
    status text DEFAULT 'Ordered'::text NOT NULL,
    line_remarks text,
    quantity numeric DEFAULT 1 NOT NULL,
    backordered_on date,
    backordered_eta date,
    part_type text,
    cost numeric
);
CREATE TABLE public.parts_orders (
    id uuid DEFAULT public.gen_random_uuid() NOT NULL,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL,
    jobid uuid NOT NULL,
    vendorid uuid NOT NULL,
    order_date date DEFAULT now() NOT NULL,
    user_email text NOT NULL,
    status text NOT NULL,
    deliver_by date,
    order_number integer NOT NULL,
    return boolean DEFAULT false NOT NULL,
    returnfrombill uuid
);
CREATE SEQUENCE public.parts_order_order_number_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;
ALTER SEQUENCE public.parts_order_order_number_seq OWNED BY public.parts_orders.order_number;
CREATE TABLE public.scoreboard (
    id uuid DEFAULT public.gen_random_uuid() NOT NULL,
    jobid uuid NOT NULL,
    painthrs numeric DEFAULT 0 NOT NULL,
    bodyhrs numeric DEFAULT 0 NOT NULL,
    date date DEFAULT now() NOT NULL
);
CREATE TABLE public.timetickets (
    id uuid DEFAULT public.gen_random_uuid() NOT NULL,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL,
    date date DEFAULT now() NOT NULL,
    cost_center text NOT NULL,
    employeeid uuid NOT NULL,
    jobid uuid,
    rate numeric DEFAULT 0 NOT NULL,
    productivehrs numeric DEFAULT 0 NOT NULL,
    actualhrs numeric DEFAULT 0 NOT NULL,
    clockon timestamp with time zone,
    clockoff timestamp with time zone,
    ciecacode text,
    bodyshopid uuid NOT NULL,
    memo text
);
CREATE TABLE public.users (
    email text NOT NULL,
    authid text NOT NULL,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL,
    fcmtokens jsonb DEFAULT '{}'::jsonb,
    dashboardlayout jsonb DEFAULT jsonb_build_array() NOT NULL,
    validemail boolean DEFAULT true NOT NULL
);
CREATE TABLE public.vendors (
    id uuid DEFAULT public.gen_random_uuid() NOT NULL,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL,
    bodyshopid uuid NOT NULL,
    name text NOT NULL,
    street1 text,
    street2 text,
    city text,
    state text,
    zip text,
    country text,
    email text,
    discount numeric DEFAULT 0 NOT NULL,
    due_date integer,
    cost_center text,
    favorite jsonb DEFAULT jsonb_build_array(),
    phone text,
    active boolean DEFAULT true NOT NULL
);
ALTER TABLE ONLY public.audit_trail ALTER COLUMN id SET DEFAULT nextval('public.audit_trail_id_seq'::regclass);
ALTER TABLE ONLY public.cccontracts ALTER COLUMN agreementnumber SET DEFAULT nextval('public.cccontract_agreementnumber_seq'::regclass);
ALTER TABLE ONLY public.ioevents ALTER COLUMN id SET DEFAULT nextval('public.ioevents_id_seq'::regclass);
ALTER TABLE ONLY public.owners ALTER COLUMN accountingid SET DEFAULT nextval('public.owners_accountingid_seq'::regclass);
ALTER TABLE ONLY public.parts_orders ALTER COLUMN order_number SET DEFAULT nextval('public.parts_order_order_number_seq'::regclass);
ALTER TABLE ONLY public.allocations
    ADD CONSTRAINT allocations_pkey PRIMARY KEY (id);
ALTER TABLE ONLY public.appointments
    ADD CONSTRAINT appointments_pkey PRIMARY KEY (id);
ALTER TABLE ONLY public.associations
    ADD CONSTRAINT associations_pkey PRIMARY KEY (id);
ALTER TABLE ONLY public.audit_trail
    ADD CONSTRAINT audit_trail_pkey PRIMARY KEY (id);
ALTER TABLE ONLY public.available_jobs
    ADD CONSTRAINT available_jobs_clm_no_bodyshopid_key UNIQUE (clm_no, bodyshopid);
ALTER TABLE ONLY public.available_jobs
    ADD CONSTRAINT available_jobs_pkey PRIMARY KEY (id);
ALTER TABLE ONLY public.bodyshops
    ADD CONSTRAINT bodyshops_autohouseid_key UNIQUE (autohouseid);
ALTER TABLE ONLY public.bodyshops
    ADD CONSTRAINT bodyshops_imexshopid_key UNIQUE (imexshopid);
ALTER TABLE ONLY public.bodyshops
    ADD CONSTRAINT bodyshops_pkey PRIMARY KEY (id);
ALTER TABLE ONLY public.cccontracts
    ADD CONSTRAINT cccontract_pkey PRIMARY KEY (id);
ALTER TABLE ONLY public.conversations
    ADD CONSTRAINT conversations_pkey PRIMARY KEY (id);
ALTER TABLE ONLY public.counters
    ADD CONSTRAINT counters_pkey PRIMARY KEY (id);
ALTER TABLE ONLY public.courtesycars
    ADD CONSTRAINT courtesycars_pkey PRIMARY KEY (id);
ALTER TABLE ONLY public.csi
    ADD CONSTRAINT csiinvites_pkey PRIMARY KEY (id);
ALTER TABLE ONLY public.csiquestions
    ADD CONSTRAINT csiquestion_pkey PRIMARY KEY (id);
ALTER TABLE ONLY public.documents
    ADD CONSTRAINT documents_pkey PRIMARY KEY (id);
ALTER TABLE ONLY public.employees
    ADD CONSTRAINT employees_pkey PRIMARY KEY (id);
ALTER TABLE ONLY public.employees
    ADD CONSTRAINT employees_user_email_key UNIQUE (user_email);
ALTER TABLE ONLY public.exportlog
    ADD CONSTRAINT exportlog_pkey PRIMARY KEY (id);
ALTER TABLE ONLY public.billlines
    ADD CONSTRAINT invoicelines_pkey PRIMARY KEY (id);
ALTER TABLE ONLY public.bills
    ADD CONSTRAINT invoices_pkey PRIMARY KEY (id);
ALTER TABLE ONLY public.ioevents
    ADD CONSTRAINT ioevents_pkey PRIMARY KEY (id);
ALTER TABLE ONLY public.job_conversations
    ADD CONSTRAINT job_conversations_id_key UNIQUE (id);
ALTER TABLE ONLY public.job_conversations
    ADD CONSTRAINT job_conversations_pkey PRIMARY KEY (id);
ALTER TABLE ONLY public.joblines
    ADD CONSTRAINT joblines_pkey PRIMARY KEY (id);
ALTER TABLE ONLY public.jobs
    ADD CONSTRAINT jobs_pkey PRIMARY KEY (id);
ALTER TABLE ONLY public.masterdata
    ADD CONSTRAINT masterdata_pkey PRIMARY KEY (key);
ALTER TABLE ONLY public.messages
    ADD CONSTRAINT messages_msid_key UNIQUE (msid);
ALTER TABLE ONLY public.messages
    ADD CONSTRAINT messages_pkey PRIMARY KEY (id);
ALTER TABLE ONLY public.notes
    ADD CONSTRAINT notes_pkey PRIMARY KEY (id);
ALTER TABLE ONLY public.owners
    ADD CONSTRAINT owners_pkey PRIMARY KEY (id);
ALTER TABLE ONLY public.parts_order_lines
    ADD CONSTRAINT parts_order_lines_pkey PRIMARY KEY (id);
ALTER TABLE ONLY public.parts_orders
    ADD CONSTRAINT parts_order_pkey PRIMARY KEY (id);
ALTER TABLE ONLY public.payments
    ADD CONSTRAINT payments_pkey PRIMARY KEY (id);
ALTER TABLE ONLY public.phonebook
    ADD CONSTRAINT phonebook_pkey PRIMARY KEY (id);
ALTER TABLE ONLY public.scoreboard
    ADD CONSTRAINT scoreboard_pkey PRIMARY KEY (id);
ALTER TABLE ONLY public.timetickets
    ADD CONSTRAINT timetickets_pkey PRIMARY KEY (id);
ALTER TABLE ONLY public.users
    ADD CONSTRAINT users_pkey PRIMARY KEY (email);
ALTER TABLE ONLY public.vehicles
    ADD CONSTRAINT vehicles_pkey PRIMARY KEY (id);
ALTER TABLE ONLY public.vehicles
    ADD CONSTRAINT vehicles_v_vin_shopid_key UNIQUE (v_vin, shopid);
ALTER TABLE ONLY public.vendors
    ADD CONSTRAINT vendors_pkey PRIMARY KEY (id);
CREATE INDEX bills_idx_date ON public.bills USING btree (date);
CREATE INDEX bills_idx_invoice_number ON public.bills USING btree (lower(invoice_number));
CREATE INDEX cccontracts_idx_actual_return ON public.cccontracts USING btree (actualreturn);
CREATE INDEX conversations_idx_phone_num ON public.conversations USING btree (phone_num);
CREATE INDEX conversations_idx_updated_at ON public.conversations USING btree (updated_at);
CREATE INDEX courtesycars_idx_fleet ON public.courtesycars USING btree (fleetnumber);
CREATE INDEX employees_idx_employee_number ON public.employees USING btree (employee_number);
CREATE INDEX idx_invoices_invoicenumber ON public.bills USING gin (invoice_number public.gin_trgm_ops);
CREATE INDEX idx_jobs_clmno ON public.jobs USING gin (clm_no public.gin_trgm_ops);
CREATE INDEX idx_jobs_ownrfn ON public.jobs USING gin (ownr_fn public.gin_trgm_ops);
CREATE INDEX idx_jobs_ownrln ON public.jobs USING gin (ownr_ln public.gin_trgm_ops);
CREATE INDEX idx_jobs_plateno ON public.jobs USING gin (plate_no public.gin_trgm_ops);
CREATE INDEX idx_jobs_ronumber ON public.jobs USING gin (ro_number public.gin_trgm_ops);
CREATE INDEX idx_jobs_vmakedesc ON public.jobs USING gin (v_make_desc public.gin_trgm_ops);
CREATE INDEX idx_jobs_vmodeldesc ON public.jobs USING gin (v_model_desc public.gin_trgm_ops);
CREATE INDEX idx_owner_name ON public.owners USING gin ((((((ownr_fn || ' '::text) || ownr_ln) || ' '::text) || ownr_co_nm)) public.gin_trgm_ops);
CREATE INDEX idx_owners_estnumber ON public.owners USING gin (ownr_co_nm public.gin_trgm_ops);
CREATE INDEX idx_owners_ownr_addr1 ON public.owners USING gin (ownr_addr1 public.gin_trgm_ops);
CREATE INDEX idx_owners_ownr_co_nm ON public.owners USING gin (ownr_co_nm public.gin_trgm_ops);
CREATE INDEX idx_owners_ownr_fn ON public.owners USING gin (ownr_fn public.gin_trgm_ops);
CREATE INDEX idx_owners_ownr_ln ON public.owners USING gin (ownr_ln public.gin_trgm_ops);
CREATE INDEX idx_owners_ownr_ph1 ON public.owners USING gin (ownr_ph1 public.gin_trgm_ops);
CREATE INDEX idx_owners_ownrfn ON public.owners USING gin (ownr_fn public.gin_trgm_ops);
CREATE INDEX idx_owners_ownrln ON public.owners USING gin (ownr_ln public.gin_trgm_ops);
CREATE INDEX idx_payments_memo ON public.payments USING gin (memo public.gin_trgm_ops);
CREATE INDEX idx_payments_payer ON public.payments USING gin (payer public.gin_trgm_ops);
CREATE INDEX idx_payments_txnid ON public.payments USING gin (transactionid public.gin_trgm_ops);
CREATE INDEX idx_vehicles_plateno ON public.vehicles USING gin (plate_no public.gin_trgm_ops);
CREATE INDEX idx_vehicles_vin ON public.vehicles USING gin (v_vin public.gin_trgm_ops);
CREATE INDEX joblines_idx_line_no ON public.joblines USING btree (line_no, jobid);
CREATE INDEX joblines_idx_mod_lbr_ty ON public.joblines USING btree (mod_lbr_ty);
CREATE INDEX joblines_idx_removed ON public.joblines USING btree (removed);
CREATE INDEX jobs_idx_date_exported ON public.jobs USING btree (date_exported);
CREATE INDEX jobs_idx_ownr_fn ON public.jobs USING btree (lower(ownr_fn));
CREATE INDEX jobs_idx_ownr_ln ON public.jobs USING btree (lower(ownr_ln));
CREATE INDEX jobs_idx_ro_number ON public.jobs USING btree (lower(ro_number));
CREATE INDEX jobs_idx_status ON public.jobs USING btree (status);
CREATE INDEX messages_idx_created_at ON public.messages USING btree (created_at);
CREATE INDEX owners_idx_ownr_co_nm ON public.owners USING btree (lower(ownr_co_nm));
CREATE INDEX owners_idx_ownr_fn ON public.owners USING btree (lower(ownr_fn));
CREATE INDEX owners_idx_ownr_ln ON public.owners USING btree (lower(ownr_ln));
CREATE INDEX parts_orders_idx_order_date ON public.parts_orders USING btree (order_date);
CREATE INDEX vehicles_idx_v_make_desc ON public.vehicles USING btree (lower(v_make_desc));
CREATE INDEX vehicles_idx_v_model_desc ON public.vehicles USING btree (lower(v_model_desc));
CREATE INDEX vendor_idx_active ON public.vendors USING btree (active);
CREATE INDEX vendor_idx_name ON public.vendors USING btree (name);
CREATE TRIGGER bills_assign_ihbnum BEFORE INSERT ON public.bills FOR EACH ROW EXECUTE FUNCTION public.assign_ibh_number();
CREATE TRIGGER jobs_assign_ro BEFORE UPDATE ON public.jobs FOR EACH ROW EXECUTE FUNCTION public.assign_ro_number();
CREATE TRIGGER jobs_assign_ro_insert BEFORE INSERT ON public.jobs FOR EACH ROW EXECUTE FUNCTION public.assign_ro_number();
CREATE TRIGGER payments_assign_paymentnum BEFORE INSERT ON public.payments FOR EACH ROW EXECUTE FUNCTION public.assign_payment_number();
CREATE TRIGGER set_public_allocations_updated_at BEFORE UPDATE ON public.allocations FOR EACH ROW EXECUTE FUNCTION public.set_current_timestamp_updated_at();
COMMENT ON TRIGGER set_public_allocations_updated_at ON public.allocations IS 'trigger to set value of column "updated_at" to current timestamp on row update';
CREATE TRIGGER set_public_appointments_updated_at BEFORE UPDATE ON public.appointments FOR EACH ROW EXECUTE FUNCTION public.set_current_timestamp_updated_at();
COMMENT ON TRIGGER set_public_appointments_updated_at ON public.appointments IS 'trigger to set value of column "updated_at" to current timestamp on row update';
CREATE TRIGGER set_public_available_jobs_updated_at BEFORE UPDATE ON public.available_jobs FOR EACH ROW EXECUTE FUNCTION public.set_current_timestamp_updated_at();
COMMENT ON TRIGGER set_public_available_jobs_updated_at ON public.available_jobs IS 'trigger to set value of column "updated_at" to current timestamp on row update';
CREATE TRIGGER set_public_bodyshops_updated_at BEFORE UPDATE ON public.bodyshops FOR EACH ROW EXECUTE FUNCTION public.set_current_timestamp_updated_at();
COMMENT ON TRIGGER set_public_bodyshops_updated_at ON public.bodyshops IS 'trigger to set value of column "updated_at" to current timestamp on row update';
CREATE TRIGGER set_public_cccontract_updated_at BEFORE UPDATE ON public.cccontracts FOR EACH ROW EXECUTE FUNCTION public.set_current_timestamp_updated_at();
COMMENT ON TRIGGER set_public_cccontract_updated_at ON public.cccontracts IS 'trigger to set value of column "updated_at" to current timestamp on row update';
CREATE TRIGGER set_public_conversations_updated_at BEFORE UPDATE ON public.conversations FOR EACH ROW EXECUTE FUNCTION public.set_current_timestamp_updated_at();
COMMENT ON TRIGGER set_public_conversations_updated_at ON public.conversations IS 'trigger to set value of column "updated_at" to current timestamp on row update';
CREATE TRIGGER set_public_counters_updated_at BEFORE UPDATE ON public.counters FOR EACH ROW EXECUTE FUNCTION public.set_current_timestamp_updated_at();
COMMENT ON TRIGGER set_public_counters_updated_at ON public.counters IS 'trigger to set value of column "updated_at" to current timestamp on row update';
CREATE TRIGGER set_public_courtesycars_updated_at BEFORE UPDATE ON public.courtesycars FOR EACH ROW EXECUTE FUNCTION public.set_current_timestamp_updated_at();
COMMENT ON TRIGGER set_public_courtesycars_updated_at ON public.courtesycars IS 'trigger to set value of column "updated_at" to current timestamp on row update';
CREATE TRIGGER set_public_csiinvites_updated_at BEFORE UPDATE ON public.csi FOR EACH ROW EXECUTE FUNCTION public.set_current_timestamp_updated_at();
COMMENT ON TRIGGER set_public_csiinvites_updated_at ON public.csi IS 'trigger to set value of column "updated_at" to current timestamp on row update';
CREATE TRIGGER set_public_csiquestion_updated_at BEFORE UPDATE ON public.csiquestions FOR EACH ROW EXECUTE FUNCTION public.set_current_timestamp_updated_at();
COMMENT ON TRIGGER set_public_csiquestion_updated_at ON public.csiquestions IS 'trigger to set value of column "updated_at" to current timestamp on row update';
CREATE TRIGGER set_public_documents_updated_at BEFORE UPDATE ON public.documents FOR EACH ROW EXECUTE FUNCTION public.set_current_timestamp_updated_at();
COMMENT ON TRIGGER set_public_documents_updated_at ON public.documents IS 'trigger to set value of column "updated_at" to current timestamp on row update';
CREATE TRIGGER set_public_employees_updated_at BEFORE UPDATE ON public.employees FOR EACH ROW EXECUTE FUNCTION public.set_current_timestamp_updated_at();
COMMENT ON TRIGGER set_public_employees_updated_at ON public.employees IS 'trigger to set value of column "updated_at" to current timestamp on row update';
CREATE TRIGGER set_public_exportlog_updated_at BEFORE UPDATE ON public.exportlog FOR EACH ROW EXECUTE FUNCTION public.set_current_timestamp_updated_at();
COMMENT ON TRIGGER set_public_exportlog_updated_at ON public.exportlog IS 'trigger to set value of column "updated_at" to current timestamp on row update';
CREATE TRIGGER set_public_invoicelines_updated_at BEFORE UPDATE ON public.billlines FOR EACH ROW EXECUTE FUNCTION public.set_current_timestamp_updated_at();
COMMENT ON TRIGGER set_public_invoicelines_updated_at ON public.billlines IS 'trigger to set value of column "updated_at" to current timestamp on row update';
CREATE TRIGGER set_public_invoices_updated_at BEFORE UPDATE ON public.bills FOR EACH ROW EXECUTE FUNCTION public.set_current_timestamp_updated_at();
COMMENT ON TRIGGER set_public_invoices_updated_at ON public.bills IS 'trigger to set value of column "updated_at" to current timestamp on row update';
CREATE TRIGGER set_public_joblines_updated_at BEFORE UPDATE ON public.joblines FOR EACH ROW EXECUTE FUNCTION public.set_current_timestamp_updated_at();
COMMENT ON TRIGGER set_public_joblines_updated_at ON public.joblines IS 'trigger to set value of column "updated_at" to current timestamp on row update';
CREATE TRIGGER set_public_jobs_updated_at BEFORE UPDATE ON public.jobs FOR EACH ROW EXECUTE FUNCTION public.set_current_timestamp_updated_at();
COMMENT ON TRIGGER set_public_jobs_updated_at ON public.jobs IS 'trigger to set value of column "updated_at" to current timestamp on row update';
CREATE TRIGGER set_public_messages_updated_at BEFORE UPDATE ON public.messages FOR EACH ROW EXECUTE FUNCTION public.set_current_timestamp_updated_at();
COMMENT ON TRIGGER set_public_messages_updated_at ON public.messages IS 'trigger to set value of column "updated_at" to current timestamp on row update';
CREATE TRIGGER set_public_notes_updated_at BEFORE UPDATE ON public.notes FOR EACH ROW EXECUTE FUNCTION public.set_current_timestamp_updated_at();
COMMENT ON TRIGGER set_public_notes_updated_at ON public.notes IS 'trigger to set value of column "updated_at" to current timestamp on row update';
CREATE TRIGGER set_public_owners_updated_at BEFORE UPDATE ON public.owners FOR EACH ROW EXECUTE FUNCTION public.set_current_timestamp_updated_at();
COMMENT ON TRIGGER set_public_owners_updated_at ON public.owners IS 'trigger to set value of column "updated_at" to current timestamp on row update';
CREATE TRIGGER set_public_parts_order_lines_updated_at BEFORE UPDATE ON public.parts_order_lines FOR EACH ROW EXECUTE FUNCTION public.set_current_timestamp_updated_at();
COMMENT ON TRIGGER set_public_parts_order_lines_updated_at ON public.parts_order_lines IS 'trigger to set value of column "updated_at" to current timestamp on row update';
CREATE TRIGGER set_public_parts_order_updated_at BEFORE UPDATE ON public.parts_orders FOR EACH ROW EXECUTE FUNCTION public.set_current_timestamp_updated_at();
COMMENT ON TRIGGER set_public_parts_order_updated_at ON public.parts_orders IS 'trigger to set value of column "updated_at" to current timestamp on row update';
CREATE TRIGGER set_public_payments_updated_at BEFORE UPDATE ON public.payments FOR EACH ROW EXECUTE FUNCTION public.set_current_timestamp_updated_at();
COMMENT ON TRIGGER set_public_payments_updated_at ON public.payments IS 'trigger to set value of column "updated_at" to current timestamp on row update';
CREATE TRIGGER set_public_phonebook_updated_at BEFORE UPDATE ON public.phonebook FOR EACH ROW EXECUTE FUNCTION public.set_current_timestamp_updated_at();
COMMENT ON TRIGGER set_public_phonebook_updated_at ON public.phonebook IS 'trigger to set value of column "updated_at" to current timestamp on row update';
CREATE TRIGGER set_public_timetickets_updated_at BEFORE UPDATE ON public.timetickets FOR EACH ROW EXECUTE FUNCTION public.set_current_timestamp_updated_at();
COMMENT ON TRIGGER set_public_timetickets_updated_at ON public.timetickets IS 'trigger to set value of column "updated_at" to current timestamp on row update';
CREATE TRIGGER set_public_users_updated_at BEFORE UPDATE ON public.users FOR EACH ROW EXECUTE FUNCTION public.set_current_timestamp_updated_at();
COMMENT ON TRIGGER set_public_users_updated_at ON public.users IS 'trigger to set value of column "updated_at" to current timestamp on row update';
CREATE TRIGGER set_public_vehicles_updated_at BEFORE UPDATE ON public.vehicles FOR EACH ROW EXECUTE FUNCTION public.set_current_timestamp_updated_at();
COMMENT ON TRIGGER set_public_vehicles_updated_at ON public.vehicles IS 'trigger to set value of column "updated_at" to current timestamp on row update';
CREATE TRIGGER set_public_vendors_updated_at BEFORE UPDATE ON public.vendors FOR EACH ROW EXECUTE FUNCTION public.set_current_timestamp_updated_at();
COMMENT ON TRIGGER set_public_vendors_updated_at ON public.vendors IS 'trigger to set value of column "updated_at" to current timestamp on row update';
CREATE TRIGGER trigger_update_conversation_on_message AFTER INSERT ON public.messages FOR EACH ROW EXECUTE FUNCTION public.update_conversation_on_message();
ALTER TABLE ONLY public.allocations
    ADD CONSTRAINT allocations_employeeid_fkey FOREIGN KEY (employeeid) REFERENCES public.employees(id) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE ONLY public.allocations
    ADD CONSTRAINT allocations_joblineid_fkey FOREIGN KEY (joblineid) REFERENCES public.joblines(id) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE ONLY public.appointments
    ADD CONSTRAINT appointments_bodyshopid_fkey FOREIGN KEY (bodyshopid) REFERENCES public.bodyshops(id) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE ONLY public.appointments
    ADD CONSTRAINT appointments_jobid_fkey FOREIGN KEY (jobid) REFERENCES public.jobs(id) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE ONLY public.associations
    ADD CONSTRAINT associations_shopid_fkey FOREIGN KEY (shopid) REFERENCES public.bodyshops(id) ON UPDATE RESTRICT ON DELETE RESTRICT;
ALTER TABLE ONLY public.associations
    ADD CONSTRAINT associations_useremail_fkey FOREIGN KEY (useremail) REFERENCES public.users(email) ON UPDATE RESTRICT ON DELETE RESTRICT;
ALTER TABLE ONLY public.audit_trail
    ADD CONSTRAINT audit_trail_bodyshopid_fkey FOREIGN KEY (bodyshopid) REFERENCES public.bodyshops(id) ON UPDATE RESTRICT ON DELETE RESTRICT;
ALTER TABLE ONLY public.audit_trail
    ADD CONSTRAINT audit_trail_useremail_fkey FOREIGN KEY (useremail) REFERENCES public.users(email) ON UPDATE RESTRICT ON DELETE RESTRICT;
ALTER TABLE ONLY public.available_jobs
    ADD CONSTRAINT available_jobs_bodyshopid_fkey FOREIGN KEY (bodyshopid) REFERENCES public.bodyshops(id) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE ONLY public.available_jobs
    ADD CONSTRAINT available_jobs_jobid_fkey FOREIGN KEY (jobid) REFERENCES public.jobs(id) ON UPDATE RESTRICT ON DELETE CASCADE;
ALTER TABLE ONLY public.billlines
    ADD CONSTRAINT billlines_joblineid_fkey FOREIGN KEY (joblineid) REFERENCES public.joblines(id) ON UPDATE RESTRICT ON DELETE SET NULL;
ALTER TABLE ONLY public.cccontracts
    ADD CONSTRAINT cccontract_courtesycarid_fkey FOREIGN KEY (courtesycarid) REFERENCES public.courtesycars(id) ON UPDATE RESTRICT ON DELETE RESTRICT;
ALTER TABLE ONLY public.cccontracts
    ADD CONSTRAINT cccontract_jobid_fkey FOREIGN KEY (jobid) REFERENCES public.jobs(id) ON UPDATE RESTRICT ON DELETE RESTRICT;
ALTER TABLE ONLY public.conversations
    ADD CONSTRAINT conversations_bodyshopid_fkey FOREIGN KEY (bodyshopid) REFERENCES public.bodyshops(id) ON UPDATE RESTRICT ON DELETE RESTRICT;
ALTER TABLE ONLY public.counters
    ADD CONSTRAINT counters_shopid_fkey FOREIGN KEY (shopid) REFERENCES public.bodyshops(id) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE ONLY public.courtesycars
    ADD CONSTRAINT courtesycars_bodyshopid_fkey FOREIGN KEY (bodyshopid) REFERENCES public.bodyshops(id) ON UPDATE RESTRICT ON DELETE RESTRICT;
ALTER TABLE ONLY public.csi
    ADD CONSTRAINT csi_questionset_fkey FOREIGN KEY (questionset) REFERENCES public.csiquestions(id) ON UPDATE RESTRICT ON DELETE RESTRICT;
ALTER TABLE ONLY public.csi
    ADD CONSTRAINT csiinvites_bodyshopid_fkey FOREIGN KEY (bodyshopid) REFERENCES public.bodyshops(id) ON UPDATE RESTRICT ON DELETE RESTRICT;
ALTER TABLE ONLY public.csi
    ADD CONSTRAINT csiinvites_jobid_fkey FOREIGN KEY (jobid) REFERENCES public.jobs(id) ON UPDATE RESTRICT ON DELETE RESTRICT;
ALTER TABLE ONLY public.csiquestions
    ADD CONSTRAINT csiquestion_bodyshopid_fkey FOREIGN KEY (bodyshopid) REFERENCES public.bodyshops(id) ON UPDATE RESTRICT ON DELETE RESTRICT;
ALTER TABLE ONLY public.documents
    ADD CONSTRAINT documents_billid_fkey FOREIGN KEY (billid) REFERENCES public.bills(id) ON UPDATE RESTRICT ON DELETE SET NULL;
ALTER TABLE ONLY public.documents
    ADD CONSTRAINT documents_bodyshopid_fkey FOREIGN KEY (bodyshopid) REFERENCES public.bodyshops(id) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE ONLY public.documents
    ADD CONSTRAINT documents_jobid_fkey FOREIGN KEY (jobid) REFERENCES public.jobs(id) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE ONLY public.employees
    ADD CONSTRAINT employees_shopid_fkey FOREIGN KEY (shopid) REFERENCES public.bodyshops(id) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE ONLY public.employees
    ADD CONSTRAINT employees_user_email_fkey FOREIGN KEY (user_email) REFERENCES public.users(email) ON UPDATE SET NULL ON DELETE SET NULL;
ALTER TABLE ONLY public.exportlog
    ADD CONSTRAINT exportlog_billid_fkey FOREIGN KEY (billid) REFERENCES public.bills(id) ON UPDATE RESTRICT ON DELETE RESTRICT;
ALTER TABLE ONLY public.exportlog
    ADD CONSTRAINT exportlog_bodyshopid_fkey FOREIGN KEY (bodyshopid) REFERENCES public.bodyshops(id) ON UPDATE RESTRICT ON DELETE RESTRICT;
ALTER TABLE ONLY public.exportlog
    ADD CONSTRAINT exportlog_jobid_fkey FOREIGN KEY (jobid) REFERENCES public.jobs(id) ON UPDATE RESTRICT ON DELETE RESTRICT;
ALTER TABLE ONLY public.exportlog
    ADD CONSTRAINT exportlog_paymentid_fkey FOREIGN KEY (paymentid) REFERENCES public.payments(id) ON UPDATE RESTRICT ON DELETE RESTRICT;
ALTER TABLE ONLY public.exportlog
    ADD CONSTRAINT exportlog_useremail_fkey FOREIGN KEY (useremail) REFERENCES public.users(email) ON UPDATE RESTRICT ON DELETE RESTRICT;
ALTER TABLE ONLY public.billlines
    ADD CONSTRAINT invoicelines_invoiceid_fkey FOREIGN KEY (billid) REFERENCES public.bills(id) ON UPDATE RESTRICT ON DELETE CASCADE;
ALTER TABLE ONLY public.bills
    ADD CONSTRAINT invoices_jobid_fkey FOREIGN KEY (jobid) REFERENCES public.jobs(id) ON UPDATE RESTRICT ON DELETE CASCADE;
ALTER TABLE ONLY public.bills
    ADD CONSTRAINT invoices_vendorid_fkey FOREIGN KEY (vendorid) REFERENCES public.vendors(id) ON UPDATE RESTRICT ON DELETE RESTRICT;
ALTER TABLE ONLY public.job_conversations
    ADD CONSTRAINT job_conversations_conversationid_fkey FOREIGN KEY (conversationid) REFERENCES public.conversations(id) ON UPDATE RESTRICT ON DELETE RESTRICT;
ALTER TABLE ONLY public.job_conversations
    ADD CONSTRAINT job_conversations_jobid_fkey FOREIGN KEY (jobid) REFERENCES public.jobs(id) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE ONLY public.joblines
    ADD CONSTRAINT joblines_jobid_fkey FOREIGN KEY (jobid) REFERENCES public.jobs(id) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE ONLY public.jobs
    ADD CONSTRAINT jobs_employee_body_fkey FOREIGN KEY (employee_body) REFERENCES public.employees(id) ON UPDATE SET NULL ON DELETE SET NULL;
ALTER TABLE ONLY public.jobs
    ADD CONSTRAINT jobs_employee_csr_fkey FOREIGN KEY (employee_csr) REFERENCES public.employees(id) ON UPDATE RESTRICT ON DELETE RESTRICT;
ALTER TABLE ONLY public.jobs
    ADD CONSTRAINT jobs_employee_prep_fkey FOREIGN KEY (employee_prep) REFERENCES public.employees(id) ON UPDATE SET NULL ON DELETE SET NULL;
ALTER TABLE ONLY public.jobs
    ADD CONSTRAINT jobs_employee_refinish_fkey FOREIGN KEY (employee_refinish) REFERENCES public.employees(id) ON UPDATE SET NULL ON DELETE SET NULL;
ALTER TABLE ONLY public.jobs
    ADD CONSTRAINT jobs_ownerid_fkey FOREIGN KEY (ownerid) REFERENCES public.owners(id) ON UPDATE RESTRICT ON DELETE CASCADE;
ALTER TABLE ONLY public.jobs
    ADD CONSTRAINT jobs_shopid_fkey FOREIGN KEY (shopid) REFERENCES public.bodyshops(id) ON UPDATE RESTRICT ON DELETE RESTRICT;
ALTER TABLE ONLY public.jobs
    ADD CONSTRAINT jobs_vehicleid_fkey FOREIGN KEY (vehicleid) REFERENCES public.vehicles(id) ON UPDATE RESTRICT ON DELETE CASCADE;
ALTER TABLE ONLY public.messages
    ADD CONSTRAINT messages_conversationid_fkey FOREIGN KEY (conversationid) REFERENCES public.conversations(id) ON UPDATE RESTRICT ON DELETE RESTRICT;
ALTER TABLE ONLY public.messages
    ADD CONSTRAINT messages_userid_fkey FOREIGN KEY (userid) REFERENCES public.users(email) ON UPDATE SET NULL ON DELETE SET NULL;
ALTER TABLE ONLY public.notes
    ADD CONSTRAINT notes_created_by_fkey FOREIGN KEY (created_by) REFERENCES public.users(email) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE ONLY public.notes
    ADD CONSTRAINT notes_jobid_fkey FOREIGN KEY (jobid) REFERENCES public.jobs(id) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE ONLY public.owners
    ADD CONSTRAINT owners_shopid_fkey FOREIGN KEY (shopid) REFERENCES public.bodyshops(id) ON UPDATE RESTRICT ON DELETE CASCADE;
ALTER TABLE ONLY public.parts_orders
    ADD CONSTRAINT parts_order_jobid_fkey FOREIGN KEY (jobid) REFERENCES public.jobs(id) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE ONLY public.parts_order_lines
    ADD CONSTRAINT parts_order_lines_job_line_id_fkey FOREIGN KEY (job_line_id) REFERENCES public.joblines(id) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE ONLY public.parts_order_lines
    ADD CONSTRAINT parts_order_lines_orderid_fkey FOREIGN KEY (orderid) REFERENCES public.parts_orders(id) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE ONLY public.parts_orders
    ADD CONSTRAINT parts_order_ordered_by_user_id_fkey FOREIGN KEY (user_email) REFERENCES public.users(email) ON UPDATE RESTRICT ON DELETE RESTRICT;
ALTER TABLE ONLY public.parts_orders
    ADD CONSTRAINT parts_orders_returnfrombill_fkey FOREIGN KEY (returnfrombill) REFERENCES public.bills(id) ON UPDATE SET NULL ON DELETE SET NULL;
ALTER TABLE ONLY public.parts_orders
    ADD CONSTRAINT parts_orders_vendorid_fkey FOREIGN KEY (vendorid) REFERENCES public.vendors(id) ON UPDATE SET NULL ON DELETE SET NULL;
ALTER TABLE ONLY public.payments
    ADD CONSTRAINT payments_jobid_fkey FOREIGN KEY (jobid) REFERENCES public.jobs(id) ON UPDATE RESTRICT ON DELETE RESTRICT;
ALTER TABLE ONLY public.phonebook
    ADD CONSTRAINT phonebook_bodyshopid_fkey FOREIGN KEY (bodyshopid) REFERENCES public.bodyshops(id) ON UPDATE RESTRICT ON DELETE RESTRICT;
ALTER TABLE ONLY public.scoreboard
    ADD CONSTRAINT scoreboard_jobid_fkey FOREIGN KEY (jobid) REFERENCES public.jobs(id) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE ONLY public.timetickets
    ADD CONSTRAINT timetickets_bodyshopid_fkey FOREIGN KEY (bodyshopid) REFERENCES public.bodyshops(id) ON UPDATE RESTRICT ON DELETE RESTRICT;
ALTER TABLE ONLY public.timetickets
    ADD CONSTRAINT timetickets_employeeid_fkey FOREIGN KEY (employeeid) REFERENCES public.employees(id) ON UPDATE RESTRICT ON DELETE RESTRICT;
ALTER TABLE ONLY public.timetickets
    ADD CONSTRAINT timetickets_jobid_fkey FOREIGN KEY (jobid) REFERENCES public.jobs(id) ON UPDATE RESTRICT ON DELETE RESTRICT;
ALTER TABLE ONLY public.vehicles
    ADD CONSTRAINT vehicles_shopid_fkey FOREIGN KEY (shopid) REFERENCES public.bodyshops(id) ON UPDATE RESTRICT ON DELETE CASCADE;
ALTER TABLE ONLY public.vendors
    ADD CONSTRAINT vendors_bodyshopid_fkey FOREIGN KEY (bodyshopid) REFERENCES public.bodyshops(id) ON UPDATE CASCADE ON DELETE CASCADE;
