CREATE TABLE t_identity (
    id_identity INT IDENTITY(1,1) PRIMARY KEY,
    no_pekerja NVARCHAR(50) NULL,
    nama NVARCHAR(200) NOT NULL,
    emel NVARCHAR(200) NOT NULL UNIQUE,
    katalaluan NVARCHAR(255) NOT NULL,
    jabatan NVARCHAR(200) NULL,
    jawatan NVARCHAR(200) NULL,
    telefon NVARCHAR(50) NULL,
    status_aktif BIT NOT NULL DEFAULT 1,
    is_admin BIT NOT NULL DEFAULT 0,
    created_at DATETIME2 NOT NULL DEFAULT SYSDATETIME(),
    updated_at DATETIME2 NULL
);

CREATE TABLE t_sistem (
    id_sistem INT IDENTITY(1,1) PRIMARY KEY,
    kod_sistem NVARCHAR(50) NOT NULL UNIQUE,
    nama_sistem NVARCHAR(200) NOT NULL,
    url_callback NVARCHAR(500) NULL,
    status_aktif BIT NOT NULL DEFAULT 1,
    created_at DATETIME2 NOT NULL DEFAULT SYSDATETIME()
);

CREATE TABLE t_role (
    id_role INT IDENTITY(1,1) PRIMARY KEY,
    kod_role NVARCHAR(50) NOT NULL UNIQUE,
    nama_role NVARCHAR(100) NOT NULL,
    tahap INT NOT NULL DEFAULT 1
);

CREATE TABLE t_user_role_sistem (
    id_user_role INT IDENTITY(1,1) PRIMARY KEY,
    id_identity INT NOT NULL,
    id_sistem INT NOT NULL,
    id_role INT NOT NULL,
    assigned_by INT NULL,
    assigned_at DATETIME2 NOT NULL DEFAULT SYSDATETIME(),
    CONSTRAINT FK_user_role_identity FOREIGN KEY (id_identity) REFERENCES t_identity(id_identity),
    CONSTRAINT FK_user_role_sistem FOREIGN KEY (id_sistem) REFERENCES t_sistem(id_sistem),
    CONSTRAINT FK_user_role_role FOREIGN KEY (id_role) REFERENCES t_role(id_role),
    CONSTRAINT UQ_user_sistem UNIQUE (id_identity, id_sistem)
);

CREATE TABLE t_otp (
    id_otp INT IDENTITY(1,1) PRIMARY KEY,
    id_identity INT NOT NULL,
    otp_hash NVARCHAR(255) NOT NULL,
    tujuan NVARCHAR(50) NOT NULL DEFAULT 'LOGIN',
    expires_at DATETIME2 NOT NULL,
    used_at DATETIME2 NULL,
    ip_address NVARCHAR(100) NULL,
    user_agent NVARCHAR(500) NULL,
    created_at DATETIME2 NOT NULL DEFAULT SYSDATETIME(),
    CONSTRAINT FK_otp_identity FOREIGN KEY (id_identity) REFERENCES t_identity(id_identity)
);

CREATE TABLE t_login_log (
    id_log INT IDENTITY(1,1) PRIMARY KEY,
    id_identity INT NULL,
    emel NVARCHAR(200) NULL,
    status_log NVARCHAR(50) NOT NULL,
    mesej NVARCHAR(500) NULL,
    ip_address NVARCHAR(100) NULL,
    user_agent NVARCHAR(500) NULL,
    created_at DATETIME2 NOT NULL DEFAULT SYSDATETIME()
);

CREATE TABLE t_rate_limit (
    id_rate INT IDENTITY(1,1) PRIMARY KEY,
    rate_key NVARCHAR(255) NOT NULL,
    action_name NVARCHAR(50) NOT NULL,
    attempts INT NOT NULL DEFAULT 1,
    locked_until DATETIME2 NULL,
    last_attempt_at DATETIME2 NOT NULL DEFAULT SYSDATETIME(),
    CONSTRAINT UQ_rate_key_action UNIQUE(rate_key, action_name)
);

CREATE INDEX IX_t_otp_identity_expiry ON t_otp(id_identity, expires_at, used_at);
CREATE INDEX IX_t_login_log_created_at ON t_login_log(created_at DESC);
