CREATE TABLE IF NOT EXISTS kimai2_ext_approval (
    id INT AUTO_INCREMENT NOT NULL,
    user_id INT NOT NULL,
    creation_date DATE NOT NULL,
    status_id INT NOT NULL DEFAULT 1,
    comment LONGTEXT DEFAULT NULL,
    INDEX IDX_APPROVAL_USER (user_id),
    PRIMARY KEY(id)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS kimai2_ext_approval_history (
    id INT AUTO_INCREMENT NOT NULL,
    approval_id INT NOT NULL,
    user_id INT NOT NULL,
    status_id INT NOT NULL,
    created_at DATETIME NOT NULL,
    comment LONGTEXT DEFAULT NULL,
    INDEX IDX_HISTORY_APPROVAL (approval_id),
    INDEX IDX_HISTORY_USER (user_id),
    PRIMARY KEY(id)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB;

ALTER TABLE kimai2_ext_approval_history 
ADD CONSTRAINT FK_APPROVAL_HISTORY_APPROVAL 
FOREIGN KEY (approval_id) 
REFERENCES kimai2_ext_approval(id) 
ON DELETE CASCADE;