CREATE TABLE IF NOT EXISTS field_tasks (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    programme_id INT DEFAULT NULL,
    beneficiary_id INT DEFAULT NULL,
    household_id INT DEFAULT NULL,
    assigned_to INT DEFAULT NULL,
    task_code VARCHAR(60) NOT NULL,
    task_type VARCHAR(80) DEFAULT 'field_visit',
    title VARCHAR(180) NOT NULL,
    lga VARCHAR(120) DEFAULT '',
    community VARCHAR(150) DEFAULT '',
    priority VARCHAR(30) DEFAULT 'medium',
    due_date DATE DEFAULT NULL,
    status VARCHAR(40) DEFAULT 'planned',
    notes TEXT NULL,
    completed_at DATETIME NULL,
    created_by INT DEFAULT 0,
    created_at DATETIME NULL,
    INDEX idx_field_tasks_tenant_status (tenant_id, status),
    INDEX idx_field_tasks_assigned (assigned_to),
    INDEX idx_field_tasks_due_date (due_date),
    INDEX idx_field_tasks_code (task_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO permissions (id, module, name, slug) VALUES
(43, 'field_tasks', 'View field operations tasks', 'field_tasks.view'),
(44, 'field_tasks', 'Manage field operations tasks', 'field_tasks.manage');

INSERT IGNORE INTO role_permissions (role_id, permission_id) VALUES
(2,43),(2,44),
(3,43),(3,44),
(4,43),
(5,43),
(6,43),(6,44),
(7,43),(7,44),
(9,43),
(11,43);