IF DB_ID(N'myPortfolioMBIP') IS NULL
BEGIN
    CREATE DATABASE myPortfolioMBIP;
END
GO

USE myPortfolioMBIP;
GO

IF OBJECT_ID(N'dbo.t_peranan', N'U') IS NULL
BEGIN
    CREATE TABLE dbo.t_peranan (
        kod_peranan NVARCHAR(30) NOT NULL PRIMARY KEY,
        nama_peranan NVARCHAR(100) NOT NULL,
        created_at DATETIME2 NOT NULL CONSTRAINT DF_t_peranan_created DEFAULT SYSDATETIME()
    );
END
GO

IF OBJECT_ID(N'dbo.t_status_portfolio', N'U') IS NULL
BEGIN
    CREATE TABLE dbo.t_status_portfolio (
        kod_status NVARCHAR(30) NOT NULL PRIMARY KEY,
        nama_status NVARCHAR(100) NOT NULL,
        susunan INT NOT NULL,
        created_at DATETIME2 NOT NULL CONSTRAINT DF_t_status_portfolio_created DEFAULT SYSDATETIME()
    );
END
GO

IF OBJECT_ID(N'dbo.t_pengguna', N'U') IS NULL
BEGIN
    CREATE TABLE dbo.t_pengguna (
        id_pengguna INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
        no_pekerja NVARCHAR(50) NOT NULL,
        nama NVARCHAR(200) NOT NULL,
        emel NVARCHAR(200) NULL,
        jawatan NVARCHAR(200) NULL,
        gred NVARCHAR(50) NULL,
        jabatan NVARCHAR(200) NULL,
        bahagian NVARCHAR(200) NULL,
        unit NVARCHAR(200) NULL,
        id_pengarah INT NULL,
        peranan NVARCHAR(30) NOT NULL CONSTRAINT DF_t_pengguna_peranan DEFAULT N'STAF',
        status NVARCHAR(30) NOT NULL CONSTRAINT DF_t_pengguna_status DEFAULT N'AKTIF',
        created_at DATETIME2 NOT NULL CONSTRAINT DF_t_pengguna_created DEFAULT SYSDATETIME(),
        updated_at DATETIME2 NOT NULL CONSTRAINT DF_t_pengguna_updated DEFAULT SYSDATETIME(),
        CONSTRAINT UQ_t_pengguna_no_pekerja UNIQUE (no_pekerja),
        CONSTRAINT FK_t_pengguna_pengarah FOREIGN KEY (id_pengarah) REFERENCES dbo.t_pengguna(id_pengguna)
    );
END
GO

IF OBJECT_ID(N'dbo.t_portfolio', N'U') IS NULL
BEGIN
    CREATE TABLE dbo.t_portfolio (
        id_portfolio INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
        id_pengguna INT NOT NULL,
        tajuk_portfolio NVARCHAR(250) NOT NULL,
        tahun INT NOT NULL,
        status_portfolio NVARCHAR(30) NOT NULL CONSTRAINT DF_t_portfolio_status DEFAULT N'DRAF',
        peratus_lengkap INT NOT NULL CONSTRAINT DF_t_portfolio_percent DEFAULT 0,
        tarikh_draf DATETIME2 NULL,
        tarikh_hantar DATETIME2 NULL,
        tarikh_lulus DATETIME2 NULL,
        diluluskan_oleh INT NULL,
        catatan_terkini NVARCHAR(MAX) NULL,
        created_at DATETIME2 NOT NULL CONSTRAINT DF_t_portfolio_created DEFAULT SYSDATETIME(),
        updated_at DATETIME2 NOT NULL CONSTRAINT DF_t_portfolio_updated DEFAULT SYSDATETIME(),
        CONSTRAINT FK_t_portfolio_pengguna FOREIGN KEY (id_pengguna) REFERENCES dbo.t_pengguna(id_pengguna),
        CONSTRAINT FK_t_portfolio_pengarah FOREIGN KEY (diluluskan_oleh) REFERENCES dbo.t_pengguna(id_pengguna),
        CONSTRAINT CK_t_portfolio_percent CHECK (peratus_lengkap BETWEEN 0 AND 100)
    );
END
GO

