CREATE TABLE lead_acquisition_costs (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    source VARCHAR(80) NOT NULL,
    channel VARCHAR(80) NULL,
    campaign VARCHAR(120) NULL,
    cost_center VARCHAR(80) NULL,
    period_start DATE NOT NULL,
    period_end DATE NOT NULL,
    amount_cents BIGINT NOT NULL,
    notes VARCHAR(255) NULL,
    metadata JSON NULL,
    created_by INT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL,
    CONSTRAINT fk_lead_acq_cost_created_by FOREIGN KEY (created_by) REFERENCES users (id) ON DELETE SET NULL,
    INDEX idx_lead_acq_cost_source_period (source, period_start, period_end),
    INDEX idx_lead_acq_cost_campaign (campaign)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
