CREATE TABLE micro_task_sets (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    goal_id INT UNSIGNED NULL,
    assignment_id INT UNSIGNED NULL,
    owner_type ENUM('broker','employee','team') NOT NULL DEFAULT 'employee',
    owner_id INT UNSIGNED NOT NULL,
    title VARCHAR(160) NOT NULL,
    reference_period_start DATE NOT NULL,
    reference_period_end DATE NOT NULL,
    status ENUM('pending','in_progress','completed','expired','cancelled') NOT NULL DEFAULT 'pending',
    completion_rate DECIMAL(5, 2) NOT NULL DEFAULT 0,
    total_weight DECIMAL(8, 2) NOT NULL DEFAULT 0,
    due_at DATETIME NULL,
    generated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    expires_at DATETIME NULL,
    metadata JSON NULL,
    created_by INT UNSIGNED NULL,
    updated_by INT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_micro_task_sets_goal FOREIGN KEY (goal_id) REFERENCES advanced_goals (id) ON DELETE SET NULL,
    CONSTRAINT fk_micro_task_sets_assignment FOREIGN KEY (assignment_id) REFERENCES goal_assignments (id) ON DELETE SET NULL,
    CONSTRAINT fk_micro_task_sets_owner FOREIGN KEY (owner_id) REFERENCES users (id) ON DELETE CASCADE,
    CONSTRAINT fk_micro_task_sets_creator FOREIGN KEY (created_by) REFERENCES users (id) ON DELETE SET NULL,
    CONSTRAINT fk_micro_task_sets_updater FOREIGN KEY (updated_by) REFERENCES users (id) ON DELETE SET NULL
);

CREATE INDEX idx_micro_task_sets_owner ON micro_task_sets (owner_type, owner_id);
CREATE INDEX idx_micro_task_sets_status ON micro_task_sets (status);
CREATE INDEX idx_micro_task_sets_period ON micro_task_sets (reference_period_start, reference_period_end);

CREATE TABLE micro_tasks (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    set_id INT UNSIGNED NOT NULL,
    indicator_id INT UNSIGNED NULL,
    title VARCHAR(180) NOT NULL,
    description TEXT NULL,
    status ENUM('pending','in_progress','completed','overdue','cancelled') NOT NULL DEFAULT 'pending',
    weight DECIMAL(8, 2) NOT NULL DEFAULT 1,
    assigned_to INT UNSIGNED NULL,
    due_at DATETIME NULL,
    started_at DATETIME NULL,
    completed_at DATETIME NULL,
    effort_minutes INT UNSIGNED NULL,
    actual_value DECIMAL(15, 4) NULL,
    notes TEXT NULL,
    metadata JSON NULL,
    created_by INT UNSIGNED NULL,
    updated_by INT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_micro_tasks_set FOREIGN KEY (set_id) REFERENCES micro_task_sets (id) ON DELETE CASCADE,
    CONSTRAINT fk_micro_tasks_indicator FOREIGN KEY (indicator_id) REFERENCES goal_indicators (id) ON DELETE SET NULL,
    CONSTRAINT fk_micro_tasks_assigned FOREIGN KEY (assigned_to) REFERENCES users (id) ON DELETE SET NULL,
    CONSTRAINT fk_micro_tasks_creator FOREIGN KEY (created_by) REFERENCES users (id) ON DELETE SET NULL,
    CONSTRAINT fk_micro_tasks_updater FOREIGN KEY (updated_by) REFERENCES users (id) ON DELETE SET NULL
);

CREATE INDEX idx_micro_tasks_status ON micro_tasks (status);
CREATE INDEX idx_micro_tasks_due ON micro_tasks (due_at);