IF OBJECT_ID(N'dbo.t_seksyen_template', N'U') IS NULL
BEGIN
    CREATE TABLE dbo.t_seksyen_template (
        id_template INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
        kod_seksyen NVARCHAR(80) NOT NULL,
        tajuk_seksyen NVARCHAR(250) NOT NULL,
        susunan INT NOT NULL,
        jenis_input NVARCHAR(30) NOT NULL CONSTRAINT DF_t_seksyen_template_input DEFAULT N'textarea',
        field_schema NVARCHAR(MAX) NULL,
        wajib BIT NOT NULL CONSTRAINT DF_t_seksyen_template_wajib DEFAULT 0,
        status NVARCHAR(30) NOT NULL CONSTRAINT DF_t_seksyen_template_status DEFAULT N'AKTIF',
        created_at DATETIME2 NOT NULL CONSTRAINT DF_t_seksyen_template_created DEFAULT SYSDATETIME(),
        updated_at DATETIME2 NOT NULL CONSTRAINT DF_t_seksyen_template_updated DEFAULT SYSDATETIME(),
        CONSTRAINT UQ_t_seksyen_template_kod UNIQUE (kod_seksyen)
    );
END
GO

IF OBJECT_ID(N'dbo.t_portfolio_seksyen', N'U') IS NULL
BEGIN
    CREATE TABLE dbo.t_portfolio_seksyen (
        id_seksyen INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
        id_portfolio INT NOT NULL,
        kod_seksyen NVARCHAR(80) NOT NULL,
        tajuk_seksyen NVARCHAR(250) NOT NULL,
        kandungan NVARCHAR(MAX) NULL,
        susunan INT NOT NULL,
        jenis_input NVARCHAR(30) NOT NULL CONSTRAINT DF_t_portfolio_seksyen_input DEFAULT N'textarea',
        field_schema NVARCHAR(MAX) NULL,
        wajib BIT NOT NULL CONSTRAINT DF_t_portfolio_seksyen_wajib DEFAULT 0,
        status_lengkap BIT NOT NULL CONSTRAINT DF_t_portfolio_seksyen_lengkap DEFAULT 0,
        created_at DATETIME2 NOT NULL CONSTRAINT DF_t_portfolio_seksyen_created DEFAULT SYSDATETIME(),
        updated_at DATETIME2 NOT NULL CONSTRAINT DF_t_portfolio_seksyen_updated DEFAULT SYSDATETIME(),
        CONSTRAINT FK_t_portfolio_seksyen_portfolio FOREIGN KEY (id_portfolio) REFERENCES dbo.t_portfolio(id_portfolio) ON DELETE CASCADE,
        CONSTRAINT UQ_t_portfolio_seksyen UNIQUE (id_portfolio, kod_seksyen)
    );
END
GO

IF OBJECT_ID(N'dbo.t_proses_kerja', N'U') IS NULL
BEGIN
    CREATE TABLE dbo.t_proses_kerja (
        id_proses INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
        id_portfolio INT NOT NULL,
        tajuk_proses NVARCHAR(250) NOT NULL,
        objektif NVARCHAR(MAX) NULL,
        keterangan NVARCHAR(MAX) NULL,
        pemilik_proses NVARCHAR(200) NULL,
        status_aktif BIT NOT NULL CONSTRAINT DF_t_proses_kerja_aktif DEFAULT 1,
        created_at DATETIME2 NOT NULL CONSTRAINT DF_t_proses_kerja_created DEFAULT SYSDATETIME(),
        updated_at DATETIME2 NOT NULL CONSTRAINT DF_t_proses_kerja_updated DEFAULT SYSDATETIME(),
        CONSTRAINT FK_t_proses_kerja_portfolio FOREIGN KEY (id_portfolio) REFERENCES dbo.t_portfolio(id_portfolio) ON DELETE CASCADE
    );
END
GO

IF OBJECT_ID(N'dbo.t_proses_langkah', N'U') IS NULL
BEGIN
    CREATE TABLE dbo.t_proses_langkah (
        id_langkah INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
        id_proses INT NOT NULL,
        susunan INT NOT NULL,
        jenis_langkah NVARCHAR(30) NOT NULL,
        aktiviti NVARCHAR(MAX) NOT NULL,
        pegawai_bertanggungjawab NVARCHAR(200) NULL,
        dokumen_berkaitan NVARCHAR(250) NULL,
        norma_masa NVARCHAR(100) NULL,
        next_step_id INT NULL,
        yes_step_id INT NULL,
        no_step_id INT NULL,
        catatan NVARCHAR(MAX) NULL,
        created_at DATETIME2 NOT NULL CONSTRAINT DF_t_proses_langkah_created DEFAULT SYSDATETIME(),
        updated_at DATETIME2 NOT NULL CONSTRAINT DF_t_proses_langkah_updated DEFAULT SYSDATETIME(),
        CONSTRAINT FK_t_proses_langkah_proses FOREIGN KEY (id_proses) REFERENCES dbo.t_proses_kerja(id_proses) ON DELETE CASCADE,
        CONSTRAINT FK_t_proses_langkah_next FOREIGN KEY (next_step_id) REFERENCES dbo.t_proses_langkah(id_langkah),
        CONSTRAINT FK_t_proses_langkah_yes FOREIGN KEY (yes_step_id) REFERENCES dbo.t_proses_langkah(id_langkah),
        CONSTRAINT FK_t_proses_langkah_no FOREIGN KEY (no_step_id) REFERENCES dbo.t_proses_langkah(id_langkah),
        CONSTRAINT CK_t_proses_langkah_jenis CHECK (jenis_langkah IN (N'MULA', N'PROSES', N'KEPUTUSAN', N'DOKUMEN', N'TAMAT'))
    );
