CREATE TABLE IF NOT EXISTS activity_types (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    code VARCHAR(60) NOT NULL,
    name VARCHAR(120) NOT NULL,
    icon VARCHAR(40) NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    sort_order INT UNSIGNED NOT NULL DEFAULT 0,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_activity_types_tenant_code (tenant_id, code),
    CONSTRAINT fk_activity_types_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS sales_activities (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    type_id BIGINT UNSIGNED NULL,
    title VARCHAR(190) NOT NULL,
    details TEXT NULL,
    status ENUM('planned', 'in_progress', 'done', 'cancelled') NOT NULL DEFAULT 'planned',
    priority ENUM('low', 'medium', 'high') NOT NULL DEFAULT 'medium',
    due_at DATETIME NULL,
    starts_at DATETIME NULL,
    completed_at DATETIME NULL,
    assigned_to BIGINT UNSIGNED NULL,
    contact_id BIGINT UNSIGNED NULL,
    lead_capture_id BIGINT UNSIGNED NULL,
    opportunity_id BIGINT UNSIGNED NULL,
    account_id BIGINT UNSIGNED NULL,
    created_by BIGINT UNSIGNED NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_sales_activities_tenant_status_due (tenant_id, status, due_at),
    INDEX idx_sales_activities_tenant_assigned (tenant_id, assigned_to, status),
    CONSTRAINT fk_sales_activities_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_sales_activities_type FOREIGN KEY (type_id) REFERENCES activity_types(id) ON DELETE SET NULL,
    CONSTRAINT fk_sales_activities_assigned FOREIGN KEY (assigned_to) REFERENCES users(id) ON DELETE SET NULL,
    CONSTRAINT fk_sales_activities_contact FOREIGN KEY (contact_id) REFERENCES contacts(id) ON DELETE SET NULL,
    CONSTRAINT fk_sales_activities_lead FOREIGN KEY (lead_capture_id) REFERENCES lead_captures(id) ON DELETE SET NULL,
    CONSTRAINT fk_sales_activities_opportunity FOREIGN KEY (opportunity_id) REFERENCES opportunities(id) ON DELETE SET NULL,
    CONSTRAINT fk_sales_activities_account FOREIGN KEY (account_id) REFERENCES accounts(id) ON DELETE SET NULL,
    CONSTRAINT fk_sales_activities_creator FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS activity_reminders (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    activity_id BIGINT UNSIGNED NOT NULL,
    remind_at DATETIME NOT NULL,
    channel ENUM('in_app', 'email') NOT NULL DEFAULT 'in_app',
    status ENUM('pending', 'sent', 'cancelled') NOT NULL DEFAULT 'pending',
    sent_at DATETIME NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_activity_reminders_tenant_status (tenant_id, status, remind_at),
    CONSTRAINT fk_activity_reminders_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_activity_reminders_activity FOREIGN KEY (activity_id) REFERENCES sales_activities(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS activity_status_history (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    activity_id BIGINT UNSIGNED NOT NULL,
    old_status VARCHAR(40) NULL,
    new_status VARCHAR(40) NOT NULL,
    note VARCHAR(255) NULL,
    changed_by BIGINT UNSIGNED NULL,
    changed_at DATETIME NOT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_activity_status_history_tenant_activity (tenant_id, activity_id, changed_at),
    CONSTRAINT fk_activity_status_history_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_activity_status_history_activity FOREIGN KEY (activity_id) REFERENCES sales_activities(id) ON DELETE CASCADE,
    CONSTRAINT fk_activity_status_history_user FOREIGN KEY (changed_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO activity_types (tenant_id, code, name, icon, is_active, sort_order)
SELECT t.id, 'call', 'Llamada', 'bi-telephone', 1, 1 FROM tenants t WHERE t.slug = 'demo'
UNION ALL
SELECT t.id, 'meeting', 'Reunion', 'bi-calendar-event', 1, 2 FROM tenants t WHERE t.slug = 'demo'
UNION ALL
SELECT t.id, 'email', 'Email', 'bi-envelope', 1, 3 FROM tenants t WHERE t.slug = 'demo'
UNION ALL
SELECT t.id, 'task', 'Tarea', 'bi-check2-square', 1, 4 FROM tenants t WHERE t.slug = 'demo'
UNION ALL
SELECT t.id, 'follow_up', 'Seguimiento', 'bi-arrow-repeat', 1, 5 FROM tenants t WHERE t.slug = 'demo'
ON DUPLICATE KEY UPDATE name = VALUES(name), icon = VALUES(icon), is_active = VALUES(is_active), sort_order = VALUES(sort_order);
