USE myPortfolioMBIP;
GO

IF NOT EXISTS (SELECT 1 FROM dbo.t_peranan WHERE kod_peranan = N'STAF')
    INSERT INTO dbo.t_peranan (kod_peranan, nama_peranan) VALUES (N'STAF', N'Pengguna / Staf');
IF NOT EXISTS (SELECT 1 FROM dbo.t_peranan WHERE kod_peranan = N'PENGARAH')
    INSERT INTO dbo.t_peranan (kod_peranan, nama_peranan) VALUES (N'PENGARAH', N'Pengarah / Penyelia');
IF NOT EXISTS (SELECT 1 FROM dbo.t_peranan WHERE kod_peranan = N'ADMIN')
    INSERT INTO dbo.t_peranan (kod_peranan, nama_peranan) VALUES (N'ADMIN', N'Pentadbir Sistem');
GO

IF NOT EXISTS (SELECT 1 FROM dbo.t_status_portfolio WHERE kod_status = N'DRAF')
    INSERT INTO dbo.t_status_portfolio (kod_status, nama_status, susunan) VALUES (N'DRAF', N'Draf', 1);
IF NOT EXISTS (SELECT 1 FROM dbo.t_status_portfolio WHERE kod_status = N'DIHANTAR')
    INSERT INTO dbo.t_status_portfolio (kod_status, nama_status, susunan) VALUES (N'DIHANTAR', N'Dihantar', 2);
IF NOT EXISTS (SELECT 1 FROM dbo.t_status_portfolio WHERE kod_status = N'DALAM SEMAKAN')
    INSERT INTO dbo.t_status_portfolio (kod_status, nama_status, susunan) VALUES (N'DALAM SEMAKAN', N'Dalam Semakan', 3);
IF NOT EXISTS (SELECT 1 FROM dbo.t_status_portfolio WHERE kod_status = N'DITOLAK')
    INSERT INTO dbo.t_status_portfolio (kod_status, nama_status, susunan) VALUES (N'DITOLAK', N'Ditolak', 4);
IF NOT EXISTS (SELECT 1 FROM dbo.t_status_portfolio WHERE kod_status = N'LULUS')
    INSERT INTO dbo.t_status_portfolio (kod_status, nama_status, susunan) VALUES (N'LULUS', N'Lulus', 5);
GO

DECLARE @sections TABLE (
    kod NVARCHAR(80),
    tajuk NVARCHAR(250),
    susunan INT,
    jenis NVARCHAR(30),
    schema_json NVARCHAR(MAX),
    wajib BIT
);

INSERT INTO @sections (kod, tajuk, susunan, jenis, schema_json, wajib)
VALUES
(N'MAKLUMAT_PEGAWAI', N'Maklumat Pegawai', 1, N'fields', N'[{"name":"tarikh_penempatan","label":"Tarikh Penempatan","type":"date"},{"name":"maklumat_tambahan","label":"Maklumat Tambahan","type":"textarea"}]', 1),
(N'CARTA_ORGANISASI_MBIP', N'1. Carta Organisasi - MBIP', 2, N'textarea', N'[]', 1),
(N'CARTA_ORGANISASI_UNIT', N'1.2 Carta Organisasi - Unit / Seksyen', 3, N'textarea', N'[]', 1),
(N'CARTA_FUNGSI', N'2. Carta Fungsi', 4, N'textarea', N'[]', 1),
(N'AKTIVITI_FUNGSI', N'3. Aktiviti-Aktiviti Bagi Fungsi', 5, N'textarea', N'[]', 1),
(N'DESKRIPSI_TUGAS', N'4. Deskripsi Tugas', 6, N'fields', N'[{"name":"tujuan_pewujudan","label":"Tujuan Pewujudan Jawatan","type":"textarea"},{"name":"akauntabiliti","label":"Akauntabiliti","type":"textarea"},{"name":"tugas_utama","label":"Tugas Utama","type":"textarea"},{"name":"dimensi","label":"Dimensi","type":"textarea"},{"name":"kelayakan","label":"Kelayakan Akademik / Ikhtisas","type":"textarea"},{"name":"kompetensi","label":"Kompetensi","type":"textarea"},{"name":"pengalaman","label":"Pengalaman","type":"textarea"}]', 1),
(N'PERATURAN_PENTADBIRAN', N'Peraturan Pentadbiran', 7, N'textarea', N'[]', 0),
(N'NORMA_KERJA', N'Norma Kerja', 8, N'textarea', N'[]', 0),
(N'SENARAI_UNDANG', N'6. Senarai Undang-Undang, Peraturan dan Punca Kuasa', 9, N'textarea', N'[]', 1),
(N'SENARAI_BORANG', N'7. Senarai Borang', 10, N'textarea', N'[]', 0),
(N'JAWATANKUASA', N'8. Senarai Jawatankuasa Yang Dianggotai', 11, N'textarea', N'[]', 0),
(N'LAIN_LAIN', N'Lain-Lain Seksyen', 12, N'textarea', N'[]', 0);

MERGE dbo.t_seksyen_template AS target
USING @sections AS source
ON target.kod_seksyen = source.kod
WHEN MATCHED THEN
    UPDATE SET
        tajuk_seksyen = source.tajuk,
        susunan = source.susunan,
        jenis_input = source.jenis,
        field_schema = source.schema_json,
        wajib = source.wajib,
        status = N'AKTIF',
        updated_at = SYSDATETIME()
WHEN NOT MATCHED THEN
    INSERT (kod_seksyen, tajuk_seksyen, susunan, jenis_input, field_schema, wajib, status, created_at, updated_at)
    VALUES (source.kod, source.tajuk, source.susunan, source.jenis, source.schema_json, source.wajib, N'AKTIF', SYSDATETIME(), SYSDATETIME());
GO