CREATE TABLE micro_task_events (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    task_id INT UNSIGNED NOT NULL,
    actor_id INT UNSIGNED NULL,
    old_status ENUM('pending','in_progress','completed','overdue','cancelled') NULL,
    new_status ENUM('pending','in_progress','completed','overdue','cancelled') NOT NULL,
    notes TEXT NULL,
    payload JSON NULL,
    logged_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_micro_task_events_task FOREIGN KEY (task_id) REFERENCES micro_tasks (id) ON DELETE CASCADE,
    CONSTRAINT fk_micro_task_events_actor FOREIGN KEY (actor_id) REFERENCES users (id) ON DELETE SET NULL
);

CREATE INDEX idx_micro_task_events_task ON micro_task_events (task_id);

CREATE TABLE badge_library (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    slug VARCHAR(100) NOT NULL,
    name VARCHAR(150) NOT NULL,
    description TEXT NULL,
    monetary_value DECIMAL(10, 2) NOT NULL DEFAULT 0,
    image_path VARCHAR(255) NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_by INT UNSIGNED NULL,
    updated_by INT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_badge_library_creator FOREIGN KEY (created_by) REFERENCES users (id) ON DELETE SET NULL,
    CONSTRAINT fk_badge_library_updater FOREIGN KEY (updated_by) REFERENCES users (id) ON DELETE SET NULL
);

CREATE UNIQUE INDEX idx_badge_library_slug ON badge_library (slug);

CREATE TABLE badge_rules (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    badge_id INT UNSIGNED NOT NULL,
    goal_id INT UNSIGNED NULL,
    rule_type ENUM('goal_completion','set_completion','custom') NOT NULL DEFAULT 'goal_completion',
    min_completion_rate DECIMAL(5, 2) NULL,
    min_total_weight DECIMAL(8, 2) NULL,
    metadata JSON NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_badge_rules_badge FOREIGN KEY (badge_id) REFERENCES badge_library (id) ON DELETE CASCADE,
    CONSTRAINT fk_badge_rules_goal FOREIGN KEY (goal_id) REFERENCES advanced_goals (id) ON DELETE CASCADE
);

CREATE INDEX idx_badge_rules_goal ON badge_rules (goal_id);
CREATE INDEX idx_badge_rules_active ON badge_rules (is_active);

CREATE TABLE micro_task_set_badges (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    set_id INT UNSIGNED NOT NULL,
    badge_id INT UNSIGNED NOT NULL,
    rule_id INT UNSIGNED NULL,
    awarded_by INT UNSIGNED NULL,
    awarded_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_micro_task_set_badges_set FOREIGN KEY (set_id) REFERENCES micro_task_sets (id) ON DELETE CASCADE,
    CONSTRAINT fk_micro_task_set_badges_badge FOREIGN KEY (badge_id) REFERENCES badge_library (id) ON DELETE CASCADE,
    CONSTRAINT fk_micro_task_set_badges_rule FOREIGN KEY (rule_id) REFERENCES badge_rules (id) ON DELETE SET NULL,
    CONSTRAINT fk_micro_task_set_badges_user FOREIGN KEY (awarded_by) REFERENCES users (id) ON DELETE SET NULL
);

CREATE INDEX idx_micro_task_set_badges_set ON micro_task_set_badges (set_id);

CREATE TABLE user_badges (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    badge_id INT UNSIGNED NOT NULL,
    set_id INT UNSIGNED NULL,
    rule_id INT UNSIGNED NULL,
    awarded_by INT UNSIGNED NULL,
    awarded_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    notes TEXT NULL,
    is_revoked TINYINT(1) NOT NULL DEFAULT 0,
    revoked_at DATETIME NULL,
    revoked_by INT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_user_badges_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE,
    CONSTRAINT fk_user_badges_badge FOREIGN KEY (badge_id) REFERENCES badge_library (id) ON DELETE CASCADE,
    CONSTRAINT fk_user_badges_set FOREIGN KEY (set_id) REFERENCES micro_task_sets (id) ON DELETE SET NULL,
    CONSTRAINT fk_user_badges_rule FOREIGN KEY (rule_id) REFERENCES badge_rules (id) ON DELETE SET NULL,
    CONSTRAINT fk_user_badges_awarded_by FOREIGN KEY (awarded_by) REFERENCES users (id) ON DELETE SET NULL,
    CONSTRAINT fk_user_badges_revoked_by FOREIGN KEY (revoked_by) REFERENCES users (id) ON DELETE SET NULL
);

