-------Indexes--------------

-- Stocks table
CREATE INDEX idx_stocks_id ON stocks(id);
CREATE INDEX idx_stocks_company_status_quantity ON stocks(company_id, status_id, quantity);
CREATE INDEX idx_stocks_item_id ON stocks(item_id);
CREATE INDEX idx_stocks_final_item_id ON stocks(final_item_id);
CREATE INDEX idx_stocks_parent_id ON stocks(parent_id);
CREATE INDEX idx_stocks_zain_tag ON stocks(zain_tag);
CREATE INDEX idx_stocks_uom_id ON stocks(uom_id);
CREATE INDEX idx_stocks_created_at ON stocks(created_at);
CREATE INDEX idx_stocks_project_id ON stocks(project_id);
CREATE INDEX idx_stocks_team_id ON stocks(team_id);
CREATE INDEX idx_stocks_vendor_id ON stocks(vendor_id);
CREATE INDEX idx_stocks_bin_id ON stocks(bin_id);
CREATE INDEX idx_stocks_site_id_origin_destn ON stocks(site_id_origin, site_id_destn);
CREATE INDEX idx_stocks_old_warehouse_id ON stocks(old_warehouse_id);
CREATE INDEX idx_stocks_warehouse_id ON stocks(warehouse_id);
CREATE INDEX idx_stocks_region_id ON stocks(region_id);
CREATE INDEX idx_stocks_item_action_id ON stocks(item_action_id);
CREATE INDEX idx_stocks_item_condition_id ON stocks(item_condition_id);

-- Items table
CREATE INDEX idx_items_id ON items(id);
CREATE INDEX idx_items_sku ON items(sku);
CREATE INDEX idx_items_manufacturer_id ON items(manufacturer_id);
CREATE INDEX idx_items_department_id ON items(department_id);
CREATE INDEX idx_items_item_type_id ON items(item_type_id);

-- Parent Items
CREATE INDEX idx_parent_items_id ON items(id);

-- Final Items
CREATE INDEX idx_final_items_id ON items(id);

-- Item Types
CREATE INDEX idx_item_types_id ON item_types(id);

-- Units of Measure
CREATE INDEX idx_uom_id ON units_of_measure(id);

-- Item UOMs
CREATE INDEX idx_item_uoms_item_uom ON item_uoms(item_id, uom_id);
CREATE INDEX idx_item_uoms_is_base ON item_uoms(item_id, is_base_uom);

-- MRN Line Items
CREATE INDEX idx_mrn_line_items_zain_tag ON mrn_line_items(zain_tag);
CREATE INDEX idx_mrn_line_items_header_id ON mrn_line_items(mrn_header_id);

-- MTR Line Items
CREATE INDEX idx_mtr_line_items_zain_tag ON mtr_line_items(zain_tag);

-- MRN Header
CREATE INDEX idx_mrn_header_id ON mrn_header(id);

-- MIN Line Items
CREATE INDEX idx_min_line_items_zain_tag ON min_line_items(zain_tag);

-- MIN Header
CREATE INDEX idx_min_header_id_status ON min_header(id, min_status);

-- Vendors, Teams, Departments, Sites, Warehouses
CREATE INDEX idx_vendors_id ON vendors(id);
CREATE INDEX idx_teams_id ON teams(id);
CREATE INDEX idx_departments_id ON departments(id);
CREATE INDEX idx_sites_id ON sites(id);
CREATE INDEX idx_warehouses_id ON warehouses(id);
CREATE INDEX idx_regions_id ON regions(id);
CREATE INDEX idx_bins_id ON bins(id);
CREATE INDEX idx_pallets_id ON pallets(id);
CREATE INDEX idx_projects_id ON projects(id);



-----------Indexes---------------------------


