-- Initial MVP slice: single-table OKR workflow storage
CREATE TABLE IF NOT EXISTS okr_entries (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    objective_title VARCHAR(190) NOT NULL,
    owner_name VARCHAR(120) NOT NULL,
    owner_email VARCHAR(160) NOT NULL,
    owner_role VARCHAR(40) NOT NULL DEFAULT 'Staff',
    department_name VARCHAR(120) NOT NULL,
    period_label VARCHAR(60) NOT NULL,
    approver_name VARCHAR(120) NOT NULL,
    approver_level VARCHAR(40) NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'Draft',
    objective_score DECIMAL(5,2) NOT NULL DEFAULT 0,
    key_results_json LONGTEXT NOT NULL,
    comments_json LONGTEXT NULL,
    activity_json LONGTEXT NULL,
    submitted_at DATETIME NULL,
    approved_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_status (status),
    INDEX idx_department (department_name),
    INDEX idx_owner_email (owner_email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;