CREATE TABLE manual_task_templates (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    created_by INT UNSIGNED NOT NULL,
    updated_by INT UNSIGNED NULL,
    title VARCHAR(180) NOT NULL,
    description TEXT NULL,
    recurrence_type ENUM('none','daily','weekly','monthly','interval','monthly_days') NOT NULL DEFAULT 'none',
    interval_days INT UNSIGNED NULL,
    monthly_days VARCHAR(120) NULL,
    start_date DATE NOT NULL,
    end_date DATE 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_manual_task_templates_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE,
    CONSTRAINT fk_manual_task_templates_creator FOREIGN KEY (created_by) REFERENCES users (id) ON DELETE CASCADE,
    CONSTRAINT fk_manual_task_templates_updater FOREIGN KEY (updated_by) REFERENCES users (id) ON DELETE SET NULL
);

CREATE INDEX idx_manual_task_templates_user ON manual_task_templates (user_id);
CREATE INDEX idx_manual_task_templates_active ON manual_task_templates (is_active, start_date, end_date);

CREATE TABLE manual_task_conditions (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    template_id INT UNSIGNED NOT NULL,
    condition_type ENUM('leads_created','proposals_created','budgets_sent','calls_made','conversion_rate') NOT NULL,
    target_value DECIMAL(10,2) NOT NULL,
    metadata JSON NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_manual_task_conditions_template FOREIGN KEY (template_id) REFERENCES manual_task_templates (id) ON DELETE CASCADE
);

CREATE TABLE manual_task_instances (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    template_id INT UNSIGNED NOT NULL,
    micro_task_set_id INT UNSIGNED NULL,
    period_start DATE NOT NULL,
    period_end DATE NOT NULL,
    status ENUM('pending','completed','expired') NOT NULL DEFAULT 'pending',
    completed_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_manual_task_instances_template FOREIGN KEY (template_id) REFERENCES manual_task_templates (id) ON DELETE CASCADE,
    CONSTRAINT fk_manual_task_instances_set FOREIGN KEY (micro_task_set_id) REFERENCES micro_task_sets (id) ON DELETE SET NULL
);

CREATE UNIQUE INDEX idx_manual_task_instance_unique ON manual_task_instances (template_id, period_start, period_end);
CREATE INDEX idx_manual_task_instance_status ON manual_task_instances (status, period_end);