CREATE MATERIALIZED VIEW stocks_export_mat AS
SELECT DISTINCT
	ON (STOCKS.ID) STOCKS.ID AS ID,
	1 as company_id,
	1 as status_id,
	STOCKS.UUID AS UUID,
	STOCKS.LOT_NO AS STOCKS_REPORT_LOT_NO,
	ITEMS.SKU AS STOCKS_REPORT_SKU,
	PARENT_ITEMS.SKU AS STOCKS_REPORT_PARENT_SKU,
	FINAL_ITEMS.SKU AS STOCKS_REPORT_FINAL_SKU,
	FINAL_ITEM_TYPES.NAME AS STOCKS_REPORT_FINAL_ITEM_TYPE,
	UPPER(ITEMS.NAME) AS STOCKS_REPORT_DESCRIPTION,
	CURR_UOM.NAME AS STOCKS_REPORT_UOM_CURRENT,
	STOCKS.QUANTITY * COALESCE(U.CONVERSION_FACTOR, 1) AS STOCKS_REPORT_UOM_QUANTITY,
	U.UOM_NAME AS STOCKS_REPORT_UOM,
	(STOCKS.QUANTITY * CURR_UOM_C.CONVERSION_FACTOR) - STOCKS.QUANTITY AS STOCKS_REPORT_ALLOCATED_QTY,
	STOCKS.QUANTITY AS STOCKS_REPORT_BALANCE_QTY,
	U1.UOM_NAME AS STOCKS_REPORT_UOM1,
	CASE
		WHEN U1.UOM_NAME != '' THEN ROUND(
			STOCKS.QUANTITY / COALESCE(U1.CONVERSION_FACTOR, 1),
			2
		)
	END AS STOCKS_REPORT_UOM_QUANTITY1,
	U2.UOM_NAME AS STOCKS_REPORT_UOM3,
	CASE
		WHEN U2.UOM_NAME != '' THEN ROUND(
			STOCKS.QUANTITY / COALESCE(U2.CONVERSION_FACTOR, 1),
			2
		)
	END AS STOCKS_REPORT_UOM_QUANTITY2,
	STOCKS.SERIAL_NO AS STOCKS_REPORT_SERIAL_NO,
	STOCKS.ZAIN_TAG AS STOCKS_REPORT_ZAIN_TAG,
	ITEM_CONDITIONS.NAME AS STOCKS_REPORT_ITEM_CONDITION,
	MANUFACTURERS.NAME AS STOCKS_REPORT_MANUFACTURER,
	SITE_ORIGIN.NAME AS STOCKS_REPORT_SITE_ORIGIN,
	UPPER(SITE_DESTN.NAME) AS STOCKS_REPORT_SITE_DESTN,
	EXTRACT(
		DAY
		FROM
			NOW() - STOCKS.CREATED_AT
	) AS STOCKS_REPORT_AGE_IN_DAYS,
	CAST(STOCKS.STORAGE_AGE AS INTEGER) AS STOCKS_REPORT_STORAGE_AGE,
	CASE
		WHEN EXTRACT(
			DAY
			FROM
				NOW() - STOCKS.CREATED_AT
		) >= 365 THEN CONCAT(
			FLOOR(
				EXTRACT(
					DAY
					FROM
						NOW() - STOCKS.CREATED_AT
				) / 365
			) + 0,
			' --',
			FLOOR(
				EXTRACT(
					DAY
					FROM
						NOW() - STOCKS.CREATED_AT
				) / 365
			) + 1
		)
		ELSE '0--1'
	END AS STOCKS_REPORT_AGE_IN_YEARS,
	UPPER(H.NAMMA_REFERENCE_NUMBER) AS STOCKS_REPORT_LSC_REF,
	VENDORS.NAME AS STOCKS_REPORT_VENDOR,
	DEPARTMENTS.NAME AS STOCKS_REPORT_DEPARTMENT,
	TEAMS.NAME AS STOCKS_REPORT_TEAM,
	OLD_WH.NAME AS STOCKS_REPORT_OLD_WH,
	UPPER(NEW_WH.NAME) AS STOCKS_REPORT_NEW_WH,
	FINAL_ITEMS.NAME AS STOCKS_REPORT_FINAL_ITEMS_DESCRIPTION,
	NEW_WH_LOC.NAME AS STOCKS_REPORT_NEW_WH_LOC,
	UNITS_OF_MEASURE.NAME AS STOCKS_REPORT_FINAL_SKU_UOM,
	NEW_REGION.NAME AS STOCKS_REPORT_NEW_REGION,
	ITEM_ACTIONS.NAME AS STOCKS_REPORT_ITEM_ACTION,
	UPPER(
		CASE
			WHEN STOCKS.IS_ITEM_REUSABLE = TRUE THEN 'Yes'
			ELSE 'No'
		END
	) AS STOCKS_REPORT_IS_ITEM_REUSABLE,
	UPPER(CONCAT(STOCKS.REQUESTER_NAME)) AS STOCKS_REPORT_REQUESTOR_NAME,
	DATE (STOCKS.MTR_REQUEST_DATETIME) AS STOCKS_REPORT_ADD_DATE,
	DATE (STOCKS.UPDATED_AT) AS STOCKS_REPORT_EDIT_DATE,
	STOCKS.REMARKS AS STOCKS_REPORT_REMARKS,
	ITEM_TYPES.NAME AS STOCKS_REPORT_ITEM_TYPE,
	PALLETS.NAME AS STOCKS_REPORT_PALLET,
	STOCKS.PARENT_TAG AS STOCKS_MRN_PARENT_TAG,
	STOCKS.MTR_SEQ AS STOCKS_MTR_SEQ,
	UPPER(STOCKS.AMU_RESPONSE) AS STOCKS_AMU_RESPONSE,
	UPPER(STOCKS.AMU_REF) AS STOCKS_AMU_REF,
	STOCKS.AMU_NBV AS STOCKS_AMU_NBV,
	UPPER(STOCKS.WH_REMARKS) AS STOCKS_MRN_WH_REMARKS,
	CASE
		WHEN (
			STOCKS.QUANTITY * COALESCE(U.CONVERSION_FACTOR, 1)
		) != STOCKS.QUANTITY THEN 'PARTIAL'
	END AS STOCKS_SYS_TAG_STATUS,
	STOCKS.PO_NUMBER AS STOCKS_MTR_PO_NUMBER,
	PROJ.NAME AS STOCKS_MTR_PROJECT_NAME,
	STOCKS.ZAIN_PROJECT_OWNER_ID AS STOCKS_MTR_ZAIN_PROJECT_OWNER,
	TEAMS.NAME AS STOCKS_MTR_ITEM_RELATED_TO_TEAM,
	UPPER(
		CASE
			WHEN STOCKS.IS_SUB_ITEM THEN 'Sub Item'
			ELSE 'Main Item'
		END
	) AS STOCKS_MTR_MAIN_SUB,
	CASE
		WHEN STOCKS.IS_SUB_ITEM THEN STOCKS.MAIN_ITEM_ID
		ELSE 'NA'
	END AS STOCKS_MTR_MAIN_SEQ,
	MTRL.ITEM_DIMENSIONS AS STOCKS_MI_ITEM_DIMENSION,
	STOCKS.MTR_REFERENCE_NUMBER AS STOCKS_MTR_REFERENCE_NUMBER,
	STOCKS.STATUS_ID AS STATUS
