DROP TABLE IF EXISTS im.dim_shops;
CREATE TABLE im.dim_shops AS
SELECT 
	hs.bk AS shop_id,
	info."name" AS shop_name,
	info."country" AS shop_location,
	info.city AS shop_city,
	info.country AS shop_country
	snap.snapshot_date
FROM dv.h_shop hs
CROSS JOIN ref.snapshot_dates snap
INNER JOIN dv.sh_shop_ctx ctx ON ctx.hk = hs.hk AND snap.snapshot_date BETWEEN ctx.load_dts AND ctx.load_end_dts 
INNER JOIN dv.vsh_shop_hr_info info ON info.hk = hs.hk AND snap.snapshot_date BETWEEN info.load_dts AND info.load_end_dts ;
    
DROP TABLE IF EXISTS im.dim_contracts;
CREATE TABLE im.dim_contracts AS
SELECT
	hc.bk AS contract_id,
	info.status AS contract_status, 
	info.discount AS contract_discount,
	info.closing_date AS contract_closing_date,
	snap.snapshot_date AS snapshot_date
FROM dv.h_contract hc
CROSS JOIN ref.snapshot_dates snap
INNER JOIN dv.sh_contract_ctx ctx ON ctx.hk = hc.hk AND snap.snapshot_date BETWEEN ctx.load_dts AND ctx.load_end_dts AND ctx.src = 'crm'
INNER JOIN dv.vsh_contract_crm_info info ON info.hk = hc.hk AND snap.snapshot_date BETWEEN info.load_dts AND info.load_end_dts;
    
DROP TABLE IF EXISTS im.dim_customers;
CREATE TABLE im.dim_customers AS
SELECT
	hc.bk AS customer_id,
	info.first_name AS customer_first_name,
	info.last_name AS customer_last_name,
	CONCAT(info.first_name,' ',info.last_name) AS customer_full_name,
	info."location" AS customer_location,
	snap.snapshot_date
FROM dv.h_customer hc
CROSS JOIN ref.snapshot_dates snap
INNER JOIN dv.sh_customer_ctx ctx ON ctx.hk = hc.hk AND snap.snapshot_date BETWEEN ctx.load_dts AND ctx.load_end_dts AND ctx.src = 'crm'
INNER JOIN dv.vsh_customer_crm_info info ON info.hk = hc.hk AND snap.snapshot_date BETWEEN info.load_dts AND info.load_end_dts;
   
DROP TABLE IF EXISTS im.dim_cars;
CREATE TABLE im.dim_cars AS
SELECT
	hc.bk,
	info.brand,
	info.model,
	info.price,
	info.release_date,
	snap.snapshot_date
FROM dv.h_car hc
CROSS JOIN ref.snapshot_dates snap
INNER JOIN dv.sh_customer_ctx ctx ON ctx.hk = hc.hk AND snap.snapshot_date BETWEEN ctx.load_dts AND ctx.load_end_dts AND ctx.src = 'crm'
INNER JOIN dv.vsh_car_crm_info info ON info.hk = hc.hk AND snap.snapshot_date BETWEEN info.load_dts AND info.load_end_dts;

DROP TABLE IF EXISTS im.dim_employees;
CREATE TABLE im.dim_employees AS
SELECT
    COALESCE(he.bk,crm.bk) AS employee_id,
    CASE WHEN he.bk is not null
        THEN CONCAT(info.first_name, ' ',last_name) 
        ELSE crm.name
    END AS employee_full_name,
    manager.manager_bk AS manager_id,
    CASE WHEN he.bk is not null
        THEN true
        ELSE FALSE
    END AS is_in_hr,
    CASE WHEN crm.bk IS NOT NULL
        THEN true
        ELSE false
    END AS is_in_crm,
    COALESCE(snap.snapshot_date, crm.snapshot_date)
