DROP VIEW IF EXISTS v_aset_master;
CREATE VIEW v_aset_master AS
SELECT m.tahun,
-- m.tipe,
CASE
	WHEN lower(m.keterangan) like '%saluran induk%' then 'irigasi'
	WHEN lower(m.keterangan) like '%bangunan air irigasi%' then 'irigasi'
	WHEN lower(m.keterangan) like '%rumah%' then 'rumah_negara'
	else 'irigasi'
END as tipe,
m.hak as kepemilikan,
m.prop as propinsi,
m.kab as kabupaten,
m.kec as kecamatan,
m.kel as kelurahan,
m.bpn_jenis as jenis_sertifikat,
m.bpn_luas as luas,
m.bpn_nib as nib,
a.nup as nup,
m.bpn_nomorbuku as nomorbuku,
m.bpn_nomorsertifikat as nomor_sertifikat,
m.bpn_penerbit as penerbit,
m.bpn_suratukur as suratukur,
m.bpn_tglsuratukur as tgl_suratukur,
ifnull(m.keterangan, a.nama_barang) AS keterangan,
m.daerah_irigasi
FROM v_asetinframap m
INNER JOIN aset_siman a ON a.bpn_nomorbuku = m.bpn_nomorbuku

UNION

-- Non-matching records from v_asetinframap
SELECT m.tahun,
CASE
	WHEN lower(m.keterangan) like '%saluran induk%' then 'irigasi'
	WHEN lower(m.keterangan) like '%air irigasi%' then 'irigasi'
	WHEN lower(m.keterangan) like '%rumah%' then 'rumah_negara'
	else 'irigasi'
END as tipe,
m.hak as kepemilikan,
m.prop as propinsi,
m.kab as kabupaten,
m.kec as kecamatan,
m.kel as kelurahan,
m.bpn_jenis as jenis_sertifikat,
m.bpn_luas as luas,
m.bpn_nib as nib,
a.nup as nup,
NULL AS bpn_nomorbuku ,
m.bpn_nomorsertifikat as nomor_sertifikat,
m.bpn_penerbit as penerbit,
m.bpn_suratukur as suratukur,
m.bpn_tglsuratukur as tgl_suratukur,
ifnull(m.keterangan, a.nama_barang) AS keterangan,
m.daerah_irigasi -- Replace column1 and column2 with the actual column names from aset_tanah table
FROM v_asetinframap m
LEFT JOIN aset_siman a ON a.bpn_nomorbuku = m.bpn_nomorbuku
WHERE a.bpn_nomorbuku IS NULL

UNION

SELECT YEAR(a.tgl_rekam) as tahun, 
-- a.nama_barang AS keterangan,
CASE
	WHEN lower(a.nama_barang) like '%air baku%' then 'airbaku'
	WHEN lower(a.nama_barang) like '%air irigasi%' then 'irigasi'
	WHEN lower(a.nama_barang) like '%tanah kosong%' then 'rumah_negara'
	WHEN lower(a.nama_barang) like '%kantor pemerintah%' then 'rumah_negara'
	WHEN lower(a.nama_barang) like '%stasiun penelitian%' then 'rumah_negara'
	WHEN lower(a.nama_barang) like '%rumah penjaga%' then 'rumah_negara'
	WHEN lower(a.nama_barang) like '%rumah negara%' then 'rumah_negara'
	WHEN lower(a.nama_barang) like '%aliran sungai%' then 'sungai'
	WHEN lower(a.nama_barang) like '%waduk%' then 'waduk'
	WHEN lower(a.nama_barang) like '%pengaman sungai%' then 'rumah_negara'
	WHEN lower(a.nama_barang) like '%bangunan air%' then 'rumah_negara'
    ELSE 'lainnya'
  END AS tipe,
a.kepemilikan,
a.propinsi,
a.kabupaten,
a.kecamatan,
a.kelurahan,
a.jenis_sertifikat AS jenis_sertifikat,
a.luas_tanah_seluruh AS luas,
NULL AS nib,
a.nup as nup,
a.bpn_nomorbuku,
a.keterangan_sertifikat AS nomor_sertifikat,
NULL AS penerbit,
NULL AS suratukur,
NULL AS tgl_suratukur,
a.nama_barang AS keterangan,
NULL AS daerah_irigasi
FROM aset_siman a
LEFT JOIN v_asetinframap v ON a.bpn_nomorbuku = v.bpn_nomorbuku
WHERE v.bpn_nomorbuku IS NULL;asetbbwsc3asetbbwsc3