FROM
	STOCKS
	LEFT JOIN ITEMS ON STOCKS.ITEM_ID = ITEMS.ID
	LEFT JOIN STOCKS AS PARENT_STOCKS ON STOCKS.PARENT_ID = PARENT_STOCKS.ID
	LEFT JOIN ITEMS AS PARENT_ITEMS ON PARENT_STOCKS.ITEM_ID = PARENT_ITEMS.ID
	LEFT JOIN ITEMS AS FINAL_ITEMS ON STOCKS.FINAL_ITEM_ID = FINAL_ITEMS.ID
	LEFT JOIN ITEM_TYPES AS FINAL_ITEM_TYPES ON FINAL_ITEMS.ITEM_TYPE_ID = FINAL_ITEM_TYPES.ID
	LEFT JOIN MRN_LINE_ITEMS AS L ON STOCKS.ZAIN_TAG = L.ZAIN_TAG
	LEFT JOIN UNITS_OF_MEASURE CURR_UOM ON STOCKS.UOM_ID = CURR_UOM.ID
	LEFT JOIN ITEM_UOMS CURR_UOM_C ON STOCKS.UOM_ID = CURR_UOM_C.UOM_ID
	AND STOCKS.ITEM_ID = CURR_UOM_C.ITEM_ID
	LEFT JOIN MTR_LINE_ITEMS AS MTRL ON STOCKS.ZAIN_TAG = MTRL.ZAIN_TAG
	LEFT JOIN (
		SELECT
			R_UOM.NAME AS UOM_NAME,
			ITEM_UOMS.CONVERSION_FACTOR,
			ITEM_UOMS.ITEM_ID,
			ITEM_UOMS.UOM_ID,
			ITEM_UOMS.IS_BASE_UOM,
			ITEM_UOMS.ID,
			ROW_NUMBER() OVER (
				PARTITION BY
					ITEM_UOMS.ITEM_ID
				ORDER BY
					ITEM_UOMS.ID
			) AS UOMS_RANK
		FROM
			ITEM_UOMS
			LEFT JOIN UNITS_OF_MEASURE R_UOM ON R_UOM.ID = ITEM_UOMS.UOM_ID
	) AS U ON U.ITEM_ID = ITEMS.ID
	AND U.IS_BASE_UOM = TRUE
	AND U.UOMS_RANK = 1
	LEFT JOIN (
		SELECT
			R_UOM.NAME AS UOM_NAME,
			ITEM_UOMS.CONVERSION_FACTOR,
			ITEM_UOMS.ITEM_ID,
			ITEM_UOMS.UOM_ID,
			ITEM_UOMS.IS_BASE_UOM,
			ITEM_UOMS.ID,
			ROW_NUMBER() OVER (
				PARTITION BY
					ITEM_UOMS.ITEM_ID
				ORDER BY
					ITEM_UOMS.ID
			) AS UOMS_RANK
		FROM
			ITEM_UOMS
			LEFT JOIN UNITS_OF_MEASURE R_UOM ON R_UOM.ID = ITEM_UOMS.UOM_ID
	) AS U1 ON U1.ITEM_ID = ITEMS.ID
	AND U1.UOMS_RANK = 2
	LEFT JOIN (
		SELECT
			R_UOM.NAME AS UOM_NAME,
			ITEM_UOMS.CONVERSION_FACTOR,
			ITEM_UOMS.ITEM_ID,
			ITEM_UOMS.UOM_ID,
			ITEM_UOMS.IS_BASE_UOM,
			ITEM_UOMS.ID,
			ROW_NUMBER() OVER (
				PARTITION BY
					ITEM_UOMS.ITEM_ID
				ORDER BY
					ITEM_UOMS.ID
			) AS UOMS_RANK
		FROM
			ITEM_UOMS
			LEFT JOIN UNITS_OF_MEASURE R_UOM ON R_UOM.ID = ITEM_UOMS.UOM_ID
	) AS U2 ON U2.ITEM_ID = ITEMS.ID
	AND U2.UOMS_RANK = 3
	LEFT JOIN ITEM_UOMS ON STOCKS.ITEM_ID = ITEM_UOMS.ITEM_ID
	AND STOCKS.UOM_ID = ITEM_UOMS.UOM_ID
	AND ITEM_UOMS.IS_BASE_UOM = TRUE
	LEFT JOIN ITEM_CONDITIONS ON STOCKS.ITEM_CONDITION_ID = ITEM_CONDITIONS.ID
	LEFT JOIN MANUFACTURERS ON ITEMS.MANUFACTURER_ID = MANUFACTURERS.ID
	LEFT JOIN SITES AS SITE_ORIGIN ON STOCKS.SITE_ID_ORIGIN = SITE_ORIGIN.ID
	LEFT JOIN SITES AS SITE_DESTN ON STOCKS.SITE_ID_DESTN = SITE_DESTN.ID
	LEFT JOIN MRN_HEADER H ON L.MRN_HEADER_ID = H.ID
	LEFT JOIN MIN_LINE_ITEMS MINL ON STOCKS.ZAIN_TAG = MINL.ZAIN_TAG
	LEFT JOIN MIN_HEADER MINH ON MINL.MIN_HEADER_ID = MINH.ID
	AND MINH.MIN_STATUS = 'completed'
	LEFT JOIN VENDORS ON STOCKS.VENDOR_ID = VENDORS.ID
	LEFT JOIN DEPARTMENTS ON ITEMS.DEPARTMENT_ID = DEPARTMENTS.ID
	LEFT JOIN TEAMS ON STOCKS.TEAM_ID = TEAMS.ID
	LEFT JOIN WAREHOUSES AS OLD_WH ON STOCKS.OLD_WAREHOUSE_ID = OLD_WH.ID
	LEFT JOIN WAREHOUSES AS NEW_WH ON STOCKS.WAREHOUSE_ID = NEW_WH.ID
	LEFT JOIN REGIONS AS NEW_REGION ON STOCKS.REGION_ID = NEW_REGION.ID
	LEFT JOIN UNITS_OF_MEASURE ON STOCKS.UOM_ID = UNITS_OF_MEASURE.ID
	LEFT JOIN BINS AS NEW_WH_LOC ON STOCKS.BIN_ID = NEW_WH_LOC.ID
	LEFT JOIN ITEM_ACTIONS ON STOCKS.ITEM_ACTION_ID = ITEM_ACTIONS.ID
	LEFT JOIN ITEM_TYPES ON ITEMS.ITEM_TYPE_ID = ITEM_TYPES.ID
	LEFT JOIN PALLETS ON STOCKS.PALLET_ID = PALLETS.ID
	LEFT JOIN ITEM_UOMS AS MIRL_UOM ON MIRL_UOM.ITEM_ID = MINL.ITEM_ID
	AND MIRL_UOM.UOM_ID = MINL.UOM_ID
	LEFT JOIN PROJECTS AS PROJ ON PROJ.ID = STOCKS.PROJECT_ID