FROM dv.h_employee he
CROSS JOIN ref.snapshot_dates snap
INNER JOIN dv.sh_employee_ctx ctx ON ctx.hk = he.hk AND snap.snapshot_date BETWEEN ctx.load_dts AND ctx.load_end_dts 
INNER JOIN dv.vsh_employee_hr_info info ON info.hk = he.hk AND snap.snapshot_date BETWEEN info.load_dts AND info.load_end_dts 
LEFT JOIN(
    SELECT
        leh."employeeChild_hk" AS employee_hk,
        he.bk AS manager_bk
    FROM dv.l_employee_hierarchy leh
    CROSS JOIN ref.snapshot_dates snap
    INNER JOIN dv.sl_employee_hierarchy_ctx ctx ON ctx.hk = leh.hk AND snap.snapshot_date BETWEEN ctx.load_dts AND ctx.load_end_dts AND ctx.src = 'hr'
    INNER JOIN dv.h_employee he ON he.hk = leh."employeeParent_hk" 
)manager ON manager.employee_hk = he.hk
FULL JOIN(
    SELECT
        he.bk,
        info_crm."name",
        snap.snapshot_date
    FROM dv.h_employee he
    CROSS JOIN ref.snapshot_dates snap
    INNER JOIN dv.sh_employee_ctx ctx ON ctx.hk = he.hk AND snap.snapshot_date BETWEEN ctx.load_dts AND ctx.load_end_dts AND ctx.src = 'crm'
    INNER JOIN dv.vsh_employee_crm_info info_crm ON info_crm.hk = he.hk AND snap.snapshot_date BETWEEN info_crm.load_dts AND info_crm.load_end_dts 
)crm ON crm.bk = he.bk;
   


DROP TABLE IF EXISTS im.fact_contract_details;
CREATE TABLE im.fact_contract_details AS
SELECT
	hc.bk AS contract_id,
	he.bk AS employee_id,
	hcar.bk AS car_id,
	hcu.bk AS customer_id,
	hs.bk AS shop_id,
	hcar_info.price,
	info.discount,
	hcar_info.price::numeric * (1 -info.discount::numeric) AS contract_total_amount,
	hcar_info.price::numeric * (1- info.discount::numeric) * 0.1 AS contract_employee_comission,
	hcar_info.price::numeric * (1- info.discount::numeric) * 0.01 AS contract_manager_commission,
	snap.snapshot_date
FROM dv.h_contract hc
CROSS JOIN ref.snapshot_dates snap
INNER JOIN dv.sh_contract_ctx ctx ON ctx.hk = hc.hk AND snap.snapshot_date BETWEEN ctx.load_dts AND ctx.load_end_dts 
INNER JOIN dv.vsh_contract_crm_info info ON info.hk = hc.hk AND snap.snapshot_date BETWEEN info.load_dts AND info.load_end_dts 
--CAR
INNER JOIN dv.l_contract_car lcc ON lcc.contract_hk = hc.hk
INNER JOIN dv.sl_contract_car_ctx lcc_ctx ON lcc_ctx.hk = lcc.hk AND snap.snapshot_date BETWEEN lcc_ctx.load_dts AND lcc_ctx.load_end_dts 
INNER JOIN dv.h_car hcar ON hcar.hk = lcc.car_hk
INNER JOIN dv.vsh_car_crm_info hcar_info ON hcar_info.hk = hcar.hk AND snap.snapshot_date BETWEEN hcar_info.load_dts AND hcar_info.load_end_dts 
--EMPLOYEE
INNER JOIN dv.l_contract_employee lce ON lce.contract_hk = hc.hk
INNER JOIN dv.sl_contract_employee_ctx lce_ctx ON lce_ctx.hk = lce.hk AND snap.snapshot_date BETWEEN lce_ctx.load_dts AND lce_ctx.load_end_dts 
INNER JOIN dv.h_employee he ON he.hk = lce.employee_hk
--CUSTOMER
INNER JOIN dv.l_customer_contract lcuc ON lcuc.contract_hk = hc.hk
INNER JOIN dv.sl_customer_contract_ctx lcuc_ctx ON lcuc_ctx.hk = lcuc.hk AND snap.snapshot_date BETWEEN lcuc_ctx.load_dts AND lcuc_ctx.load_end_dts 
INNER JOIN dv.h_customer hcu ON hcu.hk = lcuc.customer_hk
--SHOP
INNER JOIN dv.l_employee_shop les ON les.employee_hk = he.hk
INNER JOIN dv.sl_employee_shop_ctx les_ctx ON les_ctx.hk = les.hk AND snap.snapshot_date BETWEEN les_ctx.load_dts AND les_ctx.load_end_dts 
INNER JOIN dv.h_shop hs ON hs.hk = les.shop_hk
;