END
GO

IF OBJECT_ID(N'dbo.t_kelulusan_portfolio', N'U') IS NULL
BEGIN
    CREATE TABLE dbo.t_kelulusan_portfolio (
        id_kelulusan INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
        id_portfolio INT NOT NULL,
        tindakan NVARCHAR(50) NOT NULL,
        status_sebelum NVARCHAR(30) NULL,
        status_selepas NVARCHAR(30) NOT NULL,
        catatan NVARCHAR(MAX) NULL,
        id_pengarah INT NOT NULL,
        tarikh_tindakan DATETIME2 NOT NULL CONSTRAINT DF_t_kelulusan_tarikh DEFAULT SYSDATETIME(),
        CONSTRAINT FK_t_kelulusan_portfolio FOREIGN KEY (id_portfolio) REFERENCES dbo.t_portfolio(id_portfolio) ON DELETE CASCADE,
        CONSTRAINT FK_t_kelulusan_pengarah FOREIGN KEY (id_pengarah) REFERENCES dbo.t_pengguna(id_pengguna)
    );
END
GO

IF OBJECT_ID(N'dbo.t_dokumen_jana', N'U') IS NULL
BEGIN
    CREATE TABLE dbo.t_dokumen_jana (
        id_dokumen INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
        id_portfolio INT NOT NULL,
        jenis_dokumen NVARCHAR(20) NOT NULL,
        nama_fail NVARCHAR(255) NOT NULL,
        path_fail NVARCHAR(500) NOT NULL,
        generated_by INT NOT NULL,
        generated_at DATETIME2 NOT NULL CONSTRAINT DF_t_dokumen_generated DEFAULT SYSDATETIME(),
        CONSTRAINT FK_t_dokumen_portfolio FOREIGN KEY (id_portfolio) REFERENCES dbo.t_portfolio(id_portfolio) ON DELETE CASCADE,
        CONSTRAINT FK_t_dokumen_pengguna FOREIGN KEY (generated_by) REFERENCES dbo.t_pengguna(id_pengguna),
        CONSTRAINT CK_t_dokumen_jenis CHECK (jenis_dokumen IN (N'WORD', N'PDF'))
    );
END
GO

IF OBJECT_ID(N'dbo.t_audit_log', N'U') IS NULL
BEGIN
    CREATE TABLE dbo.t_audit_log (
        id_log BIGINT IDENTITY(1,1) NOT NULL PRIMARY KEY,
        id_pengguna INT NULL,
        modul NVARCHAR(80) NOT NULL,
        tindakan NVARCHAR(80) NOT NULL,
        rujukan_id INT NULL,
        keterangan NVARCHAR(MAX) NULL,
        ip_address NVARCHAR(80) NULL,
        user_agent NVARCHAR(500) NULL,
        created_at DATETIME2 NOT NULL CONSTRAINT DF_t_audit_log_created DEFAULT SYSDATETIME(),
        CONSTRAINT FK_t_audit_pengguna FOREIGN KEY (id_pengguna) REFERENCES dbo.t_pengguna(id_pengguna)
    );
END
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'IX_t_portfolio_status' AND object_id = OBJECT_ID(N'dbo.t_portfolio'))
    CREATE INDEX IX_t_portfolio_status ON dbo.t_portfolio(status_portfolio, updated_at);
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'IX_t_proses_langkah_susunan' AND object_id = OBJECT_ID(N'dbo.t_proses_langkah'))
    CREATE INDEX IX_t_proses_langkah_susunan ON dbo.t_proses_langkah(id_proses, susunan);
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'IX_t_audit_log_modul' AND object_id = OBJECT_ID(N'dbo.t_audit_log'))
    CREATE INDEX IX_t_audit_log_modul ON dbo.t_audit_log(modul, tindakan, created_at);
GO