WHERE
	STOCKS.COMPANY_ID = '1'
	AND STOCKS.STATUS_ID != '3'
	AND STOCKS.QUANTITY != '0'
GROUP BY
	STOCKS.ZAIN_PROJECT_OWNER_ID,
	CURR_UOM.NAME,
	CURR_UOM_C.CONVERSION_FACTOR,
	STOCKS.ID,
	STOCKS.UUID,
	STOCKS.LOT_NO,
	ITEMS.SKU,
	PARENT_ITEMS.SKU,
	FINAL_ITEMS.SKU,
	FINAL_ITEM_TYPES.NAME,
	ITEMS.NAME,
	U.UOM_NAME,
	STOCKS.QUANTITY,
	U1.UOM_NAME,
	U2.UOM_NAME,
	STOCKS.SERIAL_NO,
	STOCKS.ZAIN_TAG,
	ITEM_CONDITIONS.NAME,
	MANUFACTURERS.NAME,
	SITE_ORIGIN.NAME,
	SITE_DESTN.NAME,
	H.NAMMA_REFERENCE_NUMBER,
	VENDORS.NAME,
	DEPARTMENTS.NAME,
	TEAMS.NAME,
	OLD_WH.NAME,
	NEW_WH.NAME,
	FINAL_ITEMS.NAME,
	NEW_WH_LOC.NAME,
	UNITS_OF_MEASURE.NAME,
	NEW_REGION.NAME,
	ITEM_ACTIONS.NAME,
	STOCKS.IS_ITEM_REUSABLE,
	STOCKS.REQUESTER_NAME,
	STOCKS.CREATED_AT,
	STOCKS.UPDATED_AT,
	STOCKS.REMARKS,
	ITEM_TYPES.NAME,
	PALLETS.NAME,
	STOCKS.STORAGE_AGE,
	MIRL_UOM.CONVERSION_FACTOR,
	U1.UOM_ID,
	STOCKS.UOM_ID,
	STOCKS.QUANTITY,
	U1.CONVERSION_FACTOR,
	U2.UOM_ID,
	U2.CONVERSION_FACTOR,
	STOCKS.MTR_REFERENCE_NUMBER,
	STOCKS.PARENT_TAG,
	STOCKS.MTR_SEQ,
	STOCKS.AMU_RESPONSE,
	STOCKS.AMU_REF,
	STOCKS.AMU_NBV,
	STOCKS.WH_REMARKS,
	STOCKS.PO_NUMBER,
	PROJ.NAME,
	STOCKS.IS_SUB_ITEM,
	STOCKS.MAIN_ITEM_ID,
	MTRL.ITEM_DIMENSIONS,
	U.CONVERSION_FACTOR
WITH DATA;

-- Add index for fast paging
CREATE INDEX idx_stocks_export_mat_id ON stocks_export_mat(id);

-- Refresh as needed
REFRESH MATERIALIZED VIEW stocks_export_mat;