CREATE INDEX idx_user_badges_user ON user_badges (user_id);
CREATE INDEX idx_user_badges_badge ON user_badges (badge_id);

CREATE TABLE monthly_bonus_payouts (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    period_start DATE NOT NULL,
    period_end DATE NOT NULL,
    total_badge_value DECIMAL(12, 2) NOT NULL DEFAULT 0,
    adjustments_total DECIMAL(12, 2) NOT NULL DEFAULT 0,
    status ENUM('open','finalized','paid') NOT NULL DEFAULT 'open',
    generated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    finalized_at DATETIME NULL,
    paid_at DATETIME NULL,
    metadata JSON NULL,
    created_by INT UNSIGNED NULL,
    updated_by INT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_monthly_bonus_payouts_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE,
    CONSTRAINT fk_monthly_bonus_payouts_creator FOREIGN KEY (created_by) REFERENCES users (id) ON DELETE SET NULL,
    CONSTRAINT fk_monthly_bonus_payouts_updater FOREIGN KEY (updated_by) REFERENCES users (id) ON DELETE SET NULL
);

CREATE UNIQUE INDEX idx_monthly_bonus_unique ON monthly_bonus_payouts (user_id, period_start, period_end);
CREATE INDEX idx_monthly_bonus_status ON monthly_bonus_payouts (status);

CREATE TABLE bonus_adjustments (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    payout_id BIGINT UNSIGNED NULL,
    amount DECIMAL(12, 2) NOT NULL,
    reason VARCHAR(255) NOT NULL,
    notes TEXT NULL,
    created_by INT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_bonus_adjustments_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE,
    CONSTRAINT fk_bonus_adjustments_payout FOREIGN KEY (payout_id) REFERENCES monthly_bonus_payouts (id) ON DELETE SET NULL,
    CONSTRAINT fk_bonus_adjustments_creator FOREIGN KEY (created_by) REFERENCES users (id) ON DELETE SET NULL
);

CREATE INDEX idx_bonus_adjustments_user ON bonus_adjustments (user_id);

CREATE TABLE governance_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    actor_id INT UNSIGNED NULL,
    entity_type VARCHAR(120) NOT NULL,
    entity_id BIGINT UNSIGNED NULL,
    action VARCHAR(80) NOT NULL,
    changes JSON NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_governance_logs_actor FOREIGN KEY (actor_id) REFERENCES users (id) ON DELETE SET NULL
);

CREATE INDEX idx_governance_logs_entity ON governance_logs (entity_type, entity_id);

CREATE TABLE automation_jobs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    job_type VARCHAR(150) NOT NULL,
    payload JSON NULL,
    status ENUM('queued','running','completed','failed') NOT NULL DEFAULT 'queued',
    attempts TINYINT UNSIGNED NOT NULL DEFAULT 0,
    max_attempts TINYINT UNSIGNED NOT NULL DEFAULT 3,
    scheduled_for DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    started_at DATETIME NULL,
    finished_at DATETIME NULL,
    error_message TEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
);

CREATE INDEX idx_automation_jobs_schedule ON automation_jobs (scheduled_for, status);

CREATE TABLE automation_job_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    job_id BIGINT UNSIGNED NOT NULL,
    message TEXT NULL,
    context JSON NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_automation_job_logs_job FOREIGN KEY (job_id) REFERENCES automation_jobs (id) ON DELETE CASCADE
);

CREATE INDEX idx_automation_job_logs_job ON automation_job_logs (job_id);